Rows returned are out of sync with the request.

I'm not quite sure how to describe this problem.

When I make a call to an Oracle 10g database using DBI and SQLRelay (for
connection pooling) I seem to get results from the previous SELECT.

For example, If I send the following:

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 = ?'

Then execute it with 29898535 for the bind variable, I get:

'9' 'Pending' '3' 'Waiting for info from Client' '28757671'
'08-22-2006 14:13:42'

Note that the certno's do not match.

If I make the next call with 29818691, I get:

'7' 'Completed' undef undef '29898535' '08-22-2006 14:13:43'

Not only do the certno's not match, the row returned in the second
example matches the certno in the previous attempt.

I have a whole log full of this weirdness. Each execute seem to return
the row from the previous one. It doesn't start that way but somewhere
aong the way things are getting seriously out of whack.

Suggestaion are welcome. I know it could be in the connection pooling
and I'll happily try something else if someone has a suggestion.

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

Re: Rows returned are out of sync with the request.

Stephen Carville schreef:

> When I make a call to an Oracle 10g database using DBI and SQLRelay
> (for connection pooling) I seem to get results from the previous
> SELECT.
>
> For example, If I send the following:
>
> 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 = ?'
>
> Then execute it with 29898535 for the bind variable,

Show us that part of your code, maybe it is just the binding that goes
wrong?
The "tranid" and "certno" in your "where" don't have table identifiers.
If you assume that the result is one record, check for more.

--
Affijn, Ruud

"Gewoon is een tijger."

RE: Rows returned are out of sync with the request.

Stephen Carville wrote:
> I'm not quite sure how to describe this problem.
>=20
> When I make a call to an Oracle 10g database using DBI and SQLRelay
> (for connection pooling) I seem to get results from the previous
> SELECT.=20

What happens when you use DBI *without* SQLRelay?

Philip

Re: Rows returned are out of sync with the request.

Garrett, Philip (MAN-Corporate) wrote:
> Stephen Carville wrote:
>
>>I'm not quite sure how to describe this problem.
>>
>>When I make a call to an Oracle 10g database using DBI and SQLRelay
>>(for connection pooling) I seem to get results from the previous
>>SELECT.
>
>
> What happens when you use DBI *without* SQLRelay?

Dunno yet. I have to rewrite this and one other program to use direct
connections. I cannot switch to direct connections across the board
beacause other programs share the code and make aobur 3000+ connections
per hour during the busy part of the day

> Philip


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

Re: Rows returned are out of sync with the request.

Dr.Ruud wrote:
> Stephen Carville schreef:
>
>
>>When I make a call to an Oracle 10g database using DBI and SQLRelay
>>(for connection pooling) I seem to get results from the previous
>>SELECT.
>>
>>For example, If I send the following:
>>
>>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 = ?'
>>
>>Then execute it with 29898535 for the bind variable,
>
>
> Show us that part of your code, maybe it is just the binding that goes
> wrong?
> The "tranid" and "certno" in your "where" don't have table identifiers.
> If you assume that the result is one record, check for more.
>

I didn't know that dropping the table ids could make a difference. I'll
try adding them. Thanks.

These are the subs where I setup and make the calls to DBI.

# this gets the status by certno.
# connection handle is already open

use sql;

{ my ($sth);
sub getstatus_by_certno {
my ($certno) = @_;
my ($status,$line,$timeout);
my (%results);
my $SCRIPT = "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 = ?";

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 staid and associated message
return evaluate_status(%results);
}
}

# from sql.pm

use DBI;
use Sys::SigAction qw (set_sig_handler);

# prepare for execution
# return sth on success or 0/undef on error
#
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;
}

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


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

Re: Rows returned are out of sync with the request.

Stephen Carville schreef:
> Dr.Ruud:

>> The "tranid" and "certno" in your "where" don't have table
>> identifiers. If you assume that the result is one record, check for
>> more.
>
> I didn't know that dropping the table ids could make a difference.
> I'll try adding them. Thanks.

They shouldn't make a difference, unless the names are (or once become)
ambiguous.


> These are the subs where I setup and make the calls to DBI.


Don't forget the

use warnings ;
use strict ;


> # this gets the status by certno.
> # connection handle is already open
>
> use sql;
> { my ($sth);
>
> sub getstatus_by_certno {
> my ($certno) = @_;
> my ($status,$line,$timeout);
> my (%results);
> my $SCRIPT = "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 = ?";
>
> unless ($sth) {

Can $sth ever be already defined here?


> $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);

The $sths get undefined.


> $line = edi_get_last_results() . "\n";
>
> log_db_addlines($line);
>
> # get the staid and associated message
> return evaluate_status(%results);
> }
> }
>
>
> # from sql.pm
>
> use DBI;
> use Sys::SigAction qw (set_sig_handler);
>
> # prepare for execution
> # return sth on success or 0/undef on error
> #
> 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);
> }

