TQuery: How to get query record number different from physical record number

Posted on 1998-09-16
Last Modified: 2010-04-06
Hi all!

I have a TQuery, doing SQL onto local FoxPro files thru LocalSQL.

The problem is that when I check the TQuery.RecNo property, it tells me the physical record number.

But first a little side note.  This only happens when I set the TQuery.RequestLive property to TRUE.  That is when the .RecNo property reflects the physical records.  If the .RequestLive is false (the default) then the .RecNo reflects the position in the TQuery.  But, I need .RequestLive to be true because this has to be a live dataset.

I'm needing to know the record index of a live query..

I need this because I have a TList full of other temporary properties that tie to each element in a TDBgrid... owner draw stuff.

I have the option of making the TList fill up to the max size the query could get, but this table has millions and millions of records... but what is displayed is quite small and frequently changes, so rebuilding this massive TList is not on the top of my choices.


Question by:greendot3
  • 4
  • 4
  • 3
  • +2

Author Comment

ID: 1339925
Adjusted points to 300

Expert Comment

ID: 1339926
Hi greendot,

if you save the Bookmark property of each row that is of type TBookmark, you can use GoToBookmark method to access the desired row.

I hope this can helps somehow.


Expert Comment

ID: 1339927

it´s me again. Just a correction:

actually, bookmark is not a property, but you can get it calling the method GetBookmark
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Expert Comment

ID: 1339928
I'm not sure .... but try to go to the last record on the query then read the records number .RecNo :


I say I'm not sure !!. just try.

Motaz from Sudan.

Expert Comment

ID: 1339929
If i understand, Query.RecNo doesn't work?
I try SQL on FoxPro db with LiveResults=true and LiveResults=false, but everything was ok.
Put piece of your code, so I can look into it.


Author Comment

ID: 1339930

Query.RecNo does work, but just not what I want it to do.
if LiveResults=True then RecNo returns the physical record number.  So say if the next to last record in the query is physically located 2000 records away from the last record, that number shows the large jump.

But, if LiveResults=True then it seems the BDE returns a temporary table that only contains what you ask for so RecNo is indeed the sequence number.

And for those who are wondering WHY I am doing this.  :)  I have a DBGrid that is databound to this query, AND it has formatting information and some virtual fields stored in a TList.  So, when I am owner-drawing the DBGrid, I need to know which record in the query is being drawn.



Expert Comment

ID: 1339931
And what about GetBookmark ?

Author Comment

ID: 1339932
I'm a little fuzzy on how bookmarks work.
From what I remember reading, aren't they a way to remember
a location in a query, then be able to go back to that location

Do they do more?

I just need a way to find out what place I am at.. like 2nd record or 10000th record or the 5 millionth record.  I don't need to go to a particular record because I'm already there.

Then again, I may not fully understand what bookmarks do for me.

How would resources be if I had to apply 5 million bookmarks?




Accepted Solution

ugo072998 earned 300 total points
ID: 1339933
You can maintain a list reflecting the position you are in the grid (the result of your query)
simply adding to a StringList recordCount elements containing a key field from your dataset.
You can do that rolling on your query like :

MyQuery : TQuery;
list : TStringList;

with MyQuery do
      while not eof do

You can get your position in the dead dataset any time with something like :

function GetPos : longInt;
var i : integer;
   GetPos := -1;
   for i := 0 to (list.count - 1) do
      if list[i] = MyQuery.fieldByName(''KeyField').asString then
         GetPos := i + 1;

It will return the position in the stringList that exactly match the row position in the grid.
Of course if you delete/add records or edit the keyFiled in the dataSet you must update the stringList also.

If you think that the function GetPos should be too much expensive of resources because of the large numb. of recs you can define list as a SORTED stringList adding for any item :
1) the key field as string
2) the position in the loop in an object you must define
(this case you must use list.addObject instead of list.add)
By a call to list.find(keyField, i) you will have your position = i + 1.
Low level Delphi routines will work for you faster than everything we can write.

I've tried just the first way on Delphi 3.0 and it works fine.
Hoping that I could help you,

Expert Comment

ID: 1339934
Hi greendot3,
sorry I always forget something.
The loop (GetPos) building up the stringList can be executed on both cases of requestLive and doesn't change the result.
You don't need to run it on the very large database you were speaking about, just on the result of your query.
I've tried it in a simple app with a 70000 rec.s wide table, extracting about 2500 recs and it works fine.

Expert Comment

ID: 1339935
Hi green,

indeed I didn't get the point of your question. Sorry...


Expert Comment

ID: 1339936
About bookmarks,
Delphi help doesn't show so much about them, in a test app I've tried to understand what's a TBookmarklist.
Just I could find that it's possible to save and get bookmarks using a TstringList,
in fact they are something looking like a string, but their size it's larger than they seems.
Length(...) applied to a bookmark return something about 1340 chars but they binary encoded and so it's impossible  to compare them or apply any other kind of logical operator on strings.
I hope Borland/Inprise will do something to manage them better.


Author Comment

ID: 1339937
I ended up doing the stringlist thing.  That was my first choice before all of this started but I thought it would be too slow.  It wasn't that bad.  But, I'm on a 400mhz machine.. I wonder what it will be like on the spec machine that is 166.. heh. :)


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Making delphi communicate with a c# service 16 112
Delphi...Split view - idea? 1 90
Tvertscrollbox like a whatsapp layout delete messages 1 24
enhance the following code 3 37
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question ( here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question