Output of CGI with DBI

brp4h has asked for the wisdom of the Perl Monks concerning the following question:

Can someone tell me why this script prints correctly from the command line, but won't display in a browser? It cuts off after the SQL statement is prepared.

#!/usr/bin/perl

  use DBI;
  use CGI;

  my $col1;
  my $col2;
  my $col3;

# Print HTML headers, etc.
  $cgi = new CGI;
  print $cgi->header;
  print $cgi->start_html();

# Open a connection
    %attr = (PrintError => 0, RaiseError => 0);
    $dbh = DBI->connect("dbi:DB2:CDWP", "xxxx", "XXXX", \%attr);

# Prepare SQL statement
    $sql = "SELECT * FROM delete.metadata";
    $sql.= " FETCH FIRST 10 ROWS ONLY";

# Execute SQL against CDW connection
    $sth = $dbh->prepare($sql);
    $sth->execute();

# Bind results to local variables
    $sth->bind_col(1,\$col1);
    $sth->bind_col(2,\$col2);
    $sth->bind_col(3,\$col3);

# Print results
    print $cgi->table();
    while ($sth->fetch) {
        print "<tr><td>$col1</td><td>$col2</td></tr>\n";
    }

# Clean up
    $dbh->disconnect();
    print $cgi->end_table;
    print $cgi->end_html;

exit;
[download]

Here's the output from the command line (correct):

Content-Type: text/html; charset=ISO-8859-1

<!DOCTYPE html
        PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
         "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-U
+S">
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1
+" />
</head>
<body>
<table /><tr><td>Loan</td><td>Attribute</td></tr>
<tr><td>Loan</td><td>Attribute</td></tr>
<tr><td>Loan</td><td>Attribute</td></tr>
<tr><td>Loan</td><td>Attribute</td></tr>
<tr><td>Loan</td><td>Attribute</td></tr>
<tr><td>Loan</td><td>Attribute</td></tr>
<tr><td>Loan</td><td>Attribute</td></tr>
<tr><td>Loan</td><td>Attribute</td></tr>
<tr><td>Loan</td><td>Attribute</td></tr>
<tr><td>Loan</td><td>Attribute</td></tr>
</table>
</body>
</html>
[download]

And in the browser...

<!DOCTYPE html
    PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-U
+S">
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1
+" />
</head>
<body>
[download]

Where's the rest of my output!?

Comment on Output of CGI with DBI Select or Download Code

Replies are listed 'Best First'.
Re: Output of CGI with DBI by jethro (Monsignor) on Jul 08, 2010 at 17:11 UTC
Your web server usually executes as a different user and with a different environment as you on the command line. In your case it could be that $dbh is undef because the web server couldn't connect to the database. So better check those return values, especially if you turned off any error-raising as you seem to have done PS: Please 'use warnings' at a minimum, 'use strict' is also advisable	[reply]
Re^2: Output of CGI with DBI by brp4h (Acolyte) on Jul 08, 2010 at 19:07 UTC
Thanks, roboticus and jethro. I fixed my issue by setting the environment variables for DB2_PATH, DB2INSTANCE, and SQLLIB in the script to the correct values.	[reply]
Re^2: Output of CGI with DBI by brp4h (Acolyte) on Jul 08, 2010 at 17:25 UTC
Thanks... I guess by command line I meant I'm sshing into the web server and running the CGI script and get the correct output, so I thought the environment would be the same. I tried checking the return values, but I can't get ANY sort of output from the web after the connection should be established.	[reply]
Re^3: Output of CGI with DBI by roboticus (Chancellor) on Jul 08, 2010 at 18:03 UTC
brp4h: You can definitely ssh into the webserver, but for it to do you any good, you have to ssh into it using the same account that the webserver is running. Otherwise, it will have its own path and other environment variables. Heck, it could even be running a different perl than your account. So unless you're running under the webserver account, running it at the command line won't tell you much. ...roboticus	[reply]
Re^4: Output of CGI with DBI by brp4h (Acolyte) on Jul 08, 2010 at 18:37 UTC
Re^5: Output of CGI with DBI by roboticus (Chancellor) on Jul 11, 2010 at 14:23 UTC
Re: Output of CGI with DBI by roboticus (Chancellor) on Jul 08, 2010 at 17:11 UTC
brp4h: Where's the rest of my output!? Probably in your webserver log files where there's an error message telling you about a database access problem. ...roboticus	[reply]