Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > perl > answers

perl script to query database

Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#1: Oct 5 '08
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
Expand|Select|Wrap|Line Numbers
  1.  
  2. #!c:\perl\bin\perl.exe -w
  3. #!/usr/bin/perl -w
  4.  
  5. use DBI;
  6. $dbh = DBI->connect('dbi:mysql:test','root','mysql1')
  7. or die "Connection Error: $DBI::errstr\n";
  8. $sql = "select * from samples";
  9. $sth = $dbh->prepare($sql);
  10. $sth->execute
  11. or die "SQL Error: $DBI::errstr\n";
  12. while (@row = $sth->fetchrow_array) {
  13. print "@row\n";
  14.  
  15. } 
  16.  
Expand|Select|Wrap|Line Numbers
  1. a) apache error log
  2. [Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] Premature end of script headers: dbconn.pl
  3. [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
  4. [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
  5. [Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] or perhaps the capitalisation of 'mysql' isn't right.\r
  6. [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
  7. [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's Avatar
Moderator
  
Join Date: Jul 2007
Location: Arkansas
Posts: 900
#2: Oct 5 '08

re: perl script to query database

You will need to print the headers before you try and print anything to the screen. Add this line of code to your script.
Expand|Select|Wrap|Line Numbers
  1. print "Content-type: text/html", "\n\n";
If you are using the CGI.pm then you can do the following.
Expand|Select|Wrap|Line Numbers
  1. use CGI;
  2. my $q = CGI->new();
  3.  
  4. print $q->header();
Also to help debug you can add the following as well.
Expand|Select|Wrap|Line Numbers
  1. 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's Avatar
Moderator
  
Join Date: Jul 2007
Location: Arkansas
Posts: 900
#3: Oct 5 '08

re: perl script to query database

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

--Kevin
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#4: Oct 6 '08

re: perl script to query database

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
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#5: Oct 6 '08

re: perl script to query database

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. #!c:\perl\bin\perl.exe -w
  3. #!/usr/bin/perl -w
  4.  
  5.  
  6. use DBI;
  7.  
  8. use CGI; 
  9. my $q = CGI->new(); 
  10.  
  11. print $q->header(); 
  12.  
  13.  
  14. $dbh = DBI->connect('dbi:mysql:test','root','mysql1')
  15. or die "Connection Error: $DBI::errstr\n";
  16. $sql = "select * from samples";
  17. $sth = $dbh->prepare($sql);
  18. $sth->execute
  19. or die "SQL Error: $DBI::errstr\n";
  20. print "Content-type: text/html", "\n\n"; 
  21.  
  22. while (@row = $sth->fetchrow_array) {
  23. print "@row\n";
  24.  
  25.  
  26. } 
  27.  
  28.  
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#6: Oct 6 '08

re: perl script to query database

try changing 'root' to 'localhost'
eWish's Avatar
Moderator
  
Join Date: Jul 2007
Location: Arkansas
Posts: 900
#7: Oct 6 '08

re: perl script to query database

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

Expand|Select|Wrap|Line Numbers
  1. my ($dbh, $data_source);
  2. my $mysql_server_name   = 'localhost';
  3. my $mysql_database_name = 'xxx';
  4. my $mysql_user_name     = 'root';
  5. my $mysql_password      = 'xxx';
  6.  
  7. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;
  8. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {PrintError=>1}) ||  die "$DBI::errstr"; 
--Kevin
Newbie
  
Join Date: Oct 2008
Location: Cochin
Posts: 4
#8: Oct 6 '08

re: perl script to query database

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.
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#9: Oct 6 '08

re: perl script to query database

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 :

Expand|Select|Wrap|Line Numbers
  1. #!c:\perl\bin\perl.exe -w
  2. #!/usr/bin/perl -w
  3.  
  4. use DBI;
  5. use mysqlpp;
  6. use apachemysql;
  7.  
  8. print "Content-type: text/html", "\n\n";
  9.  
  10. my ($dbh, $data_source); 
  11. my $mysql_server_name   = 'localhost'; 
  12. my $mysql_database_name = 'test'; 
  13. my $mysql_user_name     = 'root'; 
  14. my $mysql_password      = 'mysql1'; 
  15.  
  16. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name; 
  17. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {PrintError=>1}) ||  die "$DBI::errstr";  
  18.  
  19.  
  20. $sql = "select * from samples";
  21. $sth = $dbh->prepare($sql);
  22. $sth->execute
  23. or die "SQL Error: $DBI::errstr\n";
  24.  
  25. print "testing output : $sth ";
  26.  print "Press the ENTER key to exit program ..."; 
  27.  $pause = <STDIN>;  #Like a PAUSE statement in DOS .bat files 
  28.  
  29.  exit; 
  30.  
  31.  
eWish's Avatar
Moderator
  
Join Date: Jul 2007
Location: Arkansas
Posts: 900
#10: Oct 6 '08

re: perl script to query database

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.

Expand|Select|Wrap|Line Numbers
  1. #! /usr/bin/perl -T
  2.  
  3. use strict;
  4. use warnings;
  5.  
  6. use DBI;
  7. use CGI::Carp qw/fatalsToBrowser/;
  8.  
  9. print "Content-type: text/html", "\n\n";
  10.  
  11. my ($dbh, $data_source); 
  12. my $mysql_server_name   = 'localhost'; 
  13. my $mysql_database_name = 'test'; 
  14. my $mysql_user_name     = 'root'; 
  15. my $mysql_password      = ''; 
  16.  
  17. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name; 
  18. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";  
  19.  
  20.  
  21. my $sql = $dbh->prepare(qq{SELECT * FROM samples});
  22.    $sql->execute();
  23.  
  24. while (my @data = $sql->fetchrow_array()) {
  25.  
  26.       # Print the date from the first two columns in the table
  27.       print $data[0], "\t", $data[1], "<br>";
  28. }
  29.  
  30.  
  31. 1;
--Kevin
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#11: Oct 7 '08

re: perl script to query database

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.



Expand|Select|Wrap|Line Numbers
  1. #!c:\perl\bin\perl.exe
  2. #!/usr/bin/perl -T 
  3.  
  4. use strict; 
  5. use warnings; 
  6. use mysqlpp;
  7.  
  8. use DBI; 
  9. use CGI::Carp qw(fatalsToBrowser); 
  10.  
  11. print "Content-type: text/html", "\n\n"; 
  12.  
  13. my ($dbh, $data_source);  
  14. my $mysql_server_name   = 'localhost';  
  15. my $mysql_database_name = 'test';  
  16. my $mysql_user_name     = 'root';  
  17. my $mysql_password      = 'mysql1';  
  18.  
  19. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
  20. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  21.  
  22.  
  23. my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  24.    $sql->execute(); 
  25.  
  26. while (my @data = $sql->fetchrow_array()) { 
  27.  
  28.       # Print the date from the first two columns in the table 
  29.       print $data[0], "\t", $data[1], "<br>"; 
  30. } 
  31.  
  32.  
  33. 1; 
  34.  
  35.  
  36.  
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#12: Oct 7 '08

re: perl script to query database

I am using windows perl and windows apache btw. Cheers...
eWish's Avatar
Moderator
  
Join Date: Jul 2007
Location: Arkansas
Posts: 900
#13: Oct 7 '08

re: perl script to query database

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

Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl
  2.  
  3. use strict;
  4. use warnings;
  5.  
  6. use CGI;
  7. use CGI::Carp qw/fatalsToBrowser/;
  8. use DBI;
  9. use DBD::mysql;
  10.  
  11. my $q = CGI->new;
  12.  
  13. print $q->header();
  14. print "$ENV{'DOCUMENT_ROOT'} has DBI Version: " .$DBI::VERSION. " installed on it.<br>";
  15. print "$ENV{'DOCUMENT_ROOT'} has DBD::mysql Version: " .$DBD::mysql::VERSION. " installed on it.<br>";
  16.  
  17.  
  18. 1;
--Kevin
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#14: Oct 7 '08

re: perl script to query database

As mentioned previously in this thread, you need to install DBD::Mysql
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#15: Oct 7 '08

re: perl script to query database

See this thread:

http://bytes.com/forum/thread840298-...A%3Amysql.html
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#16: Oct 7 '08

re: perl script to query database

Hi Kevin,

scan through and searched few times...I cant find this module DBD::Mysql, i can only install mysqlpp module. It is strange...

Thanks,
Andrew
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#17: Oct 7 '08

re: perl script to query database

Quote:

Originally Posted by happyse27

Hi Kevin,

scan through and searched few times...I cant find this module DBD::Mysql, i can only install mysqlpp module. It is strange...

Thanks,
Andrew

Did you read the thread I linked for you?
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#18: Oct 7 '08

re: perl script to query database

Hi Kevin,

Thanks again. I read throught the thread you gave. And it is working now.
http://bytes.com/forum/thread840298...3A%3Amysql.html Installed mysql module, the exact name is use dbd::mysql in windows perl script. Cheers!!! again.

Try this:

START>>PROGRAMS>>ACTIVEPERL>>PPM

Once thePPM GUI is running click on the EDIT tab, then PREFERENCES, then click on the REPOSITORIES tab then on the SUGGESTED tab select "uwinnipeg" and click on ADD. After the uwinnipeg packages are viewable see if DBD-mysql is listed. If not try the same thing and add "bribes" to the list of repositories. Once the DBD-mysql module is listed mark it for install then install it. Read the PPM help files if my instructions are confusing.

--------------------------------------------------------------------------------

complete code
Expand|Select|Wrap|Line Numbers
  1. #!c:\perl\bin\perl.exe
  2. #!/usr/bin/perl -T 
  3.  
  4. use strict; 
  5. use DBD::mysql;
  6. use DBI; 
  7. use CGI::Carp qw(fatalsToBrowser); 
  8.  
  9. print "Content-type: text/html", "\n\n"; 
  10.  
  11. my ($dbh, $data_source);  
  12. my $mysql_server_name   = 'localhost';  
  13. my $mysql_database_name = 'test';  
  14. my $mysql_user_name     = 'root';  
  15. my $mysql_password      = 'mysql1';  
  16.  
  17. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
  18. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  19.  
  20.  
  21. my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  22.    $sql->execute(); 
  23.  
  24. while (my @data = $sql->fetchrow_array()) { 
  25.  
  26.       # Print the date from the first two columns in the table 
  27.       print $data[0], "\t", $data[1], "<br>"; 
  28. } 
  29.  
  30.  
  31. 1; 
  32.  
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#19: Oct 7 '08

re: perl script to query database

Good. In the future please read what people post and try and follow any instructions you are given. Sometimes you are not reading all the replies or doing what people ask. Since we are trying to help you, you need to try and follow instructions and any other requests or advise posted.

Regards,
Kevin
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#20: Oct 8 '08

re: perl script to query database

Hi...

sorry Kevin, I missed out the instructions sometimes as I do panic and scramble for more information. Will be more attentive. if not missed the instruction I also wanted and would have wanted to resolve the matter earlier, alot of time wasted in resolving bugs...

Btw, if I use to use OOP(object based programming), which are the things to look out for to install, which references sites to read and what is the way to make sure I can re-use codes...


Cheers...
Andrew
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#21: Oct 8 '08

re: perl script to query database

Hi All,

Two questions to consult...

1) I can insert database information successfully using insert below in the code(see code below).

2) but I cant display any output in html. Any idea? I got the error below (see section a below).

