Search code examples
sqlpython-2.7binarypyramidpymssql

Insert binary file into MSSQL db (varbinary) with python pymssql


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?


Solution

  • 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.