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 <[EMAIL PROTECTED]>
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602
