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-24 03:13:10
|
I've been reviewing the sqltrace logs and I thinks this may be a case
where safe signals are biting me in the butt.
-> 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 for certno = 29721783. However the alarm was
triggered (18 second time out BTW!) and I 'timed out" the call and
undef'ed 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.
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.
>
> 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
|