MySQL Error

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

Hey All,

Can anyone see any bugs here, it seems to be erroring when it gets to execute() but i cant see any errors at all:

if (defined $status)    {
    $statement = $dbh->prepare('SELECT COUNT(*) FROM name WHERE status
+ = "' . "$status" . '"') or die DBI->errstr;
    } else     {
    $statement = $dbh->prepare('SELECT COUNT(*) FROM name WHERE age = 
+"4"') or die DBI->errstr;
    $status = 'OPEN';
     }
    $sth = $dbh->prepare($statement) or die print "Couldn't prepare st
+atement: $DBI::errstr; stopped";
    $sth->execute() or die print "Couldn't execute statement: $DBI::er
+rstr; stopped";
    my $count = $sth->fetchrow_array();
    $sth->finish;
[download]

Error Message:

Couldn't execute statement: You have an error in your SQL syntax near 
+'DBI::st=HASH(0x8298e60)' at line 1;
[download]

Thank you very much

Comment on MySQL Error Select or Download Code

Replies are listed 'Best First'.
Re: MySQL Error by Hero Zzyzzx (Curate) on Nov 20, 2001 at 08:25 UTC
You're prepare()ing your statement twice before executing it. That's your problem. You really should look into placeholders, too. If you use them, they'll allow you to avoid that annoying single-quote/double-quote breakdancing you're doing in your first prepare statement. They take care of all quote escaping, too, so you'll never have to worry about quotes breaking queries again. -Any sufficiently advanced technology is indistinguishable from doubletalk.	[reply]
Re: Re: MySQL Error by Anonymous Monk on Nov 20, 2001 at 08:37 UTC
DOH!! 3AM coding perl...never a good idea :)	[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.
Re: MySQL Error by thunders (Priest) on Nov 20, 2001 at 19:36 UTC
You want something like this: `if (defined $status) { $statement = qq(SELECT COUNT() FROM name WHERE status = $status); }else{ $statement = qq(SELECT COUNT() FROM name WHERE age = "4"); }` [download] Then call your statement like this: `$sth = $dbh->prepare($statement); $sth-> execute();` [download]	[reply] [d/l] [select]
Re: Re: MySQL Error by Caillte (Friar) on Nov 20, 2001 at 21:29 UTC
This isn't perfect as you do not quote $status. It is often better to let DBI do that for you: `my $arg; if(defined $status) { $statement = 'SELECT COUNT() FROM name WHERE status=?;'; $arg = $status; } else { $statement = 'SELECT COUNT() FROM name WHERE age=?;'; $arg=4; } $sth = $dbh->prepare($statement) or die "Cannot Prepare $DBI::errstr"; $sth->execute($arg) or die "Cannot execute $DBI::errstr";` [download] Calling the statement this way makes sure the data is properly quoted. `$japh->{'Caillte'} = $me;`	[reply] [d/l] [select]