This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Re: Delete using a cursor
From: db2perl@ca.ibm.com
Date: Wed, 20 Dec 2000 10:53:15 -0500
Hi Juan. I don't see any problem in your code (except that it's not very
efficient, I'll get to that later). I tried it with DB2 v7.1 and the
sample database and it worked fine. I suggest you read the full help for
message SQL0510 to see if any of the conditions that would prevent deleting
a row apply.
I assume your test program is a simplified version of your real program,
but just in case, the correct way to do the delete is in one line:
$dbh->do( "delete from testcat where link_id < 300" );
However, you probably already know that and there is a reason you want to
iterate through the rows before deleting each one. In that case, there is
no need to prepare a new statement in each iteration of the loop, just
prepare one statement and execute it multiple times. And don't forget to
finish every statement when you're done with it:
my $sth2 = $dbh->prepare( "delete from testcat where current of
$sth1->{CursorName}" );
while( @row = $sth1->fetchrow_array )
{
# do something with this row
$sth2->execute(); # delete the row
}
$sth2->finish();
Regards,
Robert
Juan Terrazas/Watson/Contr/IBM@IBMUS@isc.org on 19/12/2000 05:30:18 PM
Please respond to Juan Terrazas/Watson/Contr/IBM@IBMUS
Sent by: dbi-users-bounce@isc.org
To: dbi-users@isc.org
cc:
Subject: Delete using a cursor
Hi,
I'm attempting to delete a bunch of rows in a table using a cursor. My
script goes like the following :
$stmt1 = "select *
from testcat
where link_id < 300 for update;";
$sth1 = $dbh->prepare($stmt1);
$sth1->execute();
print "Attempting to delete ...\n";
while (@row = $sth1->fetchrow_array) {
$stmt2 = "delete from testcat where current of $sth1->{CursorName};";
my $sth2 = $dbh->prepare($stmt2);
$sth2->execute();
}
$sth1->finish();
$dbh->disconnect();
However, I'm getting the following error :
DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/NT] SQL0510N UPDATE or
DELETE is not
allowed against the specified cursor. SQLSTATE=42828
I'm a newbie at Perl and DBI. What Am I doing wrong?
Thanks,
Juan Terrazas
===
Subject: Re: Delete using a cursor
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Wed, 20 Dec 2000 16:32:18 +0000
On Wed, Dec 20, 2000 at 10:53:15AM -0500, Liisa Huffman wrote:
>
> And don't forget to
> finish every statement when you're done with it:
>
> my $sth2 = $dbh->prepare( "delete from testcat where current of
> $sth1->{CursorName}" );
> $sth2->finish();
DELETE statements should never need a finish().
The finish() method is only appropriate for SELECT statements and
even then only rarely.
Tim.
===