[egenix-users] cursor.executedirect configurable?
mal at egenix.com
Wed Sep 10 18:23:17 CEST 2008
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
> 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.
> 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
>>> 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
>> 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/
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
More information about the egenix-users