ZeosLib to fetch data from query [SOLVED]
lazarus / 16-Databases

----------------------------------------------------------------------
PaulW · 2011-05-20 17:46

Lazarus 0.9.31;  FPC 2.4.2;  SQLite3 0.4;  ZeosLib 7.7.0-dev;  Win
Vista (64-bit)

I am trying to learn Lazarus/FPC by re-writing an application that I
had originally developed in MS-Access and then subsequently re-wrote
in Visual Basic.  The application utilizes a .mdb back end data file.
 Having little luck in accessing the .mdb file in Lazarus via ODBC
(issues with 64-bit ODBC driver), I decided to convert the .mdb data
file to .db3 (SQLite-3).  Upon reading several posts on the this
forum, I decided to go with ZeosLib for data access.  So far, I've
successfully created a TDBGrid and populated it via a TZConnection ->
TZReadOnlyQuery -> TDatasource.  So, now comes the "easy" part...

I need to retrieve a couple of fields from a record in the query to
populate TEdit fields on my form.  For the life of me I haven't been
able to find any documentation or program examples that retrieve a
record in code.  Based on my past experience, I would expect the
scenario to go something like this:

Modify the existing query, if needed, with a filter or write an SQL
statement for the query.
Execute the query.
Loop through the query with something like:
   _____.Find First
   If _____.Found then
      While not _____.EOF do
         begin
            Something := _____.FieldByName('myfield');
            If Something = {my criteria} then
               begin
                  .....
                  myTEdit.text := Something;
                  .....
               end;
            _____.Next;
         end;
or some similar code.
My problem primarily is what should _____ be?  Does _____ reference my
query or the datasource?  What is the correct syntax?  Is there a
better/easier way to re3trieve the field contents?

I appreciate some guidance.  Also, can anyone point me to a reference
(on-line or in print) that would address this type of issue?

Thanks.

----------------------------------------------------------------------
JD · 2011-05-20 18:50

I know of two quick ways to accomplish what I think you want. Each of
these methods will retrieve fields from just ONE record. If you want
results from many records, the first method will return ONLY the first
record found while the second will give you the fields from the last
record in the dataset (because the loop will overwrite the previous
contents of the TEdit fields).

So if what you want is to retrieve the fields of just ONE record, this
is how to do it.

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 // Method A
- using TZTablewith ZTable1 dobegin  Open;  if Locate('LAST_NAME',
Trim(edSearch_LastName.Text), [loCaseInsensitive]) then  begin   
edFirstName.Text := FieldByName('FIRST_NAME').AsString;   
edLastName.Text := FieldByName('LAST_NAME').AsString;    edAge.Text :=
FieldByName('AGE').AsString;  end  else  begin    Beep;              
// Beep if employee was not found    ShowMessage('The employee with
last name ' + Trim(edSearch_LastName.Text) + ' was not found!';  end 
Close;  end; // Method B - Using TZQuery or TZReadOnlyQuerywith
ZROQuery1 dobegin  SQL.Clear;  SQL.Add('SELECT first_name, last_name,
age FROM EMPLOYEE where last_name = ' +
QuotedStr(Trim(edLastName.Text)) );  Open;    // Open the dataset 
First;   // Go to the first record in the dataset    while not EOF do 
begin    edFirstName.Text := FieldByName('FIRST_NAME').AsString;   
edLastName.Text := FieldByName('LAST_NAME').AsString;    edAge.Text :=
FieldByName('AGE').AsString;    Next;  end;  Close;   // Close the
datasetend; I personally use the first method to locate a record & the
second method to fill combo boxes, listboxes etc from many records. To
fill a combobox for example, use the code snippet below:

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 // Using
TZQuery or TZReadOnlyQuerywith ZROQuery1 dobegin  SQL.Clear; 
SQL.Add('SELECT first_name, last_name FROM EMPLOYEE');  Open;    //
Open the dataset  First;   // Go to the first record in the dataset  
Combobox1.Items.Clear;    // Clear the contents of the combobox  while
not EOF do  begin   
ComboBox1.Items.Add(UPPER(FieldByName('LAST_NAME').AsString) + ', ' +
FieldByName('FIRST_NAME').AsString);    Next;  end;  Close;   // Close
the datasetend; The second code snippet will fill the combobox with
ALL the employee first name & last name in the table in the format
BOND, James
TEMPLER, Simon
POIROT, Hercule
HOLMES, Sherlock  etc

I hope this helps.

Cheers,

JD

----------------------------------------------------------------------
PaulW · 2011-05-20 21:17

@JD:

I'm running out of time for today, so I'll dig into your response
later.
Your example uses a table; will it work the same with a query?

Thanks.

----------------------------------------------------------------------
JD · 2011-05-20 23:10

Quote from: PaulW on May 20, 2011, 11:17:00 pm@JD:

I'm running out of time for today, so I'll dig into your response
later.
Your example uses a table; will it work the same with a query?

Thanks.

Yes, it will.

----------------------------------------------------------------------
PaulW · 2011-05-21 13:45

@JD:

Thanks a lot.  That certainly helped.

----------------------------------------------------------------------
JD · 2011-05-21 17:52

Quote from: PaulW on May 21, 2011, 03:45:10 pm@JD:

Thanks a lot.  That certainly helped.
You're welcome. BTW you have no reason to give up on Lazarus. With a
little bit of patience, this forum & some astute searches on Google
you will get the answers you need for your programming difficulties.

Happy coding!

----------------------------------------------------------------------
marcov · 2011-05-22 14:07

Note that JD's SQL example (the second method) can be also rewritten
using parameters

So instead of
Code: [Select]  SQL.Clear; 
  SQL.Add('SELECT first_name, last_name, age FROM EMPLOYEE where
last_name = ' + QuotedStr(Trim(edLastName.Text)) ); 
  Open;    // Open the dataset 
  First; 

one populates a zquery with "SELECT first_name, last_name, age FROM
EMPLOYEE where last_name = :last_name"

and then sets parameters via sql.params[0].asstring:='%namemask%';

Though probably sqlite doesn't prepare queries and it doesn't matter
that much in this case.