The script which part is going wrong? Also the 1; not sure how to resolve? Thanks in advance again.


Cheers...
Andrew


a) Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, admin@gateway.2wire.net and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Expand|Select|Wrap|Line Numbers
  1.  
  2. #!c:\perl\bin\perl.exe 
  3. #!/usr/bin/perl -T 
  4.  
  5. use strict; 
  6. use warnings; 
  7. use CGI; 
  8. use CGI::Carp qw/fatalsToBrowser/; 
  9. use File::Basename; 
  10.  
  11. use DBD::mysql;
  12. use DBI; 
  13.  
  14.  
  15. $CGI::POST_MAX = 1024 * 5000; #adjust as needed (1024 * 5000 = 5MB) 
  16. $CGI::DISABLE_UPLOADS = 0; #1 disables uploads, 0 enables uploads 
  17.  
  18. my $query = CGI->new; 
  19.  
  20. unless ($CGI::VERSION >= 2.47) {  
  21.    error('Your version of CGI.pm is too old. You must have verison 2.47 or higher to use this script.') 
  22. } 
  23.  
  24.  
  25. #SECTION B
  26. #=========================================
  27. #Database initialization and initial query
  28. my ($dbh, $data_source);  
  29. my $mysql_server_name   = 'localhost';  
  30. my $mysql_database_name = 'test';  
  31. my $mysql_user_name     = 'root';  
  32. my $mysql_password      = 'mysql1';  
  33.  
  34. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
  35. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  36.  
  37.  
  38. my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  39.    $sql->execute(); 
  40.  
  41. while (my @data = $sql->fetchrow_array()) { 
  42.  
  43.       # Print the date from the first two columns in the table 
  44.       print $data[0], "\t", $data[1], "<br>"; 
  45. } 
  46.  
  47. 1;
  48.  
  49. # End of Database initial Query
  50. #==========================================
  51.  
  52.  
  53.  
  54.  
  55. my $upload_dir = 'D:/Program Files/Apache Software Foundation/Apache2.2/htdocs/upload'; 
  56.  
  57. # a list of valid characters that can be in filenames 
  58. my $filename_characters = 'a-zA-Z0-9_.-'; 
  59.  
  60. my $file = $query->param("photo") or error('No file selected for upload.') ; 
  61. my $email_address = $query->param("email") || 'Annonymous'; 
  62.  
  63. # get the filename and the file extension 
  64. # this could be used to filter out unwanted filetypes 
  65. # see the File::Basename documentation for details 
  66. my ($filename,undef,$ext) = fileparse($file,qr{\..*}); 
  67.  
  68. # append extension to filename 
  69. $filename .= $ext; 
  70.  
  71. # convert spaces to underscores "_" 
  72. $filename =~ tr/ /_/; 
  73.  
  74. # remove illegal characters 
  75. $filename =~ s/[^$filename_characters]//g; 
  76.  
  77. # satisfy taint checking 
  78. if ($filename =~ /^([$filename_characters]+)$/) { 
  79.    $filename = $1; 
  80. } 
  81. else{ 
  82.    error("The filename is not valid. Filenames can only contain these characters: $filename_characters") 
  83. } 
  84.  
  85. # this is very crude but validating an email address is not an easy task 
  86. # and is beyond the scope of this article. To validate an email 
  87. # address properly use the Emaill::Valid module. I do not include 
  88. # it here because it is not a core module. 
  89. unless ($email_address =~ /^[\w@.-]+$/ && length $email_address < 250) { 
  90.    error("The email address appears invalid or contains too many characters. Limit is 250 characters.") 
  91. }     
  92.  
  93. my $upload_filehandle = $query->upload("photo"); 
  94.  
  95. open (UPLOADFILE, ">$upload_dir/$filename") or error($!); 
  96. binmode UPLOADFILE; 
  97. while ( <$upload_filehandle> ) { 
  98.    print UPLOADFILE; 
  99. } 
  100. close UPLOADFILE; 
  101.  
  102. my $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello2", "passhello2", '$email_address', '$filename' ) } ); 
  103.    $sql->execute(); 
  104.  
  105. while (my @data = $sql->fetchrow_array()) { 
  106.  
  107.       # Print the date from the first four columns in the table 
  108.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  109.  
  110. }
  111.  
  112. 1;
  113.  
  114. print $query->header(), 
  115.       $query->start_html(-title=>'Upload Successful'), 
  116.       $query->p('Thanks for uploading your photo!'), 
  117.       $query->p("Your email address: $email_address"), 
  118.       $query->p("Your photo $filename:"), 
  119.       $query->img({src=>"../upload/$filename",alt=>''}), 
  120.       $query->end_html; 
  121.  
  122.  
  123. sub error { 
  124.    print $query->header(), 
  125.          $query->start_html(-title=>'Error'), 
  126.          shift, 
  127.          $query->end_html; 
  128.    exit(0); 
  129. } 
  130.  
  131.  
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#22: Oct 8 '08

