[egenix-users] cursor.executedirect configurable?

[M.-A. Lemburg]

On 2008-09-10 15:48, Brad Allen wrote:
> Thanks. I haven't yet done any serious performance comparisons between
> execute and executedirect with the ODBC driver we're using ( the
> built-in Windows ODBC driver for SQL Server).
> 
> Instead, I was motivated by the finding that some of the large join
> queries I generated using Storm worked with executedirect, but failed
> cryptically using plain cursor.execute. I would prefer to find out why
> the same query failed with cursor.execute rather than switch to
> executedirect.
> 
> Any ideas on what would cause this a select query to fail with the plain
> execute, but works fine with executedirect? 

The plain .execute() uses a prepare step, SQL type binding and
does some processing directly on the client, whereas .executedirect()
sends all data to the server for processing and uses Python type
binding for parameters.

> Here is the error:
> 
> mx.ODBC.Error.InterfaceError: ('07009', 0, '[Microsoft][ODBC SQL Server
> Driver]Invalid Descriptor Index', 6793)

Could also post the query and parameters that caused this problem ?

The error is related to a parameter you have in the query which
the client apparently does not detect correctly.

Thanks.

> Thanks.
> 
> M.-A. Lemburg wrote:
>> Hello Brad,
>>
>> On 2008-09-10 04:18, Brad Allen wrote:
>>  
>>> I am using mxODBC 3 with the Storm ORM, which makes use of
>>> cursor.execute. I would like to find a way to configure mxODBC to use
>>> executedirect without having to change or override Storm's call to
>>> cursor.execute.
>>>
>>> Is there some way to configure mxODBC to use the executedirect behavior
>>> when cursor.execute is called?
>>>     
>>
>> No, that's not possible without wrapping the cursor object and
>> then redirecting the method in the wrapper.
>>
>> Note that cursor.execute() will work like cursor.executedirect()
>> when called without parameters.
>>
>> You should check the performance of using one over the other.
>> cursor.executedirect() will bypass the prepare step, so the
>> statement will have to be parsed and processed over and over
>> again.
>>
>> Some database backends do clever caching on the server, so this
>> is not an issue, in fact, it's faster for simple queries since
>> you avoid a few network round-trips.
>>
>> For other backends, it's slower, since they don't implement
>> such caching.
>>
>> Another issue is related to parameter binding: using
>> cursor.executedirect() the ODBC driver does not have any
>> parameter type information available (this only becomes
>> available via the prepare step), so mxODBC cannot do
>> any efficient conversion to database data types on the
>> client side. This may result in conversion problems on
>> the server side and introduce extra overhead.
>>
>>   
> 
> 
> ------------------------------------------------------------------------
> 
> 
> _______________________________________________________________________
> eGenix.com User Mailing List                     http://www.egenix.com/
> https://www.egenix.com/mailman/listinfo/egenix-users

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Sep 10 2008)
>>> 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 mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX 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