Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Re: mySQL Query Problem

by Jazz (Curate)
on Feb 13, 2003 at 02:42 UTC ( #234890=note: print w/replies, xml ) Need Help??

in reply to mySQL Query Problem

As others have said, posting the error that mysqlPP is giving you would help identify the error. It'll let you know if it's dying on the prepare or execute instead of assuming it's on the prepare (which it can't be, because it's the execute that's trying to insert the values of your variables). If memory serves, passing values through the execute statement automatically (and correctly) quotes your variables.

Are you checking to make sure that each of your variables ($user,$title,$msg,$cat,$state,$country,$img,'left',$expiration,$name) have values and that each of those values match the type of data that the database is expecting? If one or more of them are undefined, you're binding 10 fields to less than 10 variables and mysql doesn't like that. Also, if a field is defined as an integer, but receives varchar, mysql doesn't like that either.

If this doesn't help, please post the dbi error message.

Replies are listed 'Best First'.
Re: Re: mySQL Query Problem
by kidd (Curate) on Feb 13, 2003 at 12:58 UTC

    Here is a test I created to see the problem...considering this code:

    #!/usr/bin/perl -w use strict; use CGI::Carp qw(fatalsToBrowser); use BaboonDB; my $user = "user\"; my $name = "kidd"; my $place = "CANCUN%%MEXICO"; my $title = "A test to see the problem"; my $img = "none"; my $msg = "This is a test with a question, how are you?"; my $cat = "probl"; $user = lc($user); if($img =~ /^\s*$/){ $img = "none"; } $place = lc($place); my($state,$country) = split("%%", $place); #split te place $country =~ s/usa/estados unidos/g; $country = "\u$country"; $country =~ s/\s+(\w)/ \u$1/gi; $country =~ s///gi; $state = "\u$state"; $state =~ s/\s+(\w)/ \u$1/gi; my($dbh,$sth,@data,$actual); $dbh = BaboonDB->connect(); my $interval = "12"; #First we take the current date $sth = $dbh->prepare('SELECT CURRENT_DATE') or die("Couldn't prepar +e statement: " . $dbh->errstr); $sth->execute() or die("Couldn't execute statement: " . $sth->errst +r); $actual = $sth->fetchrow_array(); $sth->finish; #Now we add the date for the expiration $sth = $dbh->prepare('SELECT DATE_ADD("?", INTERVAL ? MONTH)') or d +ie("Couldn't prepare statement: " . $dbh->errstr); $sth->execute($actual,$interval) or die("Couldn't execute statement +: " . $sth->errstr); my $expiration = $sth->fetchrow_array(); $sth->finish; # $msg = $dbh->quote($msg); $sth = $dbh->prepare('INSERT INTO anuncios (usuario,titulo,mensaje, +categoria,estado,pais,imagen,image_align,expiracion,firma) VALUES(?,? +,?,?,?,?,?,?,?,?)') or die("Couldn't prepare statement: " . $dbh->err +str); $sth->execute($user,$title,$dbh->quote($msg),$cat,$state,$country,$ +img,'left',$expiration,$name) or die("Couldn't execute statement: " . + $sth->errstr); $sth->finish; $dbh->disconnect; exit(1);

    I get this error:

    DBD::mysqlPP::st execute failed: You have an error in your SQL syntax +near 'probl'''','Cancun','Mexico','none','left','2004-02-13','kidd',? +)' at line 1 at C:\WIN98\TEMP\DZPRLTMP.PL line 56.

    But if a get the ? out of the $msg variable the script runs fine...

    Im using the DBD::mysqlPP module...


      What version of DBD::mysqlPP are you using? There's a bug in versions earlier than 0.04 that might be causing your placeholder problem. See change log for details.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://234890]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (2)
As of 2017-03-25 06:11 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (311 votes). Check out past polls.