[DB-SIG] Integer problem with mxODBC & unixODBC

[M.-A. Lemburg]

M.-A. Lemburg wrote:
> 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.

BTW, I think that in this particular case it's the FreeTDS ODBC
driver which is not working right: the driver seems not to
support all ODBC SQL_C_* type codes. Most interesting is that
it doesn't support the two signed types used by mxODBC to fetch
integer data: SQL_C_SSHORT and SQL_C_SLONG.

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

def converter(position, sqltype, sqllen):
     # modify sqltype and sqllen as appropriate
     return mx.ODBC.unixODBC.SQL.VARCHAR, 25

# Now tell the cursor to use this converter:
cursor.setconverter(converter)

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