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). If I run the code 2 I don't get the error and the program works fine. This always happens when I create a Table, then I create a query with parameters, then I create another table and then I run the query. Otherwise (code 2) if I create a table, then I create and run the query as a statement without parameters, then I create another table and then run the same insert query statement without parameter I don't get the error. Is it a bug or something related to SQLite? (Same situation, same queries and table I don't get any error using Firebird). Thanks,
Andrea

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;
 SQLite3Connection1.ExecuteDirect('create table T2 (ID integer not null,
COGNOME varchar(200))');
  SQLQuery1.Params.ParamByName('ID').AsInteger:= 2;
  SQLQuery1.Params.ParamByName('NOME').AsString:= 'Marco';
  SQLQuery1.ExecSQL;
  SQLTransaction1.CommitRetaining;

code 2:

  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 (1, ''Andrea'')';
  SQLQuery1.ExecSQL;
  SQLite3Connection1.ExecuteDirect('create table T2 (ID integer not
null, COGNOME varchar(200))');
  sqlquery1.SQL.Text:= 'insert into T1 (ID, NOME) values (2, ''Pippo'')';
  SQLQuery1.ExecSQL;
  SQLTransaction1.CommitRetaining;

Op dinsdag 02-12-2008 om 12:47 uur [tijdzone +0100], schreef Andrea Mauri:

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.

Thanks Joost.

Joost van der Sluis ha scritto:

Try to use the function sqlite3_enable_shared_cache with true as parameter in the start of your application.

The function declaration:

function sqlite3_enable_shared_cache(Enable: Boolean): LongInt; cdecl;
external 'sqlite3';

Luiz

En/na Luiz Americo Pereira Camara ha escrit:

I don't think it will help, sqlite will always return the error "database schema has changed" if you ALTER any table in the database. There's a bunch of enhanced "v2" functions (see
http://sqlite.org/c3ref/funclist.html) to avoid that class of problems, but their bindings, AFAIK, aren't available in fpc.

E.g., there's sqlite3_prepare_v2 and sqlite3_prepare16_v2

Quoting from http://sqlite.org/c3ref/prepare.html:

The sqlite3_prepare_v2() and sqlite3_prepare16_v2() interfaces are recommended for all new programs. The two older interfaces are retained for backwards compatibility, but their use is discouraged. In the "v2" interfaces, the prepared statement that is returned (the sqlite3_stmt object) contains a copy of the original SQL text. This causes the sqlite3_step() interface to behave a differently in two ways:

1. If the database schema changes, instead of returning SQLITE_SCHEMA as it always used to do, sqlite3_step() will automatically recompile the SQL statement and try to run it again. If the schema has changed in a way that makes the statement no longer valid, sqlite3_step() will still return SQLITE_SCHEMA. But unlike the legacy behavior, SQLITE_SCHEMA is now a fatal error. Calling sqlite3_prepare_v2() again will not make the error go away. Note: use sqlite3_errmsg() to find the text of the parsing error that results in an SQLITE_SCHEMA return.

2. When an error occurs, sqlite3_step() will return one of the detailed error codes or extended error codes. The legacy behavior was that sqlite3_step() would only return a generic SQLITE_ERROR result code and you would have to make a second call to sqlite3_reset() in order to find the underlying cause of the problem. With the "v2" prepare interfaces, the underlying reason for the error is returned immediately.

Bye

Op woensdag 03-12-2008 om 09:25 uur [tijdzone +0100], schreef Luca Olivetti:

Patches are welcome. And when you're at it, also take a look at http://bugs.freepascal.org/view.php?id=10821 ;)

Joost.

It worked for me. I had the same problem in my app. Did you tried?

Luiz

En/na Luiz Americo Pereira Camara ha escrit:

No, I didn't try, I just coped with the error (preparing the query again). Note also that I was not using sqldb but the raw sqlite functions.

Bye