Re: [Sqlrelay-discussion] Show Stopper? Workaround

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

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
> 
> 
> 