re: perl script to query database

A problem I see in your script is using "my". The only one that will have any impact is here:

Expand|Select|Wrap|Line Numbers
  1. my $sql = $dbh->prepare(qq{SELECT * FROM samples});
later, and more importantly, in the same scope of the $sql variable you have:

Expand|Select|Wrap|Line Numbers
  1. my $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello2", "passhello2", '$email_address', '$filename' ) } ); 
Any previous value $sql had is lost when you redeclare it with "my" in the same scope. So you need to remove "my" from the second line.

I don't think that is the source of the error you get because that error should be trapped by CGI::Carp and printed out to the browser.

This line is not necessary:

Expand|Select|Wrap|Line Numbers
  1. use DBD::mysql; 
I don't know if it will cause a problem to include it but you can remove it and see if that helps resolve any errors. the DBI module knows where to get the driver you need in the connect string.

Remove any blank lines from the beginning of your script (before the shebang line) if there are any.
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#23: Oct 8 '08

re: perl script to query database

Also, at line 44 of your code there is a print command. But you have not printed an HTTP header first. Since that print command is output to STDOUT it will cause the script to fail.
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#24: Oct 9 '08

re: perl script to query database

Hi Kevin / all,

Thanks Guru Kevin! I troubleshooted the script and the output is below. But the software error is there, not sure what it is, tried without or with the mysql module "use DBD::mysql;" and taken out my $sql in the insert statement. And partial html output is shown only. Code below is right after the html output. Kindly Advise... Thanks in advance.

