Re: Re: Re: Re: Re: Re: Re: DBD::CSV - how to install? (FTP only)

[jZed]

Hi Lance, 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.

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:

    my $sth = $dbh->prepare("
        INSERT INTO foo (first_col,second_col) VALUES (?,?)
    ");
    $sth->execute( $string, $number );

No quotes, no muss, no fuss. See the DBI docs on placeholders.

Another good way to catch problems is to always (when debugging) print out the SQL before executing it:

    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 );

DBI::trace() will also show you that same information, eventually you might want to check it out (see the DBI docs).

Hopefully all that is enough for you to be able to debug your script. If not, holler.