Hi,

I am using Hibernate 2.1.7, I had a problem with Sybase database(12.5)
ERROR JDBCExceptionReporter:58 - SET CHAINED command not allowed within multi-statement transaction

I found the problem is solved on JIRA:
HB-1415 : Sybase 12.5 Fails in batch Hibernate 2.1.7

Apparently Gavin has fixed this problem. Fix versions are reported as
2.1.9, 3.0 beta 4

However, the latest version for hibernate2 is 2.1.8 Production.
Is hibernate team going to post 2.1.9 ?

Thanks,
-Kourosh

if you cant wait 2.1.9 release then use the hibernate2 cvs version - our cvs versions are very stable.

Just wondering if it is hibernate or sybase!!!!

We have the same problem, we are using SSLB->Spring->Hibernate3-jConnect5.5->Sybase 12.5?

Any help would be appreciated.

Regards

Khurram

its a sybase issue - look up the execption via google and read about it.

And just use transactions and it should work.

hi max

many thanks for ur quick reply, let me add some more details

We have a MDB which runs a Global transaction(XA), it calls an EJB(Stateless Session Bean), which uses Spring + Hibernate to insert a row in sybase table using jCOnnect jdbc, Sybase has XA switched on. After the insert MDB posts the message to MQ.

We use Websphere 5.1, Sybase 12.5, Spring 1.2.1, Hibernate 3.0.5.

If we don't use transactions all work fine but obviously if after the insert an error there is no rollback. So we introduced declarative transaction, pretty standard stuff, using Spring's org.springframework.orm.hibernate3.HibernateTransactionManager, and then using PROPAGATION_REQUIRED.

This produces the above error (SET CHAINED).

When u say "just use transactions and it should work" can u please explain a bit more on what changes we need to make to our tranx config.

Many thanks


Khurram.

normally this is a problem because connections are being used outside a transaction - but again, google for it - its not hibernate specific, but sybase connection handling specific.

I too am having the problem.

Yes ... this is a Sybase issue in a sense.

The reason is that, when hibernate ... or in this case, JConnect JDBC goes off to find the columns of the table, it calls the stored procedure sp_jdbc_columns.

The problem here is, that procedure creates a temporary table in tempdb, ... and the JDBC connection is set to autocommit=false by default ... and you cannot have "create table" commands while in the "middle" of a database transaction.

The only way to call these metadata stored procedures is to have autocommit set to true.

Is there anyway we can have this set to true only when calling the stored procs ?? ... and then return to autocommit = false ??

Anyone else have a better idea / solution ??

[quote="jsalvo"]I too am having the problem.
[/quote]

I should add that I am seeing this problem from HIbernateTools ( Eclipse plug-in ).

guys - go read about this on google! There is an option to allow using DDL in a transaction, you just need to enable it in sybase.

Here is One way to solve the problem.
protected Session getSession()
{
logger.debug("getSession -- entry");
Session session = null;
try
{
session = sessionFactory.openSession();
Connection conn = session.connection();
PreparedStatement pqs = conn.prepareStatement .nativeSQL("SET CHAINED OFF"));
pqs.execute();
}
catch( SQLException ex)
{
logger.error(ex);
}
return session;
}

Better still, create your own UserDataSource object and when you supply a connection (through getConnection()) do something like this.

Cheers,
David