Writing NULL to SQLServer varbinary column via binding fails

[GoogleCodeExporter]

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.

Original issue reported on code.google.com by char...@dakim.com on 15 Aug 2011 at 4:59

[GoogleCodeExporter]

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

• Changed state: Investigating

[GoogleCodeExporter]

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

[GoogleCodeExporter]

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

[GoogleCodeExporter]

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:

• SQL.LOG