Oracle and escaping bind parameters

[Martijn Pieters]

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.

Thanks,

Martijn Pieters

[Michael Bayer]

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).

[Martijn Pieters]

On Sun, Apr 13, 2008 at 10:36 PM, Michael Bayer
<mik...@zzzcomputing.com> wrote:
> 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.

Cool, thanks. I'll try this out tomorrow morning (something along
return self.preparer.quote(None, name) may work).

> ( we should add a trac ticket for this issue).

I'll file one once I found a workable solution, if you like.

--
Martijn Pieters

[Martijn Pieters]

On Mon, Apr 14, 2008 at 12:40 AM, Martijn Pieters <m...@zopatista.com> wrote:
> I'll file one once I found a workable solution, if you like.

I found http://www.sqlalchemy.org/trac/ticket/994 and commented. It's
not as easy as just specifying bindparam_string unfortunately.

I added:

def bindparam_string(self, name):
# Oracle needs reserved words in parameters to be quoted
if self.preparer._requires_quotes(name):
name = self.preparer.quote_identifier(name)
return super(OracleCompiler, self).bindparam_string(name)

and instead of ORA-01745: invalid host/bind variable name I now get
ORA-01036: illegal variable name/number.

The workaround for me is to use a different local key (Column(...
key='newkey-notreserved')).

--
Martijn Pieters