Reusing ?'s on a DBI prepare

Question

Is there a way to reuse the ?'s used on a DBI prepare statement. Consider the following code:

$sth=$dbh->prepare("INSERT INTO mytable(a,b,c) SELECT ?,B(?),C(?)");
$sth->execute($a,$a,$a);

It would be very nice to instead use something like this:

#I'm making this up as something I hope exists
$sth=$dbh->prepare("INSERT INTO mytable(a,b,c) SELECT ?,B(?:1),C(?:1)");
$sth->execute($a);

Notice that only one $a is passed to the execute instead of three. Is there a way to do this in real life?

Answer 1

If you use a library to generate your SQL statements for you, e.g. SQL::Abstract or a full-on ORM like DBIx::Class, you won't have to worry about things like that.

Alternatively you can do something similar with just a few lines of code:

my $sql = 'INSERT INTO ...blah blah... VALUES (' . (join(', ', ('?') x scalar(@insert_elements))) . ')';

Answer 2

It depends on your DBD. For example, using DBD::Pg with the $1 style of placeholders, or DBD::Oracle with named placeholders and bind_param, you can do exactly what you like. But using the general purpose ? style of placeholders that works DBI-wide, it's not possible.

Answer 3

@hobbs' answer is right -- default DBI placeholders can't do it. @Ether's answer is right -- a SQL abstraction can make this a non-issue.

However, typically one need only bind each distinct parameterized value once. In your example, using a scalar derived table makes the user-supplied value available by name to the rest of the query:

my $sth = $dbh->prepare(<<'__eosql');
    INSERT INTO mytable(a,b,c)
                SELECT x, B(x), C(x) FROM (SELECT ? AS x) subq
                              -- Subquery may vary from DB to DB:
                              --    "FROM (SELECT ? AS x FROM DUAL) subq"
                              --    "FROM (SELECT ? FROM rdb$database) subq(x)"
                              --    "FROM (VALUES (?)) subq(x)"
                              -- etc.
__eosql

for $v (@values) {
    $sth->execute($v);
}

Usually this is incrementally more "wire efficient" than the alternative, since the user-supplied parameter is typically transmitted just once instead of N times.