MySQL and PERL tribulations....

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

O.K. I'm looking at it and can't figure out why it's not working. I think I'm simply tired at this point. Here is a piece of code:

$dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1 })
+;

    $query = "SELECT nw_ucountry FROM $Table";
    $query .= " WHERE nw_ucountry LIKE '$country'";

    $sth = $dbh->prepare($query);
    $sth->execute;


    while (($qcountry) = $sth->fetchrow_array()) {
        unless ($country =~ /$qcountry/i) {
        
            my $mdate = `date +'%Y-%m-%d'`;
            my $mtime = `date +'%H:%M:%S'`;
        
            $rows = $dbh->do("INSERT INTO $Table VALUES('','$country',
+'$passwd1','$email','$fname','$lname','$mdate','$mtime','')");
            &header("We are done");
            print "We're done.\n";
            &footer();            
            
        } else {
            
            &header("Duplicate Country Name");
        
            print "<h3>You have selected $country for your country.</h
+3><p>\n";
            print "<h4>It already exists in our database.</h4>\n";
            print "<hr>\n";

            &footer();                            
        }
    }

$sth->finish();
$dbh->disconnect();

exit(0);
[download]

I am blind. Why do I get server errors?

Thanks.

Comment on MySQL and PERL tribulations.... Download Code

Replies are listed 'Best First'.
Re: MySQL and PERL tribulations.... by extremely (Priest) on Nov 08, 2000 at 12:46 UTC
Try `$sth->trace(4);` just before the execute and see what the script dumps out STDERR. If this is a CGI it'll be in your error_log file. -- $you = new YOU; honk() if $you->love(perl)	[reply] [d/l]
Re: MySQL and PERL tribulations.... by toadi (Chaplain) on Nov 08, 2000 at 14:08 UTC
`my $sql = qq(INSERT INTO db.table("field1","field2","field3") VALUES( +?, ?,now())); my $sth = $dbh->prepare($sql) or die "Not prepared: ".DBI->errstr; $sth->execute($var_field1,$var_field2) or die "Not executed: ".DBI->er +rstr;` [download] best is to use the ? as placeholders. This is one cause that can give errors with ' escaping the insert values string. The placeholders fix this problem! Like set before is for timestamp fields in mysql the now() function to best choice. IMPORTANT: always use the placeholders! -- My opinions may have changed, but not the fact that I am right	[reply] [d/l]
Re: MySQL and PERL tribulations.... by tune (Curate) on Nov 08, 2000 at 13:52 UTC
better way is: `$sth = $dbh->prepare($query) or die "DB Error: ".$dbh->errstr."\n";` [download] at every database execution. After that you should examine the error code in error.log, or where errors are written. It can be so much thing, that we have to have a look at that first. (e.g. different database table format, etc) Tip: besides your method fill up date and time fields with mysql now() function - if it is mysql - or with CURRENT_TIMESTAMP variable. They also work with Postgres. cheers -- tune	[reply] [d/l]
RE: Re: MySQL and PERL tribulations.... by agoth (Chaplain) on Nov 08, 2000 at 15:23 UTC
Why is your way a better way to do the errors? type or die on each statement? or use RaiseError that he correctly did in the first place and save all the typing and get the errors anyway?	[reply]
Re: MySQL and PERL tribulations.... by Jonathan (Curate) on Nov 08, 2000 at 18:17 UTC
Why not create your whole INSERT statement via a sprintf before the 'do'? You could then check it for SQL syntax accuracy and also the contents of your variables (perhaps all you need is to use the $db->quote($sql_string) method). Try running with `PrintError =>1` in your connection statement as well (can't hurt might help) What is in the $rows variable? BTW this $rows scalar is a little misnamed in that if no rows affected then the variable may well contain '0E0'- not 0! Which also of course is 'true' not 'false' - not necessarily what you want. I could lie down like a tired child, And weep away the life of care Which I have borne, and yet must bear. Shelley 1792-1822	[reply] [d/l]
RE: MySQL and PERL tribulations.... by lachoy (Parson) on Nov 08, 2000 at 18:26 UTC
In addition to the other suggestions here, you might wrap your DBI calls in an `eval {}` block, check for errors and then print out a helpful message to the browser if a message is found. Also, you should print out your http header before you do anything else to make this possible. `# I assume that this routine prints out the http header &header("We are done"); $query = "SELECT nw_ucountry FROM $Table"; $query .= " WHERE nw_ucountry LIKE '$country'"; my ($sth); eval { $sth = $dbh->prepare($query); $sth->execute; }; if ( $@ ) { print "<p>Error found executing SQL.\n", "<pre>$query\nError: $@</pre>"; } else { while (($qcountry) = $sth->fetchrow_array()) { ... } }` [download] It also helps greatly if you can keep an eye on your web server's error logs. Messages output from a `die` (either explicitly or implicitly using the `RaiseError` attribute) should appear there and would be much more helpful than the message that appears to the browser. Chris M-x auto-bs-mode	[reply] [d/l] [select]
RE: RE: MySQL and PERL tribulations.... by bman (Sexton) on Nov 10, 2000 at 02:20 UTC
Thanks to all the suggestions. However, after taking a closer look at the code next day I think, I figured out the problem (well, the script is working now). I have included my "INSERT" statement within "SELECT" while loop. It seems like when running "while" loop with select, MySQL locked the table and this was the reason why I could not write anything to it. I simply took "INSERT" out of the loop and now it's O.K. Thanks again.	[reply]
RE: MySQL and PERL tribulations.... by agoth (Chaplain) on Nov 08, 2000 at 15:24 UTC
What server errors do you get?	[reply]
RE: RE: MySQL and PERL tribulations.... by Anonymous Monk on Nov 08, 2000 at 16:43 UTC
The errors I'm getting is "Premature end of script headers: /home/httpd/cgi-bin/signmeup.p". I was checking that script and everything is workin up to the "INSERT" point. I commented the whole subroutine out and the script is working, I commented out INSERT and it's working. After I try to insert values passed from the form into the table, that's when the error stikes....	[reply]