Why doesn't my Perl DBI query return any results in a CGI script?

Question

I'm using DBI for the first time (and not long started Perl [2 weeks]) and I can't seem to get any results from the database. Here's what I have:

if( defined( $get{findAllPages} ) && defined( $post{ki} ) ){

   my ($database, $hostname, $port, $password, $user );

   $database = "#########";
   $hostname = "localhost";
   $password = "#########";
   $user = "###########";
   my $KI = $post{ki};

   # connect to the database
   my $dsn = "DBI:mysql:database=$database;host=$hostname;";
   my $dbh = DBI->connect($dsn, $user, $password);
   my $sth = $dbh->prepare("SELECT * FROM accounts WHERE KI = '" . $dbh->quote($KI) . "' ") or die "Could not select from table";
   $sth->execute();
   if( $sth->rows != 0 ) {
      my $ref = $sth->fetchrow_hashref();
      my $domain = $ref->{website};
      my $DB_username = $ref->{db_name};
      my $DB_password = $ref->{db_pass};
      $sth->finish();
      $dbh->disconnect();

      print "domian: " . $domain . "<br />\n";

      chomp(my $url = trim($domain));

As it stands it checks to see if KI is correct and then checks row amount which works. The bit I can't get to work is returning values from the array;

my $ref = $sth->fetchrow_hashref();
my $domain = $ref->{website};
my $DB_username = $ref->{db_name};
my $DB_password = $ref->{db_pass};

If anyone can let me know where I am going wrong it would be much appreciated.

brian d foy · Accepted Answer · 2010-02-05 11:07:21Z

2

Try to use DBI's error handling to see what's wrong. See Programming the Perl DBI, chapter 4 (Error handling) or "DBI::mysql error handling" on Perlmonks for references.

edited Feb 5, 2010 at 11:07

brian d foy

134k31 gold badges214 silver badges613 bronze badges

answered Feb 5, 2010 at 8:37

ghostdog74

346k62 gold badges264 silver badges349 bronze badges

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

5 Comments

Phil Jackson Over a year ago

Thanks, could you tell me if "or die" works in online cgi files as I can never seem to get it to output to the browser.

Phil Jackson Over a year ago

fetch() without execute().. so im guessing... my $dsn = "DBI:mysql:database=$database;host=$hostname;"; my $dbh = DBI->connect($dsn, $user, $password); my $sth = $dbh->prepare("SELECT * FROM accounts WHERE KI = '" . $dbh->quote($KI) . "' ") or die "Could not select from table"; $sth->execute(); if( $sth->rows != 0 ) { my $ref = $sth->fetchrow_hashref(); $ref->execute(); print $DBI::errstr; my $domain = $ref->{website}; my $DB_username = $ref->{db_name}; my $DB_password = $ref->{db_pass}; $sth->finish(); $dbh->disconnect(); shuld do it

ghostdog74 Over a year ago

if you are using cgi, one method is to use CGI::Carp. see perldoc.perl.org/CGI/…. by the way, post your code in your question, not in the comments because the formatting is bad.

Phil Jackson Over a year ago

grrr... it works fine when I run from cmd on my local machine but when I use via browser tells me to do one thing, then when i go back to my local machine that change i made causes an error...

brian d foy Over a year ago

What does the browser tell you to do?

libdave · Accepted Answer · 2010-02-14 20:41:46Z

0

What I often do when I can't see what is going on:

use Data::Dumper;

my $ref = $sth->fetch();

print Dumper $ref;

and you'll see the data structure layout. Sometimes it is a matter of incorrect indexing into an array or hash.

answered Feb 14, 2010 at 20:41

libdave

1

Collectives™ on Stack Overflow

Why doesn't my Perl DBI query return any results in a CGI script?

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