[egenix-users] mx.ODBC error: Invalid Descriptor Index
charlie at egenix.com
Thu Apr 15 21:42:55 CEST 2010
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)
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:
"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
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
- The application can control when the execution plan is created and
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.
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
More information about the egenix-users