error in SQlite raw acces to a table
lazarus / 16-Databases

----------------------------------------------------------------------
ba59 · 2021-02-22 17:06

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 · 2021-02-23 00:44

Quote from: ba59 on February 22, 2021, 06:06:24 pmquery := '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 · 2021-02-23 08:04

Quote from: ba59 on February 22, 2021, 06:06:24 pmI 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var 
i,k,ColCount,lon : integer;  query,tex : string; //UTF8String;  ok :
boolean;  CodeResult : integer;  stmt : pointer;  pz   : PChar;  qcm :
Tqcm;beginlis.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;
Second, take a look at the declaration of sqlite3_prepare_v2:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i {$IFDEF
S}function{$ELSE}var{$ENDIF}sqlite3_prepare_v2{$IFDEF D}:
function{$ENDIF}(  db: psqlite3;             (* Database handle *) 
zSql: pansichar;          (* SQL statement, UTF-8 encoded *)  nByte:
cint;              (* Maximum length of zSql in bytes. *)  ppStmt:
ppsqlite3_stmt;   (* OUT: Statement handle *)  pzTail: ppansichar    
  (* OUT: Pointer to unused portion of zSql *)): 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  //
other vars  stmt: psqlite3_stmt;  pz: PChar;begin  // other code 
CodeResult := sqlite3_prepare_v2(F_DB,PChar(query),lon,@stmt,@pz);  //
other codeend;
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 · 2021-02-23 09:50

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 · 2021-02-23 12:23

Quote from: ba59 on February 23, 2021, 10:50:15 amAbout
"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.