perl script to query database

[happyse27]

Hi All,

I am doing a connection to database from perl to mysql via apache. Apache is configured correctly.

Mysql username is root and password is mysql1, and database name is test.

when I type in url 127.0.0.1/cgi-bin/dbconn.pl, it showed internal server error 500. And errors in apache section a) below and errors in access. The samples table is created under test, residing in mysql database.

Tried to triple check and the configurations looks fine. Anyone can kindly assist? Thanks in advance



Andrew


dbconn.pl

 
#!c:\perl\bin\perl.exe -w
#!/usr/bin/perl -w

use DBI;
$dbh = DBI->connect('dbi:mysql:test','root','mysql1')
or die "Connection Error: $DBI::errstr\n";
$sql = "select * from samples";
$sth = $dbh->prepare($sql);
$sth->execute
or die "SQL Error: $DBI::errstr\n";
while (@row = $sth->fetchrow_array) {
print "@row\n";

}

a) apache error log
[Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] Premature end of script headers: dbconn.pl
[Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: C:/perl/site/lib C:/perl/lib .) at (eval 4) line 3.\r
[Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] Perhaps the DBD::mysql perl module hasn't been fully installed,\r
[Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] or perhaps the capitalisation of 'mysql' isn't right.\r
[Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] Available drivers: CSV, DBM, ExampleP, File, Gofer, ODBC, Oracle, Proxy, SQLite, Sponge.\r
[Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1]  at D:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/dbconn.pl line 5\r

[eWish]

You will need to print the headers before you try and print anything to the screen. Add this line of code to your script.

print "Content-type: text/html", "\n\n";

If you are using the CGI.pm then you can do the following.

use CGI;
my $q = CGI->new();

print $q->header();

Also to help debug you can add the following as well.

use CGI::Carp qw/fatalsToBrowser/;

Edit: It appears that you don't have DBD::Mysql installed. This a must when you are connecting to a MySQL DB.
--Kevin

[eWish]

Here is a link to a resource to help determining some of the common problems when you get the dreaded 500 error.

--Kevin

[happyse27]

Hi Kevin,

Thanks again. Will try out what you mentioned...

I was using this link http://perl.about.com/od/perltutorials/a/perlmysql_3.htm



Cheers...
Andrew

[happyse27]

Hi...

I tried out the below script, this time the url 127.0.0.1/cgi-bin/dbconn.pl printed out blank screen(no more screen error). I had installed dbd-mysqlpp.

Apache error log showed 500 and 543 errors. Apache access log showed 127.0.0.1 - - [06/Oct/2008:08:35:18 -0700] "GET /cgi-bin/dbconn.pl HTTP/1.1" 200 -

Kindly assist. I will however try some other methods further.

Btw, what is CGI PM? Also, the print "Content-type: text/html", "\n\n"; which line I should put in?


Thanks and Best Rgds,
Andrew

#!c:\perl\bin\perl.exe -w
#!/usr/bin/perl -w


use DBI;

use CGI; 
my $q = CGI->new(); 
  
print $q->header(); 


$dbh = DBI->connect('dbi:mysql:test','root','mysql1')
or die "Connection Error: $DBI::errstr\n";
$sql = "select * from samples";
$sth = $dbh->prepare($sql);
$sth->execute
or die "SQL Error: $DBI::errstr\n";
print "Content-type: text/html", "\n\n"; 

while (@row = $sth->fetchrow_array) {
print "@row\n";


}

[KevinADC]

try changing 'root' to 'localhost'

[eWish]

I did not even look at the connection string. Here is an example of a connection string that works.

my ($dbh, $data_source);
my $mysql_server_name   = 'localhost';
my $mysql_database_name = 'xxx';
my $mysql_user_name     = 'root';
my $mysql_password	  = 'xxx';

my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;
my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {PrintError=>1}) ||  die "$DBI::errstr";

--Kevin

[maestria]

Hi,

Make sure that there are entries there in the database, otherwise the result would be a blank screen again.
search for Mysql+connect +perl in google and you get thousands of snippets which you can use.

[happyse27]

Hi All,

I tried both methods and it show the content type Content-type: text/html in the url 127.0.0.1/cgi-bin/dbconn2.pl. And alternately it shows internal error.

apache access show status 200 ok, but it showed error 25.

