|
|
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
|