Maybe make that something like:

$sth or $edi_lastresult .= "=> prepare() timed out in $timeout
seconds.\n" ;


> return $sth;
> }
>
> 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:");

Why the || here? Maybe change that to:

$edi_lastresult .= "=> execute() timed out in $timeout
seconds.\n" ;

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


--
Affijn, Ruud

"Gewoon is een tijger."

Re: Rows returned are out of sync with the request.

Dr.Ruud wrote:
> Stephen Carville schreef:
>
>>Dr.Ruud:
>

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 fro certno = 29721783. However the alarm was
triggered (18 second time out!) and I 'timed out" the call and undefed
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.

Thanks for your help...

>>>The "tranid" and "certno" in your "where" don't have table
>>>identifiers. If you assume that the result is one record, check for
>>>more.
>>
>>I didn't know that dropping the table ids could make a difference.
>>I'll try adding them. Thanks.
>
>
> They shouldn't make a difference, unless the names are (or once become)
> ambiguous.
>
>
>
>>These are the subs where I setup and make the calls to DBI.
>
>
>
> Don't forget the
>
> use warnings ;
> use strict ;
>
>
>
>># this gets the status by certno.
>># connection handle is already open
>>
>>use sql;
>>{ my ($sth);
>>
>> sub getstatus_by_certno {
>> my ($certno) = @_;
>> my ($status,$line,$timeout);
>> my (%results);
>> my $SCRIPT = "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 = ?";
>>
>> unless ($sth) {
>
>
> Can $sth ever be already defined here?
>
>
>
>> $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);
>
>
> The $sths get undefined.

I used to preserve $sth between calls and if I can figure this problem
out I will again. Using bind variable and not having to prepare() for
each call doesn't save a lot on each run but this script can get
executed 20,000 times or more every day.

>
>
>> $line = edi_get_last_results() . "\n";
>>
>> log_db_addlines($line);
>>
>> # get the staid and associated message
>> return evaluate_status(%results);
>> }
>>}
>>
>>
>># from sql.pm
>>
>>use DBI;
>>use Sys::SigAction qw (set_sig_handler);
>>
>># prepare for execution
>># return sth on success or 0/undef on error
>>#
>>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);
>> }
>
>
> Maybe make that something like:
>
> $sth or $edi_lastresult .= "=> prepare() timed out in $timeout
> seconds.\n" ;
>
>
>> return $sth;
>>}
>>
>>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:");
>
>
> Why the || here? Maybe change that to:
>
> $edi_lastresult .= "=> execute() timed out in $timeout
> seconds.\n" ;
>
>
>> 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;
>>}
>
>
>


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

Re: Rows returned are out of sync with the request.

On Tue, 2006-08-22 at 16:11 -0700, Stephen Carville wrote:
> I'm not quite sure how to describe this problem.
>
> When I make a call to an Oracle 10g database using DBI and SQLRelay (for
> connection pooling) I seem to get results from the previous SELECT.
>

Is the Oracle version 10.2?

Are multiple schema's with the same table names involved?

Are unqualified tablenames used?
( no schema name - 'select * from my table'
not 'select * from scott.my_table')

If all of the above are true, then you should probably
contact Oracle Support. There's a bug that causes
some concurrency issues under those circumstances.
(Sorry, don't have the bug #)

Jared

Re: Rows returned are out of sync with the request.

Jared Still wrote:
> On Tue, 2006-08-22 at 16:11 -0700, Stephen Carville wrote:
>
>>I'm not quite sure how to describe this problem.
>>
>>When I make a call to an Oracle 10g database using DBI and SQLRelay (for
>>connection pooling) I seem to get results from the previous SELECT.
>>
>
>
> Is the Oracle version 10.2?

Yes. 10.2.0.2

> Are multiple schema's with the same table names involved?

I don't know but I'm going to find out.

> Are unqualified tablenames used?
> ( no schema name - 'select * from my table'
> not 'select * from scott.my_table')

Yes

> If all of the above are true, then you should probably
> contact Oracle Support. There's a bug that causes
> some concurrency issues under those circumstances.
> (Sorry, don't have the bug #)

Thanks for the pointer.


> Jared
>
>


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