[DB-SIG] Integer problem with mxODBC & unixODBC

[M.-A. Lemburg]

Adam Israel wrote:
> 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.

Interesting that you are getting any results back from
the FreeTDS ODBC driver... I've looked into creating a subpackage
for it in mxODBC but failed due to the fact that the FreeTDS
ODBC has so many dummy implementations of important ODBC
APIs.

Note that mxODBC relies on the type information provided
by the ODBC driver. Tools like isql simply ask for the
string representation, which is why you are not seeing the
same output.

> 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,)]

This is a float... and that looks wrong, since count(*)
should normally return an integer.

> 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.

No need for that. You can use the converter function feature
in mxODBC to force fetching values using different types.

To fully debug the situation, please build a debug version of
mxODBC (see the docs) and create an mxODBC.log file with the
above script.

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
_______________________________________________________________________
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/