Re: [Sqlrelay-discussion] Show Stopper? Workaround

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

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



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