I have written that a script that opens a cursor on a table in a DB2 database
then processes each record in the result set inside a loop.
In the course of the loop I prepare and execute another SQL statement to get a
maximum value from the same table for a given condition. This worked fine.
I then decided that to speed up the script I would prepare this lookup SQL
outside the loop using placeholders, so I can just re-execute the same SQL with
the different values plugged in each time.
I now find that on the second pass through the loop the execute of the
pre-prepared SQL fails with the message :
DBD::DB2::db disconnect failed: [IBM][CLI Driver] CLI0116E Invalid transaction
state. SQLSTATE=25000
If I go back to prepare and execute each time through the loop there is no
problem, but I think I am going to need the efficiency of using a pre-prepared
SQL statement, so I would prefer a solution to the problem if anyone can shed
any light.
Code snip follows.
Ashley
=======================
my $last_rec = join("-", "xxxxxxxxx", "xxxxxxxxx", "xxxxxxxxx","xxxxxxxxx");
my $max_sequence = 0;
my $cursor = new Cursor($dbh, $table_ref->{TABLE_NAME},
'ACCTNUM','ACCTNAME','DUEDATE','ISSUEDATE','DOC_NAME','DOC_OFF');
my $where_clause = join(" ", "where sequence = \'0\'",
"and acctnum in (select bvrn from
salond.registeredpayers)",
"order by acctnum,acctname,duedate,issuedate");
$cursor->where($where_clause);
my $sql_max = join(" ", "select max(f.sequence)",
"from $table_ref->{TABLE_NAME} f",
"where f.acctnum=?",
"and f.acctname=?",
"and f.duedate=?",
"and f.issuedate=?",
"and f.sequence>'0'");
my $sth = $dbh->prepare($sql_max);
while (my $row = $cursor->fetch) {
my $this_rec =
join("-",$row->{ACCTNUM},$row->{ACCTNAME},$row->{DUEDATE},$row->{ISSUEDATE});
if ($this_rec ne $last_rec) {
$sth->execute($row->{ACCTNUM},$row->{ACCTNAME},$row->{DUEDATE},$row->{ISSUEDATE});
my @max = $sth->fetchrow_array;
$max_sequence = ($#max == -1) ? 0 : $max[0];
}
$max_sequence++;
$cursor->set(SEQUENCE => $max_sequence);
my $updcount = $cursor->update;
}
***********************************************************************************
This e-mail, including any attachments to it, may contain confidential and/or
personal information.
If you have received this e-mail in error, you must not copy, distribute, or
disclose it, use or take any action
based on the information contained within it.
Please notify the sender immediately by return e-mail of the error and then
delete the original e-mail.
The information contained within this e-mail may be solely the opinion of the
sender and may not necessarily
reflect the position, beliefs or opinions of Salmat on any issue.
This email has been swept for the presence of computer viruses known to
Salmat's anti-virus systems.
For more information, visit our website at www.salmat.com.au.
***********************************************************************************
