Recent

Author Topic: Looking for generic SQLite3 SELECT example, multiple records  (Read 2744 times)

jbmckim

  • Jr. Member
  • **
  • Posts: 84
Looking for generic SQLite3 SELECT example, multiple records
« on: March 01, 2018, 08:38:18 pm »
I can't find a LP example approaching what I'm looking for so I thought I'd ask the group.

I'm looking for ideas that would support a generic SQLITE approach to a SELECT.

For purpose of illustration, I do something like this in a VB.Net app.  This is the read loop that constructs the output after the SELECT has fetched all the qualifying records:

Code: Pascal  [Select]
  1.  While SQLreader.Read()
  2.                
  3.     For j = 0 To (iColCount - 1)
  4.  
  5.         strRtnBuf &= String.Format("{0};", SQLreader(j))
  6.                  
  7.     Next
  8.  
  9.     colLocal.Add(strRtnBuf)
  10.     strRtnBuf = ""
  11.              
  12.  End While

In this case, The SQLreader manages the row cursor.  The data for each column within a given row is appended to strRtnBuf with a delimiter. Each returned data row is added to a collection.



 

valdir.marcos

  • Hero Member
  • *****
  • Posts: 544
Re: Looking for generic SQLite3 SELECT example, multiple records
« Reply #1 on: March 01, 2018, 10:26:52 pm »
Maybe something like:

http://wiki.lazarus.freepascal.org/SqlDBHowto
http://wiki.freepascal.org/SQLdb_Tutorial3
http://wiki.freepascal.org/Working_With_TSQLQuery