The insert statement worked, the html PARTIALLY worked when I shift the html after the printing of the statements and the picture below. Not sure what is wrong, been circling around and still got problem.

Apache access log showed : 127.0.0.1 - - [10/Oct/2008:00:15:17 -0700] "POST /cgi-bin/u2.pl HTTP/1.1" 200 902

And perl -c u2.pl showed :
C:\Perl\bin>perl -c d:u2.pl
[Fri Oct 10 00:20:58 2008] u2.pl: "my" variable $data_source masks earlier decla
ration in same scope at d:u2.pl line 31.
[Fri Oct 10 00:20:58 2008] u2.pl: "my" variable $dbh masks earlier declaration i
n same scope at d:u2.pl line 32.
d:u2.pl syntax OK




html output(including software error below)
===============================
Thanks for uploading your photo!

Your email address: h6@email.com

Your photo E-028.JPG:
[photo is shown]


Software error:
DBD::mysql::st fetchrow_array failed: fetch() without execute() at D:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/u2.pl line 121, <fh00001D%3A\E-028.JPG> line 313.
For help, please send mail to the webmaster (admin@gateway.2wire.net), giving this error message and the time and date of the error.


Expand|Select|Wrap|Line Numbers
  1. #!c:\perl\bin\perl.exe 
  2. #!/usr/bin/perl -T 
  3. use strict; 
  4. use warnings; 
  5. use CGI; 
  6. use CGI::Carp qw(fatalsToBrowser); 
  7. use File::Basename; 
  8. use DBD::mysql;
  9. use DBI; 
  10.  
  11.  
  12. $CGI::POST_MAX = 1024 * 5000; #adjust as needed (1024 * 5000 = 5MB) 
  13. $CGI::DISABLE_UPLOADS = 0; #1 disables uploads, 0 enables uploads 
  14.  
  15. my $query = CGI->new; 
  16.  
  17. unless ($CGI::VERSION >= 2.47) {  
  18.    error('Your version of CGI.pm is too old. You must have verison 2.47 or higher to use this script.') 
  19. } 
  20.  
  21.  
  22. #SECTION B
  23. #=========================================
  24. #Database initialization and initial query
  25. my ($dbh, $data_source);  
  26. my $mysql_server_name   = 'localhost';  
  27. my $mysql_database_name = 'test';  
  28. my $mysql_user_name     = 'root';  
  29. my $mysql_password      = 'mysql1';  
  30.  
  31. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
  32. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  33.  
  34.  
  35. my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  36.    $sql->execute(); 
  37.  
  38. while (my @data = $sql->fetchrow_array()) { 
  39.  
  40.       # Print the date from the first two columns in the table 
  41.       print $data[0], "\t", $data[1], "<br>"; 
  42. } 
  43.  
  44. 1;
  45.  
  46. # End of Database initial Query
  47. #==========================================
  48.  
  49.  
  50.  
  51.  
  52. my $upload_dir = 'D:/Program Files/Apache Software Foundation/Apache2.2/htdocs/upload'; 
  53.  
  54. # a list of valid characters that can be in filenames 
  55. my $filename_characters = 'a-zA-Z0-9_.-'; 
  56.  
  57. my $file = $query->param("photo") or error('No file selected for upload.') ; 
  58. my $email_address = $query->param("email") || 'Annonymous'; 
  59.  
  60. # get the filename and the file extension 
  61. # this could be used to filter out unwanted filetypes 
  62. # see the File::Basename documentation for details 
  63. my ($filename,undef,$ext) = fileparse($file,qr{\..*}); 
  64.  
  65. # append extension to filename 
  66. $filename .= $ext; 
  67.  
  68. # convert spaces to underscores "_" 
  69. $filename =~ tr/ /_/; 
  70.  
  71. # remove illegal characters 
  72. $filename =~ s/[^$filename_characters]//g; 
  73.  
  74. # satisfy taint checking 
  75. if ($filename =~ /^([$filename_characters]+)$/) { 
  76.    $filename = $1; 
  77. } 
  78. else{ 
  79.    error("The filename is not valid. Filenames can only contain these characters: $filename_characters") 
  80. } 
  81.  
  82. # this is very crude but validating an email address is not an easy task 
  83. # and is beyond the scope of this article. To validate an email 
  84. # address properly use the Emaill::Valid module. I do not include 
  85. # it here because it is not a core module. 
  86. unless ($email_address =~ /^[\w@.-]+$/ && length $email_address < 250) { 
  87.    error("The email address appears invalid or contains too many characters. Limit is 250 characters.") 
  88. }     
  89.  
  90. my $upload_filehandle = $query->upload("photo"); 
  91.  
  92. open (UPLOADFILE, ">$upload_dir/$filename") or error($!); 
  93. binmode UPLOADFILE; 
  94. while ( <$upload_filehandle> ) { 
  95.    print UPLOADFILE; 
  96. } 
  97. close UPLOADFILE; 
  98.  
  99.  
  100. print $query->header(), 
  101.       $query->start_html(-title=>'Upload Successful'), 
  102.       $query->p('Thanks for uploading your photo!'), 
  103.       $query->p("Your email address: $email_address"), 
  104.       $query->p("Your photo $filename:"), 
  105.       $query->img({src=>"../upload/$filename",alt=>''}), 
  106.       $query->end_html; 
  107.  
  108.  
  109. sub error { 
  110.    print $query->header(), 
  111.          $query->start_html(-title=>'Error'), 
  112.          shift, 
  113.          $query->end_html; 
  114.    exit(0); 
  115. } 
  116.  
  117.  
  118.    $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", '$email_address', '$filename' ) } ); 
  119.    $sql->execute(); 
  120.  
  121. while (my @data = $sql->fetchrow_array()) { 
  122.  
  123.       # Print the date from the first four columns in the table 
  124.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  125.  
  126. }
  127.  
  128.  
  129. 1;
  130.  
  131.  
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#25: Oct 9 '08

