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

[Paul Moore]

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.

The cons:

  * It's potentially harder for module authors to implement (as you
    pointed out for Oracle)
  * It may be slower in cases where it's not needed.
  * 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)

The pros:

  * Supports using the same parameter dictionary for multiple SQL
    statements.
  * 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).
  * 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.

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

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
   mxODBC, at least, rejects parameter lists of the wrong length]
-- 
This signature intentionally left blank