find - incremental search

Michael Gaihede

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

TQuery record search/find - incremental search

Hi,

I just want to check out something: is it correct that - when using a
TQuery - the locate method will find the first, and only the first, record
satisfying the search criteria ?

I.e. it appears to be impossible to use the locate method to do incremental
searches in that only the first record will be found, regardless of the
dataset's cursor position. Consequently, there seems to be no apparent way
of making incremental searches using a standard TDataSet (by incremental I
mean locating *all* records satisfying the same criteria - e.g. in a DBgrid
where the user wants to jump to the next row containing the given search
criteria when clicking a search button, and the search criteria has not
changed between clicks).

Any alternative solutions will be highly appreciated.

Best regards and thanks,

Michael

cbaug

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:TQuery record search/find - incremental search

I'm not quite sure what you are asking. Make sure you work with the
following concepts.

1) a query will work at the server. Examples: select where name = 'abc' will give
abc only. Select where name > 'abc' / select where name like 'abc%', etc.All of this
happens at the server.

2) A locate works off of the data that has been brought down to the client. So standard
locate / locatenext / first / last / your own loop, etc all apply. Realize also that no indexes
will be used on a query result set since the client is doing the lookup.

So a simple first / locate / locatenext would (I believe) solve your problem.

Depending on what you want, I have yet to find something that you couldn't do using
one of the two approaches.

HTH,

Craig Baugh

Quote

Michael Gaihede <m...@nykredit.dk> wrote in message news:7ppbjt$drf3@forums.borland.com...
> Hi,

> I just want to check out something: is it correct that - when using a
> TQuery - the locate method will find the first, and only the first, record
> satisfying the search criteria ?

> I.e. it appears to be impossible to use the locate method to do incremental
> searches in that only the first record will be found, regardless of the
> dataset's cursor position. Consequently, there seems to be no apparent way
> of making incremental searches using a standard TDataSet (by incremental I
> mean locating *all* records satisfying the same criteria - e.g. in a DBgrid
> where the user wants to jump to the next row containing the given search
> criteria when clicking a search button, and the search criteria has not
> changed between clicks).

> Any alternative solutions will be highly appreciated.

> Best regards and thanks,

> Michael

Chris Schaeffe

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:TQuery record search/find - incremental search

cbaugh <cba...@home.com> schreef in berichtnieuws
7pprk9$e...@forums.borland.com...

Quote

> So a simple first / locate / locatenext would (I believe) solve your
problem.

can you please tel us more about locatenext?
I have the same problem and I can't find anything about "locatenext" in the
help
Thanks a lot,
Chris

Vesso Zaimo

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:TQuery record search/find - incremental search

Two remarks here - the servers are quite optimized for fast searching - so
better try to do the search there.

And, be carefull with Locate - on big datasets it can cause extreme delays.

Cheers.

Vesso

Quote

Michael Gaihede <m...@nykredit.dk> wrote in message

news:7ppbjt$drf3@forums.borland.com...

Quote

> Hi,

> I just want to check out something: is it correct that - when using a
> TQuery - the locate method will find the first, and only the first, record
> satisfying the search criteria ?

> I.e. it appears to be impossible to use the locate method to do
incremental
> searches in that only the first record will be found, regardless of the
> dataset's cursor position. Consequently, there seems to be no apparent way
> of making incremental searches using a standard TDataSet (by incremental I
> mean locating *all* records satisfying the same criteria - e.g. in a
DBgrid
> where the user wants to jump to the next row containing the given search
> criteria when clicking a search button, and the search criteria has not
> changed between clicks).

> Any alternative solutions will be highly appreciated.

> Best regards and thanks,

> Michael

Michael Gaihed

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:TQuery record search/find - incremental search

Thanks for your response. I am, however, fully aware of the points you make.
I didn't know that a 'locatenext' method was available for a query - if so
it's not documented.

Continuing my previous message I did manage to find a workable and perhaps
desirable solution:

1) add another TQuery to whatever is being used (TForm, Grid, etc.)
2) Use the same select statement as in the original TQuery but add a where
clause
specifying the desired search criteria (e.g. "where name like ':John'").
Also, add zero or more
fields making the record uniquely identifiable (for Oracle e.g. the
rowid column) in both queries
3) execute 2) and retrieve the unique field (e.g. rowid)
4) use 3) as input parameter to the locate method in the first TQuery (there
will always be only one hit in the result set)
5) when the user clicks a next button (with the same search parameter),
execute the "next" method in the second TQuery.
6) ->4) (either continue from eof->bof or stop at eof).

It works, but of course still has to run at the client.

Best regards,

Michael

cbaugh skrev i meddelelsen <7pprk9$e...@forums.borland.com>...

Quote

>I'm not quite sure what you are asking. Make sure you work with the
>following concepts.

>1) a query will work at the server. Examples: select where name = 'abc'
will give
>abc only. Select where name > 'abc' / select where name like 'abc%',
etc.All of this
>happens at the server.

>2) A locate works off of the data that has been brought down to the client.
So standard
>locate / locatenext / first / last / your own loop, etc all apply. Realize

also that no indexes

Quote

>will be used on a query result set since the client is doing the lookup.

>So a simple first / locate / locatenext would (I believe) solve your
problem.

