Recent

Author Topic: [solved]multiple sqlite SELECT statements  (Read 1725 times)

oldfart

  • New Member
  • *
  • Posts: 15
[solved]multiple sqlite SELECT statements
« on: October 10, 2021, 12:33:25 pm »
Sorry guys - really messed up with this, will post later my code
Thanks for your patience
« Last Edit: October 10, 2021, 04:01:54 pm by oldfart »

oldfart

  • New Member
  • *
  • Posts: 15
Re: multiple sqlite SELECT statements
« Reply #1 on: October 10, 2021, 01:11:48 pm »
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][+][-]
  1. procedure TForm1.doCheckClick(Sender: TObject);
  2. var
  3.   i, count: Integer;
  4.   ndays, mysql, temp: string;
  5. begin
  6.   SQLite3Connection1.close;
  7.   SQLTransaction1.Active := false;
  8.    SqlQuery1.SQL.Clear;
  9.    StringGrid1.visible:= true;
  10.    SqlQuery1.SQL.Text := 'select DISTINCT number_of_days from certificates;';
  11.    SQLite3Connection1.Open;
  12.    SQLTransaction1.Active := true;
  13.    SqlQuery1.Open;
  14.    If SQLQuery1.Eof then showmessage('no check found');
  15.  
  16.    // put the results of the SELECT into a listbox, so I can check correct execution
  17.    ListBox1.Items.Clear;
  18.  
  19.   while not SqlQuery1.Eof do
  20.   begin
  21.     temp:= SqlQuery1.Fields[0].AsString;
  22.     // temporary variable, so I can set a break point to check
  23.  
  24.     if temp <> '' then ListBox1.Items.Add(temp);
  25.     SqlQuery1.Next;
  26.   end;
  27. // ListBox1 now contains:
  28. // 1
  29. // 8
  30. // 21
  31. // 31
  32.  
  33.   count:= 1;
  34.   for i:= 0 to ListBox1.Items.Count -1 do
  35.   begin
  36.    ndays:= ListBox1.Items[i];  // get number of days from listbox
  37.  
  38.     SqlQuery1.SQL.Clear;
  39. // using any these, does not affect the outcome of the query
  40. //   SqlQuery1.Close;
  41. //   SqlQuery1.Free;
  42. //   SQLite3Connection1.Close;
  43. //   SQLTransaction1.Active := false;
  44.    mysql:= 'select COUNT(*) from certificates where '+
  45.            'number_of_days = '+ ndays +
  46.            ' and used = "unused";';
  47.    //
  48.      SqlQuery1.SQL.Text := mysql;
  49. // tried this code instead of mysql = etc .. still same result
  50. //   SqlQuery1.SQL.Text := 'select COUNT(*) from certificates '+
  51. //                         'where number_of_days = :ndays' +
  52. //                         ' and used = "unused";';
  53. //    SqlQuery1.Params.ParamByName('ndays').AsString := ndays;
  54.  
  55.      SQLite3Connection1.Open;
  56.      SQLTransaction1.Active := true;
  57.      SqlQuery1.Open;
  58.  
  59.    temp:= SqlQuery1.Fields[0].AsString;
  60. // temp now contains the last result of the previous SELECT statement
  61. // which is wrong
  62.  
  63. // put query results ino a string grid
  64.    StringGrid1.Cells[0,count]:= ndays;
  65.    StringGrid1.Cells[1,count]:= temp;
  66.    count +=1;
  67.   end;
  68.  
  69.  
  70. end;
         
« Last Edit: October 10, 2021, 01:15:08 pm by oldfart »

korba812

  • Full Member
  • ***
  • Posts: 224
Re: multiple sqlite SELECT statements
« Reply #2 on: October 10, 2021, 02:35:01 pm »
Maybe you should close TSQLQuery at end of loop?
Code: Pascal  [Select][+][-]
  1.    temp:= SqlQuery1.Fields[0].AsString;
  2. // temp now contains the last result of the previous SELECT statement
  3. // which is wrong
  4.  
  5. // put query results ino a string grid
  6.    StringGrid1.Cells[0,count]:= ndays;
  7.    StringGrid1.Cells[1,count]:= temp;
  8.    count +=1;
  9.    SqlQuery1.Close;   // <----------
  10.   end;
  11.  
  12.  

oldfart

  • New Member
  • *
  • Posts: 15
Re: [solved]multiple sqlite SELECT statements
« Reply #3 on: October 10, 2021, 04:02:37 pm »
solved with
myquery:=TSQLQuery.create(self);
..
..
myquery.destroy

 

TinyPortal © 2005-2018