Re: Informix error messages

[Curt Russell Crandall]

Forgot this in the last reply... it's the code I'm using to examine this
problem.  Basically, it provides a text box for you to enter 1 SQL
statement.  If executed successfully, it prints the results otherwise it
prints the error.  Ignore the Javascript, it's not completed, but it's
meant to display query result (ONLY) in a separate window... doesn't work
yet, though.  Regardless, it's not related to the problem... we see this
in all our fcgi scripts.
As stated previously, $ENV{'INFORMIXDIR'} is always correctly
printed.  However, the only time the $DBI::errstr string prints correctly
is the first time after the Apache server is restarted.  After the first
time, subsequent executions of this script have $DBI::errstr displaying
only the error codes and indicating in the error_log that there was a
failure to locate the SQL error message.



#!/usr/bin/perl 

use strict;
use warnings;

use appbase;
use CGI::Carp qw(fatalsToBrowser);
use CGI::Fast qw(:standard -no_debug);
use DBI;
use Data::Dumper;
use POSIX     qw(tmpnam);

$CGI::POST_MAX        = 1000000;
$CGI::DISABLE_UPLOADS = 1;

my %app = get_appbase();  ### module that extracts env info from flat file
$ENV{'INFORMIXSERVER'} = $app{'ifxsvr'};   ### either 'test' or 'prod'
$ENV{'INFORMIXDIR'} = '/usr/informix';

my $dbh = DBI->connect( $app{'dbconn'}, $app{'dbuser'}, $app{'dbpass'} );
$dbh->{RaiseError} = 0;
$dbh->{PrintError} = 1;
$dbh->{AutoCommit} = 0;

my $q = undef;
while ($q = CGI::Fast->new())
 {
     my $status = '';

     my $random_win_name = tmpnam();
     $random_win_name =~ s/\/tmp\///;

     if ($q->param('go'))
      {
          $q->print(
                    style_hdg(
                              {'title' => "SQL Interface to
$dbh->{Name}"},
                              $status,
                              show_action($random_win_name),
                              show_result()
                             )
                   );
      }
     else
      {
          $q->print(
                    style_hdg(
                              {'title' => "SQL Interface to
$dbh->{Name}"},
                              show_action($random_win_name)
                             )
                   );
      }
 }

##
## Exit
##
END
 {
     $dbh->disconnect();
     undef($dbh);
 }

exit(0);


### Subroutines ###

sub show_action
 {
     my $random_win_name = shift;

     my $html = <<EOF;
<SCRIPT LANGUAGE=JavaScript>
function results_win(name)
 {
     var query = document.sql_form.sql.value;
     window.open("", name, "width=500, height=500, status=yes,
resizable=yes");
     return true;
 }
</SCRIPT>
EOF

     $html .= $q->start_form({-name=>'sql_form'})
             . p( 'Enter SQL: ',
                  font({-face => 'monospace'},
                  $q->textarea(-name => 'sql',
                               -rows => 20,
                               -cols => 60)),
                  '<br>Number<br>of Results<br>',
                  $q->popup_menu(-name => 'num_results',

-value=>['50','100','150','200','300','ALL'],
                                 -default=>'ALL'),
                  '&nbsp;&nbsp;&nbsp;',
                  $q->submit(-value    => 'Submit Query',
                             -name     => 'go',
                             -onSubmit => "return
results_win($random_win_name);") )
             . $q->end_form
             . '<hr><br>';

     return $html;
 }



sub show_result
 {
     my $html = '';
     my $sql  = $q->param('sql');

     $dbh->do('set isolation to dirty read');
     if ($DBI::errstr)
      {
          return p(['Set Isolation Level failed: ', $DBI::errstr]);
      }

     my $stmt = $dbh->prepare($sql);
     if ($DBI::errstr)
      {
          return p(["INFORMIXDIR: $ENV{'INFORMIXDIR'} Prepare failed: ",
$DBI::errstr]);
      }

     $stmt->execute();
     if ($DBI::errstr)
      {
          return p(['Execute failed: ', $DBI::errstr]);
      }


     if ($sql =~ /\s*select/i)
      {
          my @rows = @{$stmt->fetchall_arrayref()};
          if ($DBI::errstr)
           {
               return p(['fetchall->arrayref failed:', $DBI::errstr]);
           }

          my $cols = $stmt->{NUM_OF_FIELDS};
          my $rows_affected = $stmt->rows();

          $html .= <<EOF;
<table border="1" cellpadding="4" cellspacing="0">
<tr><td colspan="$cols" align="left">
Result: $rows_affected rows found, $cols columns
</td></tr>
EOF

          $html .= Tr(td({-align => 'center'}, ['ROW',
@{$stmt->{NAME}}]));

          my $stop_at = $q->param('num_results');
          if ($stop_at eq 'ALL') { $stop_at = $rows_affected; }

          my $count = 1;
          foreach my $row (@rows)
           {
               $html .= Tr(td([$count,@{$row}]));
               if ($count == int($stop_at)) { last; }
               $count++;
           }

          $html .= "</table>\n";
      }
     else
      {
          my $rows_affected = $stmt->rows();

          $dbh->commit();
          if ($DBI::errstr)
           {
               return p(['Commit failed: ', $DBI::errstr]);
           }
          $html .= p("$rows_affected rows affected");
      }

     return $html;
 }

__END__