[egenix-users] Datetime field overflow
mal at egenix.com
Wed Aug 25 21:18:57 CEST 2010
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)
An alternative work-around would be using Python datetime.datetime
objects, since these store the second fraction as microseconds
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 which is a mx.DateTime object. Not sure how to workaround this issue.
>> Any ideas?
>> sql = """
>> 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, last_record, last_record))
> 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:
> 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,
> 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).
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
More information about the egenix-users