Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Mysql syntax error from DBI

by chriso (Sexton)
on Nov 10, 2003 at 18:11 UTC ( #305925=perlquestion: print w/replies, xml ) Need Help??

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");

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.

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);
    (Use a comma instead of AND)
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);
    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?
      my $sth =$dbh ->prepare("UPDATE products set pname=? and unit=? and qty=? and cost=? WHERE id = ?");
      should do the trick.

      No. It shouldn't.

      The SQL syntax is wrong (see meetraz's answer.

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!

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://305925]
Approved by Paladin
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (10)
As of 2023-02-09 08:22 GMT
Find Nodes?
    Voting Booth?
    I prefer not to run the latest version of Perl because:

    Results (44 votes). Check out past polls.