Support for SQLDescribeParam

[jimhester]

The API Summary shows this is the only function completely unimplemented from the specification.

SQLDescribeParam is used extensively in nanodbc, particularly when binding insertion vectors, which makes freeTDS drivers unusable when using that library.

Are there blockers preventing implementation of this function or other reasons it has not been implemented?

[freddy77]

It requires to parse the sql command and build a command to ask server for types.

[freddy77]

Question: do you only need SQLDescribeParam for store procedures? This should not require a full parser.

[jimhester]

I believe that is correct, SQLDescribeParam is only used when binding parameters in nanodbc, so should only be used for store procedures, if I understand that term properly.

[freddy77]

Parameters (but only input ones) could be provided also to normal queries, like

SELECT * FROM table WHERE id = ?

or

INSERT INTO table(field) VALUES(?)

I don't know nanoodbc to tell how SQLDescribeParam is used. For stored procedure a specific syntax should be used, like

{call store_name(?)}

(this is ODBC syntax, nothing FreeTDS/MSSQL specific), our driver already detect this and is not hard to get parameters from store procedure (this obviously require a server query).

[jimhester]

SQLDescribeParam is only used in two places by nanodbc, in retrieving the parameter attributes to bind parameters and to get the parameter size alone. If we could get this information without calling SQLDescribeParam, perhaps directly from the implementation parameter descriptor as suggested by the documentation of SQLDescribeParam we could remove this dependency entirely.

[gordthompson]

For what it's worth, when pyodbc calls SQLDescribeParam, Microsoft's ODBC Driver for SQL Server translates that into a call to sp_describe_undeclared_parameters. For example, for

crsr.execute("update t1 set n=?, blob=?", param1, param2)

the SQL Server receives

exec sp_describe_undeclared_parameters N'update t1 set n=@P1, blob=@P2'

[freddy77]

Looks interesting.

[krlmlr]

sp_describe_undeclared_parameters returns a table with suggested_system_type_id columns, which could be translated back to ODBC data types (e.g. 165 -> XSYBVARBINARY -> SQL_BINARY).

A few months ago nanodbc gained describe_parameters() as a workaround, the missing implementation here might be less of a problem now.

[jnahmias]

Hi @freddy77 - I'm hitting this issue when using FreeTDS with PHP's pdo_odbc. They have some heuristic at https://github.com/php/php-src/blob/master/ext/pdo_odbc/odbc_stmt.c#L327-L347 which they use to try and guess the proper values, but it's failing for me in some cases, specifically with nvarchar columns/parameters. Any opposition if I try and implement SQLDescribeParam using sp_describe_undeclared_parameters as mentioned by @gordthompson and @krlmlr above?

[freddy77]

Nothing against it

[jnahmias]

cool, I plan to follow the same path that Microsoft uses in their JDBC Driver to support java.sql.PreparedStatement.getParameterMetaData() https://github.com/microsoft/mssql-jdbc/blob/main/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerParameterMetaData.java