Closed GoogleCodeExporter closed 8 years ago
Apparently FreeTDS 0.82 does not support SQLDescribeParam.
One problem with ODBC (and JDBC) is that you cannot insert a NULL without
telling the database the type of parameter. The pyodbc library will ask the
driver/database for the type and will use that. For example, if you insert a
NULL into a VARCHAR(10) field, pyodbc will set the NULL parameter type to
SQLCHAR.
If the driver does not support SQLDescribeParam, pyodbc uses SQLCHAR as its
best guess. Almost every type can be converted to from a SQLCHAR. The
exception is binary.
The only workaround I can think of is to supply a special object
(pyodbc.BinaryNull / BinaryNone) to use in place of None when you know it is a
binary column. Would that even help? It would require a lot more effort on
the client side.
The best solution would be to find out how Microsoft's ODBC driver implements
SQLDescribeParam and have FreeTDS emulate that. I'll have to look into that.
Original comment by mkleehammer
on 21 Aug 2011 at 7:25
According to the FreeTDS mailing list, they have no plans to support
SQLDescribeParam besides "waiting for Batman to show up with an SQL parser". I
wouldn't wait for that to happen or attempt it myself.
http://lists.ibiblio.org/pipermail/freetds/2009q3/024935.html
The suggested workaround sounds great, though I am afraid it might lead to lots
of code like:
(val if val is not None else pyodbc.BinaryNull)
... so how about generalizing the idea to support annotating any parameter
value with a type? eg:
pyodbc.TypedParam(val, pyodbc.SQL_BINARY)
Original comment by lukedell...@gmail.com
on 22 Aug 2011 at 12:32
According to http://technet.microsoft.com/en-us/library/ms130945.aspx :
"To describe the parameters of any SQL statement, the SQL Server Native Client
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 metadata of the result
set determines the characteristics of the parameters in the prepared statement."
Original comment by char...@dakim.com
on 23 Aug 2011 at 6:50
I believe I'm experiencing the same problem on Teradata. The ODBC driver
supports SQLDescribeParam, but inserting a null on any numeric column results
in an error, such as "Error HY000: The source parcel length does not match data
that was defined."
The table has these columns:
a_text VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
a_memo VARCHAR(16000) CHARACTER SET LATIN NOT CASESPECIFIC,
a_long_integer INTEGER,
a_byte BYTEINT,
a_integer SMALLINT,
a_single FLOAT,
a_double FLOAT,
a_decimal DECIMAL(18,0),
a_date_time TIMESTAMP(0),
a_currency DECIMAL(18,4),
a_yes_no BYTEINT NOT NULL
I've attached the SQL.LOG.
Original comment by dominick...@comcast.net
on 29 Sep 2011 at 3:00
Attachments:
Original comment by mkleehammer
on 18 Dec 2011 at 6:55
FYI: Microsoft's new SQL Server driver for Linux may solve the problem. I've
used it on Linux and it passes the complete SQL Server unit tests, which
includes this issue. It supports SQLDescribeParam, so when a NULL is inserted,
pyodbc can ask what the expected type is.
Original comment by mkleehammer
on 18 Dec 2011 at 10:58
Added pyodbc.binaryNull to 3.0.2-beta05
I want to hold off on the generic binding only because people could bind an int
and say it was a string, etc. I could ignore the type unless the parameter is
None, but I want to think it about it.
This is different than the Teradata issue, so that will need be tracked
elsewhere.
Original comment by mkleehammer
on 18 Dec 2011 at 11:10
Is there something I can try to help isolate the Teradata issue? I tried
upgrading to pyodbc 3.0.2, but the result is the same.
Original comment by dominick...@comcast.net
on 18 Jan 2012 at 7:30
Original issue reported on code.google.com by
char...@dakim.com
on 15 Aug 2011 at 4:59