I am experiencing some problems with pyodbc and an Oracle 11.2 Express databank. I cannot send unicode objects to fill the placeholders.
I first create the connection and the cursor:
>>> con = pyodbc.connect('uid=oracleuser;driver={microsoft odbc for oracle};server=//localhost:1521/xe;pwd=xxx')
>>> cur = con.cursor()
Then I can perform some simple queries that work, like:
>>> cur.execute(u'SELECT * FROM MY_TEST_TABLE')
<pyodbc.Cursor object at 0x07C92988>
>>> list(_)
[('pepe', )]
Then some more complex ones with placeholders, that work as well:
>>> cur.execute('SELECT * FROM MY_TEST_TABLE WHERE column1 = ?', 'lol')
<pyodbc.Cursor object at 0x07C92988>
>>> list(_)
[] # No result as expected
But when I test the real case with unicode strings, I get the following problems:
>>> cur.execute('SELECT * FROM MY_TEST_TABLE WHERE column1 = ?', u'lol')
Traceback (most recent call last):
File "<console>", line 1, in <module>
Error: ('HY004', '[HY004] [Microsoft][ODBC Driver Manager] SQL data type out of range (0) (SQLBindParameter)')
Funny, but if the main query is unicode, it works:
>>> cur.execute(u'SELECT * FROM MY_TEST_TABLE WHERE column1 = ?', 'lol')
<pyodbc.Cursor object at 0x07C92988>
>>> list(_)
[]
This code worked well with SQLite and SQL Server, but it crashes with an Oracle 11.2 Express DB.
I ran the following SQL to check the encoding, and it is UTF8
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET'
>>> AL32UTF8
Problems that I found that are very related but did not bring me the answer:
- using pyodbc on linux to insert unicode or utf-8 chars in a nvarchar mssql field
- How do I encode Unicode strings using pyodbc to save to a SAS dataset?
I don't know how to continue... do I have to encode each unicode object I send? that's messy... and I don't have to do it with MS SQL Server. Any help is appreciated.
