Values concatenation for SQL query

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

Hi Monks!

I am trying to concatenate these values to pass to my SQL call as this because of other logic I have:

…
my $string = ${middle}."%".', '.${last}.', '.${first};

my $sql = ” SELECT  MIDDLE, LAST, FIRST
FROM   mytable
WHERE  MIDDLE  like ? AND LAST = ? AND FIRST = ?   order by FIRST asc”

my $sth = $dbh->prepare($sql) or die "Prepare Error: $DBI::errstr";
       $sth->execute($string) or die "SQL Error: $DBI::errstr\n";
…
[download]

I am getting an error as :

called with 1 bind variables when 3 are needed
[download]

Some how it treats the values in “$string” as one, is there a way of doing this?

Thanks for looking!

Comment on Values concatenation for SQL query Select or Download Code

Replies are listed 'Best First'.
Re: Values concatenation for SQL query by LanX (Saint) on Mar 10, 2015 at 12:11 UTC
you most probably want to pass an array @values with 3 arguments or 3 separated $scalars, and not one concatenated string. edit what's wrong with... `$sth->execute( "${middle}%" , $last, $first );` ? Cheers Rolf _{(addicted to the Perl Programming Language and ☆☆☆☆ :)} PS: Je suis Charlie!	[reply] [d/l]
Re^2: Values concatenation for SQL query by Anonymous Monk on Mar 10, 2015 at 12:28 UTC
I know that works, I would like to know why it wouldn’t accepted the way I am trying to do it, and if there is a way of doing it.	[reply]
Re^3: Values concatenation for SQL query by Corion (Patriarch) on Mar 10, 2015 at 12:33 UTC
If you have three placeholders, DBI expects three values to be passed in for the three placeholders. There is no way around it. Maybe you want a way to extract three values out of your one concatenated string, but you haven't described your problem in a way that I can easily infer that from what you've told us so far. If you have the three values separated already, I recommend keeping them separate.	[reply]
Re^3: Values concatenation for SQL query by LanX (Saint) on Mar 10, 2015 at 12:36 UTC
> why it wouldn’t accept because with 3 `?` exactly 3 scalar arguments are expected and you only pass one > if there is a way of doing it. As already explained you could pass an array `@values` which is initialized with something like `@values=($a,$b,$c)` > the way I am trying to do it are you probably trying to code like in Tcl where (IIRC) ~~comma~~ space separated strings and arrays belong to the same data type? That's not Perl, sorry. Cheers Rolf _{(addicted to the Perl Programming Language and ☆☆☆☆ :)} PS: Je suis Charlie!	[reply] [d/l] [select]
Re: Values concatenation for SQL query by KurtSchwind (Chaplain) on Mar 10, 2015 at 12:33 UTC
What Lanx said. You have three variable bindings in your prepared sql, and you are passing in one variable. The execute is expecting to find 3 variables to pass to the bindings. What you probably want to do is something like this `my @bindings = ( $middle, $last, $first); ... $sth->execute(@bindings); ...` [download] $string is a single scalar. -- “For the Present is the point at which time touches eternity.” - CS Lewis	[reply] [d/l]
Re^2: Values concatenation for SQL query by Anonymous Monk on Mar 10, 2015 at 12:42 UTC
Thank you, just tried using an array as I read your post, but thanks a lot, it works now!	[reply]

edit