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

[M.-A. Lemburg]

Jim Vickroy wrote:
> 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 ?
>>
>>  
>>>> The ODBC trace log is produced by the ODBC driver not by mxODBC.
>>>>       
>>> I was thinking of the mxODBC.log file mentionned in §4.7 of the
>>> mxODBC documentation. Is it possible to enable ODBC driver tracing
>>> when you don't go through the ODBC Data Source manager to connect to
>>> the database?     
>>
>> That depends on the driver you are using.
>>
>> For the mxODBC.log to be generated you need a special debug build
>> which we can provide if needed.
>>
>>   
> 
> 
> FWIW, I have applications that perform SQL Server (2003 and 2008)
> inserts using datetime.datetime instances as a matter of course
> (1-minute cadence 24x7).  -- jv

There's a little known fact about Python datetime instances:
they omit the fractions part if the microsecond value is 0.

>>> import datetime
>>> str(datetime.datetime(2010,4,15,0,0,0,1))
'2010-04-15 00:00:00.000001'

Precision is 6 if you provide microseconds.

>>> str(datetime.datetime(2010,4,15,0,0,0,0))
'2010-04-15 00:00:00'

Precision is 0 if the microsecond value is 0.

>>> str(datetime.datetime.now())
'2010-04-15 19:53:04.532707'

With .now() on Linux and Windows (and probably other
platforms as well) it's likely that you'll get a
precision 6 string value.

In any case, we'll try to reproduce the problem with our
test servers.

-- 
Marc-Andre Lemburg
eGenix.com

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