sybperl-l Archive

From: Mark Haviland <mhaviland at harmonic dot com>
Subject: Re: DBD::Sybase error...
Date: Sep 6 2000 8:51PM

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.

thanks

-Mark Haviland


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