TQuery.RecordCount returns -1

[Attila Krüzsely]

I'm selecting a row from a table using a TQuery (SelectItem) :

SelectItem.Close;
SelectItem.ParamByName('p_id_item').AsInteger := id_item;
SelectItem.Open;
SelectItem.First;

After these I got the row I wanted in the SelectItem (and I can access
the values through SelectItem.FieldValues[]), but :

- SelectItem.RecordCount returns -1 !
- and SelectItem.Eof returns FALSE

I have to rely on the RecordCount property in order to do the error
handling in my program.
Does anybody know why RecordCount returns -1 ?

I'm using D4 and Oracle 8.0.5.

Thanks

[Philip Cain]

Attila Krüzsely <krue...@attila.ch> wrote:

>- SelectItem.RecordCount returns -1 !
>- and SelectItem.Eof returns FALSE
>
>I have to rely on the RecordCount property in order to do the error
>handling in my program.
>Does anybody know why RecordCount returns -1 ?
>
>I'm using D4 and Oracle 8.0.5.

Attila,

This is a combination of a "feature" of relational databases and some
efficiencies by Delphi.

The SQL SELECT statement is a function that produces a set of ordered
values that contains an indeterminate number of members. What that
means is that the dataset you get from SELECT is not a physical file.
It's just a set of stuff. Because it's not physically defined in the
database and exists only because of the SELECT statement, the database
doesn't know how many records are in it. It can find out how many if
you also run a separate query using SELECT COUNT(*) and the same
constraints, but there's no other way for sure of knowing otherwise.

In such a case, Delphi provides what support it can. What it knows for
certain after a SELECT is whether there are records or not in the
dataset and so it sets RecordCount to zero if there are no records and
-1 if there is at least one record. You can depend on that.

But Delphi is a little smarter. When you first run the query, the
count is unknown and the data cursor is positioned at the first
record. As you go through the dataset (e.g. TQuery.Next), Delphi keeps
count. If you use a method like TQuery.Last, then look at the
RecordCount, you'll see that the value is now the count of records.

This happens automatically (most of the time) when you connect the
query to a grid. Because Delphi must populate the grid, it has to scan
the dataset and so Delphi keeps count. Very often you'll see that a
dataset connected to a grid shows an accurate count in RecordCount but
a dataset connected to a scalar control like TDBEdit does not. That's
because Delphi doesn't have to scan the dataset to put a value in
TDBEdit.

But this scanning takes time and work by the database. And so does an
extra query with SELECT COUNT(*). Many times you don't need to know
the exact count, you just need to know if there are any records in the
dataset. In that case
if TQuery.RecordCount <> 0 then
{there's something there)
is sufficient and a lot faster than doing a count.

You have to be the judge of what you need and code accordingly.

Phil Cain

--

[Jeff Overcash (TeamB)]

SQL backends don't always return RecordCounts so that is not reliable with
anything but pDox and dBase tables. You can try calling last and then first and
see if the record count is correct then. That works for some SQL db's.
Alternatively is to have a second querry that only counts the result set for
your main SQL.

Also asking Oracle question in the database.sqlservers will find many more
Oracle users than in the desktop group since it is a SQL server DB and not a
file based one.

Attila Krüzsely wrote:
>
> I'm selecting a row from a table using a TQuery (SelectItem) :
>
> SelectItem.Close;
> SelectItem.ParamByName('p_id_item').AsInteger := id_item;
> SelectItem.Open;
> SelectItem.First;
>
> After these I got the row I wanted in the SelectItem (and I can access
> the values through SelectItem.FieldValues[]), but :
>

> - SelectItem.RecordCount returns -1 !
> - and SelectItem.Eof returns FALSE
>
> I have to rely on the RecordCount property in order to do the error
> handling in my program.
> Does anybody know why RecordCount returns -1 ?
>
> I'm using D4 and Oracle 8.0.5.
>

> Thanks

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
If there is somebody up there could they throw me down a line. Just a
little helping hand just a little understanding. Just some answers to the
questions that surround me now. If there's somebody up there could
they throw me down a line. (Fish)
--

[Attila Krüzsely]

Philip, Cain,

I did as you suggested : called .Last and then .First, .RecordCount now
seems to hold the right value !

Thanks a lot !

Attila

[John Elrick]

Record count is not accurate until the entire dataset has been read.

Try:

> SelectItem.Close;
> SelectItem.ParamByName('p_id_item').AsInteger := id_item;
> SelectItem.Open;

SelectItem.Last;
> SelectItem.First;

John Elrick
jo...@improgrammer.com

Attila Krüzsely <krue...@attila.ch> wrote in message
news:37568898...@attila.ch...