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

[Charlie Clark]

Am 15.04.2010, 19:56 Uhr, schrieb Alexandre Fayolle  
<alexandre.fayolle at logilab.fr>:

> So this would be why I get a failure for
> SELECT DISTINCT rel_require_permission0.eid_to
> FROM require_permission_relation AS rel_require_permission0
> WHERE rel_require_permission0.eid_from=?
> AND NOT EXISTS
(SELECT 1 FROM require_permission_relation AS
> rel_require_permission1 WHERE rel_require_permission1.eid_from=?
> AND rel_require_permission0.eid_to=rel_require_permission1.eid_to)
> correct?

Yes, the driver chokes on the parameter in the sub-select.

>> Since SQL Server's ODBC driver supports SQL type binding, this
>> is what mxODBC uses per default.
>>
>> cursor.executedirect() is a way to work around that default on
>> a per-statement basis. It forces mxODBC to use Python type binding
>> for that statement.

> But if I read the documentation correctly, doing this won't prepare the
> statement, which will worsen the perfs if the statement is reused a lot.

In theory this would be the case. In practice it *might* not be so  
dramatic but you should definitely profile it. We've seen some discussion  
even favouring this approach:

http://msdn.microsoft.com/en-us/library/ms175528%28SQL.90%29.aspx

"In SQL Server 2005, the prepare/execute model has no significant
performance advantage over direct execution, because of the way SQL Server
2005 reuses execution plans. SQL Server 2005 has efficient algorithms for
matching current SQL statements with execution plans that are generated for
prior executions of the same SQL statement. If an application executes a  
SQL
statement with parameter markers multiple times, SQL Server 2005 will reuse
the execution plan from the first execution for the second and subsequent
executions (unless the plan ages from the procedure cache). The
prepare/execute model still has these benefits:

    - Finding an execution plan by an identifying handle is more efficient
    than the algorithms used to match an SQL statement to existing execution
    plans.
    - The application can control when the execution plan is created and  
when
    it is reused.
    - The prepare/execute model is portable to other databases, including
    earlier versions of SQL Server."

Hopefully enough bug reports will encourage Microsoft to fix it.

Charlie
-- 
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> 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/