?
Solved

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

Posted on 1998-09-16
13
Medium Priority
?
984 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Suggested Courses
Course of the Month3 days, 22 hours left to enroll

601 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