Stephen Carville schreef:
> Dr.Ruud:
>> The "tranid" and "certno" in your "where" don't have table
>> identifiers. If you assume that the result is one record, check for
>> more.
>
> I didn't know that dropping the table ids could make a difference.
> I'll try adding them. Thanks.
They shouldn't make a difference, unless the names are (or once become)
ambiguous.
> These are the subs where I setup and make the calls to DBI.
Don't forget the
use warnings ;
use strict ;
> # this gets the status by certno.
> # connection handle is already open
>
> use sql;
> { my ($sth);
>
> sub getstatus_by_certno {
> my ($certno) = @_;
> my ($status,$line,$timeout);
> my (%results);
> my $SCRIPT = "select a.statid, b.webstatdesc, a.pendid,
> c.penddesc, a.certno, to_char(sysdate,'MM-DD-YYYY hh24:mi:ss')
> from viewrequestmasall a, requeststatusparm b, pendingreasonparm c
> where a.statid = b.statid
> and a.pendid = c.pendid (+)
> and tranid = 1
> and certno = ?";
>
> unless ($sth) {
Can $sth ever be already defined here?
> $timeout = log_get_timeout();
> $sth = edi_prepare_sql($SCRIPT,$timeout);
> }
>
> $timeout = log_get_timeout();
> %results = edi_run_select($sth,$timeout,$certno);
> undef $sth if ($sth);
The $sths get undefined.
> $line = edi_get_last_results() . "\n";
>
> log_db_addlines($line);
>
> # get the staid and associated message
> return evaluate_status(%results);
> }
> }
>
>
> # from sql.pm
>
> use DBI;
> use Sys::SigAction qw (set_sig_handler);
>
> # prepare for execution
> # return sth on success or 0/undef on error
> #
> sub edi_prepare_sql {
> my($script,$timeout) = @_;
>
> my ($sth);
>
> $edi_lastresult = "";
> $timeout = $EDI_TIMEOUT unless ($timeout);
>
> unless ($edi_dbh) {
> $edi_lastresult = "connection to DB lost";
> return 0;
> }
>
> eval {
> my $h = set_sig_handler('ALRM',
> sub {$sth = 0; die;},
> { mask=>[qw(INT ALRM)],safe =>
> $edi_safe_signal } );
> # set alarm
> alarm($timeout);
>
> # prepare the script
> $sth = $edi_dbh->prepare($script);
>
> # reset alarm
> alarm(0);
> };
> # reset alarm JIC
> alarm(0);
>
> if ($@) {
> $edi_lastresult = DBI::errstr;
> }
>
> unless ($sth) {
> $edi_lastresult = "Prepared timed out in $timeout seconds"
> unless ($edi_lastresult);
> }
Maybe make that something like:
$sth or $edi_lastresult .= "=> prepare() timed out in $timeout
seconds.\n" ;
> return $sth;
> }
>
> sub edi_run_select {
> my($sth,$timeout,@bind_vars) = @_;
>
> my ($rv,$val,$cntr,$row);
> my (%tbl);
>
> $tbl{0}[0] = "ERROR";
> $tbl{1}[0] = "noservice";
> $edi_lastresult = "";
> $timeout = $EDI_TIMEOUT unless ($timeout);
>
> # if handle is not there
> unless ($sth) {
> $edi_lastresult = "cannot execute (connection lost?)";
> return %tbl;
> }
>
> eval {
> my $h = set_sig_handler('ALRM',
> sub {$rv = 0; die;},
> { mask=>[qw(INT ALRM)],safe =>
> $edi_safe_signal } );
> # set alarm
> alarm($timeout);
>
> # execute the script
> $rv = $sth->execute(@bind_vars);
> # reset alarm on success
> alarm(0);
> };
> # reset alarm JIC
> alarm(0);
>
> # if execute failed
> if ($@) {
> $edi_lastresult = DBI::errstr;
> return %tbl;
> }
> unless ($rv) {
> $edi_lastresult = ($edi_lastresult ||"Select timed out in
> $timeout seconds:");
Why the || here? Maybe change that to:
$edi_lastresult .= "=> execute() timed out in $timeout
seconds.\n" ;
> return %tbl;
> }
>
> $cntr = 0;
> # no headers yet...
> $tbl{0}[0] = "SUCCESS";
> $tbl{1} = ();
> while ($row = $sth->fetchrow_arrayref) {
> $cntr++;
> foreach (@$row) {
> $val = (trim($_) || "");
> push @{$tbl{$cntr}},$val;
> $edi_lastresult .= ($val || "undef") . "\t";
> }
> # sorta tabular format
> chop $edi_lastresult;
> $edi_lastresult .= "\n";
> }
>
> # if no rows -- no rows returned is not always an error
> unless ($cntr) {
> $edi_lastresult = "no rows returned";
> $tbl{1}[0] = "no rows";
> }
> return %tbl;
> }
--
Affijn, Ruud
"Gewoon is een tijger."
