error in SQlite raw acces to a table

[ba59]

Hi all
Trying to develop an application using SQlite tables to store forms structure and contents, I use raw access as I did in Delphi with the last Lazarus IDE 2.0.10 on windows 8 :

"var
  i,k,ColCount,lon : integer;
  query,tex : string; //UTF8String;
  ok : boolean;
  CodeResult : integer;
  stmt : pointer;
  pz   : PChar;
  qcm : Tqcm;
begin
lis.Clear;
Ferr_bd := false;
Ferreur := '';
query := 'SELECT * FROM "' + table_bd + '";' + #0;

stmt := nil; pz := nil;
lon := Length(query) + 1;
CodeResult := sqlite3_prepare_v2(F_DB,PChar(query),lon,stmt,pz);
ok := CodeResult = SQLITE_OK;
"

and get always CodeResult == 1 (generic error).

I tried with #0 (or without it) and also try UTF8String instead of string, and always get the same error.

The first SQlite operations :
sqlite3_open_v2(),
sqlite3_prepare_v2() with "query := 'PRAGMA foreign_keys = ON;';" or
sqlite3_libversion;

work well.

I don't how to find why it's wrong !

Thanks a lot for your help.
Bernard 
 

[PierceNg]

query := 'SELECT * FROM "' + table_bd + '";' + #0;

If you try hardcoding table name in the query string - 'select * from my_table' and 'select * from "my_table"' - do they work?

You should provide a complete program that can be compiled and run. That helps others to help you.

[PascalDragon]

I don't how to find why it's wrong !

First, use [ code ]-Tags, it makes your code better readable on this forum:

Code: Pascal  [Select][+][-]

1. var
2.   i,k,ColCount,lon : integer;
3.   query,tex : string; //UTF8String;
4.   ok : boolean;
5.   CodeResult : integer;
6.   stmt : pointer;
7.   pz   : PChar;
8.   qcm : Tqcm;
9. begin
10. lis.Clear;
11. Ferr_bd := false;
12. Ferreur := '';
13. query := 'SELECT * FROM "' + table_bd + '";' + #0;
14.  
15. stmt := nil; pz := nil;
16. lon := Length(query) + 1;
17. CodeResult := sqlite3_prepare_v2(F_DB,PChar(query),lon,stmt,pz);
18. ok := CodeResult = SQLITE_OK;

Second, take a look at the declaration of sqlite3_prepare_v2:

Code: Pascal  [Select][+][-]

1. {$IFDEF S}function{$ELSE}var{$ENDIF}sqlite3_prepare_v2{$IFDEF D}: function{$ENDIF}(
2.   db: psqlite3;             (* Database handle *)
3.   zSql: pansichar;          (* SQL statement, UTF-8 encoded *)
4.   nByte: cint;              (* Maximum length of zSql in bytes. *)
5.   ppStmt: ppsqlite3_stmt;   (* OUT: Statement handle *)
6.   pzTail: ppansichar        (* OUT: Pointer to unused portion of zSql *)
7. ): cint; cdecl;{$IFDEF S}external Sqlite3Lib;{$ENDIF}

The ppStmt is a pointer to a psqlite3_stmt just as pzTail is a pointer to a pansichar. Thus you need to adjust your code like this:

Code: Pascal  [Select][+][-]

1. var
2.   // other vars
3.   stmt: psqlite3_stmt;
4.   pz: PChar;
5. begin
6.   // other code
7.   CodeResult := sqlite3_prepare_v2(F_DB,PChar(query),lon,@stmt,@pz);
8.   // other code
9. end;

Additionally, third, you don't need to add a #0 to your query as a String (in case of $H+) always has a NUL character at the end to allow easy casting to PChar.

[ba59]

Hi both
And thanks a lot for your help.
About "psqlite3_stmt" declaration I use an interface file which does not have this type. Could you please tell me where can I find such ?

After looking again the lines of software and the files in folder, I notice another .db file which was created by the current test (the open_v2 function created an empty file).
This empty file has almost the same name as the test file I created manually with the software DBBrowser for Sqlite !
That's a silly error to not mimic !
Best regards.
Bernard 

[PascalDragon]

About "psqlite3_stmt" declaration I use an interface file which does not have this type. Could you please tell me where can I find such ?

The SQLite API is provided as part of FPC's distribution in units sqlite3dyn or sqlite3 (if you want to use static linking). In both cases they're in packages/sqlite/src.