strange issue with varbinary and NULL
Alexandre Fayolle
I hope you're all enjoying some (presumably well earned) holidays.
For myself I'm facing a weird bug : if I run an UPDATE query involving setting
a varbinary(max) col to NULL, I get an exception if the column is not the first
one in the list.
The exception is the one I get if I try to pass a string instead of a buffer as
value to the varbinary column : a pyodbc.ProgrammingError saying that implicit
conversion from varchar to varbinary is not allowed.
The attached script reproduces the problem (obviously you'll need to tweek the
connection string).
Environment information :
Python 2.5
Windows server 2003
SQL Server 2005
ODBC driver : native client 10.0 (i.e. the one shipped with SQLServer 2008)
pyodbc 2.1.6
Thanks for your help,
--
Alexandre Fayolle LOGILAB, Paris (France)
Formations Python, Zope, Plone, Debian: http://www.logilab.fr/formations
Développement logiciel sur mesure: http://www.logilab.fr/services
Informatique scientifique: http://www.logilab.fr/science
Alexandre Fayolle
> Hi everyone,
>
> I hope you're all enjoying some (presumably well earned) holidays.
>
> For myself I'm facing a weird bug : if I run an UPDATE query involving
> setting a varbinary(max) col to NULL, I get an exception if the column is
> not the first one in the list.
>
> The exception is the one I get if I try to pass a string instead of a
> buffer as value to the varbinary column : a pyodbc.ProgrammingError saying
> that implicit conversion from varchar to varbinary is not allowed.
>
> The attached script reproduces the problem (obviously you'll need to tweek
> the connection string).
I've instrumented pyodbc a bit, and the problem seems to be in GetParamType:
the return value for SQLDescribeParam is unchecked and in the case where the
varbinary column is the second parameter, that return value is non nul: I got
an SQLState set to 07009 Invalid Descriptor Index (0).
With this in mind, I ran a few more tests. I get the same return value if I
remove the varbinary column from the request :
create table test_null(val1 int, val2 int);
insert into test_null(val1, val2) values (1, 2);
running the following request:
execute('UPDATE test_null set val1=?, val2=? where val1=?', 42, None, 1)
causes SQLDescribeParameter to return an error when trying to describe the
value for None. However this does not cause a failure in execution of the
request.
I've been looking for some memory corruption caused by rogue pointers
(BindParam being a good candidate), but so far this has not turned up
anything. It's getting late, I probably won't have time to dig up further this
year, so I'll leave it until 2010.
Happy new year everyone!
mkleehammer
Build with:
setup.py clean -a
setup.py build --trace
Adding --trace sets the TRACE_ALL flag. This may help.
Just now looking at this. With this setting, the final test fails
with:
[(1, 42, None)]
BIND: param=1 fCType=4 (SQL_C_LONG) fSqlType=4 (SQL_INTEGER)
cbColDef=0 DecimalDigits=0 cbValueMax=4 *pcb=0
BIND: param=2 fCType=99 (SQL_C_DEFAULT) fSqlType=12 (SQL_VARCHAR)
cbColDef=1 DecimalDigits=0 cbValueMax=0 *pcb=-1
BIND: param=3 fCType=4 (SQL_C_LONG) fSqlType=4 (SQL_INTEGER)
cbColDef=0 DecimalDigits=0 cbValueMax=4 *pcb=0
In RaiseError!
Traceback (most recent call last):
File "C:\dev\pyodbc\tmp\varbinary_bug.py", line 31, in <module>
crs.execute('UPDATE TestBlob SET data=?, blob=? WHERE id=?', 42,
None, 1)
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server
Native Client 10.0][SQL Server]Implicit conversion from data type
varchar to varbinary(max) is not allowed. Use the CONVERT function to
run this query. (257) (SQLExecDirectW); [42000] [Microsoft][SQL Server
Native Client 10.0][SQL Server]Statement(s) could not be prepared.
(8180)')
cnxn.clear cnxn=02313DB8 hdbc=7415360
mkleehammer
Unfortunately ODBC requires the target type (the type in the database)
when inserting. When a programmer passes None/NULL, pyodbc doesn't
know the type, so it has to ask the driver.
In the past, I used varchar as the datatype for NULL because varchar
could be converted to anything. Later versions of SQL Server (2005+ I
think) began failing when inserting a varchar into a binary/
varbinary. I then queried the target type for all columns which fixed
the problem. Unfortunately the extra call was far too slow so I had
to optimize it out a bit and only make the call when necessary.
That's why you see the map caching NULLs with SQL statements.
I consider this to be a real weakness of ODBC -- there is no way to
bind a NULL and say "I don't know/care about the target column type."
Obviously the driver/database does know the type, because I can ask
for it. (I've asked around on the MS ODBC boards, but there has been
no answer.)
Anyway, it sounds like something is wrong with that part of the code
-- it should recognize it is NULL and ask for the target type. I'm
looking now.
mkleehammer
Will be making 2.1.7 soon. Am looking for other issues that can/
should be fixed for 2.1.7 first.
Alexandre Fayolle
I've tested that changeset and it indeed fixes the problem.
Many thanks, and best wishes for 2010 :-)
