SET CHAINED OFF command

SET CHAINED OFF command

SET CHAINED OFF command is one of the big annoying error while working with hibernate. If it’s your first time working with hibernate and you get this error then will take long time to understand and resolve it because Google search does not give any good solution how to reslove this issue but some document suggest they added jars, changed configuration etc… to resolve this issue. Here you will see differnet way to resovle this issue and after reading this solution you will feel like so easy.

Exception details:

Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Stored procedure ‘OTC.dbo.testStoreProcedure’ may be run only in unchained transaction mode. The ‘SET CHAINED OFF’ command will cause the current session to use unchained transaction mode.

Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Stored procedure 'OTC.dbo.testStoreProcedure' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.

	at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4015) ~[jconn4-7.07.jar:JDK 1.6/jdbcmain/Fri Aug  2 07:00:53 PDT 2013]
	at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3105) ~[jconn4-7.07.jar:JDK 1.6/jdbcmain/Fri Aug  2 07:00:53 PDT 2013]
	at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:79) ~[jconn4-7.07.jar:JDK 1.6/jdbcmain/Fri Aug  2 07:00:53 PDT 2013]
	at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:290) ~[jconn4-7.07.jar:JDK 1.6/jdbcmain/Fri Aug  2 07:00:53 PDT 2013]
	at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:272) ~[jconn4-7.07.jar:JDK 1.6/jdbcmain/Fri Aug  2 07:00:53 PDT 2013]
	at com.sybase.jdbc4.jdbc.SybStatement.queryLoop(SybStatement.java:2409) ~[jconn4-7.07.jar:JDK 1.6/jdbcmain/Fri Aug  2 07:00:53 PDT 2013]
	at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(SybStatement.java:2395) ~[jconn4-7.07.jar:JDK 1.6/jdbcmain/Fri Aug  2 07:00:53 PDT 2013]
	at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:258) ~[jconn4-7.07.jar:JDK 1.6/jdbcmain/Fri Aug  2 07:00:53 PDT 2013]
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ~[hibernate-core-4.3.9.Final.jar:4.3.9.Final]
	... 28 more

Why this exception happens: By default hibernate and Sybase database keep autocommit false so any statement we do execute against database runs in chained mode. There is not way in hibernate with new API where you could set autocommit = false.

• Solution: By defautl all hibenate statement runs in transactional mode. So if you are tyring to run store procdure which only reads data from database that also runs in transaction mode. So to resolve this issue in Java code please add below on top of the methoed:

@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)

and of course on top of the class you will have use: @Transactional

• Sample class:

@Repository
@Transactional
public class TestDaoImpl implements TestDao {
	
	@Autowired
	private SessionFactory sessionFactory;
	
	@Override
	@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
	public List<Object> testStoreProcedure() {

		Session session = sessionFactory.getCurrentSession();
		Integer maxSize = 0;

		Query query = session.createSQLQuery("dbo.testStoreProcedure");
		if (maxSize > 0) {
			query.setMaxResults(maxSize);
		} else {
			query.setMaxResults("500");
		}
		
		List<Object> list = query.list();
		 
		

		return list;
	
	}

}

This will fix issue immediatlely as we are saying we are running this class in trasactional mode and my method is transactional but read only.

• Solution 2: If you don’t want to do above setting you could also use below prefix in your store procedure:

SET CHAINED OFF execute storeprocedurename

If your store procedure takes parameter you could use like this:

SET CHAINED OFF execute storeprocedurename :parameter1

Solution 3: How to fix this on Sybase datbase:

As Sybase database also runs all store procedure in auto commit false mode and to change this behaviour to particular store procedure you will have to excute on Sybase database.

EXEC sp_procxmode ‘dbo.testStoreProcedure’,’anymode’

As you see above Sybase provides sp_procxmode proecude to change option from chained to anymode.

Other mode you could set as below this is just for knowledge if you need it:

• EXEC sp_procxmode ‘dbo.testStoreProcedure’,’unchained’
• EXEC sp_procxmode ‘dbo.testStoreProcedure’,’chained’

For more information please visit Hibernate tuotrial here