Re: Prepared statements- variable number of placeholders

On 2012-10-22 15:16:47 +0800, Tim Bowden wrote:
> 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?

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
| |   | [email protected]      | bleiben, mir und andern."
__/   | http://www.hjp.at/ |    -- Wolfram Heinrich in desd

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)

iD8DBQFQhQvOMdFfQa64PCwRAsCFAJ46DxlcBfX1VgN2KDXRTOkqrsQqswCeOc57
jTfN7kOdhoDHMWMcRjiHa4A=
=mZUD
-----END PGP SIGNATURE-----