I have asked this question on Stack Overflow and have received minimal help. I believe the person who tried to help me may be on the right track but I still can't seem to solve this issue.
Basically I need to take a local PNG file and insert it into a varbinary(max) field in SQL Server. I also need to use adodbapi as the library for my SQL activities within Python.
What I have tried:
What I have so far is some simple code to load the image into a byte array:
with open('path.png') as image:
f = image.read()
b = bytearray(f)
I have tried a bunch of different ways to insert this into SQL, one of which successfully inserted but was in the wrong format.
The first method which inserted but was in the wrong format is to convert the byte array to a hex string:
hexConvert = '0x'.encode('ascii') + binascii.hexlify(b)
hx1 = "'" + str(hexConvert) + "'"
hx2 = str(hx1)[1:]
hx2 = hx2[2:]
And then my query becomes:
query = "insert into myTable (imageData) values (CONVERT(varbinary(max), '" + hx2 + "'))"
The above successfully inserts into SQL but the data comes out in the format: '0x383935303...' where as I need the image to be in the format: '0x89504E470D0A...'
And those two formats are the same image, the latter was put into SQL via another program that I can not use in this instance.
The next method I've tried is to just insert the bytearray into the query as a paramter. So my byte array is saved as the variable, 'b' and my query becomes:
query = "insert into myTable (imageData) values (?)"
cursor.execute(query, b)
And this gives me the following error: '[Microsoft][ODBC Driver Manager] Invalid descriptor index'
Any help on this would be greatly appreciated. Someone mentioned to use setinputsizes but I haven't been able to figure out the correct syntax and nothing I try with that seems to make any difference.