creimers creimers - 1 month ago 54
SQL Question

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?

Answer