re: perl script to query database

Hi All,

I found this, tried some methods, but still cant work well.

http://www.webmasterworld.com/forum13/3259.htm

Thanks in advance,
Andrew

Change the second statement below :

Expand|Select|Wrap|Line Numbers
  1.  
  2.    $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello66", "passhello66", '$email_address', '$filename' ) } ); 
  3.    $sql->execute() or die "$dbh->errstr\n"; 
  4. ; 
  5.  
  6. while (my @data = $sql->fetchrow_array()) { 
  7.  
  8.       # Print the date from the first four columns in the table 
  9.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  10.  
  11. }
  12.  
  13.  
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#26: Oct 9 '08

re: perl script to query database

In this line you have scalars that are single-quoted:

Expand|Select|Wrap|Line Numbers
  1. $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", '$email_address', '$filename' ) } ); 
remove the single-quotes:

Expand|Select|Wrap|Line Numbers
  1. $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", $email_address, $filename ) } ); 
Then retry your code. Scalars do not need to be quoted at all, and single-quotes just kills any variable interpolation anyway.
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#27: Oct 10 '08

re: perl script to query database

Hi Kevin / All,

Tried and it completely didnt work, only double quote works.. See code below. I am still get same error below when I tried to print out the data in html output although the data got inserted into the database successful., :Kindly assist.

