CURSOR WITH UPDATE

From looking around with Google, it would APPEAR that you can
implement a cursor WITH UPDATE in DBI, but I have not been able to
find an example of that. Would anyone happen to have a bare-bones
code framework for such a thing?

Since I should give an example of the kind of operation I hope to do...

__CODE__
my $db; # database connection
my $match = "^H[ae]";
my $stmt = "SELECT foo,bar,fee FROM baz FOR UPDATE OF bar, fee";

my $sth;
die( "\nFailed preparing SELECT:\n$db->errstr\n" )
if !( $sth = $db->prepare($stmt) );

die( "\nFailed executing SELECT:\n$db->errstr\n" )
if !$sth->execute();

while ( my ( $foo, $bar, $fee ) = $sth->fetchrow_array )
{
next if $foo !~ /$match/;

$db->do( "UPDATE baz SET bar=3,fee='fie' WHERE CURRENT OF $sth->cursor")
or die( "UPDATE FAILED: $sth->errstr\n");
}

$sth->finish;
__CODE_ENDS__

Of course, $sth->cursor don't really exist (as far as I know).

TIA!

--
pDale Campbell

Re: CURSOR WITH UPDATE

------=_Part_28979_9056625.1152641037262
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

$sth->{CursorName} is a key part of it.

See also the docs for DBD::Informix (http://search.cpan.org/) for an example
- the POD has a section CURSORS FOR UPDATE that shows how to do it.

On 7/11/06, pDale wrote:
>
> From looking around with Google, it would APPEAR that you can
> implement a cursor WITH UPDATE in DBI, but I have not been able to
> find an example of that. Would anyone happen to have a bare-bones
> code framework for such a thing?
>
> Since I should give an example of the kind of operation I hope to do...
>
> __CODE__
> my $db; # database connection
> my $match = "^H[ae]";
> my $stmt = "SELECT foo,bar,fee FROM baz FOR UPDATE OF bar, fee";
>
> my $sth;
> die( "\nFailed preparing SELECT:\n$db->errstr\n" )
> if !( $sth = $db->prepare($stmt) );
>
> die( "\nFailed executing SELECT:\n$db->errstr\n" )
> if !$sth->execute();
>
> while ( my ( $foo, $bar, $fee ) = $sth->fetchrow_array )
> {
> next if $foo !~ /$match/;
>
> $db->do( "UPDATE baz SET bar=3,fee='fie' WHERE CURRENT OF
> $sth->cursor")
> or die( "UPDATE FAILED: $sth->errstr\n");
> }
>
> $sth->finish;
> __CODE_ENDS__
>
> Of course, $sth->cursor don't really exist (as far as I know).
>
> TIA!
>
> --
> pDale Campbell
>



--
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_28979_9056625.1152641037262--

Re: CURSOR WITH UPDATE

On 7/11/06, Jonathan Leffler wrote:
>
> On 7/11/06, pDale wrote:
> > From looking around with Google, it would APPEAR that you can
> > implement a cursor WITH UPDATE in DBI, but I have not been able to
> > find an example of that. Would anyone happen to have a bare-bones
> > code framework for such a thing?
>
> $sth->{CursorName} is a key part of it.
>
> See also the docs for DBD::Informix (http://search.cpan.org/) for an example
> - the POD has a section CURSORS FOR UPDATE that shows how to do it.

I should have mentioned I'm working with Oracle. I may have found a
way by working with cursors directly, using something very loosely
related to binding cursors:
.

Any other suggestions?

--
pDale Campbell
"The moment I let go of it,
Was the moment I got more than I could handle."