Invalid cursor state on second $sth->execute



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


Relevant Pages

  • Re: Converting Perl Web Report to Python
    ... lets say that your sql script looks like this: ... This capability would allow each sql script to be extendable. ... How would you execute ...
    (comp.lang.python)
  • Re: SSIS SQL Task Dynamic File Source
    ... You will need to place your Execute SQL Task inside a Foreach Loop ...
    (microsoft.public.sqlserver.dts)
  • Re: Execute error: Type Mismatch: Execute
    ... I modified the script slightly so that it will display what ... and it is called by the Execute command. ... return to the loop ... Set Form = CreateObject ...
    (microsoft.public.scripting.vbscript)
  • Re: Execute error: Type Mismatch: Execute
    ... I modified the script slightly so that it will display what ... func = Form.DoEvents ... and it is called by the Execute command. ... return to the loop ...
    (microsoft.public.scripting.vbscript)
  • Re: Executing a SQL script on server
    ... > How can I using ADO send a SQL script to the server to be executed there? ... An example VBScript program to execute a stored procedure: ... ' Specify the SQL Server and Instance. ...
    (microsoft.public.vb.database.ado)