Recent

Author Topic: Restructuring a table, adding a field (TSqlite3Dataset)  (Read 9176 times)

Zomis

  • Jr. Member
  • **
  • Posts: 95
Restructuring a table, adding a field (TSqlite3Dataset)
« on: March 18, 2007, 01:14:21 pm »
I want to add a field to my TSqlite3Dataset table, and keeping all the records already in the table.

"levels" is the name of my TSqlite3Dataset.

Code: [Select]
 levels.FieldDefs.Add('scr_diffels', ftInteger, 0);
  levels.FieldDefs.Update;
  levels.RefetchData;// i've tried without this as well, no difference


After the above code, I go through all the records (using level.First and while not levels.EOF do...)
Code: [Select]
 levels.Edit;
  levels.FieldByName('scr_diffels').AsInteger:=64;
  levels.UpdateRecord;

But the field 'scr_diffels' does not exist.
I've also tried adding the field using levels.Fields.Add, but that didn't work because I couldn't set the "DataType" property of the field. And I wonder if it would work using that anyway...

So how to add a field to a table and keeping all the current data in the table?

jozagulikoza

  • New Member
  • *
  • Posts: 12
RE: Restructuring a table, adding a field (TSqlite3Dataset)
« Reply #1 on: March 18, 2007, 05:55:36 pm »
Dont add fields, tables or databases in your client program. its risky.
For that purpos you have DB tools.

Zomis

  • Jr. Member
  • **
  • Posts: 95
RE: Restructuring a table, adding a field (TSqlite3Dataset)
« Reply #2 on: March 18, 2007, 06:14:53 pm »
I don't see the riskiness in adding fields, tables, databases in my application, I've already created all those things and it works perfectly (now I just like to add a new field to the existing table). And also, Sqlite doesn't use a server.

So please, any other replies?

jozagulikoza

  • New Member
  • *
  • Posts: 12
RE: Restructuring a table, adding a field (TSqlite3Dataset)
« Reply #3 on: March 18, 2007, 07:28:47 pm »
Imagine you are working on stocking program and you must add one field to some table. If you do it with stocking program it must ask if field dont exists and creat it. Field dont exists just once. If your program is used 5 years ;-) day after day it is not good idea to do that control.
One level is database administration (it is good to use DB tool, eg. sqliteadmin), the next  level is data manipulation (adding, editing, deleting, sorting,... datas).

Zomis

  • Jr. Member
  • **
  • Posts: 95
Re: RE: Restructuring a table, adding a field (TSqlite3Datas
« Reply #4 on: March 18, 2007, 09:06:07 pm »
Quote from: "jozagulikoza"
Imagine you are working on stocking program and you must add one field to some table. If you do it with stocking program it must ask if field dont exists and creat it. Field dont exists just once. If your program is used 5 years ;-) day after day it is not good idea to do that control.
One level is database administration (it is good to use DB tool, eg. sqliteadmin), the next  level is data manipulation (adding, editing, deleting, sorting,... datas).
I just sent you a pm about that. I'd like a solution to my problem in this topic and not "you shouldn't do that - use a DB tool instead", please.

I've tried this code now
Code: [Select]
 levels.Close;
  levels.FieldDefs.Add('test', ftInteger, 0, False);
  levels.FieldDefs[levels.FieldDefs.Count-1].CreateField(levels);
  levels.FieldDefs.Update;
  levels.Open;

And that gave me an error after opening the table, it gives me a SIGSEGV
Address: $005D175C
Procedure: fpc_pchar_to_ansistr

And then it stops on the "S := F.DisplayText;" line in this code in 'DBGrids'
Code: [Select]
   case ColumnEditorStyle(aCol, F) of
      cbsCheckBoxColumn:
        DrawCheckBoxBitmaps(aCol, aRect, F);
      else begin
        if F<>nil then begin
          if F.dataType <> ftBlob then
            S := F.DisplayText
          else
            S := '(blob)';
        end else
          S := '';
        DrawCellText(aCol,aRow,aRect,aState,S);
      end;
    end;

So I'm still not sure about how to add a new field to an existing table.

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
RE: Re: RE: Restructuring a table, adding a field (TSqlite3D
« Reply #5 on: March 18, 2007, 11:56:41 pm »
To add a field you must use the ALTER TABLE command. Use it in ExecSql.

To see how to use see http://www.sqlite.org/lang_altertable.html

Zomis

  • Jr. Member
  • **
  • Posts: 95
RE: Re: RE: Restructuring a table, adding a field (TSqlite3D
« Reply #6 on: March 19, 2007, 12:42:46 pm »
Thank you very much, LuizAmérico, that solved my problem!

jozagulikoza

  • New Member
  • *
  • Posts: 12
RE: Re: RE: Restructuring a table, adding a field (TSqlite3D
« Reply #7 on: March 19, 2007, 01:25:08 pm »
My answers were in good will. if i was not clear i am sorry.
Sqliite has SQL statements. You can admin your  DB with SQL statements. For "running" statements you need a program (DB admin tool).
For sqlite we have sqladmin (or some other) http://sqliteadmin.orbmu2k.de/.

Once more i am sorry if i was misunderstood.

Zomis

  • Jr. Member
  • **
  • Posts: 95
RE: Re: RE: Restructuring a table, adding a field (TSqlite3D
« Reply #8 on: March 19, 2007, 01:26:38 pm »
I don't see why I should use a DB admin tool to run my SQL statements? It works perfectly to run the SQL statements in my application...?

jozagulikoza

  • New Member
  • *
  • Posts: 12
RE: Re: RE: Restructuring a table, adding a field (TSqlite3D
« Reply #9 on: March 19, 2007, 01:53:55 pm »
> I don't see why I should use a DB admin tool to run my SQL statements? It works perfectly to run the SQL statements in my application...?

If you are good with SQL you dont need use sqladmin.
How you execulete ALTER TABLE? How you create your tables, keys, triggers, fields?
You have a executer for doing that --> it is DB tool

Zomis

  • Jr. Member
  • **
  • Posts: 95
RE: Re: RE: Restructuring a table, adding a field (TSqlite3D
« Reply #10 on: March 19, 2007, 01:55:35 pm »
I only say: Trial and error. I didn't need many tries to make a perfect SQL statement which does my job.
I've been using MySQL on my homepage for years, so I know most things in the SQL language.

jozagulikoza

  • New Member
  • *
  • Posts: 12
RE: Re: RE: Restructuring a table, adding a field (TSqlite3D
« Reply #11 on: March 19, 2007, 02:08:33 pm »
> I've been using MySQL on my homepage for years, so I know most things in the SQL language.
Sqlite is like MySQL execpt it isnt server. Admin your Sqlite the seme  way you MySQL do.

 

TinyPortal © 2005-2018