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

Tue Apr 13 13:03:27 CEST 2010

Hello Alexandre,

Alexandre Fayolle wrote:
> Hi,
> 
> I have an application which currently runs with pyodbc to connect to an 
> SQLServer 2005 database. It works fine but very slowly (50x more slowly than 
> the same app running against PostrgreSQL via psycopg2), and this might kill 
> our so I'm investigating if mx.ODBC could improve the perfs in which case I'll 
> be happy to to buy a licence. 
> 
> I've solved a couple of easy problems concerning the SQL <-> Python mapping 
> differences between pyodbc and mx.ODBC, but I have an issue with the following 
> query:
> 
> SELECT ? FROM owned_by_relation 
> WHERE eid_from=? AND eid_to=? 
> 
> which is executed with parameters (2031, 2031, 5)
> 
> This query runs fine with pyodbc (and psycopg, mysqldb, sqlite...) but with 
> mx.ODBC it generates 
> 
> InterfaceError: ('07009', 0, '[Microsoft][SQL Server Native Client 
> 10.0]Invalid Descriptor Index', 7513) 
> 
> The problem is with the first ? (in the SELECT part of the request).

Right. Parameter markers are not allowed in a SELECT list.

Some more background on the subject, since we've been getting
such questions a lot in recent months.

http://msdn.microsoft.com/en-us/library/ms711808(VS.85).aspx

"""
Parameters are valid only in certain places in SQL statements. For example, they are not allowed in
the select list (the list of columns to be returned by a SELECT statement), nor are they allowed as
both operands of a binary operator such as the equal sign (=), because it would be impossible to
determine the parameter type. Generally, parameters are valid only in Data Manipulation Language
(DML) statements, and not in Data Definition Language (DDL) statements. For more information, see
Parameter Markers in Appendix C: SQL Grammar.
"""

and

http://msdn.microsoft.com/en-us/library/ms709310(VS.85).aspx

"""
Parameter Markers

In accordance with the SQL-92 specification, an application cannot place parameter markers in the
following locations. For a more comprehensive list, see the SQL-92 specification.

    *      In a SELECT list
    *      As both expressions in a comparison-predicate
    *      As both operands of a binary operator
    *      As both the first and second operands of a BETWEEN operation
    *      As both the first and third operands of a BETWEEN operation
    *      As both the expression and the first value of an IN operation
    *      As the operand of a unary + or – operation
    *      As the argument of a set-function-reference
"""

SQL Server's ODBC driver unfortunately has a few other limitations
as well. Here's a list of known situations where using
parameter markers will always fail with SQL Server Native Client's
ODBC driver and non-direct execution (ie. using prepare/execute and
SQL type binding which relies on the ODBC API SQLDescribeParam):

http://msdn.microsoft.com/en-us/library/ms130945.aspx

"""
* For any ODBC or Transact-SQL statement containing a parameter in a HAVING clause, or compared to
the result of a SUM function.
* For any ODBC or Transact-SQL statement depending on a subquery containing parameters.
* For ODBC SQL statements containing parameter markers in both expressions of a comparison, like, or
quantified predicate.
* For any queries where one of the parameters is a parameter to a function.
* When there are comments (/* */) in the Transact-SQL command.
"""

The reason you see errors with mxODBC and not with pyodbc is that
mxODBC tries to use SQL type binding (the ODBC driver tells mxODBC
what to send, avoiding temporary copies and conversions) if possible.
pyodbc only supports Python type binding (the Python application
defines what to send).

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.

> My options in tweaking the SQL are somewhat limited, because the SQL is 
> generated by some kind of ORM and we want to keep the rest of the code base as 
> backend-agnostic as possible. 
> 
> I've seen http://www.egenix.com/mailman-archives/egenix-users/2009-
> May/114571.html suggesting to use executedirect, but when I tried that, the 
> program crashed in another place because of date/time issues:
> 
> 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)
> 
> At this point, I'm a bit stuck and would appreciate any help 

How are you passing those date/time values to mxODBC ? As
mxDateTime instances, strings or datetime module instances ?

An ODBC trace log would help find out what the ODBC driver
is asking for.

> platform information: 
> 
> * windows server 2003
> * python 2.5.4
> * mxODBC 3.04
> * mx Base 3.1.3
> * SQL Server 2005
> * ODBC Driver : SQL Server Native Client 10.0
> 
> Thanks in advance
> 

-- 
Marc-Andre Lemburg
eGenix.com

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