bind variables with array

hakkr has asked for the wisdom of the Perl Monks concerning the following question:

HI, If I call the below sub with an sql statement containing placeholders and an array of values it treats @vars as a single bind variable. can execute not be called with an array, is there anyway of calling execute without explicitly declaring x number of variables for each statement? or has it just been so long since I did perl I forgot perlthink..

###call below sub
runsql("select * from table where 1=? and 2=?",$dbh,[$1,$2]);


    sub runsql {
         my $sql=shift;
         my $dbh=shift;
         my $vars=shift;
         my $sth=$dbh->prepare_cached($sql);
         $sth->execute(@$vars);

}
[download]

thanks

Comment on bind variables with array Download Code

Replies are listed 'Best First'.
Re: bind variables with array by gmax (Abbot) on Jun 07, 2004 at 15:45 UTC
I see ~~two~~ three potential problems with your code: your query is using `1 = ?`. Was that just a careless example or are you trying to put some column names as parameters? Are you sure that $1 and $2 are set to a meaningful value before you call the sub? Your sub is not returning anything. What's going to happen after the execute? Apart from that, passing an array of parameters is a legitimate way of calling execute, as you can see in DBI docs and DBI Recipes. Can you build a simple and short test case, with sample data, so we can see where and how your script fails? _ _ _ _ (_\|\| \| \|(_\|>< _\|	[reply] [d/l]
Re^2: bind variables with array by hakkr (Chaplain) on Jun 08, 2004 at 11:27 UTC
Thanks yes that was just a rough example , I found my problem !! If you call `execute(());` that is with a blank array you get an error so I had to put in a check to prevent it getting called when no placeholders are passed in `my $res = (defined @$vars) ? $sth->execute(@$vars) : $sth->execute;` Thanks for the pointer to the docs looks like execute(@) justs calls bind_param() for each value in the array so it does work.	[reply] [d/l] [select]