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


_______________________________________________________________________
eGenix.com User Mailing List                     http://www.egenix.com/
https://www.egenix.com/mailman/listinfo/egenix-users