Re: DBD::Sybase and auto commit
From: Michael Peppler (mpeppler_at_peppler.org)
Date: 07/14/04
- Next message: Michael Peppler: "Re: Re: Truncation Issues with SQL Server Insert Statement"
- Previous message: Janet Goldstein: "RE: no DBD:MSSQL?"
- In reply to: Matthew O. Persico: "DBD::Sybase and auto commit"
- Next in thread: Matthew O. Persico: "Re: DBD::Sybase and auto commit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
To: dbi-users@perl.org Date: Wed, 14 Jul 2004 08:18:27 +0200
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.
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.
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
- Next message: Michael Peppler: "Re: Re: Truncation Issues with SQL Server Insert Statement"
- Previous message: Janet Goldstein: "RE: no DBD:MSSQL?"
- In reply to: Matthew O. Persico: "DBD::Sybase and auto commit"
- Next in thread: Matthew O. Persico: "Re: DBD::Sybase and auto commit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
