Issue 198: Writing NULL to SQLServer varbinary column via binding fails
Status:  Complete
Owner: ----
Closed:  Dec 2011
Reported by char...@dakim.com, Aug 15, 2011
What steps will reproduce the problem?

Given a pyodbc connection to a SQL Server 2005 database and cursor c, execute the following:

c.execute("CREATE TABLE T (id Integer, photo Varbinary(MAX))")
c.execute("INSERT INTO T VALUES (?, ?)", (1, None))

The second command fails:

pyodbc.DataError: ('22005', '[22005] [Actual][SQL Server] Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. (257) (SQLExecDirectW)')

What is the expected output? What do you see instead?

The 2.1.7 release notes suggested that this issue had been fixed.


What version of the product are you using? On what operating system?

pyodbc 2.1.8, pyodbc 2.1.9, ubuntu 10.04.3, freetds 0.82.


Please provide any additional information below.


Aug 21, 2011
Project Member #1 mkleehammer
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.
Status: Investigating
Aug 21, 2011
#2 lukedell...@gmail.com
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)

Aug 23, 2011
#3 char...@dakim.com
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."
Sep 29, 2011
#4 dominick...@comcast.net
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.
SQL.LOG
65.3 KB   View   Download
Dec 18, 2011
Project Member #5 mkleehammer
(No comment was entered for this change.)
Labels: FreeTDS
Dec 18, 2011
Project Member #6 mkleehammer
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.

Dec 18, 2011
Project Member #7 mkleehammer
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.
Status: Complete
Jan 18, 2012
#8 dominick...@comcast.net
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.