[Solved] TSQLQuery returns no records on SQLite3 Database

[Wilko500]

This is my first foray into database access from Lazarus.  My code is based on an example I found while searching for How To's for SQLite3 access.  The task in hand is to migrate a MS Access 2003 database into SQLite3, export comma separated text files and recreate the database in Lazarus.  My Database is created in code and verified as correct using DBBrowser for SQLite.  I conclude that the Function OpenDb() is working as expected since the database is created and verified.  The last table requires lookup for getting Foreign Keys in order to import data.

I use one function to Open the database and another to try and retrieve data and this is where my problem lies.  No errors are reported but dbQuery.EOF returns no rows.  I suspect that my code is somehow in error ???

Other Stuff: Lazarus/FPC = 3.0/3.3.1 running on MacOS  Monterey 12.7.2
My query does return the correct value when run in the database, text got from ShowMessage(dbQuery.SQL.text); then copied and executed in Database browser.

UnitDatabase with Function openDB(dbName: string): boolean; I have not included all the function that create tables and import data, they all work fine.  I would be grateful for assistance.

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

1. unit UnitDataBase;
2.  
3. {$mode ObjFPC}{$H+}
4.  
5. interface
6.  
7. uses
8.   Unit1, Classes, SysUtils, Dialogs, SQLDB, SQLite3Conn;
9. Var
10.   txtFile:String;
11.   Function sqlDBError( msg: String): String;
12.   Function openDB(dbName: string): boolean;
13.   Function TblMakeUnits( ): Boolean;
14.   Function TblMakeInverters(): Boolean;
15.   Function TblMakeDays(): Boolean;
16.   Function TblMakeTimes(): Boolean;
17.   Function tblDataAddUnits(Const txtFile:String; Out icount:int32) : boolean;
18.   Function tblDataAddInverters(Const txtFile:String; Out icount:int32) : boolean;
19.   Function tblDataAddIDays(Const txtFile:String; Out icount:int32) : boolean;
20.   function DoNullStr (InStr:String ): String;
21.   Function GetColAndUnit(strCol:String; strUnit:String): Boolean;
22.   Procedure closeDB;
23.  
24. implementation
25. Var
26.   sqlite3: TSQLite3Connection;
27.   dbTrans: TSQLTransaction;
28.   dbQuery: TSQLQuery;
29.   slNames: TStringList;
30.   quit: boolean = false;
31. Function sqlDBError( msg: String): String;
32. begin
33.   //Error message reformatting
34.   result:= 'ERROR: ' + StringReplace(msg, 'TSQLite3Connection : ','',[]);
35. end;
36. Function openDB(dbName: string): boolean;
37. begin
38. // create components
39.   sqlite3 := TSQLite3Connection.Create(nil);
40.   dbTrans := TSQLTransaction.Create(nil);
41.   dbQuery := TSQLQuery.Create(nil);
42.   slNames := TStringList.Create;
43. // setup components
44.   sqlite3.Transaction   := dbTrans;
45.   dbTrans.Database      := sqlite3;
46.   dbQuery.Transaction   := dbTrans;
47.   dbQuery.Database      := sqlite3;
48.   slNames.CaseSensitive := false;
49. // setup db
50.   sqlite3.DatabaseName := dbName;
51.   sqlite3.HostName     := 'localhost';
52.   sqlite3.CharSet      := 'UTF8';
53. // open db
54. //WriteLn(dbName);
55. if  FileExists(dbName) then
56.     try
57.       sqlite3.Open;
58.       result := sqlite3.Connected;
59.     except
60.       on E: Exception do
61.       begin
62.       sqlite3.Close;
63.       writeln(sqlDBError(E.Message));
64.       end;
65.     end
66. else
67.     begin
68.     result := false;
69.     writeln('Database file "',dbName,'" is not found.');
70. {EndIf}end;
71. end;                  
72.  

and Function GetColAndUnit(strCol:String; strUnit:String): Boolean;

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

1. Function GetColAndUnit(strCol:String; strUnit:String): Boolean;
2. Var
3.   strTmp:String;
4. Begin
5. Try
6.  
7. //DataBase is already opened in function openDb
8. //dbTrans.Active:=True;
9. dbQuery.SQL.text:='Select InverterID FROM INVERTERS WHERE SystemName ="PrimrosePanels";';
10. dbQuery.Open;
11. if dbQuery.EOF THEN
12.   Begin
13.   ShowMessage('No Result');
14.   WriteLn('No Result');
15.   end
16. Else
17.   Begin
18.     strTmp:=dbQuery.FieldByName('InverterID').AsString;
19.     ShowMessage(strTmp);
20.     writeLn(' XXXXXXXX = ' + dbQuery.FieldByName('InverterID').AsString);
21. {EndIf}End;
22. Except
23.   On E: Exception  Do
24.   Begin
25.     showMessage(E.Message);
26.   end;
27. {EndTry}End;
28. End;
29.  

[rvk]

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

1. dbQuery.SQL.text:='Select InverterID FROM INVERTERS WHERE SystemName ="PrimrosePanels";';
2.  

Probably a problem with those quotes.
Try to use single quotes or better yet... Use parameters.

(So :sys in the sql and

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

1. dbQuery.params.parambyname('sys').asString := 'PrimrosePanels';

[Wilko500]

OK.  So I've got it.  I'm embarrassed to say that the problem was caused by a typo elsewhere in my code and some inconsistency with my testing.   I called my function GetColAndUnit( ) partway through the main program for testing purposes before all the tables had been created, essentially a commit was commented out thus the Inverter table was created and populated BUT NOT COMMITTED. Thus I copied the query text from the ShowMessage(dbQuery.SQL.text) and pasted it into the DataBrowser AFTER the program had finished.  My dbClose() procedure actually did the commit but during early testing each table created and populated had its own commit (except the one with the typo ).

Nevertheless it was your suggestions apparently not working that got me to look elsewhere where I had incorrectly thought my code was correct.

The good news is that I have confirmed that the several different ways to code the query all work.  I am aware that parameters are the "best' solution for secure code but in this case my conversion program will be a one time use and will not face the internet.

Thank you for your assistance.