I am trying to insert binary data into a column of image datatype in a SQL Server database. I know varbinary(max) is the preferred data type, but I don't have rights to alter the schema.
Anyhow, I am reading the contents of a file and wrapping it in pyodbc.Binary() as below:
f = open('Test.ics', 'rb')
ablob = f.read().encode('hex')
ablob = pyodbc.Binary(ablob)
When I print repr(ablob) I see the correct value bytearray(b'424547494e3a5 . . . (ellipsis added).
However, after inserting
insertSQL = """insert into documents(name, documentType, document, customerNumber) values(?,?,?,?)"""
cur.execute(insertSQL, 'test200.ics', 'text/calendar', pyodbc.Binary(ablob), 1717)
The value of the document column is 0x343234353 . . . which appears as if the hexadecimal data was converted to ASCII character codes.
I thought wrapping the value in pyodbc.Binary() would take care of this? Any help would be greatly appreciated.
I am using Python 2.7 and SQL Server 2008 R2 (10.50).
Edit:
beargle kindly pointed out that I was needlessly calling encode('hex'), which was leading to my issue. I believe this must have been coercing the data into a string (although a fuller explanation would be helpful).
Working code:
ablob = pyodbc.Binary(f.read())
cur.execute(insertSQL, 'test200.ics', 'text/calendar', ablob, 1717)