[egenix-users] Hi all, bug or feature?

[M.-A. Lemburg]

On 2007-05-17 13:05, Rafael Pinilla wrote:
> hi all.
> 
> i'm in the process to make SQL requests in an automatic way. Right now,
> the data are in a Sybase SQL anywhere 7 database providing native ODBC
> driver as System DSN on Win32 platform.
> 
> The base is quite complex, with more than 20 tables and more than
> 800.000 records in one table, 5 years old, with integrity reference
> checking from beginning.
> 
> I installed mxODBC, with test license, and I tried to reach my data.
> 
> After I figured out how to firt create an instance of the connexion,
> then an instance of the cursor, I tried hard coded SQL statement.
> 
> I gracefully had a sequence of tuples result. As I wanted to count some
> results, i tried cursor.rowcount() Afortunately the results were very
> different from direct SQL based request (like isql statements).
> 
> A deeper look showed me that the sequence of tuples were right until the
> last row, and then, a variable amount of null tuples were added.

You should be getting None returns from cur.fetchone() if you fetch
beyond the end of the result set.

> So, cursor.rowcount return the good number of rows, but there are more
> empty (null) rows than expected. I really can't figure why.
> 
> I found a workaound using
>     i=1
>     courant = list(cur.fetchone())
>     while courant[1] <> () :

I'm not sure what the above line is supposed to do. courant[1]
should be a value from a result set row and that can never be
a tuple.

>         print i
>         courant = cur.fetchone()
>         print courant
>         i = i+1
> 
> In these lines, I convert the tuple in a list to let python make a test
> on the first (second, yes I figure) item. This is a right
> cursor.rowcount workaround for suspicious dummies.
> 
> So, this bug dont stop me. Perhaps is it a bug, perhaps an unknown
> feature. I can give more information, perhaps a shelve of the resulting
> tuple. Ask me if you want. Perhaps the cursor.rowcount method should
> make a test to stop inserting null tuples at the end of the sequence of
> (good) tuples.

Please try to use cur.fetchall() which will return the complete
result set and see whether that makes a difference.

Note that some ODBC drivers don't behave well when you try
to fetch beyond the end of the result set. mxODBC has no
means of checking this, since it relies on the ODBC driver
to return a proper value for "no more data available".

Also note that .rowcount doesn't always work with ODBC drivers,
or depends on settings in the database or the driver. The reason
is that calculating the exact number of rows in a result set is
rather expensive and often drivers and/or databases fetch the
results in an on-demand way.

You can use the mx/ODBC/Misc/test.pyc script to check whether
a database driver has problems with all of the above or not.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 17 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611