| Issue 198: | Writing NULL to SQLServer varbinary column via binding fails | |
| 2 people starred this issue and may be notified of changes. | Back to list |
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
Status:
Investigating
Aug 21, 2011
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
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
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.
Dec 18, 2011
(No comment was entered for this change.)
Labels:
FreeTDS
Dec 18, 2011
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
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
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. |