Prepared statements- variable number of placeholders

[Tim Bowden]

Is it possible, and if so, how can I set the number of placeholders in a
prepared statement at runtime?

IE, given something like:

my $sth = $dbh->prepare("INSERT INTO $table ($fields) VALUES (?,?,?));

which is fine if I know how many fields I'm inserting into, but what if
I don't know till runtime how many fields? How can I put the (?,?,?)
into a variable and have it recognised as placeholders?

Thanks,
Tim Bowden

[Henri Asseily]

The $sth is a mutable string… Which means you can change it at runtime!

Here's an example of a modified $sth at runtime:

my $up_sql = defined($updated_at) ? 'updated_at < ? AND ' : undef;
my $sql = "SELECT * FROM mytable WHERE $up_sql type_id = ? AND serial_number IN (
SELECT serial_number FROM anothertable WHERE device_id = ?)";
my $sth = $dbh->prepare($sql);
my $i = 1;
if (defined($up_sql)) {
$sth->bind_param($i, $updated_at, SQL_INTEGER); $i++;
}
$sth->bind_param($i, $pass_type_id, SQL_VARCHAR); $i++;
$sth->bind_param($i, $device_id, SQL_VARCHAR); $i++;
$sth->execute;

[Tim Bowden]

Thanks Henri. Much appreciated.

[Peter J. Holzer]

You could do something like

my $placeholders = '(?,?,?)';

my $sth = $dbh->prepare("INSERT INTO $table ($fields) VALUES $placeholders");

but I guess you thought of that and your question is really

How can I get $placeholders from $fields?

If I have a variable number of fields I usually have them in array, not
a scalar, so I can just count the members:

$fields = join(',', @fields);
$placeholders = '(' . join(',', map('?', @fields)) . ')';

my $sth = $dbh->prepare("INSERT INTO $table ($fields) VALUES $placeholders");

(and then you can get rid of $fields and $placeholders and do it all in
a single line)

hp

--
_ | Peter J. Holzer | Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: "Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern."
__/ | http://www.hjp.at/ | -- Wolfram Heinrich in desd