|
|
sybperl-l Archive
Up Prev Next
From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: DBD::Sybase error...
Date: Sep 6 2000 9:02PM
Mark Haviland writes:
> Yep, it looks like it was the AutoCommit flag (which I set to 'off' when
> creating the handle). Turning it 'on' for the proc that I was calling fixed
> it.
Alternatively, setting the syb_chained_txn attribute to TRUE will also
solve the problem, as long as you don't have any BEGIN TRAN statements
in any of the stored procs that you call...
Michael
> Michael Peppler wrote:
>
> > Mark Haviland writes:
> > >
> > > I seem to be having problems when executing a proc using DBD::Sybase.
> > > The error message I get is:
> > >
> > > [Wed Sep 6 10:10:18 2000] [error] DBD::Sybase::st execute failed:
> > > Server message number=226 severity=16 state=1 line=234 server=ASE12
> > > procedure=list_configuration2 text=SELECT INTO command not allowed
> > > within multi-statement transaction.
> > > Server message number=208 severity=16 state=6 line=240 server=ASE12
> > > procedure=list_configuration2 text=#group_keys not found. Specify
> > > owner.objectname or use sp_help to check whether the object exists
> > > (sp_help may produce lots of output).
> > >
> > >
> > > The strange part is that I can exec the same proc from sqsh without any
> > > problems. I haven't tried to execute the proc using Sybase::CTlib, but
> > > does anybody out there know if this problem is related to DBD::Sybase
> > > (bug ??) or just something incorrect in the stored proc ?
> >
> > Check the value of the AutoCommit flag.
> >
> > >From the DBD::Sybase manual:
> >
> > AutoCommit, Transactions and Transact-SQL
> > When $h->{AutoCommit} is off all data modification SQL
> > statements that you issue (insert/update/delete) will only
> > take effect if you call $dbh->commit.
> >
> > DBD::Sybase implements this via two distinct methods,
> > depending on the setting of the $h->{syb_chained_txn}
> > attribute and the version of the server that is being
> > accessed.
> >
> > If $h->{syb_chained_txn} is off, then the DBD::Sybase
> > driver will send a BEGIN TRAN before the first
> > $dbh->prepare(), and after each call to $dbh->commit() or
> > $dbh->rollback(). This works fine, but will cause any SQL
> > that contains any CREATE TABLE (or other DDL) statements
> > to fail. These CREATE TABLE statements can be burried in a
> > stored procedure somewhere (for example, sp_helprotect
> > creates two temp tables when it is run). You can get
> > around this limit by setting the ddl in tran option (at
> > the database level, via sp_dboption.) You should be aware
> > that this can have serious effects on performance as this
> > causes locks to be held on certain system tables for the
> > duration of the transaction.
> >
> > If $h->{syb_chained_txn} is on, then DBD::Sybase sets the
> > CHAINED option, which tells Sybase not to commit anything
> > automatically. Again, you will need to call
> > $dbh->commit() to make any changes to the data permanent.
> > In this case Sybase will not let you issue BEGIN TRAN
> > statements in the SQL code that is executed, so if you
> > need to execute stored procedures that have BEGIN TRAN
> > statements in them you must use $h->{syb_chained_txn} = 0,
> > or $h->{AutoCommit} = 1.
> >
> > Michael
> > --
> > Michael Peppler -||- Data Migrations Inc.
> > mpeppler@peppler.org -||- http://www.mbay.net/~mpeppler
> > Int. Sybase User Group -||- http://www.isug.com
> > Sybase on Linux mailing list: ase-linux-list@isug.com
>
--
Michael Peppler -||- Data Migrations Inc.
mpeppler@peppler.org -||- http://www.mbay.net/~mpeppler
Int. Sybase User Group -||- http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com
|
