What's the recommended way to get the number of records?  So far I have:

query->SQL->Clear();
query->SQL->Add("SELECT COUNT(*) FROM table");
query->Active = true;
int count = query->???

Thanks!

In Delphi:-

qry.SQL := 'SELECT AnyField FROM Table";
qry.Open;
count := qry.RecordCount;
qry.Close;

OR

qry.SQL := 'SELECT COUNT(*) FROM table";
qryOpen:
count := qry.Fields[0].AsInteger;
qry.Close;


--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

<Matt Gregory> wrote in message
news:684FC070-6CE3-48CC-9C2A-03BAC115C72B@news.elevatesoft.com...
> What's the recommended way to get the number of records?  So far I have:
>
> query->SQL->Clear();
> query->SQL->Add("SELECT COUNT(*) FROM table");
> query->Active = true;
> int count = query->???
>
> Thanks!
>

Jeff Cook wrote:

> SELECT COUNT(*) FROM table

Me, I usually use a single field, in the hope that it reduces the
amount of data that must be looked at (and assuming that NULL is not
relevant), and then give it a name so I can access it in the usual way.
Thus:

SELECT COUNT(MyRecordID) AS MyRowCount FROM table

(I never actually use "My..." except in illustrations. In real tables,
I use a sort of hungarian notation. Every table is two words, like
"CustomerAccount" and every field in that table is caFirstName or
caCustomerID. This ensures that my SQL is always right and things like
joins are on "WHERE (caCustomerID = coCustomerID)" so I don't
accidentally join on "CustomerID = CustomerID" and get the wrong table.
But maybe that's just me...)

--

Matthew Jones

"Jeff Cook" wrote:

In Delphi:-

qry.SQL := 'SELECT AnyField FROM Table";
qry.Open;
count := qry.RecordCount;
qry.Close;

OR

qry.SQL := 'SELECT COUNT(*) FROM table";
qryOpen:
count := qry.Fields[0].AsInteger;
qry.Close;

I would like to use that second method, but the line (in C++):

qry->Fields[0].AsInteger

gives me an error:

E2316 'AsInteger' is not a member of 'TFields'

>"Matthew Jones" wrote:
>
>Jeff Cook wrote:
>
>> SELECT COUNT(*) FROM table
>
>Me, I usually use a single field, in the hope that it reduces the
>amount of data that must be looked at (and assuming that NULL is not
>relevant), and then give it a name so I can access it in the usual way.

Usually databases are optimized for COUNT(*) on a table.  If you call COUNT() on a particular field and it's nullable, then it has to scan through all the rows and count only the non-null ones.


>Thus:
>
>SELECT COUNT(MyRecordID) AS MyRowCount FROM table
>
>(I never actually use "My..." except in illustrations. In real tables,
>I use a sort of hungarian notation. Every table is two words, like
>"CustomerAccount" and every field in that table is caFirstName or
>caCustomerID. This ensures that my SQL is always right and things like
>joins are on "WHERE (caCustomerID = coCustomerID)" so I don't
>accidentally join on "CustomerID = CustomerID" and get the wrong table.
>But maybe that's just me...)

I like that convention!

I got it!  You have to say:

qry->Fields[0][0]->AsInteger

I guess the first index is for the row and the second is for the column.

Matt Gregory wrote:

> Usually databases are optimized for COUNT(*) on a table.  If you call
> COUNT() on a particular field and it's nullable, then it has to scan
> through all the rows and count only the non-null ones.

Useful to know. Thanks.

--

Matthew Jones