Re: pyodbc returns MS SQL Server money type as float

Fixes in CVS head, odbc returned SQL_FLOAT instead of SQL_DECIMAL

freddy77

2010/8/2 Michael Wolfe <[email protected]>:
> 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
>>>
>>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds