PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

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