Re: Rows returned are out of sync with the request.

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."


.

Relevant Pages

  • Re: Rows returned are out of sync with the request.
    ... sub getstatus_by_certno { ... undef $sth if; ... # reset alarm JIC ... return %tbl; ...
    (perl.dbi.users)
  • Re: Forms refresh
    ... because it is sth like form.ShowDialogand unless the messageBox is closed ... But sth in both cases is not so good....It is that your dialog ... Put the repaint functions in a Sub() and call the sub from a Timer_Tick ...
    (microsoft.public.dotnet.languages.vb)
  • Re: How to test status of handler sth?
    ... > A scenario is that a $sth is passed from a sub. ... $sth will be false. ... Give a hobbit a ring and he eats fish for an age. ...
    (perl.dbi.users)
  • How to test status of handler sth?
    ... A scenario is that a $sth is passed from a sub. ...
    (perl.dbi.users)
  • DBI sth->row problem
    ... A scenario is that a $sth is passed from a sub. ...
    (perl.beginners)