[Solved] Locate on multiple database fields not working
lazarus / 16-Databases

----------------------------------------------------------------------
JanRoza · 2018-01-14 23:15

In the Free Pascal wiki I read:
QuoteDescription
locate looks for a record in the dataset where the specified search
values for the specified fields match.
If found, the function returns true and the cursor position/current
record moves to that record.

KeyFields can be a single field name or a semicolon-separated list of
fields.
KeyValue can be a variant or a variant array and the number of items
must match the number of fields specified in KeyFields.
So I tried it in my project in a simplified way on a SQLite database:
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
DataModule1.qryNew.Locate('Name;FirstName', edtDataSearch.Text,
[loCaseInsensitive,loPartialKey]);but this only produces an error
"qryNew: Field not not found: "Name;FirstName".
The mentioned fieldnames do exist in the table behind
DataModule1.qryNew and when using just one fieldname in the locate it
does work.
Is the multiple fields feature not implemented for queries or am I
doing something wrong?

----------------------------------------------------------------------
Lutz Mändle · 2018-01-14 23:28

From the description: KeyValue can be a variant or a variant array and
the number of items must match the number of fields specified in
KeyFields.

In this case you give two Keyfields but only one KeyValue.

----------------------------------------------------------------------
JanRoza · 2018-01-15 00:11

Just tested it that way with a search value that is in the database in
the Name field:
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        if
DataModule1.qryNew.Locate('Name;FirstName', VarArrayOf(['Flynn',
'Flynn']), [loCaseInsensitive,loPartialKey])       then
showmessage('Found')       else showmessage('Not found'); Alas the
result is always "Not found", so although I don't get any errors
anymore the result is incorrect.

----------------------------------------------------------------------
Lutz Mändle · 2018-01-15 01:25

Locate finds the first dataset which matches all given conditions. In
this case the value Flynn has to be present in both fields in the same
record. I guess you want to find each record which has Flynn in Name
or FirstName. Then you have to start Locate for each field separately.
Another approach is using a filter with an or condition, then you have
all records with the value Flynn in the field Name or FirstName. The
generation of the filterstring looks like this:

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
filterstring := 'Name = ' + quotedstr('Flynn') + ' or FirstName = ' +
quotedstr('Flynn'); 
If you want partial search then replace the = with LIKE and use
wildcards (this are usually % signs in the database world).

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
filterstring := 'Name LIKE ' + quotedstr('Flynn%') + ' or FirstName
LIKE ' + quotedstr('Flynn%'); 
But I don't know whether this works in Lazarus (filtering in general
yes, but the LIKE trick), it works in Delphi.

----------------------------------------------------------------------
JanRoza · 2018-01-15 16:43

You are right, I have to loop thru all fields with a locate to get
results.
Thanks!

----------------------------------------------------------------------
Thaddy · 2018-01-15 16:47

It is much faster to implement an SQL query: locate uses local locates
and doesn't use any SQL. Its performance is therefor much slower than
a "select from where"

----------------------------------------------------------------------
JanRoza · 2018-01-15 23:12

I know, but with a locate I can wander thru all found records and my
dbgrid stays intact, where with a select I will filter the data and my
dbgrid will show only the found records. Still have to decide what I
need most, an unchanged dbgrid or the speed of filtering.

----------------------------------------------------------------------
GAN · 2018-01-15 23:23

Quote from: JanRoza on January 16, 2018, 12:12:28 amI know, but with a
locate I can wander thru all found records and my dbgrid stays intact,
where with a select I will filter the data and my dbgrid will show
only the found records. Still have to decide what I need most, an
unchanged dbgrid or the speed of filtering.

Or you can use another dataset with the query to find the records,
instead of the dataset connected to the grid.

----------------------------------------------------------------------
JanRoza · 2018-01-16 06:19

True, but I've decided on the filtering as Thaddy suggested, it's
quick and the end result from a users perspective looks good so I have
best of both worlds now.