Recent

Author Topic: error in SQlite raw acces to a table  (Read 245 times)

ba59

  • Newbie
  • Posts: 2
error in SQlite raw acces to a table
« on: February 22, 2021, 06:06:24 pm »
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

  • Jr. Member
  • **
  • Posts: 70
Re: error in SQlite raw acces to a table
« Reply #1 on: February 23, 2021, 01:44:49 am »
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

  • Hero Member
  • *****
  • Posts: 2747
  • Compiler Developer
Re: error in SQlite raw acces to a table
« Reply #2 on: February 23, 2021, 09:04:42 am »
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

  • Newbie
  • Posts: 2
Re: error in SQlite raw acces to a table
« Reply #3 on: February 23, 2021, 10:50:15 am »
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

  • Hero Member
  • *****
  • Posts: 2747
  • Compiler Developer
Re: error in SQlite raw acces to a table
« Reply #4 on: February 23, 2021, 01:23:36 pm »
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.

 

TinyPortal © 2005-2018