Skip site navigation (1) Skip section navigation (2)

Re: VB6 and RowCount fix

From: "Campbell, Greg" <greg(dot)campbell(at)us(dot)michelin(dot)com>
To: Ludek Finstrle <luf(at)pzkagis(dot)cz>
Cc: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>, pgsql-odbc(at)postgresql(dot)org
Subject: Re: VB6 and RowCount fix
Date: 2005-12-18 20:51:52
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-odbc
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.

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

Attachment: greg.campbell.vcf
Description: text/x-vcard (241 bytes)

In response to

pgsql-odbc by date

Next:From: Alejandro D. BurneDate: 2005-12-18 23:20:21
Subject: Re: psqlODBC 8.01.0105 Development snapshot
Previous:From: Ludek FinstrleDate: 2005-12-18 20:20:49
Subject: psqlODBC 8.01.0105 Development snapshot

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group