[DB-SIG] Integer problem with mxODBC & unixODBC

[Adam Israel]

The system:
Python 2.2.1 (#1, May  3 2002, 23:19:03)
Debian 3.0 (sid)/Linux 2.4.18-686-smp
egenix-mx-commercial-2.0.4
Unixodbc 2.1.1-8
FreeTDS 0.53-7

I'm attempting to connect to a MS SQL Server 2000 server running on
Windows 2000, and I'm seeing some problems returning Integer values.
I've googled, and only found one reference to this problem, posted to
the list a few months ago, but no resolution was posted.

I've tested this with isql, from python, and from c, and I'm reasonably
sure the problem lies with mxODBC.

Python code:
---- snip ----
#!/usr/bin/python2.2

import mx.ODBC.unixODBC

db = mx.ODBC.unixODBC.DriverConnect('DSN=DSNNAME;UID=USERID;PWD=PASSWD)
c = db.cursor()
sql = "select count(*) from Image"
c.execute(sql)
print c.fetchall()
c.close()
---- snip ----

Output:
$ python -d testConnection.py
query = select count(*) from Image
[(9.6917293728400134e-270,)]

The actual value returned should be: 2925279


Now, if I change this:
- sql = "select count(*) from Image"
+ sql = "select cast(count(*) as varchar) from Image"

Output:
$ python -d testConnection.py 
query = select cast(count(*) as varchar) from Image
[('2925279',)]

It seems like the problem is definitely with the handling of Integer
fields.  The workaround is to cast all integer columns to varchar, but
that's not very clean or efficient, IMO.

To isolate where the problem lies, I wrote a bit of C code against the
ODBC API, to execute the original query.  It returned the correct value.
I can send the file (~126 lines) if anyone is interested.

So I know that unixODBC/FreeTDS are working correctly, and that leaves
mxODBC.unixODBC.  Has anyone else experienced this problem and if so,
how did you fix it?  I'm sort of in a bind with this problem, and there
are not a lot of linux->ms sql server solutions around.  My choices at
this point are to find a fix for mxODBC, or to write my own python
module to wrap ODBC.

Thanks for any info,

Adam Israel
adam@battleaxe.net