Query results in Perl CGI script is returning back blank webpage with no data

Question

I am running a script to retrieve passwords for users but I am getting a blank page. When I check the Apache error logs it is saying it can't locate the Informix Perl Module. But I installed the Informix Perl Module and I am able to connect to our Informix database through the command line script.

Error found in Apache Logs:

cgi:error] [pid 30132] [client 10.199] AH01215: install_driver(Informix) failed: Can't locate DBD/Informix.pm in @INC (@INC   contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at (eval 7) line 3., 
referer: server.com/cgi-bin/ts_password_retrieval.cgi [Thu Sep 03  17:58:24.8 2015] [cgi:error] [pid 30132] [client 10.199] 
AH01215: Perhaps  the DBD::Informix perl module hasn't been fully installed,, referer: server.com/cgi-bin/ts_password_retrieval.cgi –

Perl Version and location of Informix libraries:

(v5.16.3)
export INFORMIXDIR=/opt/informix
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$INFORMIXDIR/lib:$INFORMIXDIR/lib/cli:$INFORMIXDIR/lib/esql:$INFORMIXDIR/lib/tools

Code producing error:

#!/usr/bin/perl -w


use CGI::Pretty qw(:standard *table);
use strict;
use warnings;
use DBI;
#use Config::Std;
use vars qw($FICE_CODE $SCHL_NAME $FIRST_NAME $LAST_NAME $TO_ORDER_ID $CH_FLAG $ORDER_STATUS $TO_RECIP_ID $ORDER_STATUS $RECPT_ORDER_STATUS $EMAIL $TO_DELIV_TRANS_ID $PASSWORD $FILE_NAME);
use Data::Dumper;
 #&ReadParse(*input);

 #Output display
print "Content-type: text/html\n\n";

main();

sub main
{
   print "<html><head>\n";
   print "<title>Transcript Password Retrieval</title></head>\n";
   print "<body>\n";
   print "<center>Transcript Password Retrieval</center>\n";
   print "</body></html>\n";
if (param('to_id'))
    {
    my $to_id = param('to_id');#local to_id
    my $pwd_select = ("select s.fice_code, s.schl_name,   o.first_name, o.last_name, o.to_order_id, o.ch_flag, o.order_status,r.to_recipient_id, r.order_status, r.email, dt.to_deliv_trans_id, dt.password, dt.file_name from to_scprofil s, to_order o, to_recipient r, to_deliv_trans dt where s.to_scprofil_id = o.to_scprofil_id and o.to_order_id = r.to_order_id and r.to_recipient_id = dt.to_recipient_id and o.to_order_id in ($to_id) order by o.to_order_id desc");

    my $driver = "Informix";
    my $database = "Web1";
    my $dsn = "DBI:$driver:dbname=$database";
    my $userid = "todd";
    my $password = "shumer12";
    my $dbh = DBI->connect($dsn, $userid, $password, {RaiseError => 1}) or die $DBI::errstr;
    #print "Opened db successfully\n";

    my $sth = $dbh->prepare($pwd_select);
    $sth ->execute; #make this a global variable might have to revert this back to a locak variable
    my $rv = $sth->rows;

    while(my $row = $sth->fetchrow_hashref)
    {
        print hr
        print start_table;
        foreach my $key (keys %$row)
        {
            print Tr
            (
            td(b($key)),
                td($row->{$key}),
            );
        }
        print end_table;
        #print Dumper $row;
    }   
    exit;
my $output = (our @to_pwd);#send output to the array @to_pwd so it can then be spliced into each row

$to_id = format_to_orderid($to_id);
 foreach my $output(@to_pwd)
 {
    ($FICE_CODE, $SCHL_NAME, $FIRST_NAME, $LAST_NAME, $TO_ORDER_ID, $CH_FLAG, $ORDER_STATUS, $TO_RECIP_ID, $ORDER_STATUS, $RECPT_ORDER_STATUS, $EMAIL, $TO_DELIV_TRANS_ID, $PASSWORD, $FILE_NAME)=split(",",$output);#split output with a coma from each row.
        if($FICE_CODE eq "fice_code")#header rows
        {
            print"<tr>/n";
            print"<th>$FICE_CODE</th>";
            print"<th>$SCHL_NAME</th>";
            print"<th>$FIRST_NAME</th>";
            print"<th>$LAST_NAME</th>";
            print"<th>$TO_ORDER_ID</th>";
            print"<th>$CH_FLAG</th>";
            print"<th>$ORDER_STATUS</th>";
            print"<th>$TO_RECIP_ID</th>";
            print"<th>$ORDER_STATUS</th>";
            print"<th>$RECPT_ORDER_STATUS</th>";
            print"<th>$EMAIL</th>";
            print"<th>$TO_DELIV_TRANS_ID</th>";
            print"<th>$PASSWORD</th>";
            print"<th>$FILE_NAME</th>";
            print"<tr/>/n";
            print "</table>/n";
            next;
        }
        print"<tr>/n"; #data information from sql
        print"<td>$FICE_CODE</td>";
        print"<td align 'right'>    $SCHL_NAME</td>/n";
        print"<td align 'right'>    $FIRST_NAME</td>/n";
        print"<td align 'right'>    $LAST_NAME</td>/n";
        print"<td align 'right'>    $TO_ORDER_ID</td>/n";
        print"<td align 'right'>    $CH_FLAG</td>/n";
        print"<td align 'right'>    $ORDER_STATUS</td>/n";
        print"<td align 'right'>    $TO_RECIP_ID</td>/n";
        print"<td align 'right'>    $ORDER_STATUS</td>/n";
        print"<td align 'right'>    $RECPT_ORDER_STATUS</td>/n";
        print"<td align 'right'>    $EMAIL</td>/n";
        print"<td align 'right'>    $TO_DELIV_TRANS_ID</td>/n";
        print"<td align 'right'>    $PASSWORD</td>/n";
        print"<td align 'right'>    $FILE_NAME</td>/n";
        #finish table data html
        print"<tr/>/n";
 }

}
    else
    {
        show_form();
    }
    print"</body></html>\n"
}