Software error:
DBD::mysql::st fetchrow_array failed: fetch() without execute() at D:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/u2.pl line 122, <fh00001D%3A\DSC00571-resize.JPG> line 5268.
For help, please send mail to the webmaster (admin@gateway.2wire.net), giving this error message and the time and date of the error.

Expand|Select|Wrap|Line Numbers
  1.  
  2.    $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello636", "passhello636", "$email_address", "$filename") } ); 
  3.    $sql->execute() or die "$dbh->errstr\n";; 
  4.  
  5.  
  6. while (my @data = $sql->fetchrow_array()) { 
  7.  
  8.       # Print the date from the first four columns in the table 
  9.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  10.  
  11. }
  12.  
  13.  
  14. 1;
  15.  
  16.  
  17.  
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#28: Oct 10 '08

re: perl script to query database

Sorry, my suggestion was wrong anyway, the single-quotes were OK in the context you had them because the string is inside a qq{} operator. Its the same as this:

Expand|Select|Wrap|Line Numbers
  1. $foo = 'test';
  2. $str = "this is a '$foo' ";
  3. print $str;
So the single-quotes weren't a problem.
Icecrack's Avatar
Expert
  
Join Date: Sep 2008
Location: Sydney, Australia
Posts: 173
#29: Oct 10 '08

