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

[Krjukov Victor]

Greetings again,

I've find a solution to this problem.

Actually this is the second time I found an answer on my question myself
on the same day I post it to the newsgroup; previous one was yesterday
:P.


> From: M.-A. Lemburg [mailto:mal@lemburg.com]=20
> Sent: Wednesday, October 02, 2002 2:19 PM
> To: Krjukov Victor
> Cc: db-sig@python.org
> Subject: Re: [DB-SIG] mx.ODBC.Windows - execute() parameter=20
> substitution raisesException

...

> 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.

Sincerely Yours,=20

		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.
...
-----