On Apr 13, 2008, at 4:04 PM, Martijn Pieters wrote: > > Hi, > > I am running into problems with bind parameter names and Oracle. > Oracle is picky about bind parameter names, any reserved word as a > bind parameter name is right out. You'll get an ORA-01745 error: > "invalid host/bind variable name" if you use :to, :from or :user, for > example. There is a way around this, you can escape the parameter > name: > > http://www.edhanced.com/ask-mred/?q=node/view/182 > > Unfortunately, sqlalchemy does not escape reserved words used as bind > parameter names, even though it does escape them if used as > identifiers. > > My questions: > > 1/ Should sqlalchemy escape these? Or would it be the responsibility > of cx_Oracle to do this? > > 2/ If sqlalchemy should, where would such escaping happen? > IdentifierPreparer doesn't appear to offer a method for this (yet). > > 3/ Is there a workaround for sqlalchemy 0.4.5, such as specifying an > alternative bindparam() for a given column? I didn't find one yet, but > I haven't searched that hard yet either. >
We came up with a patch for this at Pycon which would add "_" to all Oracle-reserved word bind parameter names, but didn't get around to solidifying/committing it. Its attached and currently just adds "_" to every bind name. It introduces complexity, however, in that lots of other logic has to be aware of this translation. So, the linked article above seems to present a much better way of going about it, that oracle allows quoting of bind names. It seems like just adding the quoting to OracleCompiler.bindparam_string() (which would override the same method in DefaultCompiler) could handle this. it is definitely the job of SA to produce DB-compliant SQL, and as far as an immediate workaround for 0.4.5, implementing bindparam_string() in oracle.py to just return '"%s"' % name is probably the easiest. ( we should add a trac ticket for this issue). --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
oracle_bindescape.patch
Description: Binary data
