Re: DBD::Sybase and auto commit

From: Michael Peppler (mpeppler_at_peppler.org)
Date: 07/15/04 

To: dbi-users@perl.org
Date: Thu, 15 Jul 2004 08:32:41 +0200

On Thu, 2004-07-15 at 01:26, Matthew O. Persico wrote:
> On Wed, 14 Jul 2004 08:18:27 +0200, Michael Peppler typed:
> > On Wed, 2004-07-14 at 02:46, Matthew O. Persico wrote:

> >> the $sth at prepare time and used as such at execution time,
> >> regardless of what the $dbh value is set to?
> > The first thing you can do in your stored proc is to use a
> > savepoint -
> > that should fix the immediate problem.
> > Something like:
> > create proc foo
> > ..
> > begin tran
> > save tran foo_tran
> > ..
> > if @@error
> > begin
> > rollback tran foo_tran
> > commit tran
> > return some_error_value
> > end
> > commit tran foo_tran
> > would probably work, and would be sure to rollback only the code in
> > the
> > proc, and not the rest of the transaction.
>
> Hmm. I don't quite understand it, but I'll read the docs and then try it.

It's a standard way of handling nested transactions, so that a
commit/rollback only goes to the previous savepoint, not to the
outermost transaction.

> > That being said, DBD::Sybase supports two AutoCommit "off" modes.
> > One
> > using explicit transactions, and the other using "chained" mode. In
> > the
> > "chained" mode we let the server start the implicit transaction as
> > needed, and that's the mode that *should* be the default for
> > DBD::Sybase
> > (but hasn't been up to now, for various reasons). I will probably
> > change
> > that in an upcoming release.
>
> Wow, that's going to require a boat-load of testing, isn't it? Will
> there be a way to change the default at installation time back to the
> way it was?

Yes, although I don't think that it should make any *real* difference.

Michael 

-- 
Michael Peppler                              Data Migrations, Inc.
mpeppler@peppler.org                       http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html

Relevant Pages

  • Re: Global Temp Table & Multiple Users
    ... what is happening is this a dynamic SQL ... The inner proc generates a portion of the SQL, ... >within a transaction, and at the start of the ... I want to queue up the execution if multiple ...
    (microsoft.public.sqlserver.server)
  • RE: SQL Port: using of the ROLLBACK
    ... As you stated the adapter is trying to Commit ... so you could start your own transaction in the Stored ... transaction before starting a new one in the proc. ... property on your Receive Location or Send Port to disable automatic ...
    (microsoft.public.biztalk.general)
  • Re: Running multiple sql statements from one Stored procedure
    ... >but would like to combind into one stored procedure, not sure if this can be ... CREATE PROC Combined ... Code from Proc3, ... to enclose everything in a transaction, so you won't be left with partial ...
    (microsoft.public.sqlserver.server)
  • Re: coding for no blocking or deadlocks.
    ... you should be able to write re-usable code and the ... tables in a transaction without causeing blocking. ... > proc 1 ... > This causes blocking, I need to do these things without the blocking. ...
    (microsoft.public.sqlserver.programming)
  • Re: get some records and then call a stored proc once for each??
    ... > "Keith Kratochvil" wrote in message ... > create proc foo ... > exec foo 3 ... >> I have a stored proc that gets some params. ...
    (microsoft.public.sqlserver.programming)