Page 1 of 1

sqlite_prepare_v2

Posted: 09.01.2014, 07:37
by rayanAyar
Is there any reason to use SQLite API function "sqlite_prepare" instead of "sqlite_prepare_v2" ?
Both functions are listed in code:
http://sourceforge.net/p/zeoslib/code-0 ... r.pas#l336
But used only first:
http://sourceforge.net/p/zeoslib/code-0 ... r.pas#l984

According to the SQLite documentation:
sqlite3_prepare_v2() and sqlite3_prepare16_v2() interfaces are recommended for all new programs
Main advantage of "_v2" is that "sqlite3_prepare_v2" will try to re-compile SQL statement if schema changes.

I migrated to Zeos 7.1.2 from 7.0.3. And my software begin to crash with error "database schema has changed".
SQLite FAQ says:
The easiest way to deal with SQLITE_SCHEMA errors is to always use sqlite3_prepare_v2() instead of sqlite3_prepare()
So I changed both calls of sqlite3_prepare to sqlite3_prepare_v2:

Code: Select all

{ Prepared statmenet api }
function TZSQLiteBaseDriver.Prepare(db: Psqlite; const zSql: PAnsiChar; nBytes: Integer;
  out ppStmt: Psqlite3_stmt; pzTail: PPAnsichar): Integer;
begin
  Result := SQLite_API.sqlite_prepare_v2(db, zSql, nBytes, ppStmt, pzTail);
end;

function TZSQLiteBaseDriver.Prepare_v2(db: Psqlite; const zSql: PAnsiChar; nBytes: Integer;
  out ppStmt: Psqlite3_stmt; pzTail: PPAnsichar): Integer;
begin
  Result := SQLite_API.sqlite_prepare_v2(db, zSql, nBytes, ppStmt, pzTail);
end;
And it works well for me.

Re: sqlite_prepare_v2

Posted: 09.01.2014, 21:18
by EgonHugeist
Thank you for your details.

Just commited your changes to \testing-7.2 (SVN)

Patch done R3060

What about Open_V2? Do you see some advantages if we would support the different modes?

Re: sqlite_prepare_v2

Posted: 10.01.2014, 03:14
by rayanAyar
EgonHugeist wrote: Just commited your changes to \testing-7.2 (SVN)
Patch done R3060
If patching "procedure TZSQLiteCAPIPreparedStatement.Prepare" then probably need to patch here:
http://sourceforge.net/p/zeoslib/code-0 ... r.pas#l990

Currently there is a typo:
function TZSQLiteBaseDriver.Prepare_v2(db: Psqlite; const zSql: PAnsiChar; nBytes: Integer;
out ppStmt: Psqlite3_stmt; pzTail: PPAnsichar): Integer;
begin
Result := SQLite_API.sqlite_prepare(db, zSql, nBytes, ppStmt, pzTail);
end;
EgonHugeist wrote: What about Open_V2? Do you see some advantages if we would support the different modes?
Didn't try Open_V2 because there is no need for me.

Re: sqlite_prepare_v2

Posted: 10.01.2014, 06:30
by EgonHugeist
If patching "procedure TZSQLiteCAPIPreparedStatement.Prepare" then probably need to patch here:
http://sourceforge.net/p/zeoslib/code-0 ... r.pas#l990
Of course... But all development starts in the \testing-X branches. So the patch goes to current \testing-7.2 first. Than (if tested) the changes will be merged to \trunk after a periode of a week or two. If such a patch is signed with a minior version like "*7.1*" the patch goes to the current stable release too.
Just be patiant. Two weeks later you'll have the patch in \trunk too.

Re: sqlite_prepare_v2

Posted: 10.01.2014, 07:31
by rayanAyar
I meant that patching "procedure TZSQLiteCAPIPreparedStatement.Prepare" (patch R3060) does nothing.
Because there is a typo in "TZSQLiteBaseDriver.Prepare_v2":
function TZSQLiteBaseDriver.Prepare(db: Psqlite; const zSql: PAnsiChar; nBytes: Integer;
out ppStmt: Psqlite3_stmt; pzTail: PPAnsichar): Integer;
begin
Result := SQLite_API.sqlite_prepare(db, zSql, nBytes, ppStmt, pzTail);
end;

function TZSQLiteBaseDriver.Prepare_v2(db: Psqlite; const zSql: PAnsiChar; nBytes: Integer;
out ppStmt: Psqlite3_stmt; pzTail: PPAnsichar): Integer;
begin
Result := SQLite_API.sqlite_prepare(db, zSql, nBytes, ppStmt, pzTail); // prepare instead of prepare_v2
end;

function TZSQLiteBaseDriver.Prepare16(db: Psqlite; const zSql: PWideChar; nBytes: Integer;
out ppStmt: Psqlite3_stmt; pzTail: ZPPWideChar): Integer;
begin
Result := SQLite_API.sqlite_prepare16(db, zSql, nBytes, ppStmt, pzTail);
end;

function TZSQLiteBaseDriver.Prepare16_v2(db: Psqlite; const zSql: PWideChar; nBytes: Integer;
out ppStmt: Psqlite3_stmt; pzTail: ZPPWideChar): Integer;
begin
Result := SQLite_API.sqlite_prepare16_v2(db, zSql, nBytes, ppStmt, pzTail);
end;
So changing FPlainDriver.Prepare to FPlainDriver.Prepare_v2 is not enough.

Re: sqlite_prepare_v2

Posted: 10.01.2014, 19:11
by EgonHugeist
So changing FPlainDriver.Prepare to FPlainDriver.Prepare_v2 is not enough.
Indeed you're right again. Man reading post more carefully should help... :o

Patch done R3062 \testing-7.2 (SVN)

Confirmed?

Re: sqlite_prepare_v2

Posted: 11.01.2014, 05:36
by rayanAyar
Yes, that works for me (tested R3060 &R3062 on Zeos 7.1.2).