SQLitePass - trouble adding column to table
lazarus / 16-Databases

----------------------------------------------------------------------
victorhelsing · 2010-01-03 22:49

I have just converted to using the SQLitePass package, which is very
nice, but I am having trouble adding a column to a table, using the
syntax:

SQLStr := Format ('BEGIN TRANSACTION; ALTER TABLE %s ADD COLUMN %s %s;
COMMIT;',
                         [LocalDef.Name, RefDef.Name, FieldTypeStr]);
               if (NOT (FieldCreated)) then begin
               TempQuery.SQL.Text := SQLStr;
               TempQuery.Open;
               TempQuery.ApplyChanges;
               TempQuery.Close;                                    

This produces a line such as "... ALTER TABLE PROGRESSION ADD COLUMN
Value; ..." which appears to execute without a problem, but when
checking the database using another program (I am using the SQLite
Manager addon for FireFox/Komodo Edit on Linux) I cannot find the new
column in the database.

Not sure what I am doing wrong; is there something I need to do to
write out the changes to the database file?

Thanks for the help.

----------------------------------------------------------------------
cdbc · 2010-01-04 10:05

Hi

Have you tried using 'TempQuery.ExecSql' instead of 'TempQuery.open'
?!?

Regards Benny

----------------------------------------------------------------------
Luc · 2010-01-07 18:28

Hi Victor,

Here is an example of creation and alteration of a table with the
sqlitepass lib :

Code: [Select]Procedure Something;
Const
CreateTableSQL =  'CREATE TABLE "NewTable" ([IntegerField1] INTEGER 
NOT NULL, [IntegerField2] INTEGER  NOT NULL,'
                + '[TextField3] TEXT  NULL, [FloatField1] FLOAT 
NULL);';
AlterTableSQL =   'ALTER TABLE "NewTable" ADD COLUMN [IntegerField5]
INTEGER NULL;';
begin
With Db.Engine do
     begin
     Transaction.Start;
     ExecSQL(CreateTableSQL);
     ExecSQL(AlterTableSQL);
     Transaction.Commit;
     end;
end;

Where Db is an active TSqlitePassDatabase.
Hope it will help you
Regards.

Luc.