Re: VB6 and RowCount fix

-----Original Message-----
From: Ludek Finstrle [mailto:luf@pzkagis.cz]
Sent: Sun 12/18/2005 1:06 AM
To: Dave Page
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] VB6 and RowCount fix
=20
> I disagree. I try your example even againist MS SQL and it still returns
> -1. The test suite for VB6 RowCount you sent me doesn't work at all.

The test app works perfectly well - look at the source code; all it does is=
report the value from ADODB.Recordset.RowCount. What is not clear, is whet=
her that ever worked for any data provider. I always use the "while not rs.=
EOF..." approach when I wrote VB code in the past.

Regards, Dave


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: VB6 and RowCount fix

> > I disagree. I try your example even againist MS SQL and it still returns
> > -1. The test suite for VB6 RowCount you sent me doesn't work at all.
>
> The test app works perfectly well - look at the source code; all it does

I took a look at t source code. But I don't know VB.

> is report the value from ADODB.Recordset.RowCount. What is not clear,
> is whether that ever worked for any data provider. I always use the
> "while not rs.EOF..." approach when I wrote VB code in the past.

I think the question is how VB get RowCount value. It seems to me that
VB counts it internaly from fetched rows.

I close this chapter now if noone proof the opposite.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: VB6 and RowCount fix

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

I am coming late to this thread.
The issue for RowCount is not in VB, but always in ADO regardless of language, although the overwhelming
majority is in VB.
The key to rs.RowCount behaviour is the type of recordset cursor you are opening.
For example
rs.CursorLocation = adUseClient
rs.Open conn, sql, adOpenStatic
debug.print rs.RowCount

will pull all the records into a client side recordset.
while

rs.CursorLocation = adUseServer
rs.Open conn, sql, adOpenForwardOnly
debug.print rs.RowCount

will attempt to open a server side cursor,
When first opened the recordset will NOT know how many rows there are (rs.RowCount = -1).
You have to iterate through a server side or ForwardOnly cursor to get a recordcount. The count may not be
accurate until the end of the recordset.

For small recordset, clearly the client side processing is better.
For large sets, like table access to millions of records, I am not sure on what is the best approach. I
typically try to redefine the approach, in those situations. I make everything I can a detached client
side recordset.


I am unclear on what calls the ADO will make to the ODBC driver, and if DECLARE/FETCH will be triggered
based on the pgodbc setup.

The only thing that should reliable in any case is rs.EOF.

I have not seen your sample VB code, but things like this make a huge difference. Again, I am late in
having this thread grab my attention. If my remarks are off-base, please ignore then and forgive me.






Ludek Finstrle wrote:
>>>I disagree. I try your example even againist MS SQL and it still returns
>>>-1. The test suite for VB6 RowCount you sent me doesn't work at all.
>>
>>The test app works perfectly well - look at the source code; all it does
>
>
> I took a look at t source code. But I don't know VB.
>
>
>>is report the value from ADODB.Recordset.RowCount. What is not clear,
>>is whether that ever worked for any data provider. I always use the
>>"while not rs.EOF..." approach when I wrote VB code in the past.
>
>
> I think the question is how VB get RowCount value. It seems to me that
> VB counts it internaly from fetched rows.
>
> I close this chapter now if noone proof the opposite.
>
> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--------------080400070102060102030703
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------080400070102060102030703
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--------------080400070102060102030703--

Re: VB6 and RowCount fix

> I think the question is how VB get RowCount value. It seems to me that
> VB counts it internaly from fetched rows.


From my past experience with VB and Access, I'd say that MS option was to=
=20
update RowCount in a passive way, depending on the program travel behavio=
ur=20
and driver capabilities.
In the VB6 help information, MS isn't very clear:

- For RDO, it says 'RowCount doesn't indicate how many rows will be retur=
ned=20
by an rdoResultset query until all rows have been accessed. After all row=
s=20
have been accessed, the RowCount property reflects the total number of ro=
ws=20
in the rdoResultset. Referencing the RowCount property causes RDO to=20
fully-populate the result set - just as if you had executed a MoveLast=20
method.' ;

- For ADO, it says 'If the Recordset object supports approximate position=
ing=20
or bookmarks-that is, Supports (adApproxPosition) or Supports (adBookmark=
),=20
respectively, returns True-this value will be the exact number of records=
in=20
the Recordset regardless of whether it has been fully populated. If the=20
Recordset object does not support approximate positioning, this property =
may=20
be a significant drain on resources because all records will have to be=20
retrieved and counted to return an accurate RecordCount value.';

- For DAO, it says 'The RecordCount property doesn't indicate how many=20
records are contained in a dynaset-, snapshot-, or forward-only-type=20
Recordset object until all records have been accessed. Once the last reco=
rd=20
has been accessed, the RecordCount property indicates the total number of=
=20
undeleted records in the Recordset or TableDef object. To force the last=20
record to be accessed, use the MoveLast method on the Recordset object. Y=
ou=20
can also use an SQL Count function to determine the approximate number of=
=20
records your query will return'.

I never considered RowCount to be accurate before a MoveLast or a sequenc=
e=20
of MoveNext calls reaching EOF, because of the apparent DAO inconsistency=
..
(There was also another curious MS feature that I learned to live with an=
d=20
later gave me some headaches when I began using PostgreSQL... After openi=
ng=20
some kind of non-empty recordset, RowCount would always be > 0, although =
not=20
necessarily accurate).

Considering the options, and in the absence of a formal rule, I'd be happ=
y=20
with a documented behaviour like:

- After opening a recordset, EOF signals if the recordset is empty or not=
;
- RowCount always returns an accurate value, either because all rows have=
=20
been already accessed by MoveLast or a sequence of MoveNext calls reachin=
g=20
EOF or because in the absence of that information RowCount actively fetch=
es=20
ALL rows before returning.



H=E9lder M. Vieira






---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster