[egenix-users] Datetime field overflow

[M.-A. Lemburg]

Izzet Ergas wrote:
> 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

You have a point there. We should probably round to the nearest
nanosecond in mxODBC to get around that problem:

>>> int((44.44 - 44) * 1e9 + 0.5)
440000000

An alternative work-around would be using Python datetime.datetime
objects, since these store the second fraction as microseconds
integer.

In any case, we'll look for a solution.

Thanks for bringing this up.

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