SQL Variable Substitution Question

[Paul Boddie]

Tim Roberts wrote:
> 
> liang at cad.gatech.edu (Shengquan Liang) wrote:
> >
> >say i have yyyy = 1000
> >
> >in the documentation of DC Oracle, it says that
> >i shoul d use 'select * from attt'
> >               'where bbbb = :yyyy'
> >
> >to make it work.
> 
> No, SQL doesn't have any idea what your Python variables are.  To use the
> ":" feature, you have to have set an _SQL_ variable called "yyyy".  How you
> do that varies with the backend.

Guessing, with the help of the DB-API specification, I would try:

  cursor.execute('select * from attt where bbbb = :yyyy', {"yyyy" : yyyy})

Or:

  cursor.execute('select * from attt where bbbb = :yyyy', yyyy=yyyy)

See the specification at:

  http://www.python.org/topics/database/DatabaseAPI-2.0.html

> In your case, it's probably easier to build the complete string in Python
> and pass that to the database:
> 
>   'select * from attt where bbbb = %d' % yyyy

I would advise against this, and given the numerous recent occasions when such
advice has been given on comp.lang.python, I rather suspect that most Python
database application writers who make their opinions known either don't know
about bind variables or don't trust them. As for being easier, take a look at
this suggestion:

Scott Barron wrote:
> 
> Perhaps you are looking for something like this:
> 
> query_string = "SELECT AAA FROM BBB WHERE cc = %s"
> 
> then you could do:
> 
> query_function (query_string % `pp`)  # if pp is an integer type
> 
> or
> query_function (query_string % pp)   # if pp is a string type
> 
> or change %s to %d or whatever,  depending on what pp is.

Will the second case actually work? I don't see the % operator adding '
characters to make the query string correct, or dealing appropriately with '
characters inside any string literal. It seems easier to use bind variables, in
my opinion.

Tim Roberts wrote:
> 
> liang at cad.gatech.edu (Shengquan Liang) wrote:
> >
> >BUT, when i tried it this way, the
> >iDC oracle reponded by:
> >
> >  oci.err:(1008,'ORA-1008: not all variables bound\012')
> >
> >what's the problem with that?
> 
> You haven't bound the SQL variable yyyy to any value.  As I said, the
> method for doing that varies with the backend.

Indeed. If the above methods don't work, try using numeric variable names:

  cursor.execute('select * from attt where bbbb = :1', (yyyy,))

  cursor.execute('select * from attt where bbbb = :1 and cccc = :2',
    (yyyy, zzzz))

This definitely worked in previous Oracle modules. Note that a tuple must be
supplied as the second argument.

Paul