Recent

Author Topic: [Solved] Locate on multiple database fields not working  (Read 1994 times)

JanRoza

  • Hero Member
  • *****
  • Posts: 534
    • http://www.silentwings.nl
[Solved] Locate on multiple database fields not working
« on: January 15, 2018, 12:15:05 am »
In the Free Pascal wiki I read:
Quote
Description
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]
  1. 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?

« Last Edit: January 15, 2018, 05:43:36 pm by JanRoza »
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

Lutz Mändle

  • New Member
  • *
  • Posts: 49
Re: Locate on multiple database fields not working
« Reply #1 on: January 15, 2018, 12:28:22 am »
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.
« Last Edit: January 15, 2018, 12:33:41 am by Lutz Mändle »

JanRoza

  • Hero Member
  • *****
  • Posts: 534
    • http://www.silentwings.nl
Re: Locate on multiple database fields not working
« Reply #2 on: January 15, 2018, 01:11:06 am »
Just tested it that way with a search value that is in the database in the Name field:
Code: Pascal  [Select]
  1.        if DataModule1.qryNew.Locate('Name;FirstName', VarArrayOf(['Flynn', 'Flynn']), [loCaseInsensitive,loPartialKey])
  2.        then showmessage('Found')
  3.        else showmessage('Not found');
  4.  
Alas the result is always "Not found", so although I don't get any errors anymore the result is incorrect.
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

Lutz Mändle

  • New Member
  • *
  • Posts: 49
Re: Locate on multiple database fields not working
« Reply #3 on: January 15, 2018, 02:25:36 am »
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]
  1. filterstring := 'Name = ' + quotedstr('Flynn') + ' or FirstName = ' + quotedstr('Flynn');
  2.  

If you want partial search then replace the = with LIKE and use wildcards (this are usually % signs in the database world).

Code: Pascal  [Select]
  1. filterstring := 'Name LIKE ' + quotedstr('Flynn%') + ' or FirstName LIKE ' + quotedstr('Flynn%');
  2.  

But I don't know whether this works in Lazarus (filtering in general yes, but the LIKE trick), it works in Delphi.

JanRoza

  • Hero Member
  • *****
  • Posts: 534
    • http://www.silentwings.nl
Re: Locate on multiple database fields not working
« Reply #4 on: January 15, 2018, 05:43:18 pm »
You are right, I have to loop thru all fields with a locate to get results.
Thanks!
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

Thaddy

  • Hero Member
  • *****
  • Posts: 8927
Re: [Solved] Locate on multiple database fields not working
« Reply #5 on: January 15, 2018, 05:47:54 pm »
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"
Most people that want to use threading should learn to patch their jeans first: use a needle.

JanRoza

  • Hero Member
  • *****
  • Posts: 534
    • http://www.silentwings.nl
Re: [Solved] Locate on multiple database fields not working
« Reply #6 on: January 16, 2018, 12:12:28 am »
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.
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

GAN

  • Full Member
  • ***
  • Posts: 224
Re: [Solved] Locate on multiple database fields not working
« Reply #7 on: January 16, 2018, 12:23:10 am »
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.

Or you can use another dataset with the query to find the records, instead of the dataset connected to the grid.
Lazarus 1.8.4 FPC 3.0.4 Linux Mint Mate 17.2 x86_64 GTK-2
Zeos 7.1.3 - Sqlite 3.8.2

Foro Lazarus en español http://forum.lazarus.freepascal.org/index.php/board,73.0.html

JanRoza

  • Hero Member
  • *****
  • Posts: 534
    • http://www.silentwings.nl
Re: [Solved] Locate on multiple database fields not working
« Reply #8 on: January 16, 2018, 07:19:39 am »
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.
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64