Restructuring a table, adding a field (TSqlite3Dataset)
lazarus / 16-Databases

----------------------------------------------------------------------
Zomis · 2007-03-18 12:14

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 · 2007-03-18 16:55

Dont add fields, tables or databases in your client program. its
risky.
For that purpos you have DB tools.

----------------------------------------------------------------------
Zomis · 2007-03-18 17:14

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 · 2007-03-18 18:28

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 · 2007-03-18 20:06

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 · 2007-03-18 22:56

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 · 2007-03-19 11:42

Thank you very much, LuizAmérico, that solved my problem!

----------------------------------------------------------------------
jozagulikoza · 2007-03-19 12:25

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 · 2007-03-19 12:26

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 · 2007-03-19 12:53

> 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 · 2007-03-19 12:55

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 · 2007-03-19 13:08

> 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.