[egenix-users] "right truncation" warning in mx.ODBC.Windows

[M.-A. Lemburg]

Steve Holden wrote:
> [mal]
> 
>>>datestmp = mx.DateTime.localtime(time.time())
>>
>>Try to use a string here: '2004-03-11 10:00:00' and
>>see whether that makes any difference (the ODBC driver could
>>be having trouble with the seconds fraction - even though
>>we've never heard of such problems with SQL Server before).
> 
> I'm sorry, I thought this was well-known (and I also thought I had
> reported it earlier. Maybe I invented that last piece of knowledge :-).

Probably just dropped off my horizon: looking at the list
archives, you are right. I wonder whether this only affects
certain versions of the MS ODBC driver ?!

If so, please provide us with the information from

connection.dbms_version
connection.dbms_name
connection.driver_name
connection.driver_version

We can then look into adding code to truncate the fraction in
timestamps for MS SQL Server ODBC drivers.

> The driver does indeed have problems with fractional seconds in datetime
> values. Here's a little test program that demonstrates the problem, and
> a rather gash fix that I've used in my own code. I'm sure Marc-Andre can
> suggest a more elegant workaround until the driver is fixed.

This should work:

from mx import DateTime

def msdate(datetime):
     int_second = int(datetime.second)
     if int_second != datetime.second:
         return DateTime.DateTime(datetime.year,
                                  datetime.month,
                                  datetime.day,
                                  datetime.hour,
                                  datetime.minute,
                                  int_second)
     else:
         return datetime

> By the nature of things, one time in however many mx.DateTime.now() will
> return a value without a fractional part and both inserts will work
> without any problems. Murphy's law dictates that this usually happens
> when you want to demonstrate the error!
> 
> #
> # mxdtdb.py: naff demonstration of datetime handling problem and
> workaround
> #
> import mx.ODBC.Windows as db, mx.DateTime as dt, traceback, sys
> 
> def doit(arg):
>     print "Trying", arg
>     try:
>         curs.execute("INSERT INTO TEST (dt) VALUES(?)", (arg,))
>         print "That seemed to work"
>     except:
>         print "Oops! We trod on the driver!"
>         print traceback.format_exception_only(sys.exc_type,
> sys.exc_value)
> 
> conn = db.DriverConnect("""Driver={SQL
> Server};Server=DellBoy;Database=PyCon;Uid=****;Pwd=****;""")
> curs = conn.cursor()
> 
> curs.execute("""
> CREATE TABLE TEST (
>     pk INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
>     dt DATETIME)""")
> 
> now = dt.now()
> doit(now)
> doit(dt.DateTimeFromTicks(int(now)))
> 
> # I could drop the table, but instead I just don't commit()
> 
> Output from my last run is shown below:
> 
> Trying 2004-03-11 07:57:03.88
> Oops! We trod on the driver!
> ["Warning: ('01004', 0, '[Microsoft][ODBC SQL Server Driver]Fractional
> truncation', 4579)\n"]
> Trying 2004-03-11 07:57:03.00
> That seemed to work
> 
> regards
> --
> Steve Holden                                 http://www.holdenweb.com/
> ReportLab Enterprise Publishing Solutions    http://www.reportlab.com/
> Chairman, PyCON DC 2004                          http://www.pycon.org/
> Telephone: +1-800 494 3119                        Fax: +1 703 278 8289
> 
> 

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Mar 11 2004)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
EuroPython 2004, Göteborg, Sweden                           87 days left

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::