[DB-SIG] Re: cx_Oracle - passing unnecessary bind variables

[Anthony Tuininga]

On Sun, 2003-11-23 at 12:31, Paul Moore wrote:
> Anthony Tuininga <anthony at computronix.com> writes:
> 
> > On Thu, 2003-11-20 at 14:01, Paul Moore wrote:
> >> The problem is that this doesn't work - it returns an Oracle error,
> >> cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number.
> >> 
> >> OK, this is to some extent a limitation of Oracle (or maybe of
> >> cx_Oracle, I don't know how Oracle OCI works at this low a level) -
> >> I'm passing bind variables which aren't used by the SQL statement. But
> >
> > This is definitely a limitation of Oracle. Oracle does not allow you to
> > specify variables that are not part of the statement. As far as I know
> > there is no way around this. Oracle does provide a way of returning to
> > you the bind variable names once the statement is prepared but cx_Oracle
> > does not (currently) provide that information. I have another workaround
> > for now but if you really think this would be helpful, feel free to ask
> > me for it -- this is definitely beyond the scope of the DB API but I
> > have long since stopped feeling restricted by the limitations of the API
> > (which has to accommodate each of the different database management
> > systems out there).
> 
> The behaviour of cursor.execute(sql, parameters) when there are
> variables specified in `parameters` which are not needed by `sql` is
> undefined in the DB-API. However, the more I think about it, the more
> I feel that ignoring "extra" parameters is the more user-friendly
> option.

I've never been a fan of ignoring errors, even ones that appear "safe"
to ignore. I also prefer explicit to implicit -- in other words, I
wouldn't mind implementing something like this as an __option__ that can
be explicitly chosen. Of course, I'm also willing to entertain
additional arguments for why this is a "good thing". :-)

> The cons:
> 
>   * It's potentially harder for module authors to implement (as you
>     pointed out for Oracle)

Actually, if all you care to do is ignore the error, you can quite
easily do that. In other words, check for ORA-1036 (illegal variable
name/number) and simply ignore it and move on. See above for why I'm not
really all that comfortable with this.

>   * It may be slower in cases where it's not needed.

Somewhat, true. Of course, if you code everything correctly, there is no
issue. If you add extra parameters accidentally, they will be ignored.

>   * It could hide typos (but they'd have to be pretty obscure - include
>     an extra, misspelled, variable, but still include the variable by
>     its "correct" name as well)

True. This one is pretty minor.

> The pros:
> 
>   * Supports using the same parameter dictionary for multiple SQL
>     statements.

Since cx_Oracle allows keyword arguments for executing SQL statements,
this one has not proved to be a problem to me. This is something that I
wish the DB API standard would "officially" endorse. Specifically, this
is permitted in cx_Oracle:

cursor.execute(sql, var1=value1, var2=value2, var3=value3....)

which allows for the building of a dictionary quite easily. There are a
few cases where it might be convenient to pass a prebuilt dictionary but
they are few and far between -- unless you can give me some additional
examples??

>   * There is no easy way to do this in user code (you can't get the
>     list of bind variables used by a cursor, so you have to work
>     around it by making the user pass the variables, like we said).

There is no DB API method for doing so. I have just recently added the
necessary code (cursor.bindnames()) to cx_Oracle for those who care to
do this. I am also adding the ability to subclass connections and
cursors so you can make this extremely transparent.

>   * This is DB-API compliant (if not mandated). It's only relevant for
>     paramstyles of 'named' and 'pyformat', so it doesn't apply to all
>     of the modules out there.

True. But here is another case where the DB API is not explicit enough.
That means that a module may or may not implement this behavior and thus
you cannot count on it. I think I would list this as a con.... :-)

> I checked a few of the modules I could find, mostly by just
> looking at the documented paramstyle:
> 
>     Module      Paramstyle  Notes
>     ----------  ----------  -----------------------------
>     mxODBC      qmark       not relevant
>     DCOracle2   numeric     not relevant [1]
>     pysqlite    pyformat    ignores extra arguments
>     adodbapi    qmark       not relevant
>     psycopg     pyformat    ignores extra arguments [2]
>     pyPgSQL     pyformat    ignores extra arguments [2]
>     Sybase      named       can't test
>     Ingres      ?           can't test
> 
> [1] It's a pity DCOracle2 uses numeric arguments, as this is the
>     closest equivalent to cx_Oracle (in terms of what is needed for
>     implementation, at least).
> 
> [2] I don't have Postgres to test, but scanning the implementation, it
>     seems to use the normal Python % operations to build the SQL from
>     the arguments, so it should ignore extra arguments.
> 
> Does anyone have any thoughts? Obviously, I'm not in any position to
> insist that things work this way. If I knew more about OCI coding, I'd
> try to supply a patch (I may see what I can do anyway - I assume it's
> OCIStmtGetBindInfo() that's relevant here?)

You can e-mail me directly if you wish more information on this. Perhaps
what I have already implemented will make this work for you and if not,
I'm willing to entertain suggestions.... :-)

> Paul.
> 
> PS Actually, on reflection, there's a comparable question for
>    positional styles - can you have a parameter sequence that's longer
>    than needed? I don't think this is as important, though - but that
>    may just reflect my personal view that named bindings are far more
>    useful than positional ones. [FWIW, though, I did a quick check and

Tell me about it. The code makes considerably more sense when using
named bind variables. The qmark paramstyle is the worst as the mapping
is that much harder to do in your head.

>    mxODBC, at least, rejects parameter lists of the wrong length]

With an error, right? I think that makes the most sense. And if that
makes sense, perhaps raising an error when a named variable does not
exist in the statement also makes sense, eh??? :-)

-- 
Anthony Tuininga
anthony at computronix.com
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com