[DB-SIG] Towards a single parameter style

[M.-A. Lemburg]

Matthew T. Kromer wrote:
> M.-A. Lemburg wrote:
> 
>> Now this would be hard to agree on and it will also break existing
>> code, so I think we should just make the preferred paramstyle
>> a suggestion in the DB-API. I'd vote for the simplest of them
>> all: the 'qmark' style.
> 
> Qmark is the  style that's probably the hardest to cope with for 
> binding, as well.  To do it fast you'd need a C extension just to rip 
> the SQL apart to find non-escaped or quoted question marks.

It can't be that hard: after all, all ODBC drivers have to deal
with this and they tend to not have a problem with it.

> The dictionary modes or string substitution modes would be easier to 
> deal with, but still pose problems for purely positional parameter binds.
> 
> Consider the following:
> 
>   sql, args = transform( 'SELECT FROM FOO WHERE BLA=%(bla)s AND 
> OOG=%(oog)s', {'bla': bla, 'oog': oog})
> 
> A simple translator might try:
> 
>    args = []
>    c = dict.copy()
>    for key, value in c.items():
>        args.append(value)
>        c[key] = '?'
> 
>    sql = statment % c
>    return sql, args
> 
> Yet this would be dependant on the order of keys()!  It would 
> conceivably alter the oder of BLA and OOG (in fact,  in my test case, I 
> think it does).

Right, but that's because you have to use the SQL to fetch the
values -- not the other way around.

> Here's another example:  Oracle's positional parameter binds use a 
> notation like :1, :2, :3 etc.  But as near as I can see, it doesn't 
> actually CARE what the number is -- the first one to appear in the 
> expression is 1, the second is 2, etc -- so an expression that is 
> 'SELECT FROM FOO WHERE BLA=:2 and OOG=:1' is identical to 'SELECT FROM 
> FOO WHERE BLA=:1 and OOG=:2' -- the number in the format string is 
> meaningless, only its position matters. 

Now that's an interesting approach :-) (even Microsoft wouldn't have
done better). Sounds as if you could just replace ':<n>' with '?'.
I always thought that the number indexes into the parameter
sequence.

> Fortunately, Oracle has named 
> binds as well as positional binds -- but not all databases do.
> 
> As awful as it is, the style that's probably MOST univeral is "SELECT  * 
> FROM FOO WHERE BLA=%s AND OOG=%s", (bla, oog) since this preserves the 
> order of the parameters at the sake of losing their names (but synthetic 
> names can be conjured for databases that need named binds). 

The problem I see with this is that the format marker implies
a special conversion (in this case to a string). Now this may
be useful to have, but it's not always what the database
requests. In ODBC, where you have the qmark parameter marker,
the driver usually tells the application what format to pass
in, so you'd have a conflict here: what to do if the driver
wants an integer and you specify a string in the SQL marker.

> A qmark or 
> positional binder can easily substitute in '?' or ':1', ':2' etc and 
> return the argument list as the bind parameters, and a named binder can 
> substitute in ':p1', ':p2' etc and return a dictionary of {'p1': param1, 
> ...} as the argument vector.  The only case that isn't handled properly 
> that I can see is those drivers that support NO binding at all -- but 
> even those can guess at their format based on the type of the argument 
> (ie quote strings but don't quote numbers).

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Feb 17 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
Python UK 2003, Oxford:                                     43 days left
EuroPython 2003, Charleroi, Belgium:                       127 days left