pyodbc update record with binary data in MS SQL server

Question

I am trying to update an existing record with binary data.

The sql server datatype for the column is a varbinary(MAX)

The python code I use:

result = bytes(result_string, 'utf-8')
cursor = self.connection.cursor()
date_updated = self.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
cursor.execute(f"UPDATE _nsInvoiceReconitionJob SET JobStatus = 2, DateUpdated = '{date_updated}', Result = '{result}' WHERE ID = '{job_id}'")
connection.commit()

The result is the following:

File "/home/wessel/Work/crontest/Elvpy/JobProcessing.py", line 37, in save_result
    cursor.execute(f"UPDATE _nsInvoiceReconitionJob SET JobStatus = 2, DateUpdated = '{date_updated}', Result = '{result}' WHERE ID = '{job_id}'")
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server]Syntax error, permission violation, or other nonspecific error (0) (SQLExecDirectW)')

The error mentions that this could be a syntax error, and the query works without updating the "Result" column, which is the binary data column. This means the error has to be the binary data, so I tried it without '', but that doesn't work either. It could also not be a syntax error, and mean that I'm just doing this wrong. If anyone could set me on the right path I would be most grateful!

I am using pyodbc version 4.0.30, I use the ODBC Driver 17 for SQL Server

In this case there is no risk for SQL injection because there is 0 user input, but I will look into it :) — Wessel Ottevanger
– Wessel Ottevanger, Commented May 20, 2021 at 14:53
Yes, but as you've noticed, using f-strings requires that you quote the values correctly, which you evidently are unable to do given the varbinary data. — user5386938
– user5386938, Commented May 20, 2021 at 14:56

user5386938 · Accepted Answer · 2021-05-20 14:55:21Z

3

Use parameters!

result = bytes(result_string, 'utf-8')
cursor = self.connection.cursor()
date_updated = self.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
cursor.execute(
    "UPDATE _nsInvoiceReconitionJob SET JobStatus = ?, DateUpdated = ?, Result = ? WHERE ID = ?"
    , (2, date_updated, result, job_id)
    )
connection.commit()

answered May 20, 2021 at 14:55

user5386938

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pyodbc update record with binary data in MS SQL server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related