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 3:34PM

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