Solved

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

Posted on 1998-09-16
13
850 Views
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.

Thanks!!

-greendot
0
Comment
Question by:greendot3
  • 4
  • 4
  • 3
  • +2
13 Comments
 

Author Comment

by:greendot3
ID: 1339925
Adjusted points to 300
0
 
LVL 4

Expert Comment

by:itamar
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.

Bye,
Itamar
0
 
LVL 4

Expert Comment

by:itamar
ID: 1339927
Hi,

it´s me again. Just a correction:

actually, bookmark is not a property, but you can get it calling the method GetBookmark
0
 
LVL 7

Expert Comment

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

Q.last;
RecordsCount:=Q.last

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

Motaz from Sudan.
motaz1@yahoo.com
0
 
LVL 2

Expert Comment

by:333
ID: 1339929
Hi,
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.

A.
0
 

Author Comment

by:greendot3
ID: 1339930
333,

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.

Thanks!

-j
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:itamar
ID: 1339931
And what about GetBookmark ?
0
 

Author Comment

by:greendot3
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
quickly?

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?

Thanks!

-gd


0
 

Accepted Solution

by:
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;

list.clear;
with MyQuery do
begin
      disableControls;
      open;
      while not eof do
      begin
         list.add(fieldByName('KeyField').asString);
         next;
      end;
      first;
      enableControls;
end;

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

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

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, burzox@tin.it
0
 

Expert Comment

by:ugo072998
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.
by
0
 
LVL 4

Expert Comment

by:itamar
ID: 1339935
Hi green,

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

0
 

Expert Comment

by:ugo072998
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.

0
 

Author Comment

by:greendot3
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. :)

-gd
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi selector screen 2 70
How to debug For loops? 3 46
CheckListBox usage 3 51
PHP preg_replace code convert to Delphi 14 36
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now