[solved]multiple sqlite SELECT statements
lazarus / 16-Databases

----------------------------------------------------------------------
oldfart · 2021-10-10 10:33

Sorry guys - really messed up with this, will post later my code
Thanks for your patience

----------------------------------------------------------------------
oldfart · 2021-10-10 11:11

The results of the first query are carried on to the results of the
second query and persist throughout the results. (If that makes
sense).
I get all the unique days from the database in the first query.
Then the second query does a SELECT based on those days; the problem
is, I only get the same results - which is wrong.
You might need the sqlite3.dll from
_https://www.sqlite.org/2021/sqlite-dll-win64-x64-3360000.zip

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 procedure
TForm1.doCheckClick(Sender: TObject);var  i, count: Integer;  ndays,
mysql, temp: string;begin  SQLite3Connection1.close; 
SQLTransaction1.Active := false;   SqlQuery1.SQL.Clear; 
 StringGrid1.visible:= true;   SqlQuery1.SQL.Text := 'select DISTINCT
number_of_days from certificates;';   SQLite3Connection1.Open; 
 SQLTransaction1.Active := true;   SqlQuery1.Open;   If SQLQuery1.Eof
then showmessage('no check found');    // put the results of the
SELECT into a listbox, so I can check correct execution 
 ListBox1.Items.Clear;   while not SqlQuery1.Eof do  begin    temp:=
SqlQuery1.Fields[0].AsString;    // temporary variable, so I can set a
break point to check     if temp <> '' then ListBox1.Items.Add(temp); 
  SqlQuery1.Next;  end;// ListBox1 now contains:// 1// 8// 21// 31  
count:= 1;  for i:= 0 to ListBox1.Items.Count -1 do  begin   ndays:=
ListBox1.Items[i];  // get number of days from listbox    
SqlQuery1.SQL.Clear;// using any these, does not affect the outcome of
the query//   SqlQuery1.Close;//   SqlQuery1.Free;//  
SQLite3Connection1.Close;//   SQLTransaction1.Active := false; 
 mysql:= 'select COUNT(*) from certificates where '+         
 'number_of_days = '+ ndays +           ' and used = "unused";';   // 
   SqlQuery1.SQL.Text := mysql;// tried this code instead of mysql =
etc .. still same result//   SqlQuery1.SQL.Text := 'select COUNT(*)
from certificates '+//                         'where number_of_days =
:ndays' +//                         ' and used = "unused";';//  
 SqlQuery1.Params.ParamByName('ndays').AsString := ndays;    
 SQLite3Connection1.Open;     SQLTransaction1.Active := true;   
 SqlQuery1.Open;    temp:= SqlQuery1.Fields[0].AsString;// temp now
contains the last result of the previous SELECT statement// which is
wrong // put query results ino a string grid 
 StringGrid1.Cells[0,count]:= ndays;   StringGrid1.Cells[1,count]:=
temp;   count +=1;  end;  end;

----------------------------------------------------------------------
korba812 · 2021-10-10 12:35

Maybe you should close TSQLQuery at end of loop?
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    temp:=
SqlQuery1.Fields[0].AsString;// temp now contains the last result of
the previous SELECT statement// which is wrong // put query results
ino a string grid   StringGrid1.Cells[0,count]:= ndays; 
 StringGrid1.Cells[1,count]:= temp;   count +=1;   SqlQuery1.Close;  
// <----------  end;

----------------------------------------------------------------------
oldfart · 2021-10-10 14:02

solved with
myquery:=TSQLQuery.create(self);
..
..
myquery.destroy