ZeosLib to fetch data from query [SOLVED]

[PaulW]

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]

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][+][-]

1. // Method A - using TZTable
2. with ZTable1 do
3. begin
4.   Open;
5.   if Locate('LAST_NAME', Trim(edSearch_LastName.Text), [loCaseInsensitive]) then
6.   begin
7.     edFirstName.Text := FieldByName('FIRST_NAME').AsString;
8.     edLastName.Text := FieldByName('LAST_NAME').AsString;
9.     edAge.Text := FieldByName('AGE').AsString;
10.   end
11.   else
12.   begin
13.     Beep;               // Beep if employee was not found
14.     ShowMessage('The employee with last name ' + Trim(edSearch_LastName.Text) + ' was not found!';
15.   end
16.   Close;  
17. end;
18.  
19. // Method B - Using TZQuery or TZReadOnlyQuery
20. with ZROQuery1 do
21. begin
22.   SQL.Clear;
23.   SQL.Add('SELECT first_name, last_name, age FROM EMPLOYEE where last_name = ' + QuotedStr(Trim(edLastName.Text)) );
24.   Open;    // Open the dataset
25.   First;   // Go to the first record in the dataset
26.  
27.   while not EOF do
28.   begin
29.     edFirstName.Text := FieldByName('FIRST_NAME').AsString;
30.     edLastName.Text := FieldByName('LAST_NAME').AsString;
31.     edAge.Text := FieldByName('AGE').AsString;
32.     Next;
33.   end;
34.   Close;   // Close the dataset
35. end;
36.  

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][+][-]

1. // Using TZQuery or TZReadOnlyQuery
2. with ZROQuery1 do
3. begin
4.   SQL.Clear;
5.   SQL.Add('SELECT first_name, last_name FROM EMPLOYEE');
6.   Open;    // Open the dataset
7.   First;   // Go to the first record in the dataset
8.  
9.   Combobox1.Items.Clear;    // Clear the contents of the combobox
10.   while not EOF do
11.   begin
12.     ComboBox1.Items.Add(UPPER(FieldByName('LAST_NAME').AsString) + ', ' + FieldByName('FIRST_NAME').AsString);
13.     Next;
14.   end;
15.   Close;   // Close the dataset
16. end;
17.  

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]

@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]

@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]

@JD:

Thanks a lot.  That certainly helped.

[JD]

@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]

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.