Insert binary file into MSSQL db (varbinary) with

Question

I'm trying to insert binary data into an MSSQL database using stored procedures from within an pyramid web application using pymssql.

The MSSQL datatype for the respective column is varbinary(max).

My question is: What does the varbinary datatype of the MSSQL database expect me to do with the binary file before inserting it?

Here's what I've been trying:

with open(tmp_file, 'rb') as content_file:
    filecontent = content_file.read()

... and then pass the value of filecontent to the stored procedure.

tmp_file in this case is a valid path to a .png file.

The MSSQL db answers with the following error:

*** DatabaseError: (257, 'Implicit conversion from data type varchar to varbinary(max) is not 
allowed. Use the CONVERT function to run this query.DB-Lib error message 257, severity 16:\nGeneral
SQL Server error: Check messages from the SQL Server\n')

I am able to insert the value of the filecontent variable into an SQLite (varbinary column) and a MySQL database (BLOB column) no problem.

Why is it not working with the MSSQL db?

Answer 1

As @Arno Rinker pointed out, there was indeed a problem with the conversion from varchar to varbinary. However, I ended up doing more than that to completely solve my problem.

I'm posting my whole solution here, just in case...

with open(tmp_file, 'rb') as content_file:
    filecontent = content_file.read()
    filecontent_hex = '0x'.encode('ascii') + binascii.hexlify(filecontent)

    --> post the value of filecontent_hex to a stored procedure.

The stored procedure receives the value of filecontent_hex as varchar, then converts it to varbinary.

Getting binary data out of the MSSQL-DB I then do like this:

fout = open(filename, "w")
filecontent_unhex = binascii.unhexlify(filecontent)
fout.write(filecontent_unhex[2:])
fout.close()

where filename and filecontent are in the result of the querying stored procedure.

Conclusion: The binascii.hexlify() and binascii.unhexlify() where not necessary for the binary interaction with SQLite and MySQL. However, these steps appeared to be required for the interaction with a MSSQL-DB.

Answer 2

Maybe you have to convert the varchar explicitly. That's what your error message implies.

See: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8f9d772a-4fa2-45b4-9fed-f03c73bd757a/implicit-conversion-from-data-type-varchar-to-varbinary-is-not-allowed-use-the-convert-function-to?forum=transactsql

This seems to solve the same problem.