I'd recommend that you separate the CGI stuff from the DBI stuff so that you can debug them separately. Make a get_data_from_CGI() method and get_data_from_constants() method that both return a list of values, then feed that list to a do_db_stuff_with_values() mehtod. Make sure it works with the constants ( e.g. $ID = '999' ), then you'll know your DBI stuff is working and you can then check it from CGI input.
in reply to Re: Re: Re: Re: Re: Re: DBD::CSV - how to install? (FTP only)
in thread DBD::CSV - how to install? (FTP only)
My strong suspicion is that you're not getting a $ID value from the form and that you are essentially trying to insert '' into the database which (because of a bug) produces the odd error message you got.
The other thing that is tripping you up is the quoting in SQL. A SQL insert looks something like this: INSERT INTO foo (first_col,second_col) VALUES ('string',7). Notice that string has single quotes around it and the number does not. It works the same way with variables - INSERT INTO foo (first_col,second_col) VALUES ( '$string', $number ). Your INSERT statement has no quotes around anything. The best solution is to never have to care about the quotes: USE PLACEHOLDERS:
No quotes, no muss, no fuss. See the DBI docs on placeholders.
my $sth = $dbh->prepare("
INSERT INTO foo (first_col,second_col) VALUES (?,?)
$sth->execute( $string, $number );
Another good way to catch problems is to always (when debugging) print out the SQL before executing it:
DBI::trace() will also show you that same information, eventually you might want to check it out (see the DBI docs).
my $DEBUG = 1; # comment out when not debugging
my $sql = "INSERT INTO foo (first_col,second_col) VALUES (?,?)";
my @params = ($string,$number);
print "$sql\n[@params]\n" if $DEBUG;
my $sth = $dbh->prepare( $sql );
$sth->execute( @params );
Hopefully all that is enough for you to be able to debug your script. If not, holler.