re: perl script to query database

Quote:

Originally Posted by KevinADC

Sorry, my suggestion was wrong anyway, the single-quotes were OK in the context you had them because the string is inside a qq{} operator. Its the same as this:

Expand|Select|Wrap|Line Numbers
  1. $foo = 'test';
  2. $str = "this is a '$foo' ";
  3. print $str;
So the single-quotes weren't a problem.

hey Kevin Stop confusing me there :)

also on the topic

Expand|Select|Wrap|Line Numbers
  1. Software error:
  2. DBD::mysql::st fetchrow_array failed: fetch() without execute() at D:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/u2.pl line 122, <fh00001D%3A\DSC00571-resize.JPG> line 5268.
its casing an error because SQL hates \ in its input, try to cancel this by \\\\ in perl
try

Expand|Select|Wrap|Line Numbers
  1. $filename=~ s/\/\\/g;
before you write you sql string

also watch out for single quotes ' in SQL,
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#30: Oct 10 '08

re: perl script to query database

Hi Gentlemen,

Thanks! So Which part should I try to edit? I am confused...


Thanks,
Andrew
Icecrack's Avatar
Expert
  
Join Date: Sep 2008
Location: Sydney, Australia
Posts: 173
#31: Oct 10 '08

re: perl script to query database

Quote:

Originally Posted by happyse27

Hi Gentlemen,

Thanks! So Which part should I try to edit? I am confused...


Thanks,
Andrew


Append:

Expand|Select|Wrap|Line Numbers
  1. $filename=~ s/\\/\\\\/g;
this to any place in your code where you would read, this would be after the read or this would be before you write $filename

Also

This should not work:
Expand|Select|Wrap|Line Numbers
  1.  $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", '$email_address', '$filename' ) } ); 
  2.    $sql->execute(); 
  3.  
  4. while (my @data = $sql->fetchrow_array()) { 
  5.  
  6.       # Print the date from the first four columns in the table 
  7.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  8.  
  9. }

Because this is an insert statement:

Expand|Select|Wrap|Line Numbers
  1. $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", '$email_address', '$filename' ) } ); 
and this will be calling the insert statement to write and not read:

