Board index » delphi » How to locate a record in a Query?

How to locate a record in a Query?

I am using Delphi 1.0 to write a database app.  The program will insert
records into an underlying database table that is viewed using a TQuery
component.  My problem is this: Once a record is inserted into the
table, I need to move to it in the query result set.  I can't insert
directly into the query since the query includes an ORDER BY statement
that sets the live result set to False.  While TTables allow you to
locate records quickly using the SetKey and GotoNearest methods, this
isn't possible with TQuery.

I have resorted to a brute force approach that involves moving to the
top of the query record set and individually polling each record to see
if it's the one I want (moving to the next one if it isn't).  This seems
stupid and is likely to be time consuming as the database grows to
appreciable size.

Can someone tell me a better approach?

Thanks,
Chris

 

Re:How to locate a record in a Query?


There ain't any better ways than brute force method, that you have already
discovered. D1 Local SQL does not have the Locate method. You'll have to
replace the Query with filters, ranges or something else, if the brute force
method isn't fast enough.

Markku Nevalainen

Quote
Christopher Boyd Rogers wrote:

> I am using Delphi 1.0 to write a database app.  The program will insert
> records into an underlying database table that is viewed using a TQuery
> component.  My problem is this: Once a record is inserted into the
> table, I need to move to it in the query result set.  I can't insert
> directly into the query since the query includes an ORDER BY statement
> that sets the live result set to False.  While TTables allow you to
> locate records quickly using the SetKey and GotoNearest methods, this
> isn't possible with TQuery.

> I have resorted to a brute force approach that involves moving to the
> top of the query record set and individually polling each record to see
> if it's the one I want (moving to the next one if it isn't).  This seems
> stupid and is likely to be time consuming as the database grows to
> appreciable size.

> Can someone tell me a better approach?

> Thanks,
> Chris

Re:How to locate a record in a Query?


Dear Chris,

You can add an autoincrement field in the table
 and use your query to order by that field.

Regards,
Kees Versnel.

Christopher Boyd Rogers heeft geschreven in bericht ...

Quote
>I am using Delphi 1.0 to write a database app.  The program will insert
>records into an underlying database table that is viewed using a TQuery
>component.  My problem is this: Once a record is inserted into the
>table, I need to move to it in the query result set.  I can't insert
>directly into the query since the query includes an ORDER BY statement
>that sets the live result set to False.  While TTables allow you to
>locate records quickly using the SetKey and GotoNearest methods, this
>isn't possible with TQuery.

>I have resorted to a brute force approach that involves moving to the
>top of the query record set and individually polling each record to see
>if it's the one I want (moving to the next one if it isn't).  This seems
>stupid and is likely to be time consuming as the database grows to
>appreciable size.

>Can someone tell me a better approach?

>Thanks,
>Chris

Re:How to locate a record in a Query?


If I were you I would buy the Woll2Woll components, they are REALLY GOOD
database components for Delphi1, 2 and 3....I think their homepage is
www.woll2woll.com or maybe their e-mail is woll2w...@woll2woll.com
Jens

Christopher Boyd Rogers <roger...@andrew.cmu.edu> skrev i artiklen
<Qp3L1=K00iWn07Z...@andrew.cmu.edu>...

Quote
> I am using Delphi 1.0 to write a database app.  The program will insert
> records into an underlying database table that is viewed using a TQuery
> component.  My problem is this: Once a record is inserted into the
> table, I need to move to it in the query result set.  I can't insert
> directly into the query since the query includes an ORDER BY statement
> that sets the live result set to False.  While TTables allow you to
> locate records quickly using the SetKey and GotoNearest methods, this
> isn't possible with TQuery.

> I have resorted to a brute force approach that involves moving to the
> top of the query record set and individually polling each record to see
> if it's the one I want (moving to the next one if it isn't).  This seems
> stupid and is likely to be time consuming as the database grows to
> appreciable size.

> Can someone tell me a better approach?

> Thanks,
> Chris

Re:How to locate a record in a Query?


Christopher Boyd Rogers (roger...@andrew.cmu.edu) wrote:
: I am using Delphi 1.0 to write a database app.  The program will insert
: records into an underlying database table that is viewed using a TQuery
: component.  My problem is this: Once a record is inserted into the
: table, I need to move to it in the query result set.  I can't insert
: directly into the query since the query includes an ORDER BY statement
: that sets the live result set to False.  While TTables allow you to
: locate records quickly using the SetKey and GotoNearest methods, this
: isn't possible with TQuery.

: I have resorted to a brute force approach that involves moving to the
: top of the query record set and individually polling each record to see
: if it's the one I want (moving to the next one if it isn't).  This seems
: stupid and is likely to be time consuming as the database grows to
: appreciable size.

you could make your query result to a permanent ttable you can apply
indexes to (this is the way I do it). have a look at the dbiprocs.int
and search for 'permanent'. for rather small (<500 kb) result sets, this
is a much better solution than your brute force algorithm. I do it this
way in my app, and it is pretty fast.

hth

stefan

--
-----------------------------------------------------
geb...@studserv.stud.uni-hannover.de

Toleranz ist das unbehagliche Gefuehl,
der Andere koennte am Ende vielleicht
doch recht haben.
                        Robert Forst

Other Threads