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

[M.-A. Lemburg]

Alexandre Fayolle wrote:
> 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. 

Thanks for those interesting links. This part appears to explain
the problem:

"""
Stricter SQL_C_TYPE _TIMESTAMP and DBTYPE_DBTIMESTAMP parameter validation.
	

Prior to SQL Server 2008 Native Client, datetime values were rounded to fit the scale of datetime
and smalldatetime columns by SQL Server. SQL Server 2008 Native Client now applies the stricter
validation rules that are defined in the ODBC core specification for fractional seconds. If a
parameter value cannot be converted to the SQL type by using the scale specified or implied by the
client binding without truncation of trailing digits, an error is returned.
"""

In other words, even if mxODBC uses the ODBC TIMESTAMP struct to pass
the data to the driver, it will still complain if the second fraction
value cannot be converted to the datetime accuracy without losing
trailing digits.

We will investigate this some more. The above driver behavior makes it
somewhat difficult to send fractional seconds to the database without
applying some kind of internal truncation in mxODBC in order to avoid
the mentioned error.

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

That's the same work-around that the new SQLAlchemy binding for
mxODBC is using with SQL Server Native Client.

> * 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. 

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Apr 16 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/