my database is test, user and password correct. It showed root@localhost, with mysql, test db tables. both mysql and test got the same samples rows.

What could be wrong, tried many different ways and scratching head whole nite. Thanks.

Code below :

#!c:\perl\bin\perl.exe -w
#!/usr/bin/perl -w

use DBI;
use mysqlpp;
use apachemysql;

print "Content-type: text/html", "\n\n";

my ($dbh, $data_source); 
my $mysql_server_name   = 'localhost'; 
my $mysql_database_name = 'test'; 
my $mysql_user_name     = 'root'; 
my $mysql_password      = 'mysql1'; 
  
my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name; 
my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {PrintError=>1}) ||  die "$DBI::errstr";  


$sql = "select * from samples";
$sth = $dbh->prepare($sql);
$sth->execute
or die "SQL Error: $DBI::errstr\n";

print "testing output : $sth ";
 print "Press the ENTER key to exit program ..."; 
 $pause = <STDIN>;  #Like a PAUSE statement in DOS .bat files 
  
 exit;

[eWish]

Here is your code reworked a bit. Be sure to check out the DBI documentation if you see things you don't understand on the DBI part of the script. This script is also assuming that you are accessing it via a web browser.

#! /usr/bin/perl -T
 
use strict;
use warnings;

use DBI;
use CGI::Carp qw/fatalsToBrowser/;

print "Content-type: text/html", "\n\n";
 
my ($dbh, $data_source); 
my $mysql_server_name   = 'localhost'; 
my $mysql_database_name = 'test'; 
my $mysql_user_name     = 'root'; 
my $mysql_password      = ''; 
 
my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name; 
my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";  
 
 
my $sql = $dbh->prepare(qq{SELECT * FROM samples});
   $sql->execute();
	
while (my @data = $sql->fetchrow_array()) {
	
      # Print the date from the first two columns in the table
      print $data[0], "\t", $data[1], "<br>";
}


1;

--Kevin

[happyse27]

Hi Guru Kevin,

Thanks. But Still Cannot Work after trying. got apache Errors 500 and 543. Also, the url 127.0.0.1/cgi-bin/db.pl showed is :

Software error:
install_driver( mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: C:/perl/site/lib C:/perl/lib .) at (eval 5) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: CSV, DBM, ExampleP, File, Gofer, ODBC, Oracle, Proxy, SQLite, Sponge, mysqlPP.
at D:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/db.pl line 19

For help, please send mail to the webmaster (admin@gateway. 2wire.net), giving this error message and the time and date of the error.

#!c:\perl\bin\perl.exe
#!/usr/bin/perl -T 
  
use strict; 
use warnings; 
use mysqlpp;
  
use DBI; 
use CGI::Carp qw(fatalsToBrowser); 
  
print "Content-type: text/html", "\n\n"; 
  
my ($dbh, $data_source);  
my $mysql_server_name   = 'localhost';  
my $mysql_database_name = 'test';  
my $mysql_user_name     = 'root';  
my $mysql_password      = 'mysql1';  
  
my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  
  
my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
   $sql->execute(); 
  
while (my @data = $sql->fetchrow_array()) { 
  
      # Print the date from the first two columns in the table 
      print $data[0], "\t", $data[1], "<br>"; 
} 
  
  
1;

[happyse27]

I am using windows perl and windows apache btw. Cheers...

[eWish]

Test the script with this line omitted or commented out. Just to see what happens.

use mysqlpp;

Also, run this script. What it will do is tell you if you have the DBI and DBD::mysql modules are installed and the version number of each. It really appears that you do not have the DBD::mysql module installed.

#!/usr/bin/perl

use strict;
use warnings;

use CGI;
use CGI::Carp qw/fatalsToBrowser/;
use DBI;
use DBD::mysql;

my $q = CGI->new;

print $q->header();
print "$ENV{'DOCUMENT_ROOT'} has DBI Version: " .$DBI::VERSION. " installed on it.<br>";
print "$ENV{'DOCUMENT_ROOT'} has DBD::mysql Version: " .$DBD::mysql::VERSION. " installed on it.<br>";


1;

--Kevin

[KevinADC]

As mentioned previously in this thread, you need to install DBD::Mysql

[KevinADC]

See this thread:

http://bytes.com/forum/thread840298-install+dbd%3A%3Amysql.html