CODEWITHSUNDEEP
mysqlperldbi
Matthew Tuman
Matthew Tuman

Reputation: 49

Unable to connect to mysql using perl DBI module

Hi I am trying to connect to a mysql database using the following perl script:

 #!/usr/bin/perl -w
 use strict;
 use DBI;
 my $dbh = DBI->connect(          
     "dbi:mysql:dbname=MYDATABASENAME", 
     "MYUSERNAME",                          
     "MYPASSWORD",                          
     { RaiseError => 1 },         
 ) or die $DBI::errstr;

 my $sth = $dbh->prepare( "SELECT * FROM classes" );  
 $sth->execute();

 my ($class_id, $class_name, $class_number) = $sth->fetchrow();
 print "$class_id $class_name $class_number\n";

 my $fields = $sth->{NUM_OF_FIELDS};
 print "We have selected $fields field(s)\n";

 my $rows = $sth->rows();
 print "We have selected $rows row(s)\n";

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

Right now I am just getting a blank screen and I can't figure out why I am not at least getting an error message. I've spent over 20 hours trying to figure this out which is why I'm hoping someone can see what I'm doing wrong. Thanks in advance.

Upvotes: 0

Views: 1630

Answers (2)

Dave Cross
Dave Cross

Reputation: 69244

In a comment, you add this useful information that should really have been in the original question.

Sorry I'm not running it from the command line. I have it saved in a cgi-bin folder in my web hosting account. The blank screen is in the browser when I run the script.

This program isn't going to work as a CGI program as you're not returning the content-type header. Try adding these two lines to the top of your program.

use CGI 'header';
print header('text/plain');

You should also find out where the web server error log lives and check that for errors. It's a security feature that CGI programs don't send their errors to the browser (although I'd expect to see some indication that there was a problem).

It's also worth pointing out that the use warnings pragma was added to Perl in version 5.6 (released in 2000) and that most programmers use that in place of -w on the shebang line.

I'd also suggest that it's often (probably always) a good idea to test a new Perl module as a command-line program before writing a CGI version. CGI just adds an extra, unnecessary, level of complexity when you're learning something new.

Upvotes: 2

user7818749
user7818749

Reputation:

This should work. The port is optional if you are running the script locally.

 use strict;
 use warnings;
 use DBI;

 my $database   = 'name_of_database';
 my $DBhost     = 'localhost';
 my $port       = 'portnumber';
 my $username   = 'user123';
 my $password   = 'password123';

 my $dbh = DBI->connect("dbi:mysql:database=$database;host=$DBhost;port=$port",$username,$password,                          
     {  RaiseError       => 1,
        PrintError       => 0,
        AutoCommit       => 1,
        FetchHashKeyName => 'NAME_lc'}, ) or die $DBI::errstr;

 my $sth = $dbh->prepare( "SELECT * FROM classes" );  
    $sth->execute();

 my ($class_id, $class_name, $class_number) = $sth->fetchrow();
    print "$class_id $class_name $class_number\n";

 my $fields = $sth->{NUM_OF_FIELDS};
    print "We have selected $fields field(s)\n";

 my $rows = $sth->rows();
    print "We have selected $rows row(s)\n";

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

Upvotes: 1

Related Questions