[DB-SIG] Towards a single parameter style
Harald Meland
Harald.Meland@usit.uio.no
Mon, 17 Feb 2003 20:30:46 +0100
[Matthew T. Kromer]
> 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.
That's true for *any* parameter style. I think that qmark would be a
wee bit easier than the other styles to implement, but only due to the
fact that it's the only style whose placeholder only occupies a single
character.
> 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})
Point taken, but I think you're missing the *real* pain in the neck:
Consider
sql, args = transform("""
SELECT 'This is a literal %(foo)s', bar
FROM xyzzy WHERE frobozz=%(foo)s""", {'foo': foo})
To avoid treating the %(foo)s inside the string literal the same way
you treat the bind parameter placeholder, you will have to do some
minimal tokenizing of the first argument to transform().
> 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.
Hmmm, that might in fact be the reason why perl's DBD::Oracle in fact
translates 'numeric'-style placeholders into 'named'-style
placeholders. I would be rather surprised, to say the least, by the
behaviour you describe above.
--
Harald