Looking for generic SQLite3 SELECT example, multiple records
lazarus / 16-Databases

----------------------------------------------------------------------
jbmckim · 2018-03-01 19:38

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  While
SQLreader.Read()                   For j = 0 To (iColCount - 1)      
 strRtnBuf &= String.Format("{0};", SQLreader(j))                    
Next     colLocal.Add(strRtnBuf)    strRtnBuf = ""               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 · 2018-03-01 21:26

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Program
ShowData; {$mode objfpc} {$ifdef mswindows}{$apptype
console}{$endif}uses  DB, Sysutils, sqldb, sqlite3conn; var 
AConnection  : TSQLConnection;  ATransaction : TSQLTransaction;  Query
       : TSQLQuery;begin  AConnection :=
TSQLite3Connection.Create(nil);  ATransaction :=
TSQLTransaction.Create(AConnection);  AConnection.Transaction :=
ATransaction;  AConnection.DatabaseName := 'test_dbase';  Query :=
TSQLQuery.Create(nil);  Query.SQL.Text := 'select * from tblNames'; 
Query.Database := AConnection;  Query.Open;  while not Query.Eof do 
begin    Writeln('ID: ', Query.FieldByName('ID').AsInteger, 'Name: '
+                                 
Query.FieldByName('Name').AsString);    Query.Next;  end; 
Query.Close;  AConnection.Close;  Query.Free;  ATransaction.Free; 
AConnection.Free;end.

http://wiki.freepascal.org/SQLdb_Tutorial3
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 [/// Load
from DB    try      if not FConn.Connected then        FConn.Open;   
 if not FConn.Connected then      begin        ShowMessage('Error
connecting to the database. Aborting data loading.');        exit;   
 end;       // Lowest salary      // Note: we would like to only
retrieve 1 row, but unfortunately the SQL      // used differs for
various dbs. As we'll deal with db dependent SQL later      // in the
tutorial, we leave this for now.      // MS SQL: 'select top 1 '...  
  FQuery.SQL.Text:='select ' +        '    e.first_name, ' +        '
  e.last_name, ' +        '    e.salary ' +        'from employee e '
+        'order by e.salary asc ';        // ISO SQL+Firebird SQL:
add        //'rows 1 '; here and below... won't work on e.g.
PostgreSQL though      FTran.StartTransaction;      FQuery.Open;     
SalaryGrid.Cells[1,1]:=FQuery.Fields[0].AsString;  // i.e.
Cells[Col,Row]      SalaryGrid.Cells[2,1]:=FQuery.Fields[1].AsString; 
    SalaryGrid.Cells[3,1]:=FQuery.Fields[2].AsString;     
FQuery.Close;      // Always commit(retain) an opened transaction,
even if only reading      // this will allow updates by others to be
seen when reading again      FTran.Commit;...      end;    except    
on D: EDatabaseError do      begin        MessageDlg('Error', 'A
database error has occurred. Technical error message: ' +         
D.Message, mtError, [mbOK], 0);      end;    end;

----------------------------------------------------------------------
jbmckim · 2018-03-01 22:56

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 · 2018-03-02 04:38

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 · 2018-03-02 19:14

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][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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 · 2018-03-02 19:17

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 · 2018-04-11 04:39

Quote from: jbmckim on March 02, 2018, 08:14:25 pmQuestion: 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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').AsStri
ng);The result will be: 2001.
Code: [Select]ShowMessage(DateToStr(SQLQuery1.FieldByName('BuyDate').A
sDate));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 · 2018-04-11 04:47

Quote from: jbmckim on March 02, 2018, 08:17:45 pmIt 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.

QuoteIt must be used frequently.Yes, it is.

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

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

----------------------------------------------------------------------
BrunoK · 2018-04-11 07:55

Compiles but not tested.

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
ConcatRecordFieldsToStringList(SQLreader : TDataSet;
colLocal:TStringList);var  strRtnBuf:string;  j:integer;begin 
SQLreader.First;  while not SQLreader.EOF do begin    for j := 0 To
SQLreader.FieldCount-1 do      if j=0 then       
strRtnBuf:=SQLreader.Fields[j].Text                // 1st      else  
    strRtnBuf:=strRtnBuf+';'+SQLreader.Fields[j].Text; // others   
colLocal.Add(strRtnBuf); end;end;

----------------------------------------------------------------------
Zvoni · 2018-04-11 09:55

Quote from: jbmckim on March 01, 2018, 08:38:18 pm

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Function
ConcatRecordFieldsToStringList(SQLreader : TDataSet;
Delimiter:String):TStringList;var  strRtnBuf:string;  j:integer;begin 
SQLreader.First;  while not SQLreader.EOF do begin    for j := 0 To
SQLreader.FieldCount-1 do      if j=0 then       
strRtnBuf:=SQLreader.Fields[j].Text                // 1st      else  
    strRtnBuf:=strRtnBuf+Delimiter+SQLreader.Fields[j].Text; //
others    Result.Add(strRtnBuf); end;end;