[Sqlrelay-discussion] Show Stopper?

[Stephen C. <st...@to...>]

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 I finally found a 
test case that prove 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 request from today with similar errors.

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