Mysql syntax error from DBI

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

I'm getting a syntax error for the following perl/mysql statement. Can you tell me what is wrong and how to fix it? Here is the error message:

DBD::mysql::st execute failed: You have an error in your SQL syntax near 'cleaner and unit=cn and qty=15 and cost=1.35 WHERE id = 19' at line 1

Here is the code

my $sth =$dbh ->prepare ("UPDATE products set pname=$pname and  unit=$
+unit and qty=$qty and cost=$cost WHERE id = $pid");
[download]

pname should be "drain cleaner", unit should be "cn", qty should be 15, and cost should be 1.35. Thanks. Chris. Never mind, I figured it out.

Comment on Mysql syntax error from DBI Download Code

Replies are listed 'Best First'.
Re: Mysql syntax error by meetraz (Hermit) on Nov 10, 2003 at 18:44 UTC
Does that syntax work for updates? Try this: `my $sth = $dbh->prepare(" UPDATE products set pname=?, unit=?, qty=?, cost=? WHERE id = ? "); $sth->execute($pname, $unit, $qty, $cost, $id);` [download] (Use a comma instead of AND)	[reply] [d/l]
Re: Mysql syntax error by batkins (Chaplain) on Nov 10, 2003 at 18:18 UTC
Since "drain cleaner" is two words, it should be quoted. An easy way to solve this and similar problems is to use placeholders. Something like: `my $sth =$dbh ->prepare("UPDATE products set pname=? and unit=? and q +ty=? and cost=? WHERE id = ?"); $sth->execute($pname, $unit, $qty, $cost, $id);` [download] should do the trick. The question marks are replaced by the values passed to execute. For more information, see DBI. Are you sure it was a book? Are you sure it wasn't.....nothing?	[reply] [d/l]
Re: Re: Mysql syntax error by dbwiz (Curate) on Nov 10, 2003 at 19:00 UTC
`my $sth =$dbh ->prepare("UPDATE products set pname=? and unit=? and qty=? and cost=? WHERE id = ?");` [download] should do the trick. No. It shouldn't. The SQL syntax is wrong (see meetraz's answer.	[reply] [d/l]
Re: Mysql syntax error by talexb (Chancellor) on Nov 10, 2003 at 19:29 UTC
I can also strongly recommend you use placeholders in your SQL command. That gets around the ugly quoting problems. --t. alex Life is short: get busy!	[reply]