Unable to connect to mysql using perl DBI module

Question

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.

Are you saying that, when you run it, it just hangs and you have to Ctrl-C to get the prompt back? — jrefior
– jrefior, Commented May 26, 2017 at 2:27
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. — Matthew Tuman
– Matthew Tuman, Commented May 26, 2017 at 2:30
Also, this may be a dumb question but I just want to make sure...I have a lot of experience using mysql in conjunction with php but I'm new to perl. The username and password fields are the same username and password I would use if I was connecting to the database with a php script right? — Matthew Tuman
– Matthew Tuman, Commented May 26, 2017 at 2:36
You do not specify the host. my $dbh = DBI->connect("dbi:mysql:database=$database;host=$DBhost;port=$port",$username,$password, where $DBhost might be remote or localhost — user7818749
– user7818749, Commented May 26, 2017 at 6:12

Dave Cross · Accepted Answer · 2017-05-26 08:25:51Z

2

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.

edited May 26, 2017 at 8:25

answered May 26, 2017 at 8:17

Dave Cross

69.5k3 gold badges55 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user7818749 Over a year ago

hmm, good eye you have there, never saw that comment. upvote.

Chris Turner Over a year ago

Doesn't even need to do it with CGI.pm - a simple print "Content-type: text/plain\n\n" before any other output will do the job.

Dave Cross Over a year ago

@ChrisTurner Well, pedantically, if it's a Unix-like server, \n\n will send incorrect line-end characters. The only way to be sure across all server platforms is to use \015\012\015\012. And hiding obscure stuff like that is surely why we use modules :-)

Chris Turner Over a year ago

@DaveCross never had that problem (Apache is super smart), but it's a fair point. still seems like overkill using something as heavy as CGI just to print out the header tho

Dave Cross Over a year ago

@ChrisTurner: Well, of course, Postel's Law means that it shouldn't be a problem. But it also suggests that we should make every effort to do the right thing :-)

user7818749 · Accepted Answer · 2017-05-26 06:27:04Z

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

Collectives™ on Stack Overflow

Unable to connect to mysql using perl DBI module

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related