Locate record from SQL table

Stojce Mirkovski

Delphi Developer

Thu, 14 Aug 2003 17:03:29 GMT

Locate record from SQL table

Is there any way to implement such functionallity
that Table.Locate method provide on SQL tables
(using TQuery object)?

I was thinking that Cursors on the server can be used
for this, but I don't have experience with them :)

Any hints?

Willie Marai

Delphi Developer

Thu, 14 Aug 2003 19:28:53 GMT

Re:Locate record from SQL table

Locate method works fine on queries. I'm using it a lot myself. I have seen
sometimes that you cannot use locate
if your column consists of a custom definition, but otherwise is should be
fine.

select
userid,
FullName = FirstName + Lastname,
from users

I have experienced you can use locate on columns like userid, but not on
FullName.

Hope this helps

Willie Marais

Quote

Stojce Mirkovski <o...@bangkok.com> wrote in message

news:97ajrs$d3i1@bornews.inprise.com...

Quote

> Is there any way to implement such functionallity
> that Table.Locate method provide on SQL tables
> (using TQuery object)?

> I was thinking that Cursors on the server can be used
> for this, but I don't have experience with them :)

> Any hints?

Stojce Mirkovsk

Delphi Developer

Thu, 14 Aug 2003 21:45:01 GMT

Re:Locate record from SQL table

Well, the problem with the locate is that it has to get all the needed
records
first from the server to client and then try to locate particular record on
the
client side and that is very unefficient... I thought that maybe server
cursors
can help me do this thing...

Quote

> Locate method works fine on queries. I'm using it a lot myself. I have
seen
> sometimes that you cannot use locate
> if your column consists of a custom definition, but otherwise is should be
> fine.

> ie

> select
> userid,
> FullName = FirstName + Lastname,
> from users

> I have experienced you can use locate on columns like userid, but not on
> FullName.
> > Is there any way to implement such functionallity
> > that Table.Locate method provide on SQL tables
> > (using TQuery object)?

> > I was thinking that Cursors on the server can be used
> > for this, but I don't have experience with them :)

> > Any hints?

Robert Cern

Delphi Developer

Fri, 15 Aug 2003 05:08:46 GMT

Re:Locate record from SQL table

Yes, use the WHERE clause. It's much more powerfull than Locate:
select * from Table1 where (field1='X' and field2 like '_NoFirstLetter%') or
field3 ='bljak'

And everything happens on server. If you design indexes properly, the
reponse times even on very big tables and complex queries will be
instantaneous.

--
Robert

Quote

Stojce Mirkovski wrote in message <97ajrs$d...@bornews.inprise.com>...
>Is there any way to implement such functionallity
>that Table.Locate method provide on SQL tables
>(using TQuery object)?

>I was thinking that Cursors on the server can be used
>for this, but I don't have experience with them :)

>Any hints?

Stojce Mirkovsk

Delphi Developer

Fri, 15 Aug 2003 06:09:03 GMT

Re:Locate record from SQL table

Yes I know this :), but I wanted to know if it is possible to locate
record and to see it with all others that don't fall in those of result
set... You know how Locate works - it points out on the first located
record with all others staying visible... Yes I know this is against SQL
servers rules, but I just wonder if there is any mechanism that can do
this on the client side :)

Regards.

Quote

> Yes, use the WHERE clause. It's much more powerfull than Locate:
> select * from Table1 where (field1='X' and field2 like '_NoFirstLetter%')
or
> field3 ='bljak'

> And everything happens on server. If you design indexes properly, the
> reponse times even on very big tables and complex queries will be
> instantaneous.

> --
> Robert

> Stojce Mirkovski wrote in message <97ajrs$d...@bornews.inprise.com>...
> >Is there any way to implement such functionallity
> >that Table.Locate method provide on SQL tables
> >(using TQuery object)?

> >I was thinking that Cursors on the server can be used
> >for this, but I don't have experience with them :)

> >Any hints?

Brian Feifare

Delphi Developer

Fri, 15 Aug 2003 09:47:44 GMT

Re:Locate record from SQL table

I do this in my applications by having a second TQuery retrieve the
records that match the search criteria, and then use the primary key
values to locate the record in the first TQuery being displayed on the
grid. I also give the user prior/next buttons and a record count for
the searched items. It is pretty easy to do.

Another option might be to use TxQuery, which will run queries on any
TDataset, so your second query would not even go back to the Server for
the matching records. I haven't used TxQuery, but I have seen it get
lots of recommendations. The web site is www.sigmap.com/txquery.htm

Brian

Quote

Stojce Mirkovski wrote:

> Yes I know this :), but I wanted to know if it is possible to locate
> record and to see it with all others that don't fall in those of result
> set... You know how Locate works - it points out on the first located
> record with all others staying visible... Yes I know this is against SQL
> servers rules, but I just wonder if there is any mechanism that can do
> this on the client side :)

> Regards.

Robert Cern

Delphi Developer

Fri, 15 Aug 2003 10:15:53 GMT

Re:Locate record from SQL table

Yes, Locate does that. Locate works on client side.
But with result sets over few thousand records it will be uselessly slow.
--
Robert

Quote

Stojce Mirkovski wrote in message <97bvjl$k2...@bornews.inprise.com>...
>Yes I know this :), but I wanted to know if it is possible to locate
>record and to see it with all others that don't fall in those of result
>set... You know how Locate works - it points out on the first located
>record with all others staying visible... Yes I know this is against SQL
>servers rules, but I just wonder if there is any mechanism that can do
>this on the client side :)

>Regards.

Team

Delphi Developer

Sat, 16 Aug 2003 00:34:53 GMT

Re:Locate record from SQL table

TQuery does have a locate method - see TQuery.Locate

TClientDataset enables local indexing of TQuery result sets.

Take a look at TClientDataset.FindKey

=Bill

Other Threads

1. How to locate a record in a query or table

2. 'Locate'ing a record in a table

3. Locating a record in table ??

4. Locate dosn't find an existing record in an Access table

5. Locate function locates on wrong record first time

6. Locate function locates on wrong record first time

7. Locating records in a parallel table

8. 'Record not found' with table.Locate

9. SQL, Locate, DBGrid and large tables

10. Locate method - no current record / no record found