Getting a Record Count...

Michael Mount

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Getting a Record Count...

Hi all,
I'm curious if anyone knows of a good method of getting a current record
position and record count from a TQuery. I'm using D3 with SQL Anywhere
and want to avoid the TQuery.RecordCount (in case it's huge!). Thus far
I've gotten screamingly fast response by doing a "SELECT count(*) FROM
sametable WHERE samecriteria" but this gets UGLY as the original SELECT
statement gets uglier. Also, other than counting where I was, am, and
will be, I haven't found a way to get the current position within the
dataset (like VB's AbsolutePosition method), TQuery.RecNo always gives
me -1 (which, although it is a lovely number, I don't find particularly
useful). Thanks in advance - Mike.

Derek Davidso

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting a Record Count...

Michael

Quote

> I'm using D3 with SQL Anywhere and want to avoid the TQuery.RecordCount
> (in case it's huge!).

If you're using standard Delphi, this is one of only two options which I
know works. However, BEWARE. RecordCount isn't always correct when
used with a TQuery (in fact, it's wrong often enough to be useless). The
only way I've found to get consistent results with RecordCount is to issue
a .Last method call(which forces Delphi to read the entire query, row by
row and, as you say, can slow things down a great deal).

Quote

> Thus far I've gotten screamingly fast response by doing a "SELECT
> count(*) FROM sametable WHERE samecriteria" but this gets UGLY as the
> original SELECT statement gets uglier.

SELECT COUNT(*) is the right way to go, I'm afraid

Quote

> TQuery.RecNo always gives me -1 (which, although it is a lovely number,
> I don't find particularly useful). Thanks in advance - Mike.

That's because there is no concept of RecNo in SQL. It's there only for
databases like dBase.

Derek Davidson
Author of DK's Audit Components
Get a FREE copy from my web site at :
http://freespace.{*word*269}.net/d.davidson

(Remove the x to EMail me : der...@mksoft.com)

Michael Moun

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting a Record Count...

Quote

Derek Davidson wrote:
> If you're using standard Delphi, this is one of only two options which
> I
> know works.

I'm using Delphi 3 Professional - Is there another option?

Quote

> SELECT COUNT(*) is the right way to go, I'm afraid

I see your thread post in a message furthur down the list, so I won't
ask you how to start a new thread when executing the Count(*), but (with
one "T"), do you think it would be better to execute this on a thread,
rather than in-process as I am now - the "Test" query returned a count
of 44,000 records in a "might-as-well-have-been-instantaneous" time
frame but will that degrade with complex joins and selection criteria?

Quote

> Derek Davidson

Andrew Holland

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting a Record Count...

This is a multi-part message in MIME format.
--------------DBF99C0F16A23D142975DFF2
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Quote

Derek Davidson wrote:
> > TQuery.RecNo always gives me -1 (which, although it is a lovely number,
> > I don't find particularly useful). Thanks in advance - Mike.

> That's because there is no concept of RecNo in SQL. It's there only for
> databases like dBase.

Not entirely true. The TQuery.Recno value is for flat file databases,
true. However, each database vendor ususally has some support for this
sort of thing. In Oracle, there are "pseudocolumns" you can query
upon. ROWNUM will return you the sequential count of a row within the
current query set. Ie, to get the first 10 records that match a
query, use AND ROWNUM < 10 or to set unique id number in a table use
SET someidcolumn = ROWID in an update statement. Note that ROWNUM is
relative to the current query only, not to the position of the record in
the physical db table. Thus, you can not use it in the WHERE part of a
update statement for example. But if you know you will be getting 100
rows
and your current rownumber is 50, its a good bet you are half way
through
your query result. Also of note is that ROWNUM values are assigned
*before*
an ORDER BY affects the sort of a query, which may or may not help
you...

The other is ROWID. This returns a *unique* id value for a particular
Row in the databaes table. it returns a string in the form of :

XXXXXXX:YYYYYY:ZZZZZ

where ZZZZ is the physical datafile index (usually 1) and
XXXXXXX is a datablock in that file and YYYYY is the row within that
datablock. These are HEX formatted values. Again, I would not
recommend using
ROWID in the WHERE part of an update clause, but if you want to know
where this
record fits in the overall picture of the database table, there is a way
to find
out...

Consider this ROWID value: 0000000F.0000.0002 The row corresponding to
this
ROWID is the first row (0000) in the fif{*word*249}th data block (0000000F) of
the second data file (0002).

I am not a user of SQLServer, so I can not comment on similar features
of that engine.
The above columns are non-ANSI (to the best of my knowledge).
--------------DBF99C0F16A23D142975DFF2
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Andrew Hollands
Content-Disposition: attachment; filename="vcard.vcf"

begin: vcard
fn: Andrew Hollands
n: Hollands;Andrew
email;internet: andr...@cnw.com
x-mozilla-cpt: ;0
x-mozilla-html: FALSE
version: 2.1
end: vcard

--------------DBF99C0F16A23D142975DFF2--

Derek Davidso

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting a Record Count...

Andrew

I recalled that Oracle had such a feature (non-ANSI) but it still
doesn't work like a RECNO as you quite rightly stated. In any event,
the value would not appear in the RecNo property as requested by the
thread creator and that's what my answer was directed at.

Derek Davidson
Author of DK's Audit Components
Get a FREE copy from my web site at :
http://freespace.{*word*269}.net/d.davidson

(Remove the x to EMail me : der...@mksoft.com)

Derek Davidso

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting a Record Count...

Michael

Quote

> I'm using Delphi 3 Professional - Is there another option?

Delphi 3 Professional is fine. By standard Delphi, I meant the vanilla
version without 3rd party libraries.

Quote

> Do you think it would be better to execute this on a thread,
> rather than in-process as I am now

No. Due to the extra complexity of threads and synchronization, I'd do it
in-process

Quote

> the "Test" query returned a count
> of 44,000 records in a "might-as-well-have-been-instantaneous" time
> frame but will that degrade with complex joins and selection criteria?

Yes - most certainly.

Derek Davidson
Author of DK's Audit Components
Get a FREE copy from my web site at :
http://freespace.{*word*269}.net/d.davidson

(Remove the x to EMail me : der...@mksoft.com)

Other Threads

1. Getting record count of detail table

2. getting the count of records in a filtered query

3. Record count excluding deleted records

4. Delphi 4 + Native Ms Access 97 driver + Filtered dataset + record count/record number

5. How to get Record Count of a Detail Record in DBGrid

6. presenting records while getting records...

7. Getting a count of the GROUP BY

8. Getting word count from word using Delphi

9. Getting Count of Log Items in a given year

10. IB5.6 - Getting count of active transactions