Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

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 scrutinizing the Monastery: (5)
As of 2023-02-01 18:56 GMT
Find Nodes?
    Voting Booth?
    I prefer not to run the latest version of Perl because:

    Results (12 votes). Check out past polls.