[DB-SIG] spec: named parameters: clarification needed

[Harald Meland]

[Matthew T. Kromer]

> Arguably, you really want a helper method to construct parameterized
> SQL for you, e.g.
>
>     sql = "SELECT * FROM TEST WHERE"
>     args = DB.NewBindingArgs()
>     text, arg = DB.BindParameter("FOO", foo, type="string")
>     sql + = text
>     args = DB.MergeArgs(args, arg)
>
> which might yield sql = "SELECT * FROM TEST WHERE FOO = :1" and args =
> [foo,] for one driver which does numeric binding, and sql = "SELECT *
> FROM TEST WHERE FOO = 'foo value'" and args = None for a driver which
> doesn't do parameter binding, and sql = "SELECT * FROM TEST WHERE FOO
> = :foo" and args = { 'foo': foo , } for a driver that does named
> binding.
>
> Unfortunately, its awfully crufty to generate things this way -- not
> that there aren't cases where this would be helpful.

Yes, it's somewhat crufty -- but if someone would like to look at an
implementation (which also has some hooks for translating a few SQL
dialect differences), we've implemented this in Cerebrum (which in
general requires Python 2.2.1; however, it's possible that the bind
param munging stuff can be used with older Pythons as well):

  <URL: http://sourceforge.net/projects/cerebrum>

The implementation resides in Cerebrum/Database.py, but also uses a
Plex-based (<URL:http://www.cosc.canterbury.ac.nz/~greg/python/Plex/>)
tokenizer in Cerebrum/SqlScanner.py.

(The .query() and .query_1() methods makes use of Kevin Jacobs'
excellent db_row module, so I'll use this opportunity to let him know
that I'm yet another happy user. :-)

Basically, we write all our SQL using the "named" style, use the
tokenizer to translate the SQL into the style appropriate for a
particular driver, and cache the translated SQL along with a
translator for converting "named"-based dicts into whatever
'parameters' argument that should work for the driver.

Note that this is nowhere near as fancy as the stuff Kevin keeps
whetting our appetites with ;-), but it seems to do the job for
Cerebrum (in it's current "no real release yet" state).
-- 
Harald