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

Mysql error near values(

by coldfingertips (Pilgrim)
on Feb 04, 2006 at 23:21 UTC ( [id://528016]=perlquestion: print w/replies, xml ) Need Help??

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

I am getting a mysql error near values(. Can anyone see why?

$dbh->do("INSERT INTO storage (url, altavista, yahoo, msn, teoma, go +ogle, alltheweb,Total, lastsearch, totalsearch) values($url, $altavis +ta_results, $yahoo_results, $msn_results, $teoma_results, $google_res +ults, $alltheweb_results, $total,$time, $total)") unless $dbh->do(UPD +ATE storage SET (url = "$url", altavista = "$altavista_results", yaho +o = "$yahoo_results", msn = "$msn_results", teoma = "$teoma_results", + google = "$google_results", alltheweb = "$alltheweb_results", total += total +1, time="$time") > 0;
It was suggested that I use placeholders, and I normally do but I'm not sure how to apply them to a $dbh->do. I usually use three lines to insert mysql data, not one.

Originally there were quotes around all variables in the values() but I took them out thinking they were the problem.

Replies are listed 'Best First'.
Re: Mysql error near values(
by helphand (Pilgrim) on Feb 04, 2006 at 23:27 UTC
    Originally there were quotes around all variables in the values() but I took them out thinking they were the problem.

    There's a reason single quotes would have been there, unless the variables in question are already $dbh->quote 'd, then the correct syntax for the INSERT statement calls for the data to be quoted.

    From the limited snippet you supply, that's about all one can ascertain.

    Scott

Re: Mysql error near values(
by reneeb (Chaplain) on Feb 04, 2006 at 23:29 UTC
    unless($dbh->do("UPDATE storage SET (url = ?, altavista = ?, yahoo = ? +, msn = ?, teoma = ?, google = ?, alltheweb = ?, total += total +1, time = ?",undef,$url,$altavista_results,$yahoo_results,$m +sn_results,$teoma_results,$google_results,$alltheweb_results,$time) > + 0){ $dbh->do("INSERT INTO storage (url, altavista,yahoo, msn, teoma, goo +gle, alltheweb,Total, lastsearch, totalsearch) values(?,?,?,?,?,?,?,? +,?)",undef,$url, $altavista_results, $yahoo_results, $msn_results, $t +eoma_results, $google_results, $alltheweb_results, $total,$time, $tot +al)"); }


    Edit: you should have a look on the "insert ... on duplicate key" syntax: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
      Is there a syntax error in yoru code? I C/P'd and the whole thing went nuts on me.

      I took a quick look on insert on duplicate key but I want the opposite of what it does. From what I read, it'd make a duplicate of it if it's found. I'd only want to insert IF the column "url" was not found in the first place.

      Thanks.

        I C/P'd and the whole thing went nuts on me.

        When you did that, were you careful to remove all the "+" characters that had been inserted by PM's own code-line-wrap logic?

        It looks like reneeb was not so careful about this, because that post includes  total += total +1 (where the first "+" had been added by PM line-wrapping).

        BTW, "went nuts" doesn't sound like the kind of error code that I could look up and help you with. If you don't actually look at the content of the error report, and don't show us what it is, how do you expect to fix the error?

        Any error messages??
Re: Mysql error near values(
by BazB (Priest) on Feb 04, 2006 at 23:31 UTC

    DBI's do() method does not use placeholders - it's there specifically for drivers that do not support placeholders.

    You should quote any strings in the SQL that you're executing.
    You might want to format things a little better so that any errors are easier to find, rather than having a huge block of code (which in this case has two SQL statements).

    Edit: what am I talking about? do() does support placeholders.


    If the information in this post is inaccurate, or just plain wrong, don't just downvote - please post explaining what's wrong.
    That way everyone learns.

      do does support placeholders. Have a look at http://search.cpan.org/~timb/DBI-1.50/DBI.pm#Database_Handle_Methods
Re: Mysql error near values(
by GhodMode (Pilgrim) on Feb 05, 2006 at 11:36 UTC

    It's hard to find the error because your code is all on one line, but it looks like the problem is that your URL isn't quoted. That must be the problem.

    Using placeholders might make it easier for you to code if you are using a loop to execute the statements, but it might not give you a performance benefit in MySQL. MySQL doesn't support server-side prepared statements by default and I think that's where the performance benefit comes in with placeholders and bind values. I've had some problems with turning on this setting on, but try it if you want to experiment with it. ref: DBD::mysql (search for "prepared").

    Columns with character data types will need quotes. You can quote everything, though because it won't break anything with the other data types.

    MySQL will tell you what's wrong if you set up your queries properly. Break it back out to more lines during troubleshooting, then condense it again when everything works.

    Here's my suggested method:

    use strict; use warnings; my $connect_string = "DBI:mysql:$CONFIG::database"; my $dbh = DBI->connect( $connect_string, $CONFIG::dbusername, $CONFIG::dbpassword, \%CONFIG::dbattr ); my $statement; my $result; { package CONFIG; %dbattr = ( PrintError => 0, ChopBlanks => 0, ); $database = "stuff"; $dbusername = "username"; $dbpassword = "password"; } my $insert = qq{ INSERT INTO storage ( url, altavista, yahoo, msn, teoma, google, alltheweb, Total, lastsearch, totalsearch ) values( '$url', '$altavista_results', '$yahoo_results', '$msn_results', '$teoma_results', '$google_results', '$alltheweb_results', '$total', '$time', '$total' ) }; my $update = qq{ UPDATE storage SET url = "$url", altavista = "$altavista_results", yahoo = "$yahoo_results", msn = "$msn_results", teoma = "$teoma_results", google = "$google_results", alltheweb = "$alltheweb_results", total = total +1, time = "$time" }; $statement = $update; $result = $dbh->do( $statement ); if ( ! defined $result ) { die "Fatal SQL error :\n$statement\n" . $dbh->errstr; } # If there were no rows affected by the update, execute the # insert if ( $result < 1 ) { $statement = $insert; $result = $dbh->do( $statement ); if ( ! defined $result ) { die "Fatal SQL error :\n$statement\n" . $dbh->errstr; } }
    --
    -- GhodMode
    
      Please use the placeholders!
      What if the google result contains a ' ?? Then your statement fails. Or use at least the $dbh->quote() method. It is much more security to your scripts.

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others admiring the Monastery: (4)
As of 2024-03-28 17:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found