Code: Pascal  [Select]
  1. Program ShowData;
  2.  {$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
  3. uses
  4.   DB, Sysutils, sqldb, sqlite3conn;
  5.  
  6. var
  7.   AConnection  : TSQLConnection;
  8.   ATransaction : TSQLTransaction;
  9.   Query        : TSQLQuery;
  10. begin
  11.   AConnection := TSQLite3Connection.Create(nil);
  12.   ATransaction := TSQLTransaction.Create(AConnection);
  13.   AConnection.Transaction := ATransaction;
  14.   AConnection.DatabaseName := 'test_dbase';
  15.   Query := TSQLQuery.Create(nil);
  16.   Query.SQL.Text := 'select * from tblNames';
  17.   Query.Database := AConnection;
  18.   Query.Open;
  19.   while not Query.Eof do
  20.   begin
  21.     Writeln('ID: ', Query.FieldByName('ID').AsInteger, 'Name: ' +
  22.                                   Query.FieldByName('Name').AsString);
  23.     Query.Next;
  24.   end;
  25.   Query.Close;
  26.   AConnection.Close;
  27.   Query.Free;
  28.   ATransaction.Free;
  29.   AConnection.Free;
  30. end.


http://wiki.freepascal.org/SQLdb_Tutorial3
Code: Pascal  [Select]
  1. [/
  2. // Load from DB
  3.     try
  4.       if not FConn.Connected then
  5.         FConn.Open;
  6.       if not FConn.Connected then
  7.       begin
  8.         ShowMessage('Error connecting to the database. Aborting data loading.');
  9.         exit;
  10.       end;
  11.  
  12.       // Lowest salary
  13.       // Note: we would like to only retrieve 1 row, but unfortunately the SQL
  14.       // used differs for various dbs. As we'll deal with db dependent SQL later
  15.       // in the tutorial, we leave this for now.
  16.       // MS SQL: 'select top 1 '...
  17.       FQuery.SQL.Text:='select ' +
  18.         '    e.first_name, ' +
  19.         '    e.last_name, ' +
  20.         '    e.salary ' +
  21.         'from employee e ' +
  22.         'order by e.salary asc ';
  23.         // ISO SQL+Firebird SQL: add
  24.         //'rows 1 '; here and below... won't work on e.g. PostgreSQL though
  25.       FTran.StartTransaction;
  26.       FQuery.Open;
  27.       SalaryGrid.Cells[1,1]:=FQuery.Fields[0].AsString;  // i.e. Cells[Col,Row]
  28.       SalaryGrid.Cells[2,1]:=FQuery.Fields[1].AsString;
  29.       SalaryGrid.Cells[3,1]:=FQuery.Fields[2].AsString;
  30.       FQuery.Close;
  31.       // Always commit(retain) an opened transaction, even if only reading
  32.       // this will allow updates by others to be seen when reading again
  33.       FTran.Commit;
  34. ...
  35.       end;
  36.     except
  37.       on D: EDatabaseError do
  38.       begin
  39.         MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
  40.           D.Message, mtError, [mbOK], 0);
  41.       end;
  42.     end;

jbmckim

  • Jr. Member
  • **
  • Posts: 84
Re: Looking for generic SQLite3 SELECT example, multiple records
« Reply #2 on: March 01, 2018, 11:56:03 pm »
Thanks, I should have mentioned that I've seen quite a few of the demos and they don't do what I'm looking for.  The first example has specific column references and again, I'm wanting to be generic.  Also, looking at the declaration page for TSQLQuery https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.html it seems to miss a good many attributes actually contained by TSQLQuery (check for .Eof as an example).  That makes it pretty difficult to determine what attribute(s) might be helpful unless one want to take the time to go through the intelligent list provided by the IDE and experiment with different attributes.

Therefore, I'm looking for a pattern or template that someone has had success and experience with.

Thanks again.


jbmckim

  • Jr. Member
  • **
  • Posts: 84
Re: Looking for generic SQLite3 SELECT example, multiple records
« Reply #4 on: March 02, 2018, 08:14:25 pm »
I am looking through the links but as I said above:

'Therefore, I'm looking for a pattern or template that someone has had success and experience with.'

My hope is that someone has a something similar to what I'm looking for and that they will share it. 

The example documentation isn't particularly helpful once one moves away from the context of the given example.  That's fine, but that's why I'm asking for something more specific.

 Just to show my issue, one question I have is where to access the data returned by the query.  My query is pretty simple:  'Select * From InstrumentMaster'.  When I run this query in SQLite Manager it returns 2 rows, displaying all columns of data - that's correct.  When I run it in LP I get:
Code: Pascal  [Select]
  1. Query1.Fields[0].AsString = '2';
 

All good to this point. 

Question: where is the data returned to in this context?  I tried referencing it by column header as in the example (my code here):
Code: Pascal  [Select]
  1. Query1.Fields.FieldByName('Data').AsString;
 
This ends with an error:  "Field not found: 'Data'"  This hits me as a bit of a mystery since my query is pretty much a direct analog of the example.  My assumption is that there is context I am missing.

Thanks.


jbmckim

  • Jr. Member
  • **
  • Posts: 84
Re: Looking for generic SQLite3 SELECT example, multiple records
« Reply #5 on: March 02, 2018, 08:17:45 pm »
It is curious to me that this SELECT issue is this much of a mystery.  It must be used frequently.  Further, the COUNT and INSERT implementations in my app are working fine.  Odd.  Haven't tried UPDATE yet.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 544
Re: Looking for generic SQLite3 SELECT example, multiple records
« Reply #6 on: April 11, 2018, 06:39:42 am »
Question: where is the data returned to in this context?  I tried referencing it by column header as in the example (my code here):
Code: Pascal  [Select]
  1. Query1.Fields.FieldByName('Data').AsString;
 


First, have you solved this thread?

If not, how is your complete statement for "Select * From InstrumentMaster"?

Just as an example. Let's say the complete statement is "Select  PartNumber, Name, SerialNumber, BuyDate From InstrumentMaster".
Let suppose its resultset is:
                PartNumber, Name, SerialNumber, BuyDate
Record 1:  1001, 'pen',    2001, 31.03.2018
Record 2:  1002, 'pencil', 2002, 28.02.2018

When you open SQLQuery1, you have access to all its resultset starting by the first line, of course.
Code: [Select]
SQLQuery1.Open;
So, if you ask:
Code: [Select]
ShowMessage(IntToStr(SQLQuery1.FieldByName('PartNumber').AsInteger));or
Code: [Select]
ShowMessage(SQLQuery1.FieldByName('PartNumber').AsString);or
Code: [Select]
ShowMessage(IntToStr(SQLQuery1.Fields[0].AsInteger));or
Code: [Select]
ShowMessage(SQLQuery1.Fields[0].AsString);The result will be the same: 1001.


So, if you ask:
Code: [Select]
ShowMessage(SQLQuery1.FieldByName('Name').AsString);The result will be: 'pen'.
Code: [Select]
ShowMessage(SQLQuery1.FieldByName('SerialNumber').AsString);The result will be: 2001.
Code: [Select]
ShowMessage(DateToStr(SQLQuery1.FieldByName('BuyDate').AsDate));The result will be: '31/03/2018'.

If you change the line of the recordset (Next, Prior, First or Last commands):
Code: [Select]
SQLQuery1.Next;
If you ask the same as before:
Code: [Select]
ShowMessage(SQLQuery1.FieldByName('PartNumber').AsString);
ShowMessage(SQLQuery1.FieldByName('Name').AsString);
ShowMessage(SQLQuery1.FieldByName('SerialNumber').AsString);
ShowMessage(DateToStr(SQLQuery1.FieldByName('BuyDate').AsDate));
or
Code: [Select]
ShowMessage(SQLQuery1.Fields[0].AsString);
ShowMessage(SQLQuery1.Fields[1].AsString);
ShowMessage(SQLQuery1.Fields[2].AsString);
ShowMessage(DateToStr(SQLQuery1.Fields[3].AsDate));
The result will be: 1002, 'pencil', 2002, '28.02.2018'.

So, keep reading records until the end of the dataset.
Code: [Select]
SQLQuery1.First;
while not SQLQuery1.Eof do
begin
  ... many showmessages ...
  SQLQuery1.Next;
end;
« Last Edit: April 11, 2018, 06:49:58 am by valdir.marcos »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 544
Re: Looking for generic SQLite3 SELECT example, multiple records
« Reply #7 on: April 11, 2018, 06:47:23 am »
It is curious to me that this SELECT issue is this much of a mystery.
If possible, open your mind and avoid thinking as a Visual Basic programmer all the time and remember that different programming languages have different approaches to solve the same problem.

Quote
It must be used frequently.
Yes, it is.

Quote
Further, the COUNT and INSERT implementations in my app are working fine.
Ok.

Quote
Odd.  Haven't tried UPDATE yet.
Have you tried the UPDATE command? Any doubt?

BrunoK

  • Jr. Member
  • **
  • Posts: 82
  • Retired programmer
Re: Looking for generic SQLite3 SELECT example, multiple records
« Reply #8 on: April 11, 2018, 09:55:10 am »
Compiles but not tested.

Code: Pascal  [Select]
  1. procedure ConcatRecordFieldsToStringList(SQLreader : TDataSet; colLocal:TStringList);
  2. var
  3.   strRtnBuf:string;
  4.   j:integer;
  5. begin
  6.   SQLreader.First;
  7.   while not SQLreader.EOF do begin
  8.     for j := 0 To SQLreader.FieldCount-1 do
  9.       if j=0 then
  10.         strRtnBuf:=SQLreader.Fields[j].Text                // 1st
  11.       else
  12.         strRtnBuf:=strRtnBuf+';'+SQLreader.Fields[j].Text; // others
  13.     colLocal.Add(strRtnBuf);
  14.  end;
  15. end;
  16.  
Lazarus trunk 2018.11.07 (+/- patches regarding enabled, TScrollBar). FPC 3.0.4 32 bits. (+heaptrc with leaked ClassName+Revisited TList) , Windows 10 Pro x64 (v. 1803)

Zvoni

  • Jr. Member
  • **
  • Posts: 67
Re: Looking for generic SQLite3 SELECT example, multiple records
« Reply #9 on: April 11, 2018, 11:55:04 am »


In this case, The SQLreader manages the row cursor.  The data for each column within a given row is appended to strRtnBuf with a delimiter. Each returned data row is added to a collection.

If i understood you correctly, your're looking for something like the Join-Function from Visual Basic, Pass an Array of Elements and a Delimiter, and Presto: One concatenated String back

EDIT: Expanding on Bruno's Code (Not tested)
Code: Pascal  [Select]
  1. Function ConcatRecordFieldsToStringList(SQLreader : TDataSet; Delimiter:String):TStringList;
  2. var
  3.   strRtnBuf:string;
  4.   j:integer;
  5. begin
  6.   SQLreader.First;
  7.   while not SQLreader.EOF do begin
  8.     for j := 0 To SQLreader.FieldCount-1 do
  9.       if j=0 then
  10.         strRtnBuf:=SQLreader.Fields[j].Text                // 1st
  11.       else
  12.         strRtnBuf:=strRtnBuf+Delimiter+SQLreader.Fields[j].Text; // others
  13.     Result.Add(strRtnBuf);
  14.  end;
  15. end;
  16.  
« Last Edit: April 11, 2018, 12:07:45 pm by Zvoni »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie