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

Dr.Ruud wrote:
Stephen Carville schreef:

Dr.Ruud:


I've been reviewing the sqltrace logs and I thinks this may be a case where safe signals are biting me in the ***.

-> prepare for DBD::SQLRelay::db (DBI::db=HASH(0x8e05078)~0x8de04f4 '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 = ?') thr#882a008
1 <> FETCH= ( SQLRelay::Connection=SCALAR(0x8dfff7c) ) [1 items] ('driver_connection' from cache) at SQLRelay.pm line 138
-> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4 'driver_database_handle' DBI::db=HASH(0x8de04f4)) thr#882a008
<- STORE= 1 at SQLRelay.pm line 145
-> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4 'NUM_OF_PARAMS' 1) thr#882a008
<- STORE= 1 at SQLRelay.pm line 146
-> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4 'driver_is_select' 1) thr#882a008
<- STORE= 1 at SQLRelay.pm line 147
-> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4 'driver_cursor' SQLRelay::Cursor=SCALAR(0x8dfc800)) thr#882a008
<- STORE= 1 at SQLRelay.pm line 148
-> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4 'NUM_OF_PARAMS' 1) thr#882a008
<- STORE= 1 at SQLRelay.pm line 158
<- prepare= DBI::st=HASH(0x8deb994) at sql.pm line 164
-------------------------------------
-> execute for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4 '29721783') thr#882a008
-------------------------------------
1 <> FETCH= SQLRelay::Cursor=SCALAR(0x8dfc800) ('driver_cursor' from cache) at SQLRelay.pm line 349
1 <> FETCH= SQLRelay::Cursor=SCALAR(0x8dfc800) ('driver_cursor' from cache) at SQLRelay.pm line 275
-> $DBI::errstr (&) FETCH from lasth=HASH
>> DBD::SQLRelay::st::errstr
<- $DBI::errstr= undef
-> DESTROY for DBD::SQLRelay::st (DBI::st=HASH(0x8de03a4)~INNER) thr#882a008
<- DESTROY= undef at xml-queue.pl line 309

Here I request the status fro certno = 29721783. However the alarm was triggered (18 second time out!) and I 'timed out" the call and undefed the script handle. However I suspect the signal was not delivered until the execute completed. Leading me to the next invocation:

-> prepare for DBD::SQLRelay::db (DBI::db=HASH(0x8e05078)~0x8de04f4 '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 = ?') thr#882a008
1 <> FETCH= ( SQLRelay::Connection=SCALAR(0x8dfff7c) ) [1 items] ('driver_connection' from cache) at SQLRelay.pm line 138
-> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180 'driver_database_handle' DBI::db=HASH(0x8de04f4)) thr#882a008
<- STORE= 1 at SQLRelay.pm line 145
-> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180 'NUM_OF_PARAMS' 1) thr#882a008
<- STORE= 1 at SQLRelay.pm line 146
-> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180 'driver_is_select' 1) thr#882a008
<- STORE= 1 at SQLRelay.pm line 147
-> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180 'driver_cursor' SQLRelay::Cursor=SCALAR(0x8e4d628)) thr#882a008
<- STORE= 1 at SQLRelay.pm line 148
-> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180 'NUM_OF_PARAMS' 1) thr#882a008
<- STORE= 1 at SQLRelay.pm line 158
<- prepare= DBI::st=HASH(0x8dd7f10) at sql.pm line 164
----------------------------------------------
-> execute for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180 '28888521') thr#882a008
----------------------------------------------
1 <> FETCH= SQLRelay::Cursor=SCALAR(0x8e4d628) ('driver_cursor' from cache) at SQLRelay.pm line 349
1 <> FETCH= SQLRelay::Cursor=SCALAR(0x8e4d628) ('driver_cursor' from cache) at SQLRelay.pm line 275
1 -> FETCH for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER 'NUM_OF_FIELDS') thr#882a008
1 <- FETCH= undef at SQLRelay.pm line 379
1 -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER 'NUM_OF_FIELDS' 6) thr#882a008
1 <- STORE= 1 at SQLRelay.pm line 379
1 -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER 'driver_FETCHED_ROWS' 0) thr#882a008
1 <- STORE= 1 at SQLRelay.pm line 384
1 -> FETCH for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER 'driver_param_inout_list') thr#882a008
1 <- FETCH= undef at SQLRelay.pm line 387
1 -> rows for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER) thr#882a008
2 <> FETCH= SQLRelay::Cursor=SCALAR(0x8e4d628) ('driver_cursor' from cache) at SQLRelay.pm line 446
1 <- rows= 0 at SQLRelay.pm line 399
<- execute= '0E0' at sql.pm line 212
-> fetchrow_arrayref for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180) thr#882a008
1 <> FETCH= 0 ('driver_FETCHED_ROWS' from cache) at SQLRelay.pm line 412
1 <> FETCH= SQLRelay::Cursor=SCALAR(0x8e4d628) ('driver_cursor' from cache) at SQLRelay.pm line 422
1 -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER 'driver_FETCHED_ROWS' 1) thr#882a008
1 <- STORE= 1 at SQLRelay.pm line 426
1 -> FETCH for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER 'ChopBlanks') thr#882a008
1 <- FETCH= '' at SQLRelay.pm line 429
------------------------
<- fetchrow_arrayref= [ '9' 'Pending' '3' 'Waiting for info from Client' '29721783' '08-23-2006 11:25:12' ] row1 at sql.pm line 233
------------------------
-> fetchrow_arrayref for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180) thr#882a008
1 <> FETCH= 1 ('driver_FETCHED_ROWS' from cache) at SQLRelay.pm line 412
1 <> FETCH= SQLRelay::Cursor=SCALAR(0x8e4d628) ('driver_cursor' from cache) at SQLRelay.pm line 422
<- fetchrow_arrayref= undef row1 at sql.pm line 242
-> DESTROY for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER) thr#882a008
<- DESTROY= undef at xml-queue.pl line 309

This time DBI/SQLRelay returned the results from the previous call (certno = 29721783) instead of the present one (certno = 28888521) and, I guess only believing there was one row returned, held the actual result in a buffer somewhere. Every subsequent call up until I disconnect shows this one row off behavior. then things reset and I'm OK until the the next time a script takes too long to execute.

Thanks for your help...

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.

I used to preserve $sth between calls and if I can figure this problem out I will again. Using bind variable and not having to prepare() for each call doesn't save a lot on each run but this script can get executed 20,000 times or more every day.



$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;
}





--
Stephen Carville <stephen@xxxxxxxxxxxxxx>
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602
.

Quantcast