Re: [Sqlrelay-discussion] Show Stopper? Workaround
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] Show Stopper? Workaround
|
From: Stephen C. <st...@to...> - 2006-08-16 04:18:21
|
David Muse wrote:
> Interesting. Could you send me an example of the code that doesn't work
> and the fix? I'd like to take a closer look and see what's going on.
The following code works. If I remove the line
undef $sth if ($sth);
it will begin generating the errors again.
from xmlcommon.pm:
{ my ($sth);
sub xml_getstatus_by_certno {
my ($certno) = @_;
my ($status,$line,$timeout);
my (%results);
my $SCRIPT = "select a.statid, b.webstatdesc, a.pendid, c.penddesc,
a.certno, a.reqid, 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) {
$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);
$line = edi_get_last_results() . "\n";
log_db_addlines($line);
# get the status code and message
return evaluate_status(%results);
}
}
From sql.pm:
use DBI;
use Sys::SigAction qw (set_sig_handler);
# prepare a script for execution
#
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);
}
return $sth;
}
# executre a SELECT statement
#
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:"
);
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;
}
> Dave
>
> On Sun, 2006-08-13 at 08:58 -0700, Stephen Carville wrote:
>
>>I found that if I undef the script handle after each execution, things
>>seem to work OK. If you use bind variables, Oracle will cache the
>>compiled script so the impact of a prepare() before each execute is small.
>>
>>-------- Original Message --------
>>Subject: Show Stopper?
>>Date: Thu, 19 Aug 2006 13:39:22 -0700
>>From: Stephen Carville <st...@to...>
>>To: SQlRelay List <sql...@li...>
>>
>>I've been using sqlrelay 0.37 with oracle8 connector, Perl 5.8.x and
>>Oracle 10g for about two weeks now and I've noticed an ocassional case
>>where the data returned did not seem right. This was intermittent and
>>rare -- maybe one or two every thousand order -- but finally a
>>test case proved to me sqlrelay doesn't always return the correct data.
>>
>>I sent the follwing SQL:
>>select a.statid, b.webstatdesc, a.pendid, c.penddesc,
>>a.certno, a.reqid, 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 = 29693421
>>
>>I got back
>>
>> '7' 'Completed' undef undef '29752604' '28168039' '08-10-2006 11:13:08'
>>
>>The correct values s/b:
>>
>> '9' 'Pending' '3' 'Waiting for info from Client' '29693421' '28109417'
>>'08-10-2006 11:13:08'
>>
>>Notice the difference in the 'certno' and 'reqid' fields. These _never_
>>change once an order has been submitted. At first I assumed the orders
>>were being marked completed in error but as the above shows it is the
>>data returned that is sometimes wrong.
>>
>>Here is the results from the DBI trace log:
>>
>> -> prepare for DBD::SQLRelay::db (DBI::db=HASH(0xa2d42f8)~0xa33a29c
>>'select
>>a.statid, b.webstatdesc, a.pendid, c.penddesc,
>>a.certno, a.reqid, 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 = 29693421') thr#9d30008
>>1 <- FETCH= ( SQLRelay::Connection=SCALAR(0xa33a410) ) [1 items]
>>('driver_connection' from cache) at SQLRelay.pm line 138
>> -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x9d49a84)~0xa2c774c
>>'driver_database_handle' DBI::db=HASH(0xa33a29c)) thr#9d30008
>> <- STORE= 1 at SQLRelay.pm line 145
>> -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x9d49a84)~0xa2c774c
>>'NUM_OF_PARAMS' 0) thr#9d30008
>> <- STORE= 1 at SQLRelay.pm line 146
>> -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x9d49a84)~0xa2c774c
>>'driver_is_select' 1) thr#9d30008
>> <- STORE= 1 at SQLRelay.pm line 147
>> -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x9d49a84)~0xa2c774c
>>'driver_cursor' SQLRelay::Cursor=SCALAR(0xa2c77dc)) thr#9d30008
>> <- STORE= 1 at SQLRelay.pm line 148
>> -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0x9d49a84)~0xa2c774c
>>'NUM_OF_PARAMS' 0) thr#9d30008
>> <- STORE= 1 at SQLRelay.pm line 158
>> <- prepare= DBI::st=HASH(0x9d49a84) at sql.pm line 223
>> -> execute for DBD::SQLRelay::st
>>(DBI::st=HASH(0x9d49a84)~0xa2c774c) thr#9d30008
>>1 <- FETCH= SQLRelay::Cursor=SCALAR(0xa2c77dc) ('driver_cursor' from
>>cache) at SQLRelay.pm line 349
>>1 -> FETCH for DBD::SQLRelay::st (DBI::st=HASH(0xa2c774c)~INNER
>>'NUM_OF_FIELDS') thr#9d30008
>>1 <- FETCH= 0 at SQLRelay.pm line 379
>>1 -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0xa2c774c)~INNER
>>'NUM_OF_FIELDS' 7) thr#9d30008
>>1 <- STORE= 1 at SQLRelay.pm line 379
>>1 -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0xa2c774c)~INNER
>>'driver_FETCHED_ROWS' 0) thr#9d30008
>>1 <- STORE= 1 at SQLRelay.pm line 384
>>1 -> FETCH for DBD::SQLRelay::st (DBI::st=HASH(0xa2c774c)~INNER
>>'driver_param_inout_list') thr#9d30008
>>1 <- FETCH= undef at SQLRelay.pm line 387
>>1 -> rows for DBD::SQLRelay::st (DBI::st=HASH(0xa2c774c)~INNER)
>>thr#9d30008
>>2 <- FETCH= SQLRelay::Cursor=SCALAR(0xa2c77dc) ('driver_cursor' from
>>cache) at SQLRelay.pm line 446
>>1 <- rows= 0 at SQLRelay.pm line 399
>> <- execute= '0E0' at sql.pm line 230
>> -> fetchrow_arrayref for DBD::SQLRelay::st
>>(DBI::st=HASH(0x9d49a84)~0xa2c774c) thr#9d30008
>>1 <- FETCH= 0 ('driver_FETCHED_ROWS' from cache) at SQLRelay.pm line 412
>>1 <- FETCH= SQLRelay::Cursor=SCALAR(0xa2c77dc) ('driver_cursor' from
>>cache) at SQLRelay.pm line 422
>>1 -> STORE for DBD::SQLRelay::st (DBI::st=HASH(0xa2c774c)~INNER
>>'driver_FETCHED_ROWS' 1) thr#9d30008
>>1 <- STORE= 1 at SQLRelay.pm line 426
>>1 -> FETCH for DBD::SQLRelay::st (DBI::st=HASH(0xa2c774c)~INNER
>>'ChopBlanks')thr#9d30008
>>1 <- FETCH= '' at SQLRelay.pm line 429
>> <- fetchrow_arrayref= [ '7' 'Completed' undef undef '29752604'
>>'28168039' '08-10-2006 11:13:08' ] row1 at sql.pm line 255
>>
>>I have several other requests with similar errors.
>>
>>--
>>Stephen Carville <st...@to...>
>>Unix and Network Admin
>>Nationwide Totalflood
>>6033 W. Century Blvd
>>Los Angeles, CA 90045
>>310-342-3602
>>
>>
>>
>
>
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
--
Stephen Carville <st...@to...>
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602
|
