[egenix-users] Datetime field overflow

Izzet Ergas Izzet.Ergas at citrix.com
Wed Aug 25 14:07:39 CEST 2010


Thank you. That explains it.

One problem with your workaround though. The second field is represented as a float in the DateTime module so the round() function doesn't help. From what I've gathered this is because many decimal values (like 44.44 for example) cannot be represented exactly as a float. Even using printf to convert to a string and concatenating the value to two decimal places won't work because the result will be changed back to a float in the DateTime object.

datetime.second = 44.440000000002328
round(datetime.second,2) = 44.439999999999998


-----Original Message-----
From: M.-A. Lemburg [mailto:mal at egenix.com] 
Sent: Wednesday, August 25, 2010 11:50 AM
To: Izzet Ergas
Cc: 'egenix-users at egenix.com'
Subject: Re: [egenix-users] Datetime field overflow

Izzet Ergas wrote:
> I'm getting this error when querying a MS SQL Server 2005 instance with the SQL Native client using mxODBC 3.0.4.
> 
> Error: 22008, 0, [Microsoft][SQL Native Client]Datetime field overflow, 7748
> 
> I don't always get the error, it only seems to happen for some values passed to the query. I think it is somehow related to the fact that a mx.DateTime object is being passed to the query and somehow being converted to a SQL datetime field in an improper manner.
> 
> In the query below SessionIdTime is a datetime field in SQL and I'm passing last_record[0] which is a mx.DateTime object. Not sure how to workaround this issue.
> 
> Any ideas?
> 
> sql = """
> SELECT
>     sd.SessionIdTime, sd.SessionIdSeq
> FROM SessionDetails sd
> WHERE sd.SessionIdTime > ?
>     OR (sd.SessionIdTime = ? AND sd.SessionIdSeq > ?)
> """
>  db = mx.ODBC.Windows.DriverConnect('DSN=OCS')
>  conn = db.cursor()
>  conn.execute(sql, (last_record[0], last_record[0], last_record[1]))

This problem is related to the way the SQL Native Server ODBC drivers
handles timestamps. mxODBC will send the data to the ODBC driver in
the native ODBC format for timestamps:

http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/ac1b5a6d-5e64-4603-9c92-b75ba4e51bf2/

If the timestamp contains a seconds fraction that cannot be
represented with the database field type, an error is raised
(since this causes data to be lost during INSERT).

As a result, whether or not you are getting the error depends
on the seconds fraction value. The only way around this is
to make sure that the fractional value can be represented
in SQL Server's datetime type.

Here's a helper for doing that:

from mx import DateTime

def round_seconds(datetime, digits=2):
    return DateTime.DateTime(datetime.year,
                             datetime.month,
                             datetime.day,
                             datetime.hour,
                             datetime.minute,
                             round(datetime.second, digits))

For egenix-mx-base 3.2, we'll have a .rebuild() method
on DateTime objects to make this easier (much like the
one we already have on mxURL objects).

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Aug 25 2010)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/



More information about the egenix-users mailing list