[DB-SIG] mx.ODBC.Windows - execute() parameter substitution raisesException - SOLVED!

[M.-A. Lemburg]

Krjukov Victor wrote:
>>Could be a limitation of the MS SQL Server or its ODBC
>>driver. The SQL you are using looks strange though: why
> 
> Yes, it is.
> 
> Turning SQL tracing on, I've found that it is the SQLDescribeParam who
> returns SQL_ERROR. And in the documentation to the SQL server I've found
> following excerpt (see at the end of the file).
> 
> The last statement explicitly states that parameters in subqueries are
> not supported. Uh :(.
> 
> Probably I can introduce a feature request to Mr Lembert: don't rely on
> SQLDescribeParam and implement parameter substitution yourself (at least
> in mx.ODBC.Windows) :P.

That would give away too much flexibility, esp. the implicit
SQL value quoting which is implemented in the ODBC drivers
(if needed by the wire protocol). Doing this in mxODBC would
cause much trouble since each database has its own way
of formatting value literals.

In your particular case it should be easy enough to switch
from ? to %s in the command string and from the comma to a
percent sign between the command and the argument tuple.

> Sincerely Yours, 
> 
> 		Victor.
> 
> -----> excerpt from SQL Server Info.
> SQLDescribeParam
> To describe the parameters of any SQL statement, the Microsoft(r) SQL
> Server(tm) ODBC driver builds and executes a Transact-SQL SELECT
> statement when SQLDescribeParam is called on a prepared ODBC statement
> handle. The driver uses the SET FMTONLY statement when executing the
> query. The meta data of the result set determines the characteristics of
> the parameters in the prepared statement.
> 
> Consider this ODBC SQL statement:
> 
> INSERT INTO Shippers (ShipperID, CompanyName, Phone) VALUES (?, ?, ?)
> 
> On a call to SQLDescribeParam, this ODBC SQL statement causes the driver
> to execute the following Transact-SQL statement:
> 
> SET FMTONLY ON SELECT ShipperID, CompanyName, Phone FROM Shippers SET
> FMTONLY OFF
> 
> SQLDescribeParam can, therefore, return any error code that SQLExecute
> or SQLExecDirect might return.
> 
> Further, the driver does not support calling SQLDescribeParam after
> SQLExecDirect for any Transact-SQL UPDATE or DELETE statements
> containing the FROM clause; 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; or queries where one of the parameters is a
> parameter to a function.
> ...
> -----

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
_______________________________________________________________________
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/