[egenix-users] cursor.rowcount abnormalities and performance question

[stuff at mailzilla.net]

Hello,

When using mxODBC with the Windows SQL Server Driver I've noticed that 
when performing a query with executedirect the cursor object has a 
rowcount of -1 or 0 and nothing else.  I am using Python 2.2 and the 
mxODBC package that ships with BlackAdder.

-1 seems to be the initial value of rowcount before queries are performed
but I'm unsure as to why this would remain the value after a query
considering that the count should be >= 0, at least fundamentally.  In my
tests I have only seen values of 0 (when there are no records) and -1 when
there are some records-- in these cases, I am expecting this to be a
positive value representing the number of records that matched the query.  

If I do a fetch operation (i.e. fetchone) on the cursor that has a 
rowcount of -1 I do indeed retrieve records.  There seems to be a 
disconnect between rowcount and being able to fetch records.

Is SQL Server/ODBC sending over misleading info or is this a problem with 
mxODBC?

I generally use rowcount to determine whether I need to retreive 
records using fetchone inside a while loop (no need to execute the loop 
if rowcount is 0).  It's a little cleaner if I can reliably use rowcount 
but if the value doesn't sync with the actual result set then I can safely 
do something like this:

while 1:
   row = c.fetchone()
   if not row: break
   processTheRecord(row)

If rowcount can be relied upon, I could do this, which is much cleaner:

for i in range(c.rowcount):
   row = c.fetchone
   processTheRecord(row)


Performance question- Since I need to perform some manipulation on each 
record I believe it is more efficient to perform a while loop and 
fetchone rather than getting everything via fetchall and then iterating 
over that result (since theoretically, fetchall would be iterating to get 
each record so technically speaking that would require iterating twice).  
If this assumption is false, please let me know.  Perhaps there are some 
internal ODBC/mxODBC optimizations that allow fetchall to actually perform 
much faster than several fetchone's.  My concern is that if fetchall 
iterates through the result set to produce the list of rows and then I am 
iterating through the fetched list then I can accomplish the same goal w/ 
1 less iteration via a series of fetchone's.  Also, I imagine it would be 
safer to process each record individually then fetching the entire result 
set at once in terms of system resource utilization (i.e. if the result 
set were millions of records there would need to be data structs allocated 
to hold them).  

I'm curious to see if my reluctance to use fetchall is well-founded or
overly paranoid.

Thanks for any info,

Phil