Command not allowed within multi-statement transaction

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Did a search, couldn't find anything useful :(

When trying to execute the following code,

Code:

session = sessionFactory.openSession();   
connection = session.connection();
statement = connection.prepareStatement(sql);
         
                  
if(args != null)
{
   for(int argument = 1; argument <= args.length; argument++)
      statement.setString(argument, args[argument-1]);
}

resultSet = statement.executeQuery();

I get an error SELECT INTO command not allowed within multi-statement transaction.

the sql string is actually a SP that looks like the following

Code:

  create procedure HotelAgencies @hotel_area_code varchar(10) as  
select distinct 
code, name, address, county, city, telephone, fax, online_booking_url, www, roomview, area_code 
into #hotel_agencies 
from agencies, areas_agencies 
where  
roomview='true 
 ' 
and  
code=agency_code 
 
select #hotel_agencies.* 
into #h_agencies 
from #hotel_agencies where  
area_code  like '%'+@hotel_area_code+'%' 
 
insert #h_agencies 
select #hotel_agencies.* 
from #hotel_agencies 
 where  
area_code not like '%'+@hotel_ar 
 ea_code+'%' 
 
select * from #h_agencies      

Since I dont make a call to session.beginTransaction() why is it happening? What am I missing?

Thanks...

christian · **Posted:** Fri May 14, 2004 5:55 am

What database is this?

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Oops...

Sybase 11.9.2.3
Hibernate 2.1.3

"We" must make ur life really difficult :P

christian · **Posted:** Fri May 14, 2004 6:17 am

You can't mix DDL with DML in Sybase, apparently:

http://manuals.sybase.com/onlinebooks/g ... View/34899

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

I came across that link.

Thing is, prior to using Hibernate, I used native jdbc and it worked perfect.

So maybe I'm missing smth with Hibernate :/

christian · **Posted:** Fri May 14, 2004 6:51 am

Probably, because you have been using auto commit mode (each statement gets its own transaction). This is of course not useful in real applications (you never want that transactional behavior), so Hibernate disables auto commit mode immediately.

The problem is not Hibernate, but your database: select into is not allowed if more than one SQL statement is executed per transaction. Other databases allow this (I don't think its a good idea...).

I know that your next question is "But i'm not using any transactions!". The answer is "No, you are not using the Hibernate Transaction _API_. That doesn't mean there is no underlying database transaction going on, which you, of course, have to manage/commit/rollback yourself then."

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

christian wrote:

Probably, because you have been using auto commit mode (each statement gets its own transaction). This is of course not useful in real applications (you never want that transactional behavior), so Hibernate disables auto commit mode immediately.

The problem is not Hibernate, but your database: select into is not allowed if more than one SQL statement is executed per transaction. Other databases allow this (I don't think its a good idea...).

I know that your next question is "But i'm not using any transactions!". The answer is "No, you are not using the Hibernate Transaction _API_. That doesn't mean there is no underlying database transaction going on, which you, of course, have to manage/commit/rollback yourself then."

First of all thanks for ur prompt replies. Really appreciated.

I understand what you mean by transactions (although I must admit, I'm not an expert with databases as such).

So if Hiberante disables it (due to database restrictions right?) and I go ahead and re-enable it by connection.setAutocommit(true) then I get the
SET CHAINED command not allowed within multi-statement transaction. , although according to my previous post I've set the SP's transaction mode to anymode

I just can't understand whether I need to change the implementation of the SP (nearly not possible), change some settings to the DB, or change the Java implementation

christian · **Posted:** Fri May 14, 2004 7:34 am

It is essential that you learn about database transactions, auto commit modes and related details. I actually think that concurrency control is the most important topic for a database application developer. Please be patient and use Google for your studies.

Disabling auto commit is not a "database restriction", it's not even close to that. You should never enable auto commit in an application (it's only useful for ad-hoc queries, for example, if you use a SQL query tool). Your SP executes DML _and_ DDL. This is never a good idea in an application (only ad-hoc!), and some database don't allow it. They do this by saying: "You can't execute this DDL, I see you are using a regular transaction mode!".

P.S. I'll shot the guy who "invented" the auto commit mode some day...

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

christian wrote:

It is essential that you learn about database transactions, auto commit modes and related details. I actually think that concurrency control is the most important topic for a database application developer. Please be patient and use Google for your studies.

Disabling auto commit is not a "database restriction", it's not even close to that. You should never enable auto commit in an application (it's only useful for ad-hoc queries, for example, if you use a SQL query tool). Your SP executes DML _and_ DDL. This is never a good idea in an application (only ad-hoc!), and some database don't allow it. They do this by saying: "You can't execute this DDL, I see you are using a regular transaction mode!".

P.S. I'll shot the guy who "invented" the auto commit mode some day...

Well, I'm not responsible for creating the SP. Heck, I'm not even responsible for the database configuration at all!

Ok, lets assume that I leave autocommit to "false". It seems that "SET CHAINED ON" is set automatically from the db connection that Hibernate creates therefore the SP fails. Is there a way to set it back off?

Adding the following 2 lines of code

Code:

Statement stmt = connection.createStatement();
stmt.execute("set chained off");      

I get the same error SET CHAINED command not allowed within multi-statement transaction.

The complete code for the class where the above method is the following

Code:

   private static SessionFactory sessionFactory;
   
   static
   {
      try 
      {
         System.out.println("Initializing Hibernate");         
         sessionFactory = new Configuration().configure().buildSessionFactory();
         
         System.out.println("Finished Initializing Hibernate");
      } 
      catch (HibernateException e) 
      {
         e.printStackTrace();
      }      
   }
   private static ResultSet performQuery(String sql, String[] args)
   {
      Connection connection;
      Session session = null;
      PreparedStatement statement;
      ResultSet resultSet = null;
      
      try
      {
         session = sessionFactory.openSession();
         connection = session.connection();
         
         Statement stmt = connection.createStatement();
         stmt.execute("set chained off");      
         
         statement = connection.prepareStatement(sql);
         
                  
         if(args != null)
         {
            for(int argument = 1; argument <= args.length; argument++)
               statement.setString(argument, args[argument-1]);//argument list starts at 1 but array from 0
         }
         
   
         resultSet = statement.executeQuery();
         
         session.close();
      }
      catch (Exception e)
      {
         System.out.println( e.getMessage() );
         resultSet = null;
         
         try 
         {
            session.close();
         } 
         catch (HibernateException e1) 
         {
            e1.printStackTrace();
            session.clear();
         }
      }
      
   return resultSet;
   }
   public static List find(String query)
   {
      try 
      {            
         Session session = sessionFactory.openSession();
         Transaction tx = session.beginTransaction();
         
         List result = session.find(query);
            
         tx.commit();
         session.close();
         
      return result;
      } 
      catch (HibernateException e) 
      {
         throw new RuntimeException(e.getMessage());
      }
   }

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Just to clarify. By "Heck, i'm not..." I didn't want to seem rude. :) I just wanted to indicate that I'm the Java Developer here :)

About the "SET CHAINED OFF" not working. I'm looking into the JDBC driver for Sybase jconnect in case there is smth there.

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Just to say what I did in order to solve it just in case someone stucks in the same situation.

Remove the database configuration from hibernate.cfg.xml and specify it as a datasource under the project's <Context> in the server.xml of tomcat

For example,

Code:

<Context path="[PROJECT_URL_PATH]" reloadable="true" docBase="[PROJECT_WEBAPPS_PATH]" workDir="">
        <Resource name="jdbc/[TEST]" scope="Shareable" type="javax.sql.DataSource"/>
        <ResourceParams name="jdbc/[TEST(must be same as above)]">
                <parameter>
                    <name>factory</name>
                    <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
                </parameter>

                <!-- DBCP database connection settings -->
                <parameter>
                        <name>url</name>
                        <value>[DATABASE URL]</value>
                </parameter>
                <parameter>
                        <name>driverClassName</name>
                        <value>[FULL DRIVER CLASS NAME]</value>
                </parameter>
                <parameter>
                        <name>username</name>
                        <value>[xxxx]</value>
                </parameter>
                <parameter>
                        <name>password</name>
                        <value>[xxxx]</value>
                </parameter>

                <!-- DBCP connection pooling options -->
                <parameter>
                        <name>maxWait</name>
                        <value>3000</value>
                </parameter>
                <parameter>
                        <name>maxIdle</name>
                        <value>100</value>
                </parameter>
                <parameter>
                        <name>maxActive</name>
                        <value>10</value>
                </parameter>
        </ResourceParams>
</Context>

Then inside the hibernate.cfg.xml place the following line

Code:

<property name="connection.datasource">java:comp/env/jdbc/[the name specified in he server.xml]</property>

All the above configurations are mentioned in the documentation as well.

clementsoullard · **Joined:** Mon Jul 31, 2006 3:54 am **Posts:** 4

I also experiment the same problem using HibernateUtil.class but it took time to make up solution out of this post since I was trying to change the autocommit mode using

Code:

session.connection().setAutoCommit(false);

which raise the same expcetion. (Error:226 SET CHAINED not authorized)
The problem solved when I changed autocommit in the hibernate.xml.cfg

Code:

<property name="hibernate.connection.autocommit">false</property>