>Depending on what you want, I have yet to find something that you couldn't
do using
>one of the two approaches.

>HTH,

>Craig Baugh

>Michael Gaihede <m...@nykredit.dk> wrote in message

news:7ppbjt$drf3@forums.borland.com...

- Show quoted text -

Quote

>> Hi,

>> I just want to check out something: is it correct that - when using a
>> TQuery - the locate method will find the first, and only the first,
record
>> satisfying the search criteria ?

>> I.e. it appears to be impossible to use the locate method to do
incremental
>> searches in that only the first record will be found, regardless of the
>> dataset's cursor position. Consequently, there seems to be no apparent
way
>> of making incremental searches using a standard TDataSet (by incremental
I
>> mean locating *all* records satisfying the same criteria - e.g. in a
DBgrid
>> where the user wants to jump to the next row containing the given search
>> criteria when clicking a search button, and the search criteria has not
>> changed between clicks).

>> Any alternative solutions will be highly appreciated.

>> Best regards and thanks,

>> Michael

cbaug

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:TQuery record search/find - incremental search

Locate next is just something you write. Locate is doing a
table scan, so you would have to write the procedure to do
the next locate starting from where you are. A relatively
simple task. I apologize if I made it sound like you can call
a Delphi LocateNext function. However, (my two cents) I
think that this is not the best way to develop an application. The
only time I would do a locate is with a small lookup table that I
have at the client. In that scenario I am guaranteed of a unique value
so the locate function works well there. Searching for multiple
matching values is something best left to the server.

Craig Baugh

Quote

Chris Schaeffer <c_sch...@rem.nl> wrote in message news:7pr168$fjl4@forums.borland.com...

> cbaugh <cba...@home.com> schreef in berichtnieuws
> 7pprk9$e...@forums.borland.com...

> > So a simple first / locate / locatenext would (I believe) solve your
> problem.

> can you please tel us more about locatenext?
> I have the same problem and I can't find anything about "locatenext" in the
> help
> Thanks a lot,
> Chris

Mike Packar

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:TQuery record search/find - incremental search

Quote

>> And, be carefull with Locate - on big datasets it can cause extreme

delays.
I think that needs to be qualified: only on datasets not fully returned
to the client is this true.

Otherwise, if there's an index, I believe Locate uses a binary search,
which means that finds are going to be as fast for 100 rows as 100,000.
(But I'm not sure it doesn't do a sequential search -- anyone know?)

cbaug

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:TQuery record search/find - incremental search

Maybe I'm missing the boat on this one but how can the
locate on a result set use an index? The index is at the server
and the result set is just an unindexed table sitting at the client.
Unless the bde automatically creates indexes, which I would find
extremely hard to believe, it has to do a table scan. So doing a locate
on a large result set will cause the entire result to be brought to the
client. And even if the client already has the result set, it will still do
a table scan.

my two cents,

Craig Baugh

Quote

Mike Packard <mpack...@mail.sandi.net> wrote in message news:37C1C67D.1D1F7608@mail.sandi.net...
> >> And, be carefull with Locate - on big datasets it can cause extreme
> delays.
> I think that needs to be qualified: only on datasets not fully returned
> to the client is this true.

> Otherwise, if there's an index, I believe Locate uses a binary search,
> which means that finds are going to be as fast for 100 rows as 100,000.
> (But I'm not sure it doesn't do a sequential search -- anyone know?)

Team

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:TQuery record search/find - incremental search

Quote

cbaugh wrote in message <7pshdk$gt...@forums.borland.com>...
>Maybe I'm missing the boat on this one but how can the
>locate on a result set use an index? The index is at the server
>and the result set is just an unindexed table sitting at the client.
>Unless the bde automatically creates indexes, which I would find
>extremely hard to believe, it has to do a table scan.

I haven't looked at the implementation of Locate, however, one thing it
could and may do is to compare the fields you ask it to locate by to the
fields the result set was ordered by, if they match then it can do a binary
search on the result set without having to build an index because the
records themselves are in indexed order. This does require the entire result
set to be fetched from the server though.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
Bill of NO Rights; Bill of NO Rights; ARTICLE VII: You do NOT have the right
to the possessions of others. If you rob, cheat or coerce away the goods or
services of other citizens, don't be surprised if the rest of us get
together and lock you way in a place where you still won't have the right to
a big-screen color TV or a life of leisure.

Mike Packar

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:TQuery record search/find - incremental search

Yeah, what Wayne said.

The only thing that makes me say Locate is doing a binary search is that
it's finding the last row in a 5K result set in < 1 sec. That's pretty
fast.

If I force a "next loop" it takes over 4 secs on same set.

And, yes, it's a given that the whole shebang has to be in memory.

And, yes, BDE knows about server indexes.
In fact, using SQLAnywhere requires a special ODBC setting before BDE
will pick up on them (Keys in SQL Statistics).

Other Threads

1. TQuery and Incremental searches

2. Incremental search on dataset from a TQuery - Possible?

3. Incremental listbox search - find, then page down

4. TQuery: Need help on searching for a record

5. Record Searches with TQuery and SQL Server 7.0

6. The search key was not found in any record

7. incremental search component

8. incremental & partial search

9. case unsensitivity & incremental search in Interbase

10. ComboBox with incremental search ?