Forum > Databases

[Solved] TSQLQuery returns no records on SQLite3 Database

(1/1)

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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---unit UnitDataBase; {$mode ObjFPC}{$H+} interface uses  Unit1, Classes, SysUtils, Dialogs, SQLDB, SQLite3Conn;Var  txtFile:String;  Function sqlDBError( msg: String): String;  Function openDB(dbName: string): boolean;  Function TblMakeUnits( ): Boolean;  Function TblMakeInverters(): Boolean;  Function TblMakeDays(): Boolean;  Function TblMakeTimes(): Boolean;  Function tblDataAddUnits(Const txtFile:String; Out icount:int32) : boolean;  Function tblDataAddInverters(Const txtFile:String; Out icount:int32) : boolean;  Function tblDataAddIDays(Const txtFile:String; Out icount:int32) : boolean;  function DoNullStr (InStr:String ): String;  Function GetColAndUnit(strCol:String; strUnit:String): Boolean;  Procedure closeDB; implementationVar  sqlite3: TSQLite3Connection;  dbTrans: TSQLTransaction;  dbQuery: TSQLQuery;  slNames: TStringList;  quit: boolean = false;Function sqlDBError( msg: String): String;begin  //Error message reformatting  result:= 'ERROR: ' + StringReplace(msg, 'TSQLite3Connection : ','',[]);end;Function openDB(dbName: string): boolean;begin// create components  sqlite3 := TSQLite3Connection.Create(nil);  dbTrans := TSQLTransaction.Create(nil);  dbQuery := TSQLQuery.Create(nil);  slNames := TStringList.Create;// setup components  sqlite3.Transaction   := dbTrans;  dbTrans.Database      := sqlite3;  dbQuery.Transaction   := dbTrans;  dbQuery.Database      := sqlite3;  slNames.CaseSensitive := false;// setup db  sqlite3.DatabaseName := dbName;  sqlite3.HostName     := 'localhost';  sqlite3.CharSet      := 'UTF8';// open db//WriteLn(dbName);if  FileExists(dbName) then    try      sqlite3.Open;      result := sqlite3.Connected;    except      on E: Exception do      begin      sqlite3.Close;      writeln(sqlDBError(E.Message));      end;    endelse    begin    result := false;    writeln('Database file "',dbName,'" is not found.');{EndIf}end;end;                   and Function GetColAndUnit(strCol:String; strUnit:String): Boolean;

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Function GetColAndUnit(strCol:String; strUnit:String): Boolean;Var  strTmp:String;BeginTry //DataBase is already opened in function openDb//dbTrans.Active:=True;dbQuery.SQL.text:='Select InverterID FROM INVERTERS WHERE SystemName ="PrimrosePanels";';dbQuery.Open;if dbQuery.EOF THEN  Begin  ShowMessage('No Result');  WriteLn('No Result');  endElse  Begin    strTmp:=dbQuery.FieldByName('InverterID').AsString;    ShowMessage(strTmp);    writeLn(' XXXXXXXX = ' + dbQuery.FieldByName('InverterID').AsString);{EndIf}End;Except  On E: Exception  Do  Begin    showMessage(E.Message);  end;{EndTry}End;End; 

rvk:

--- Quote from: Wilko500 on February 13, 2024, 06:08:16 pm ---
--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---dbQuery.SQL.text:='Select InverterID FROM INVERTERS WHERE SystemName ="PrimrosePanels";'; 
--- End quote ---
Probably a problem with those quotes.
Try to use single quotes or better yet... Use parameters.

(So :sys in the sql and

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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.

Navigation

[0] Message Index

Go to full version