[Lazarus] SQLite database schema has changed

[Andrea Mauri]

Thanks Joost.

Joost van der Sluis ha scritto:
> 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.
>
> _______________________________________________
> Lazarus mailing list
> Lazarus at lazarus.freepascal.org
> http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
>
>   

-- 
Dr. Andrea Mauri, PhD
Milano Chemometrics and QSAR Research Group
Department of Environmental Sciences
University of Milano-Bicocca 
P.zza della Scienza, 1
20126 Milano - Italy

Tel: ++39 02 64482801
mailto:andrea.mauri.75 at gmail.com
http://michem.disat.unimib.it/chm/