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

[Steve Holden]

[mal]
> Charles Bearden wrote:
> > I'm using mx.ODBC version 2.0.1 (that's the version in
> > site-packages\mx\ODBC\ODBC.py; the "commercial" download
> file is version
> > 2.0.6) with ActivePython 2.3.2 Build 230 to talk to a MS
> SQL Server.  I
> > am having a puzzling right truncation problem when trying
> to insert even
> > small values into a table.  In order to isolate variables, I am
> > inserting values into only required (non-NULL) fields.
> INSERTing the
> > same values into the table works via Query Analyzer.  Here is sample
> > code, traceback, & the table definition.  Any ideas on how
> to make this
> > work would be greatly appreciated--thanks in advance!
> >
> > --------------------------begin snippet-----------------------------
> > import time
> > from mx.ODBC.Windows import DriverConnect
> > import mx.DateTime
> >
> > con = DriverConnect("DSN=theDb;UID=theUser;PWD=thePwd")
> > cur = con.cursor()
> >
> > 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 :-).
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.

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