Re: [Ruby-DBI-devel] ? replacement behavior

[Paul D. <pa...@sn...>]

At 22:07 +0000 2/13/03, Gabriel Emerson wrote:
>I need to double check this, but I think in Perl DBI I can not only do the
>equivalent of this:
>
>sql = %Q{
>   SELECT *
>   FROM bar, baz
>   WHERE bar.bar_val = ?
>   AND bar.bar_id = baz.baz_id
>}
>foo = 72
>dbh.select_one(sql, foo)
>
>but this also:
>sql = %Q{
>        SELECT *
>        FROM bar, baz
>        WHERE bar.bar_val = ?
>        AND bar.bar_id = ?
>}
>foo = 72
>sql_expr = 'baz.baz_id'
>dbh.select_one(sql, foo, sql_expr)
>
>It seems that Ruby DBI doesn't support anything for ? replacement but
>constant values for quoting.  It doesn't support table or field
>identifiers, or bits of sql code.

Placeholders are for data values, not identifiers.  Correct.

And that's how it works in Perl DBI as well.

There is now in Perl DBI a quote_identifier() function that may be
used to generate properly-quoted identifiers for insertion into
queries, but it's new and not supported by all drivers. (For example,
DBD::mysql doesn't support it yet.)

>
>I am at a loss for how to test whether something is a value or a
>[field|table|bit of aql code], but some possibilities would be an abstract
>class to inherit from if you wanted to pass a non-value, like sql_expr =
>DBI::NonVal.new('baz.baz_id'), so we can do kind_of? on it. Or we could
>use perhaps a new character for the substitution for this case.
>
>I realize we can already just #{} in the strings for whatever we want to
>replace ahead of time, but sometimes it would be nice to have this type of
>substitution in automatic query generation scenarios.
>
>--Gabriel