Solved

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

Posted on 1998-09-16
13
921 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month6 days, 2 hours left to enroll

627 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