Hope it will help someone someday...

mgrouch · **Joined:** Thu Aug 04, 2005 8:41 pm **Posts:** 47

This is an issue so many people encounter with hibernate and sybase.

To achieve unchained mode and autocommit = false with sybase
was tricky (and that's what is usually needed for declarative transaction management)

I was only able to do it by specifying

SERVER_INITIATED_TRANSACTIONS=false

as a parameter in JDBC URL

Quote:

SERVER_INITIATED_TRANSACTIONS

This property allows the server to control transactions. By default, the property is set to true, and jConnect allows the server to start and control transactions by using Transact-SQL® set chained on. If the property is set to false, the transactions are started and controlled by jConnect using transact sql begin tran. Sybase recommends that you allow the server to control the transactions.

Default: True

http://infocenter.sybase.com/help/index ... BIEDDJ.htm

and setting

hibernate.connection.autocommit=false.

Otherwise Sybase driver when
Connection.setAutoCommit(false)

also switches to chained mode.

Vass · **Joined:** Mon Mar 29, 2010 10:36 am **Posts:** 2

Hi,

I'm having similar problems calling third party Sybase (12.5) stored procedures from my web application.

I'm using Hibernate within the SEAM environment (JPA rather than direct to hibernate)...
[Version] Hibernate Annotations 3.3.0.GA
[Environment] Hibernate 3.2.4.sp1
[Version] Hibernate EntityManager 3.3.1.GA
JConnect version: JConnect 6

My stored proc call:
entityManager.createNativeQuery(
"exec ExtInsertAdhocCharge :accountId, :date, :amount, :reference, :adviserId, :ttypId")
.setParameter("accountId", adhocFeeTransaction.getAccountId())
.setParameter("date", adhocFeeTransaction.getEffectiveOn())
.setParameter("amount", adhocFeeTransaction.getFees().get(0).getAmount())
.setParameter("reference", adhocFeeTransaction.getReference())
.setParameter("adviserId", adhocFeeTransaction.getFees().get(0).getOutletId())
.setParameter("ttypId", ttypId)
.executeUpdate();

The problem I am having with the call is that I get the following error...

14:52:43,822 ERROR [JDBCExceptionReporter] Stored procedure 'ExtInsertAdhocCharge' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.
14:52:43,822 WARN [AccountDAO] exception during insert of adhoc fee transaction
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute native bulk manipulation query

I therefore tried adding "SET CHAINED OFF" in various ways i.e.
- As part of the actual entityManager.createNativeQuery("SET CHAINED OFF exec ExtInsertAdhocCharge :accountId, :date, :amount, :reference, :adviserId, :ttypId")
- Before the actual call to the stored proc i.e.
entityManager.createNativeQuery("SET CHAINED OFF").executeUpdate();
entityManager.createNativeQuery(
"exec ExtInsertAdhocCharge :accountId, :date, :amount, :reference, :adviserId, :ttypId")
.setParameter("accountId", adhocFeeTransaction.getAccountId())
.setParameter("date", adhocFeeTransaction.getEffectiveOn())
.setParameter("amount", adhocFeeTransaction.getFees().get(0).getAmount())
.setParameter("reference", adhocFeeTransaction.getReference())
.setParameter("adviserId", adhocFeeTransaction.getFees().get(0).getOutletId())
.setParameter("ttypId", ttypId)
.executeUpdate();

In both cases I get the following error and warnings:
Hibernate:

SET
CHAINED OFF
14:56:38,558 WARN [JDBCExceptionReporter] SQL Error: 226, SQLState: ZZZZZ
14:56:38,558 ERROR [JDBCExceptionReporter] SET CHAINED command not allowed within multi-statement transaction.

14:56:38,558 WARN [AccountDAO] exception during insert of adhoc fee transaction
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute native bulk manipulation query

Note that my stored procedure has the following: EXEC sp_procxmode 'ExtInsertAdhocCharge','unchained' when it's compiled into the database.

After further Googling I found this thread.

So, I tried setting "SERVER_INITIATED_TRANSACTIONS=false" on the JDBC URL. I tried this (without the SET CHAINED OFF) and it worked fine. However, I now get warning messages whenever the database is hit and a query run i.e. not just when my stored procedure is called:
15:09:03,719 WARN [JDBCExceptionReporter] SQL Warning: 0, SQLState: 010CP
15:09:03,719 WARN [JDBCExceptionReporter] 010CP: AutoCommit option has changed to true. All pending statements on this transaction (if any) are committed.

Should I be concerned about this?

Also, is there an alternative to using the "SERVER_INITIATED_TRANSACTIONS=false" solution?

Any thoughts/suggestions greatly appreciated.

Thanks!