Board index » delphi » Locate record from SQL table

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?

 

Re:Locate record from SQL table


Hi

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.

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?

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?

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?

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?

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.

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.

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