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?

Antonyantonym

Please don't use $a (and $b) as nonce variables. They're special and specially defined: perldoc.perl.org/perlvar.html#%24a – Arraign

Answer 1

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.

Valarievalda

Answer 2

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))) . ')';

Mold

All these solutions would require typing $a three times, which I believe is the sole task User1 is attempting to avoid. – Seessel

@Bipedal: one could pass the bind parameters as (($a) x 3). – Mold

Yes, the x operator can be used as a workaround, but this does not change that 1) the OP asked if there is a way to pass in a variable once and have it bound to multiple placeholders and 2) using the x operator passes in the variable multiple times, once for each placeholder, which is what the question wants to avoid. – Pap

@Dave: there isn't really any good reason to avoid it though. If there are three bind parameters in the query, three arguments ought to be passed in. Perhaps I don't understand this particular type of query, but what the OP wants seems odd. – Mold

@Ether: I realize this is a super old question, but it's not odd; especially when you use an array. You might have something like select ($1)[i] from generate_series(1,array_upper($1,1) ) i, which creates a table from an array. The larger the array is, the longer its going to take to run the query round trip. If you sent the same parameter twice, it would further increase the network transfer time. Minimizing the amount of data sent over the line should increase all-around efficiency and security. – Pungent

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.

Arraign

Answer 4

You can set SQL variables in one SQL statement and then use that variable multiple times in the next query.

$dbh->do('set @reusable = ?', undef, $perl_var);
$dbh->select_arrayref('select * from table where cola = @reusable or colb = @reusable');

No duplicated variables and you still get the safety of parameterized queries.

Hinman