Expand|Select|Wrap|Line Numbers
  1. while (my @data = $sql->fetchrow_array()) { 
  2.  
  3.       # Print the date from the first four columns in the table 
  4.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  5.  
this is because you have over written $sql with the write statement.

also its complaining about: fetch() without execute()

try

Expand|Select|Wrap|Line Numbers
  1.  
  2. $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello636", "passhello636", "$email_address", "$filename") } ); 
  3.    $sql->execute() or die "$dbh->errstr\n";; 
  4.  
  5. $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  6.  
  7. while (my @data = $sql->fetchrow_array->execute()) { 
  8.  
  9.       # Print the date from the first four columns in the table 
  10.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  11.  
Familiar Sight
  
Join Date: Sep 2008
Posts: 236
#32: Oct 10 '08

re: perl script to query database

Hi Sir Icecrack,

I understand now. Thanks Again. Used your final part except one statement missing which I added... The first part not sure what you referring to. This is the code. Cheers...


Thanks,
Andrew

Expand|Select|Wrap|Line Numbers
  1. #!c:\perl\bin\perl.exe 
  2. #!/usr/bin/perl -T 
  3. use strict; 
  4. use warnings; 
  5. use CGI; 
  6. use CGI::Carp qw(fatalsToBrowser); 
  7. use File::Basename; 
  8. use DBD::mysql;
  9. use DBI; 
  10.  
  11.  
  12. $CGI::POST_MAX = 1024 * 5000; #adjust as needed (1024 * 5000 = 5MB) 
  13. $CGI::DISABLE_UPLOADS = 0; #1 disables uploads, 0 enables uploads 
  14.  
  15. my $query = CGI->new; 
  16.  
  17. unless ($CGI::VERSION >= 2.47) {  
  18.    error('Your version of CGI.pm is too old. You must have verison 2.47 or higher to use this script.') 
  19. } 
  20.  
  21.  
  22. #SECTION B
  23. #=========================================
  24. #Database initialization and initial query
  25. my ($dbh, $data_source);  
  26. my $mysql_server_name   = 'localhost';  
  27. my $mysql_database_name = 'test';  
  28. my $mysql_user_name     = 'root';  
  29. my $mysql_password      = 'mysql1';  
  30.  
  31. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
  32. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  33.  
  34.  
  35. my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  36.    $sql->execute(); 
  37.  
  38. while (my @data = $sql->fetchrow_array()) { 
  39.  
  40.       # Print the date from the first two columns in the table 
  41.       print $data[0], "\t", $data[1], "<br>"; 
  42. } 
  43.  
  44. 1;
  45.  
  46. # End of Database initial Query
  47. #==========================================
  48.  
  49.  
  50.  
  51.  
  52. my $upload_dir = 'D:/Program Files/Apache Software Foundation/Apache2.2/htdocs/upload'; 
  53.  
  54. # a list of valid characters that can be in filenames 
  55. my $filename_characters = 'a-zA-Z0-9_.-'; 
  56.  
  57. my $file = $query->param("photo") or error('No file selected for upload.') ; 
  58. my $email_address = $query->param("email") || 'Annonymous'; 
  59.  
  60. # get the filename and the file extension 
  61. # this could be used to filter out unwanted filetypes 
  62. # see the File::Basename documentation for details 
  63. my ($filename,undef,$ext) = fileparse($file,qr{\..*}); 
  64.  
  65. # append extension to filename 
  66. $filename .= $ext; 
  67.  
  68. # convert spaces to underscores "_" 
  69. $filename =~ tr/ /_/; 
  70.  
  71. # remove illegal characters 
  72. $filename =~ s/[^$filename_characters]//g; 
  73.  
  74. # satisfy taint checking 
  75. if ($filename =~ /^([$filename_characters]+)$/) { 
  76.    $filename = $1; 
  77. } 
  78. else{ 
  79.    error("The filename is not valid. Filenames can only contain these characters: $filename_characters") 
  80. } 
  81.  
  82. # this is very crude but validating an email address is not an easy task 
  83. # and is beyond the scope of this article. To validate an email 
  84. # address properly use the Emaill::Valid module. I do not include 
  85. # it here because it is not a core module. 
  86. unless ($email_address =~ /^[\w@.-]+$/ && length $email_address < 250) { 
  87.    error("The email address appears invalid or contains too many characters. Limit is 250 characters.") 
  88. }     
  89.  
  90. my $upload_filehandle = $query->upload("photo"); 
  91.  
  92. open (UPLOADFILE, ">$upload_dir/$filename") or error($!); 
  93. binmode UPLOADFILE; 
  94. while ( <$upload_filehandle> ) { 
  95.    print UPLOADFILE; 
  96. } 
  97. close UPLOADFILE; 
  98.  
  99.  
  100. print $query->header(), 
  101.       $query->start_html(-title=>'Upload Successful'), 
  102.       $query->p('Thanks for uploading your photo!'), 
  103.       $query->p("Your email address: $email_address"), 
  104.       $query->p("Your photo $filename:"), 
  105.       $query->img({src=>"../upload/$filename",alt=>''}), 
  106.       $query->end_html; 
  107.  
  108.  
  109. sub error { 
  110.    print $query->header(), 
  111.          $query->start_html(-title=>'Error'), 
  112.          shift, 
  113.          $query->end_html; 
  114.    exit(0); 
  115. } 
  116.  
  117.  
  118.    $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello636", "passhello636", "$email_address", "$filename") } );  
  119.    $sql->execute() or die "$dbh->errstr\n";;  
  120.  
  121. $sql = $dbh->prepare(qq{SELECT * FROM samples});  
  122.  $sql->execute(); 
  123.  
  124.  
  125. while (my @data = $sql->fetchrow_array()){  
  126.  
  127.       # Print the date from the first four columns in the table  
  128.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>";  
  129.  
  130. }
  131. 1;
  132.  
  133.  
Reply

« previous question | next question »

Similar Perl bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,414 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved