[egenix-users] mx.ODBC error: Invalid Descriptor Index

Alexandre Fayolle alexandre.fayolle at logilab.fr
Fri Apr 16 09:46:33 CEST 2010


On Thursday 15 April 2010 19:03:02 M.-A. Lemburg wrote:
> Alexandre Fayolle wrote:
> > Hello Charlie,
> >
> > Thanks for you answer.
> >
> > On Thursday 15 April 2010 17:30:06 Charlie Clark wrote:
> >> Am 15.04.2010, 17:14 Uhr, schrieb Alexandre Fayolle>
> >>
> >>
> >> This looks like an incorrect application of the parameters. You *use* ?
> >> for parameters in this case. You must use string formatting to generate
> >> your SQL statement for "SELECT ?, ? FROM table" from your first example
> >> because you are passing variables to a statement and not parameters to
> >> the database which has already prepared the statement.
> >
> > Let me rephrase this. I was lazy, the call generating the exception was:
> >
> > datetime1= datetime.datetime(2010, 4, 15, 16, 51, 25, 468000)
> > datetime2 = datetime.datetime(2010, 4, 15, 16, 51, 25, 453000)
> > cursor.executedirect('UPDATE cw_CWUser SET cw_modification_date = ?,
> > cw_last_login_time = ? WHERE cw_eid = ?', (datetime1, datetime2, 5))
> >
> > The exception is  mx.ODBC.Error.DataError: ('22008', 0, '[Microsoft][SQL
> > Server Native Client 10.0]Datetime field overflow. Fractional second
> > precision exceeds the scale specified in the parameter binding.', 7748)
> 
> This explains why you are seeing the error: Python datetime
> instances use more than just 2 digits precision in their
> seconds string representation, i.e. more than what SQL Server
> expects when requesting timestamps as strings.
> 
> However, I wonder why SQL Server requests this data as string
> data, since it's well possible to use a native
> timestamp ODBC data structure for this, which also assures
> that full precision is maintained and no conversion takes place.
> 
> Could you try the above with cursor.execute() and see whether
> you still get the same error ?

I've made some significant progress on this yesterday evening: 

* I've switched back from SQL Server Native Client 10 (the one for MS SQL 
2008) to SQL Server Native Client (9?) (the one for MS SQL 2005), since the 
datetime precision issue was linked to the datetime2 datatype of SQL2008 :

  -  http://msdn.microsoft.com/en-us/library/bb964722%28SQL.100%29.aspx
  - http://social.msdn.microsoft.com/Forums/en-
US/sqldataaccess/thread/ac1b5a6d-5e64-4603-9c92-b75ba4e51bf2

I was using Native Client 10 because it solved some issues I had experiences 
with the previous driver + pyodbc. I don't have an SQL2008 db at hand to dig 
further into this, but maybe this is something you will want to investigate 
for your other users. 

* I've enclosed the cursor.execute statement in a try..except block which 
retries using cursor.executedirect if execute raised an exception

* I"ve tweeked a bit our DB abstraction layer for additional differences 
between pyodbc and mxODBC (mainly related to datatype mapping)

The only requests posing problems with cursor.execute in our application are 

* using ? in a SELECT list
* using ? in a sub request with EXISTS

So far, I've been able to avoid putting my hands in the SQL generator, but 
maybe I will need to do that. 

I'm currently running a test under the python profiler with mx and pyodbc to 
compare the run times, I'll send some figures here if I get interesting 
results. 

-- 
Alexandre Fayolle                              LOGILAB, Paris (France)
Formations Python, CubicWeb, Debian :  http://www.logilab.fr/formations
Développement logiciel sur mesure :      http://www.logilab.fr/services
Informatique scientifique:               http://www.logilab.fr/science



More information about the egenix-users mailing list