[Lazarus] SQLite database schema has changed

[Joost van der Sluis]

Op dinsdag 02-12-2008 om 12:47 uur [tijdzone +0100], schreef Andrea
Mauri:
> Dear All,
> I have a question regarding SQLite.
> I got an error: 'database schema has changed' when I run the following 
> code (code 1)  (when I execute the second Insert Query).

> code1:
> 
>   if FileExists('e:\temp\prova.db') then
>     DeleteFile('e:\temp\prova.db');
>   SQLite3Connection1.DatabaseName:= 'e:\temp\prova.db';
>   SQLite3Connection1.ExecuteDirect('create table T1 (ID integer not 
> null, NOME varchar(200))');
>   sqlquery1.SQL.Text:= 'insert into T1 (ID, NOME) values (:ID, :NOME)';
>   SQLQuery1.Params.ParamByName('ID').AsInteger:= 1;
>   SQLQuery1.Params.ParamByName('NOME').AsString:= 'Andrea';
>   SQLQuery1.ExecSQL;

Here you implecitely prepare the query before it gets executed. This is
done so that if you run the query again, it's execution will be faster.
One of the advantages of using parameters.
 
>  SQLite3Connection1.ExecuteDirect('create table T2 (ID integer not null, 
> COGNOME varchar(200))');

But here you change the definition of the database. Thereafter the
preparation of the query is not valid anymore. Call sqlquery.unprepare
to solve the problem.
Apparently Firebird doesn't mind the change in the definition, because
it is on a different table. It looks like it that sqlite can not handle
that.

>   SQLQuery1.Params.ParamByName('ID').AsInteger:= 2;
>   SQLQuery1.Params.ParamByName('NOME').AsString:= 'Marco';
>   SQLQuery1.ExecSQL;
>   SQLTransaction1.CommitRetaining;

This could be fixed by automatically unpreparing all statements when a
ddl-statement (create, drop,...) is executed in sqlite. But I prefer not
to do that, so that you have to do that manually.

Joost.