Getting assignment error for parameterised SQL LIKE query in DB2i

[Siddhesh Naik]

Hi,

I am trying to execute parameterised SQL LIKE query on db2i. I am using pyodbc + AS400 dialect with Sql alchemy.

When I run my program, its throwing an following error.

sqlalchemy.exc.DataError: (pyodbc.DataError) ('22018', '[22018] [IBM][System i Access ODBC Driver]Error in assignment. (30019) (SQLExecDirectW)')

My code snippet is as below:

from sqlalchemy import create_engine

URL = "ibm_db_sa+pyodbc400://{user}:{password}@{host}/{database}"

engine = create_engine(URL)
conn = engine.connect()

query = """SELECT * FROM CUSTOMERS WHERE CUST_NAME LIKE ? AND CUST_ID > ?"""
data = conn.execute(query, ['MA%', '100'])
if data.returns_rows:
    print(data.fetchall())
else:
    print(data.rowcount)

Error:

Traceback (most recent call last):
  File "C:/developerdata/services/db2i_issue_of_like.py", line 10, in <module>
    data = conn.execute(query, ['MA%', '100'])
  File "C:\developerdata\env\lib\site-packages\sqlalchemy\engine\base.py", line 942, in execute
    return self._execute_text(object, multiparams, params)
  File "C:\developerdata\env\lib\site-packages\sqlalchemy\engine\base.py", line 1104, in _execute_text
    statement, parameters
  File "C:\developerdata\env\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "C:\developerdata\env\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "C:\developerdata\env\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\developerdata\env\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\developerdata\env\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\developerdata\env\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (pyodbc.DataError) ('22018', '[22018] [IBM][System i Access ODBC Driver]Error in assignment. (30019) (SQLExecDirectW)') [SQL: 'SELECT * FROM CUSTOMERS WHERE CUST_NAME LIKE ? AND CUST_ID > ?'] [parameters: ('MA%', '100')] (Background on this error at: http://sqlalche.me/e/9h9h)

Please help.

[Leo Pedron]

What makes you think it’s the like predicate?  Have you tried executing the query and removing one and then the other predicate to see which predicate is the actual problem?  You are passing the ‘100’ as a string, should that be a string or perhaps an integer ( without quotes )?  

Just trying to give you a few troubleshooting ideas.

--
You received this message because you are subscribed to the Google Groups "ibm_db" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ibm_db+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ibm_db/1c95a0ee-1a3b-4616-bb1b-ab865b600d87%40googlegroups.com.

[Siddhesh Naik]

Thanks Leo. But this not the case. I tried that also but issue is only LIKE query. If I remove that LIKE condition,then my script is running fine.

To unsubscribe from this group and stop receiving emails from it, send an email to ibm...@googlegroups.com.

[Siddhesh Naik]

We got solution. Just add TRANSALATE=1 in connectionstring.

connectionstring = "ibm_db_sa+pyodbc400://{user}:{password}@{host}/{database}?TRANSLATE=1"