Looking for generic SQLite3 SELECT example, multiple records

[jbmckim]

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]

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]

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.

[valdir.marcos]

Fields, FieldValues and FieldDefs provide the generic information you want:
https://www.freepascal.org/docs-html/fcl/db/tdataset.fields.html
https://www.freepascal.org/docs-html/fcl/db/tdataset.fieldvalues.html
https://www.freepascal.org/docs-html/fcl/db/tdataset.fielddefs.html

[jbmckim]

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]

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]

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;

[valdir.marcos]

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.

It must be used frequently.

Yes, it is.

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

Ok.

Odd.  Haven't tried UPDATE yet.

Have you tried the UPDATE command? Any doubt?

[BrunoK]

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.  

[Zvoni]

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.