UnicodeDecodeError in pyodbc when passing parameters to cursor.execute(), but not when writing parameters directly to string

Question

When trying to pass a parameter to a prepared statement using pyodbc, Python returns a UnicodeDecodingError. However, when adding the parameter directly to the prepared statement string, there is no such error.

I work on Windows 10 (64bit) using the pyodbc library in Python 3 and with an "Oracle in OraDB12Home1" driver. Retrieving information from the database works fine when adding all parameters directly to the string containing the sql-statements.

Here are two examples that throw the mentioned error

Example 1

cursor = cnxn.cursor()
sql_statement = "select col1 from ? where col1 is not null;"
params = ("db_name")
cursor.execute(sql_statement,params)

Example 2

cursor = cnxn.cursor()
sql_statement = "select col1 from db_name where col1 is not ?;"
params = ("null")
cursor.execute(sql_statement,params)

The resulting error is in both cases as follows:

---------------------------------------------------------------------------

UnicodeDecodeError                        Traceback (most recent call last)

~\AppData\Local\conda\conda\envs\fasttext\lib\encodings\utf_16_le.py in decode(input, errors)

     15 def decode(input, errors='strict'):

---> 16     return codecs.utf_16_le_decode(input, errors, True)

     17

UnicodeDecodeError: 'utf-16-le' codec can't decode bytes in position 160-161: illegal encoding

Note that choosing a different database (e.g. replacing "db-name" with "different_db_name") does not do the trick and still throws the same error.

Expected behaviour

I expect the same result as for the following example, which runs without error:

cursor = cnxn.cursor()
sql_statement = "select col1 from db_name where col1 is not null;"
cursor.execute(sql_statement)

Also, note that passing different parameters works fine. For example, the execution of the following code throws no error, even though the only difference to the examples provided above is the parameter that is passed.

cursor = cnxn.cursor()
sql_statement = "select ? from db_name where col1 is not null;"
params = ("col1")
cursor.execute(sql_statement)

krokodilko · Accepted Answer · 2019-01-25 16:42:50Z

X is null and X is not null are clauses of SQL language - see: NULL conditions.
There are SQL keywords, NULL keyword in these clauses is a part of the syntax, you simply can not pass a keyword as a parameter.

Therefore this is incorrect:

sql_statement = "select col1 from db_name where col1 is not ?"

You must use something like this instead:

param = "null"
if param == "null":
    sql_statement = "select col1 from db_name where col1 is null"
else:
    sql_statement = "select col1 from db_name where col1 is not null"

The same applies to table names - they cannot be passed as bind variables.
I will not explain why, there are plenty of answers on this subject, for example this one:
passing table and column name dynamically using bind variables

Therefore this is incorrect:

sql_statement = "select col1 from ? where col1 is not null"

You must use this instead:

param = "db_name"
sql_statement = "select col1 from " + param + " where col1 is not null"

Collectives™ on Stack Overflow

UnicodeDecodeError in pyodbc when passing parameters to cursor.execute(), but not when writing parameters directly to string

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related