Solved

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

Posted on 1998-09-16
13
832 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
Comment Utility
Adjusted points to 300
0
 
LVL 4

Expert Comment

by:itamar
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 4

Expert Comment

by:itamar
Comment Utility
And what about GetBookmark ?
0
 

Author Comment

by:greendot3
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi green,

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

0
 

Expert Comment

by:ugo072998
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

10 Experts available now in Live!

Get 1:1 Help Now