Re: pyodbc returns MS SQL Server money type as float

I added the following lines to /etc/freetds/freetds.conf:

# A typical Microsoft server
[MyServer]
	host = my.dbhost.local
	port = 1433
	tds version = 8.0

I then ran the following python program:


import pyodbc

cx = pyodbc.connect('SERVERNAME=MyServer;DATABASE=myDB;UID=user;PWD=pwd;DRIVER={FreeTDS}')
results = cx.execute('SELECT Amount FROM Transactions WHERE TransactionID=1')
rows = results.fetchall()
print type(rows[0][0])


The result was:

<type 'float'>


The Transactions.Amount field is an MS SQL Server 2000 money field.
If I run the same program, slightly modified, from Windows:


import pyodbc

cx = pyodbc.connect('SERVER=my.dbhost.local;DATABASE=myDB;UID=user;PWD=pwd;DRIVER={SQL
Server}')
results = cx.execute('SELECT Amount FROM Transactions WHERE TransactionID=1')
rows = results.fetchall()
print type(rows[0][0])


The result is:
<class 'decimal.Decimal'>


I am fairly confident that this means that FreeTDS running with TDS
protocol version 8.0 with unixODBC is incorrectly returning a 'float'
instead of a 'Decimal'.  Can anyone confirm that this is the expected
behavior?  If so, I'll just continue to work around the limitation.

Thanks,
Mike



On Mon, Aug 2, 2010 at 10:28 AM, Michael Wolfe
<[email protected]> wrote:
> I enabled tracing as suggested.  Here is an excerpt which appears to
> show that the Decimal format of the db field is being read correctly:
>
> [ODBC][24827][SQLDescribeCol.c][474]
>                Exit:[SQL_SUCCESS]
>                        Column Name = [TwpPenalty]
>                        Data Type = 0x7ff6554f77fe -> 6
>                        Column Size = 0x7ff6554f77f0 -> 19
>                        Decimal Digits = 0x7ff6554f77fc -> 4
>                        Nullable = 0x7ff6554f77fa -> 0
>
> TwpPenalty is an MSSQL money field.  I looked in my
> /etc/freetds/freetds.conf file and the tds version was set to 4.2.  I
> changed that to 8.0, but I don't think the change has taken effect.  I
> added the following lines to the freetds.conf file:
>
> [global]
>        # TDS protocol version
> ;       tds version = 4.2
>        tds version = 8.0
>
>        # Whether to write a TDSDUMP file for diagnostic purposes
>        # (setting this to /tmp is insecure on a multi-user system)
>        dump file = /tmp/freetds.log
>        debug flags = 0xffff
>
>
> The problem is that I don't think these changes are taking effect
> because I am not getting a freetds.log file in my tmp folder.  What do
> I need to do for my changes to the freetds.conf file to take effect?
> I've tried setting environment variables:
>
> FREETDS=/etc/freetds/freetds.conf
> TDSVER=8.0
> TDSDUMP=/tmp/freetds.log
>
> But these don't seem to have any effect.  I've stopped and restarted
> apache2 and that seems to have no effect.  This is getting really
> frustrating.  What am I missing?
>
> Thanks,
> Mike
>
> On Mon, Aug 2, 2010 at 5:22 AM, Frediano Ziglio <[email protected]> wrote:
>> 2010/7/30 Michael Wolfe <[email protected]>:
>>> I'm connecting to a SQL Server 2000 database via pyodbc and FreeTDS.
>>> I am running a query that returns the sum of a money column.  The
>>> result is being returned by pyodbc as a float, not a python Decimal.
>>>
>>> When I run the exact same python code and query against the exact same
>>> MSSQL database from Windows (ie, not using FreeTDS), pyodbc returns a
>>> python Decimal.
>>>
>>> There are obviously many differences between the two operating system
>>> environments, but I believe the central issue is with FreeTDS.  It is
>>> entirely possible that I have not configured things quite right.
>>>
>>> I am curious if this is a known limitation of FreeTDS or if I have
>>> just failed to configure something properly.
>>>
>>> Please note my comfort zone is with Windows and VBA, not Linux and
>>> python....so be gentle.
>>>
>>> Thanks in advance for any insights.
>>>
>>> -Mike
>>
>> It could depend also on DM. FreeTDS ODBC support bigints but perhaps
>> some mix between FreeTDS/DM/pyodbc make python believe is better to
>> use a float. It would be helpful to have traces in Windows and in
>> Linux. I don't know which DM you have under Linux, I use unixODBC and
>> in my /etc/odbcinst.ini I have these lines.
>>
>> [ODBC]
>> Trace      = Yes
>> TraceFile  = /tmp/sql.log
>> ForceTrace = Yes
>>
>> (obviously is better to disable tracing in production).
>>
>> Regards
>>  Frediano Ziglio
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected]
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>