[Solved] Mysql DBI placeholders not working

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

Solution: Bind variables should be an ARRAY, not an ARRAY REFERENCE. Original post follows:

It's the most bizarre thing. I've no idea what is going wrong.

I have the following code:

my $sql='UPDATE products SET sku=? WHERE sku=?';
my $sql_values = [$sku, $old_sku];
$dbh->do($sql, undef, $sql_values);
[download]

This fails with the error:

DBD::mysql::db do failed: You have an error in your SQL syntax; check 
+the manual that corresponds to your MySQL server version for the righ
+t syntax to use near '' at line 1 at ...
[download]

However, the following code works fine:

my $sql='UPDATE products SET sku="'.$sku.'" WHERE sku="'.$old_sku.'"';
$dbh->do($sql);
[download]

Of course, I want to use placeholders in my SQL. I've been staring at this for the past 4 hours and have no clue what's going on. I don't even know where to begin Googling. This is incredibly bizarre. Elsewhere in the code, placeholders are working just dandy.

Edited to Add: Wow, I'm a moron. The bind variables should be an array, not an array reference... and all it took for me to figure that out was RTFM. Apologies for wasting your time, dear monks. I'd delete this node if I could.

Comment on [Solved] Mysql DBI placeholders not working Select or Download Code

Replies are listed 'Best First'.
Re: Mysql DBI placeholders not working by bitingduck (Deacon) on Mar 17, 2015 at 05:27 UTC
The bind variables should be an array, not an array reference.... Apologies for wasting your time, dear monks. I'd delete this node if I could. Not a waste of time at all, and it's more valuable to leave the post up than to delete (or even strike through). Any mistake you make will be be made by countless others who might be spared some time because your post remains up and appears in search results. And in reading through the new nodes it's nice to be reminded of things to watch out for in the future.	[reply]
Re: Mysql DBI placeholders not working by Anonymous Monk on Mar 17, 2015 at 03:42 UTC
I bookmarked DBI recipes long ago :) see Binding columns and fetching Binding scalars Binding a list Binding an array	[reply]
Re: Mysql DBI placeholders not working by Laurent_R (Canon) on Mar 17, 2015 at 07:10 UTC
Don't delete it, people can learn from it. I saw your code and thought: "I did not know that an arratref could be used for binding variables. Or, perhaps this is the origin of the problem. Hmm, I have to look it up." It turned out that I did not need to look it up, because your update clarified it. Well, in brief, thank you, I learned something from your post. ++ Je suis Charlie.	[reply]
Re: Mysql DBI placeholders not working by choroba (Cardinal) on Mar 17, 2015 at 10:05 UTC
I'd delete this node if I could. You can add '[Solved]' to the title, though, if you want only the most curious ones to read the question. لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ	[reply]
Re^2: Mysql DBI placeholders not working by Anonymous Monk on Mar 17, 2015 at 10:10 UTC
You can also add (Solved) or {Solved} Solved: :)	[reply]