[Pdx-pm] DBI/DBD::Pg SELECT ... FOR UPDATE

[Roderick A. Anderson]

Amy K. Farrell wrote:
> This is all from memory, and with Oracle besides, but I think you're
> on the right track with this snippet. You need to make sure you create
> your database handle with Autocommit => 0, and explicitly commit at
> the end of the transaction.

Thanks for the insight Amy.

I thought I was doing that but looking at the code I see I'm not.  Can't 
remember why.

Further reading of the Cheetah book kind of confuses the issue.  Not 
sure if a UPDATE will be the same transaction as a SELECT FOR UPDATE if 
I use the same handle.

Looks like test code time.  Connect, open a handle, select for update, 
then try to select/update the data using pgAdmin3 or another script.

> Whether you need the explicit commit is something I'm particularly
> fuzzy on. That might be automatic when the connection is destroyed. 

Yes, and does a $dbh->commit() affect all the statements created from 
the handle?  Oh well. like I said test code time!

Again thanks,
Rod
-- 
 >
> 
>  - Amy
> 
> On Tue, Jul 17, 2007 at 02:58:29PM -0700, Roderick A. Anderson wrote:
> [...]
> 
>> How does/ or does DBI handle row level locks that Postgresql (and I 
>> think Oracle) provides.  Do they apply to the database handle ($dbh)?
>>
>> So if,
>>
>> ###
>> my $sth = $dbh->prepare(q{SELECT the, info FROM thetable WHERE the = 'R' 
>> FOR UPDATE });
>>
>> my $uth = $dbh->prepare(q{UPDATE thetable SET the = ? WHERE info = ?}):
>>
>> $sth->execute();
>>
>> while(( $the, $info ) = $sth->fetchrow_array ) {
>>     # lots of stuff to do
>>
>>     if ( $test_results ) {
>>        $uth->execute( $the, $inf );
>>
>>        # Will this UPDATE work
>>        # while preventing the next run of the script from getting to the
>>        # currently selected data?
>>
>>     }
>> }
>>
>> And if not is it possible?  How?
>>
>>
>> Thanks,
>> Rod
>> -- 
>> _______________________________________________
>> Pdx-pm-list mailing list
>> Pdx-pm-list at pm.org
>> http://mail.pm.org/mailman/listinfo/pdx-pm-list
>