sub show_form
{
    my $url = url;
    print qq{<form name="input" action="ts_password_retrieval.cgi" method="get">\n};
    print qq{<table align="center" border="1" bordercolor="black" cellpadding="2" cellspacing="0">\n};
    print qq{<tr>};
    print qq{<td align="right">Please enter the Transcript Order ID #</td};
    print qq{</tr>\n};
    print qq{<td align="left"><input type"text" width="7" name="to_id" value="">};
    print qq{<BR>Place each Transcript Order ID separated by a space</td>};
    print qq{</table><center><input type="submit" value="Submitted"></center></form>\n};
}
sub format_to_orderid
{
my $in= shift;
my $out = join ',', split /\s/, $in;
return $out;
}

Tried to troubleshoot by just doing a dump_results() and I'm still getting a blank page

#!/usr/bin/perl -w


use CGI::Pretty qw(:standard *table);
use strict;
use warnings;
use DBI;
#use Config::Std;
use vars qw($FICE_CODE $SCHL_NAME $FIRST_NAME $LAST_NAME $TO_ORDER_ID     $CH_FLAG $ORDER_STATUS $TO_RECIP_ID $ORDER_STATUS $RECPT_ORDER_STATUS $EMAIL $TO_DELIV_TRANS_ID $PASSWORD $FILE_NAME);
use Data::Dumper;
#&ReadParse(*input);

#Output display
print "Content-type: text/html\n\n";

main();

 sub main
 {
   print "<html><head>\n";
   print "<title>Transcript Password Retrieval</title></head>\n";
   print "<body>\n";
   print "<center>Transcript Password Retrieval</center>\n";
   print "</body></html>\n";
if (param('to_id'))
{
    my $to_id = param('to_id');#local to_id
    $to_id = format_spn_string($to_id);
    my $pwd_select = ("select s.fice_code, s.schl_name, o.first_name, o.last_name, o.to_order_id, o.ch_flag, o.order_status, r.to_recipient_id, r.order_status,r.email, dt.to_deliv_trans_id, dt.password, dt.file_name from to_scprofil s, to_order o, to_recipient r, to_deliv_trans dt where s.to_scprofil_id = o.to_scprofil_id and o.to_order_id = r.to_order_id and r.to_recipient_id = dt.to_recipient_id and o.to_order_id in ($to_id) order by o.to_order_id desc");

    my $driver = "Informix";
    my $database = "Web1";
    my $dsn = "DBI:$driver:dbname=$database";
    my $userid = "user";
    my $password = "password";
    my $dbh = DBI->connect($dsn, $userid, $password, {RaiseError => 1}) or die $DBI::errstr;
    #print "Opened db successfully\n";

    my $sth = $dbh->prepare($pwd_select);
    $sth ->execute; #make this a global variable might have to revert this back to a local variable
    $sth -> dump_results();
    $sth->finish();
    $dbh->disconnect();


}
    else
    {
        show_form();
    }
    print"</body></html>\n"
}

 sub show_form
 {
    my $url = url;
    print qq{<form name="input" action="ts_password_retrieval.cgi" method="get">\n};
    print qq{<table align="center" border="1" bordercolor="black" cellpadding="2" cellspacing="0">\n};
    print qq{<tr>};
    print qq{<td align="right">Please enter the ID #</td};
    print qq{</tr>\n};
    print qq{<td align="left"><input type"text" width="7" name="to_id" value="">};
    print qq{<BR>Place each ID # separated by a space</td>};
    print qq{</table><center><input type="submit" value="Submit"></center></form>\n};
}
 sub format_to_orderid
 {
my $in= shift;
my $out = join ',', split /\s/, $in;
return $out;
 }

What results do you get when you run that database query by hand? — Sobrique
– Sobrique, Commented Sep 2, 2015 at 11:02
Try perl -c scriptname to see if you have any errors in your script. — Jens
– Jens, Commented Sep 2, 2015 at 11:05
Does your commented out Dumper show retrieved data when you enable it? — Oleg V. Volkov
– Oleg V. Volkov, Commented Sep 2, 2015 at 11:34
There is an exit in the code. In a CGI environment, that will result in a blank page. — simbabque
– simbabque, Commented Sep 2, 2015 at 11:47

Jonathan Leffler · Accepted Answer · 2015-09-03 21:49:39Z

1

I'm the Guardian of DBD::Informix — aka developer and maintainer.

Unless you've modified the code in DBD::Informix in ways I don't know about, the notation:

my $dsn = "DBI:$driver:dbname=$database";

will not work. Informix does not accept dbname= in the connection string (or, more accurately, doesn't interpret it, so it tries to connect to a database called dbname=whatever@wherever and fails because = is not valid in a database name).

So, drop the dbname= part from the DSN.

From `perldoc DBI`:

"connect"
  $dbh = DBI->connect($data_source, $username, $password)
            or die $DBI::errstr;
  $dbh = DBI->connect($data_source, $username, $password, \%attr)
            or die $DBI::errstr;
Establishes a database connection, or session, to the requested $data_source. Returns a database handle object if the connection succeeds. Use "$dbh->disconnect" to terminate the connection.

If the connect fails (see below), it returns "undef" and sets both $DBI::err and $DBI::errstr. (It does not explicitly set $!.) You should generally test the return status of "connect" and "print $DBI::errstr" if it has failed.

…

The $data_source value must begin with ""dbi:"driver_name":"". The driver_name specifies the driver that will be used to make the connection. (Letter case is significant.)

…

Examples of $data_source values are:
  dbi:DriverName:database_name
  dbi:DriverName:database_name@hostname:port
  dbi:DriverName:database=database_name;host=hostname;port=port
There is no standard for the text following the driver name. Each driver is free to use whatever syntax it wants. The only requirement the DBI makes is that all the information is supplied in a single string. You must consult the documentation for the drivers you are using for a description of the syntax they require.

(Emphasis added.)

From `perldoc DBD::Informix`

CONNECTING TO A DATABASE

To connect to a database, you use the connect function, which yields a valid database handle if it is successful. …

…
    $dbh = DBI->connect("dbi:Informix:$database");
    $dbh = DBI->connect("dbi:Informix:$database", $user, $pass);
    $dbh = DBI->connect("dbi:Informix:$database", $user, $pass, %attr);
The DBI connect method strips the 'dbi:' prefix from the first argument and loads the DBD module identified by the next string (Informix in this case). The string following the second colon is all that is passed to the DBD::Informix code. With this format, you do not have to specify the username or password. Note that if you specify the username but not the password, DBD::Informix will silently ignore the username. …

edited Sep 3, 2015 at 21:49

answered Sep 3, 2015 at 21:44

Jonathan Leffler

760k145 gold badges961 silver badges1.3k bronze badges

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

12 Comments

BV45 Over a year ago

Thanks Jonathan. I've changed that, and I'm still getting the same error below on the Apache log? I can connect to the database fine from the command line and spew back results. But when I run it as a CGI script I get this error below:

Jonathan Leffler Over a year ago

Update the question with the Apache log information that looks relevant. Which version of Perl, DBI, DBD::Informix are you using, on which platform? What is different between the environment of your CGI script and your command line script? The key difference will be one of those, almost certainly.

BV45 Over a year ago

But the Informix module is installed: [root@sandbox cgi-bin]# perl -w /home/support/automation/driver_list.pl DBM, ExampleP, File, Gofer, Informix, Multiplex, Pg, Proxy, SQLite, Sponge, mysq l

Jonathan Leffler Over a year ago

The error message shown says that Perl can't find your driver reliably. Did you by any chance build it with DBD_INFORMIX_RELOCATABLE_INFORMIXDIR? Where is it installed if you think it is installed? Is the CGI using the same copy of Perl as you use on the command line?

BV45 Over a year ago

I don't think I set that variable when installing. I just did an echo for that variable and it isn't there.

|

Collectives™ on Stack Overflow

Query results in Perl CGI script is returning back blank webpage with no data

1 Answer 1

From `perldoc DBI`:

"connect"

From `perldoc DBD::Informix`

CONNECTING TO A DATABASE

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From perldoc DBI:

"connect"

From perldoc DBD::Informix

CONNECTING TO A DATABASE

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related

From `perldoc DBI`:

From `perldoc DBD::Informix`