Click here to Skip to main content
14,970,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 9-Dec-20 6:09am
Comments
Richard MacCutchan 9-Dec-20 10:36am
   
Don't try to convert the raw bytes into some other format. If the column type is raw binary then that is what you should store in the database. The second error message is due to invalid syntax in your execute command - check the documentation.
carltannler 9-Dec-20 11:26am
   
So I tried: query = "insert into myTable (imageData) values ({})".format(b)
And the execute statement is saying Syntax Error but I'm not sure what the error could be other than when the query tries to execute something from the byte array that's being put in to the query is throwing it off. I also tried using f which is just the bytes, f = image.read() and I get the same Syntax Error message. I checked the documentation and it looks like I'm doing everything correctly so I'm still not sure.
Richard MacCutchan 9-Dec-20 11:58am
   
Why have you changed the insert statement to store binary data as a string? As I already said, you need to store binary data as binary data, not converted to something else. The syntax error was on the execute statement, so go to the documentation and check how to add a reference to the value correctly.
carltannler 9-Dec-20 12:19pm
   
I've gone through the documentation and I thought this is how you insert a variable as it's own type without converting. I found a different way that has a parameter dictionary being created. So I tried that by setting paramDictionary = {"image": b} and then my query would become: query = "insert into myTable (imageData) values (%(image)s)" which would then make the execute statement: cursor.execute(query, paramDictionary), but I'm still getting an invalid syntax error. I'm not trying to change the insert statement to store a string instead of binary data I'm just trying to insert the binary data directly and I can't seem to figure it out.
Richard MacCutchan 9-Dec-20 12:51pm
   
Looking again at the link I gave you below, your original code appears to be OK. Did the error message provide any more information? The only thing I can possibly suggest is to remove the space between the table name and the opening parenthesis; but that is a wild guess.
carltannler 9-Dec-20 13:03pm
   
Yea I've already tried everything in that link, even though it's pyodbc instead of adodbapi they still work the same for the most part. The error is showing "incorrect syntax near '('" when I try do what I showed above, but when I try to use the ? and then insert the parameter in to the execute statement I get the invalid descriptor index error.
Richard MacCutchan 9-Dec-20 13:59pm
   
The first message implies what I suggested above. The "invalid index error" has me stumped, I am afraid. I have the possibility to try a few tests, but not until tomorrow - dinner calls.
Richard MacCutchan 10-Dec-20 5:51am
   
Unfortunately, I only have the ability to test with sqlite, and I am unable to reproduce your problem. Sorry but I cannot offer any further suggestions.

1 solution

Here is a sample of Python using ODBC: Using DataDirect ODBC drivers with Python and pyodbc[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900