[DB-SIG] Integer Problems working with Python/mxODBC and MS SQL Server 7

M.-A. Lemburg mal@lemburg.com
Fri, 05 Apr 2002 16:03:06 +0200

ojaeger@psipenta.com wrote:
> Hello,
> currently I'm trying to access MSSQL 7 on WinNT from Python 2.1 on an Linux 2.4
> (Debian) machine (python 2.1.2/mxODBC 2.0.4/ unixODBC/ FreeTDS 0.53 Driver).
> Everything works fine, exept that any query of integer values presents strange
> results. I use the FreeTDS ODBC driver with protocol version 7.0.
> Here's what happens:
> >>> import mx.ODBC.unixODBC
> >>> db = mx.ODBC.unixODBC.DriverConnect("DSN=tralala;UID=hopsasa;PWD=anything")
> >>> c = db.cursor()
> >>> c.execute("SELECT id FROM test")
> >>> res = c.fetchall()
> >>> res
>    (8,0232304319377730,)
> >>>
> The value I expected to receive is 56819 and the datafield in SQL-Server 7 is
> defined as integer.

Looks like FreeTDS is passing the value as float to mxODBC.

To debug the mxODBC-driver connection, you'll have to build
a debug version and then run the script with 'python -d'. This
will then produce a file mxODBC.log which has all the communication

Building a debug version is easy:

python setup.py mx_autoconf --enable-debugging install

> I tried with TDS protocol version 7.0 and 4.2, but there ware no different
> results. Every other fieldtype, even text  or varchar works correctly.
> There is no problem when I do the same query with the unixODBC command line
> query tool 'isql'. All integers are shown correctly. So I think, that the pure
> ODBC Connection is ok an there must be something between python and mxODBC.
> BTW, when I query a MySQL database via mxODBC (also via unixODBC)  there were no
> problems with any data type.
> Any ideas, what can I do?

Marc-Andre Lemburg
CEO eGenix.com Software GmbH
Company & Consulting:                           http://www.egenix.com/
Python Software:                   http://www.egenix.com/files/python/