apply simple underscore escaping to oracle bind param names that are reserved words

[Mike Bayer]

the attached patch modifies the behavior of bind parameters when INSERT and UPDATE statements are compiled, such that the compiler can escape the name while still allowing the natural name of the column to be used as a bind param key. A rudimentary escaping has been added to the oracle module which we want to test with oracle, and possibly make it more specific to only SQL reserved words. we have a list of reserved words in compiler.py which probably needs oracle-specific words (like 'size') added to it within the oracle.py module.

[Catherine Devlin]

I applied this patch, added the full list of Oracle reserved words from Oracle's v$RESERVED_WORDS view. This fixes virtually all outstanding broken unit tests for Oracle. Committed to trunk. Thanks, zzzeek!

[Catherine Devlin]

oops... um, spoke too soon. Functionality is correct, but test harness needs some modification to recognize that altered statements are correct.

[Former user]

Uhm, you only need to quote the reserved words:

http://www.edhanced.com/ask-mred/?q=node/view/182

Do note that like in the patch, you'll need to use the quoting both in the SQL statement and in the parameter dictionary; passing in {'user': 'foo') for 'SELECT :"user" FROM DUAL;' still will fail, while {'"user"': 'foo'} does work:

>>> # Reserved word
>>> engine.execute('SELECT :user FROM DUAL', {'user': 'foo'}).fetchall()
Traceback (most recent call last):
...
DatabaseError: (DatabaseError) ORA-01745: invalid host/bind variable name
>>> # Parameter quoted
>>> engine.execute('SELECT :"user" FROM DUAL', {'user': 'foo'}).fetchall()
Traceback (most recent call last):
...
DatabaseError: (DatabaseError) ORA-01036: illegal variable name/number
>>> # Both parameter and dictionary key quoted
>>> engine.execute('SELECT :"user" FROM DUAL', {'"user"': 'foo'}).fetchall()
[('foo',)](('foo',))

Using correct quoting instead of adding an underscore also means you don't hit the maximum parameter length as early; the quotes don't count against the name length.

[Mike Bayer]

this is fixed in the pre-.6 development branch in 419f212c3346173efb4a89ab80a4fe1ba2b4d7e0. close this ticket when the 0.6 branch is created.

[Former user]

It looks like cx_Oracle doesn't actually deal very well with quoted bind parameters. I've backported the fix (locally) for a SQLAlchemy 0.5 project using the implemented strategy (quote bindparams where needed, then adjust executioncontext.parameters in pre_exec to match the quoting), but Oracle continues to throw ORA-01036: illegal variable name/number.

Interestingly enough cx_Oracle, after preparing a statement with reserved word (but quoted) bind parameters, will list such parameters ''in unquoted form'':

>>> cursor.prepare('INSERT INTO mytable ("uid") VALUES (:"uid")')
>>> cursor.bindvars.keys()
['uid']('uid')

but trying to execute such a statement with the correct bind parameter name according to cx_Oracle still fails:

>>> cursor.execute(None, dict(uid=None))
*** DatabaseError: ORA-01036: illegal variable name/number

This may be a bug in cx_Oracle of course, I'll report this on the mailinglist there too. For the curious, I tested this with cx_Oracle 5.0.2.

[Mike Bayer]

hi Catherine -

can you reproduce the behavior with 0.6 ? what's the specific test case that fails from a SQLA perspective ?

[Former user]

Replying to zzzeek:

hi Catherine -

Perhaps you should give me a login on this trac apart from 'guest' :-) I'm Martijn Pieters, and all comments by 'guest' here are mine.

can you reproduce the behavior with 0.6 ? what's the specific test case that fails from a SQLA perspective ?

No, and I have found out why. I missed out that pre_exec in Oracle_cx_oracleExecutionContext also translates the bind parameters when calling setinputsizes on cx_Oracle's cursor. This was the cause of the ORA-01036 error, because I didn't realize that a call to setinputsizes had been made in my debugsession prior to the above test.

In other words, nothing to see here, please do move along. Please re-close this ticket as fixed.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)