Stupid Oracle question

[Bruce Johnson]

If $dbh is my database handle, to roll back the current transaction I  
do: $dbh-rollback(); right?


The DBD::Oracle docs don't explicitly say


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: Stupid Oracle question

[John Scoles]

Bruce Johnson wrote:

Depends if you have |AutoCommit| on or not and if you DB and DBD friver 
can do a rollback.


cheers
John Scoles
If $dbh is my database handle, to roll back the current transaction I 
do: $dbh-rollback(); right?


The DBD::Oracle docs don't explicitly say

Re: Stupid Oracle question

[Michael Nhan]

Hi,

Yes.  Its in the DBI docs.

Michael

On Thu, 6 May 2010, Bruce Johnson wrote:


Date: Thu, 6 May 2010 11:30:59 -0700
From: Bruce Johnson john...@pharmacy.arizona.edu
To: DBI Users Mailing List dbi-users@perl.org
Subject: Stupid Oracle question

If $dbh is my database handle, to roll back the current transaction I do: 
$dbh-rollback(); right?


The DBD::Oracle docs don't explicitly say





--
---//---
Time flies like the wind. Fruit flies like bananas.
--- Groucho Marx

Either write something worth reading or do something worth writing.
--- Benjamin Franklin

A meeting is an event at which the minutes are kept and the hours are lost

Re: Stupid Oracle question

[Bruce Johnson]

On May 6, 2010, at 11:34 AM, John Scoles wrote:


Bruce Johnson wrote:

Depends if you have |AutoCommit| on or not and if you DB and DBD  
friver can do a rollback.



I've explicitly turned autocommit off, so I can roll back transactions  
if an error occurs.


In the old Oraperl syntax it's:

if ($ora_errstr){
print $ora_errstr occurred with $statement;
ora_rollback($dbh);
}
ora_commit($dbh);

I'm redoing some old scripts to use DBI instead, so I'm guessing the  
equivalent DBI code is:


if ($ora_errstr){
print $ora_errstr occurred with $statement;
$dbh-rollback();
}
$dbh-commit();

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

RE: Stupid Oracle question

[Martin Gainty]

that should work.. here is a snippet from 
DBD-Oracle-1.24\Oracle.ex\commit.pl which creates the connection handle and 
then rolls back with it

# Connect to database
my $dbh = DBI-connect( dbi:Oracle:$inst, $user, $pass,
{ AutoCommit = 0, RaiseError = 1, PrintError = 0 } )
or die $DBI::errstr;

print rollback (, $dbh-rollback, )\n;
Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung
 
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.






 From: john...@pharmacy.arizona.edu
 To: dbi-users@perl.org
 Subject: Stupid Oracle question
 Date: Thu, 6 May 2010 11:30:59 -0700
 
 If $dbh is my database handle, to roll back the current transaction I  
 do: $dbh-rollback(); right?
 
 The DBD::Oracle docs don't explicitly say
 
 
 -- 
 Bruce Johnson
 University of Arizona
 College of Pharmacy
 Information Technology Group
 
 Institutions do not have opinions, merely customs
 
 
  
_
Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1

Re: Stupid Oracle question

[Martin J. Evans]

Bruce Johnson wrote:
 If $dbh is my database handle, to roll back the current transaction I
 do: $dbh-rollback(); right?
 
 The DBD::Oracle docs don't explicitly say
 
 

You want to look at the DBI pod. The begin_work method starts a txn and
the commit and rollback methods commit or rollback a txn. In general
DBDs don't repeat all documentation from DBI but list the exceptions or
deviations from the DBI spec. You might also want to look at the
AutoCommit attribute on a connection handle and also specifiable on the
connect method.

So to answer your question, yes the rollback method rolls back your txn
but you need to call begin_work first unless you disabled AutoCommit in
which case you will need to commit everything you do (and that can
include even selects in some DBDs).

Martin

Re: Stupid Oracle question

[Martin J. Evans]

Bruce Johnson wrote:
 
 On May 6, 2010, at 11:34 AM, John Scoles wrote:
 
 Bruce Johnson wrote:

 Depends if you have |AutoCommit| on or not and if you DB and DBD
 friver can do a rollback.
 
 
 I've explicitly turned autocommit off, so I can roll back transactions
 if an error occurs.

You do not /need/ to do that. You can leave AutoCommit turned on and
when you want to start a txn you issue a begin_work then a
commit/rollback. With some DBDs if you disable AutoCommit you'd need to
commit everything including selects which can be a bit annoying. So in
general with DBD::Oracle you can:

connect # AutoCommit is on by default
# set RaiseError = 1
eval {
  $dbh-begin_work
  # do something in the txn
};
if ($@) {
  $dbh-rollback or die Failed to rollback - perhaps add DBI-err here;
} else {
  $dbh-commit or die Failed to commit;
}

Please note - the above is simplified - I don't personally do that and
generally use Try::Tiny to avoid issues with $...@.

 In the old Oraperl syntax it's:
 
 if ($ora_errstr){
 print $ora_errstr occurred with $statement;
 ora_rollback($dbh);
 }
 ora_commit($dbh);
 
 I'm redoing some old scripts to use DBI instead, so I'm guessing the
 equivalent DBI code is:
 
 if ($ora_errstr){
 print $ora_errstr occurred with $statement;
 $dbh-rollback();
 }
 $dbh-commit();
 

Didn't use Oraperl so difficult to say - my example should work though
without having to examine any errors so long as RaiseError is enabled on
the connection handle.

Martin