Re: DBD::Sybase and auto commit
From: Matthew O. Persico (matthew.persico_at_verizon.net)
Date: 07/15/04
- Previous message: jonathan: "making oracle errors warnings or suppressing them"
- Maybe in reply to: Matthew O. Persico: "DBD::Sybase and auto commit"
- Next in thread: Michael Peppler: "Re: DBD::Sybase and auto commit"
- Reply: Michael Peppler: "Re: DBD::Sybase and auto commit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
To: <dbi-users@perl.org> Date: Wed, 14 Jul 2004 19:26:23 -0400
On Wed, 14 Jul 2004 08:18:27 +0200, Michael Peppler typed:
> On Wed, 2004-07-14 at 02:46, Matthew O. Persico wrote:
>> I have code that connects to a Sybase database and sets
>> AutoCommit to
>> 0. Subsequent statements all make that assumption and call
>> explicit
>> commits and rollbacks as needed.
>> One of those statements was a stored proc that turned out to be
>> useful
>> when accessed outside my Perl code. Since the transaction code in
>> the
>> Perl would not be accessed, we shoved a begin tran, commit and
>> rollbacks into the stored proc.
>> When the stored proc detects an error condition, my Perl code
>> gets this:
>> Failed to obtain Portia Operator:
>> DBD::Sybase::st execute failed:
>> Server message number=99999 severity=16 state=1 line=92
>> server=A2S1 procedure=am_batch_ctrl_get_oper_out
>> text=am_batch_ctrl_get_oper_out - no process found for BATCHER,
>> yet logged_on is non-zero in opf
>> Server message number=266 severity=10 state=1 line=12 server=A2S1
>> procedure=am_batch_ctrl_get_oper text=Transaction count after
>> EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing.
>> Previous count = 1, Current count = 0.
>> at /am/clrpt/lib/site_perl/5.6.1/CLRPT.pm line 1361
>> Now, the first server message is expected. The second is not. I am
>> wondering if we could get rid of the second message by setting
>> AutoCommit = 1 before the prepare for the stored proc and then
>> setting
>> it back to zero right after so as not to disturb all the other
>> statements. What is the scope of an AutoCommit setting? Is the
>> current
>> value in the $dbh accessed at execution time or is the value
>> copied to
>> 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.
> 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?
> 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
-- Matthew O. Persico
- Previous message: jonathan: "making oracle errors warnings or suppressing them"
- Maybe in reply to: Matthew O. Persico: "DBD::Sybase and auto commit"
- Next in thread: Michael Peppler: "Re: DBD::Sybase and auto commit"
- Reply: Michael Peppler: "Re: DBD::Sybase and auto commit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
