Why can't I connect to a database using DBI in my Perl CGI script?

Question

I am trying to integrate CGI with DBI . I am taking the results from the cgi and adding it on to the DBI . But cgi script not able connect to the DBI . Script is mentioned below . The script is not able to connect with DBI as its failing at connection phase .

use CGI qw/:standard/;
use Apache::DBI;
use DBI ;
    my $driver = "mysql"; 
           my $database = "testdb";
           my $dsn = "DBI:mysql:testdb";
           my $userid = "testuser";
           my $password = "test123";

    print
           header,
           start_html('Simple Script'),
           h1('Simple Script'),
           start_form,
           "What's your First name? ",textfield('First name'),p,
           "What's your Last name? ",textfield('Last name'),p,
           "What's your Age? ",textfield('Age'),p,
           "What's your Income? ",textfield('Income'),p,
            popup_menu(-name=>'Sex',
              -values=>['Male','Female']),p,
           submit,
           end_form,
           hr,"\n";
           if (param) {
           my $FIRST_NAME=em(param('First name')) ;
           my $LAST_NAME=em(param('Last name')) ;
           my $AGE=em(param('Age')) ;
           my $INCOME=em(param('Income')) ;
           my $SEX =em(param('Sex')) ;

           print 
           $FIRST_NAME ,p,     
           $LAST_NAME,p, 
           $SEX,p, 
           $AGE,p, 
           $dsn,p
           $INCOME ;    



            my $dbh = DBI->connect($dsn,"root","" ) or die "Can't connect: ",$DBI::errstr;

           my $stmt ;
           my $sql = "INSERT INTO TEST_TABLE (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME ) values (?,?, ?, ?, ?)";

           my   $stmt = $dbh->prepare($sql);    
           $stmt->execute($FIRST_NAME,$LAST_NAME, $SEX, $AGE, $INCOME) or die $DBI::errstr;

           }       
           print end_html;

Answer 1

There's not nearly enough information here to give you an answer. In particular, there is no way that we can work out what the problem is if we don't know what the error is. You say there's no error message. That's very unlikely, as your code explicitly dies with an error message if the connection fails. This leaves us with two possibilities:

• Your program is connecting successfully
• There's an error message, but you haven't seen it yet.

Let's assume it's the second option. Perhaps you don't realise that CGI programs deliberately don't send their error messages to the browser (this is a security feature and you shouldn't try to change it) and that you need to look in the web server error log.

Here are three ways that you could see the error message.

• Look in the error log. No, we don't know where the error log is. It depends on the configuration of your server. If we knew which environment you are using then we could take a guess. But as it is, you'll need to ask someone who knows how the server is configured.
• Run your program on the command line. Yes, I know that it's a CGI program. But it's often easiest to debug programs by running them on the command line. You'll need to pass a parameter in as that triggers your program connecting to the database. Then, if there's an error it will be written to STDERR (which is your console).
• Add use CGI::Carp 'fatalsToBrowser'; to your code. Remember above where I said that you shouldn't change the fact that CGI errors aren't written to the browser? Well, this is how you override that. It's a terrible idea and I really don't recommend it (mainly because you should remove it again before you put the code into production - and everyone always forgets that and leaves a gaping great security hole in their web server!) but it works in extreme situations where you really can't find out where the error log is and you don't know how to use a command line.

So one of those methods will tell you what the error is. If you edit your question to add that vital detail, then I'm sure someone here will be able to help solve your problem.

Some other tips:

• Apache::DBI is doing nothing useful here. Just remove it.
• Learn to format your code.
• The HTML generation functions have been recognised as a terrible idea since the last millennium and now they are deprecated. Please don't use them. Use a templating system instead.
• The <p> element in HTML is a container, not a separator.
• Please read CGI::Alternatives and learn something about how web applications are written in Perl in the 21st century.