"Stored procedure 'xxx' may be run only in unchained transaction mode." error when calling a procedure from a DataSource, in an EJB context

We have a Database in Sybase, which we access from a Java server.

Access to the DB was made directly through the Sybase driver, using DriverManager. It was working correctly, we were able to call our stored procedures.

Recently, we are migrating to an application server (on JBoss 5), and the calls to the database are now made through a JNDI connector, using a DataSource:

Properties ppt = new Properties();
ppt.put("java.naming.factory.initial", "org.jnp.interfaces.NamingContextFactory");
ppt.put("java.naming.factory.url.pkgs", "org.jboss.naming:org.jnp.interfaces");
ppt.put("java.naming.provider.url", "jdbc/sybase");

InitialContext ctx = new InitialContext(ppt);
DataSource ds = (DataSource) ctx.lookup(AConfig.getInstance().getDatasourceJndiName());

Connection conn = ds.getConnection();

(The DataSource is configured using the basic settings, from the JBoss example)

However, in this setting, several procedures are failing, with this error:

"Stored procedure '**' may be run only in unchained transaction mode."

or this kind, for other cases (with the failing command changing):

TRUNCATE TABLE command not allowed within multi-statement transaction

From what I found on Internet, it looks like something in the JBoss or the connector is open开发者_运维知识库ing a transaction itself, causing these errors. As such, the diverse solutions I could find for these particular problems are too localized, and it seems like a bigger issue.

Is there a way to prevent this behaviour (assuming that this is the actual problem)?

My knowledge in this particular field is quite thin, this is new to me. As such, there are probably important details missing to this description. Please indicate me how I can improve this question, what details I can add, if necessary.

Apparently in Sybase stored procedures are created to run either in chained or unchained mode.

If you are getting this error it means your SP was created as Unchained. This Java line conn.setAutoCommit(false); is translated to "set chained on".

You can run this Sybase SP to list the transaction mode of all your SP's in your DB:

sp_procxmode

So you need to invoke your SP on the unchained mode and explicitly use, create transaction, commit transaction and rollback transaction.

For instance:

insert into publishers 
    values ("9906", null, null, null)
begin transaction
delete from publishers where pub_id = "9906"
rollback transaction

Refer this link.

That is not correct.

Certainly look at the bigger picture. But there is an even bigger picture still.

Application server or not, is not the problem. Settings in JBoss vs the previous app (Java) server is the problem. Your coders have correctly ensured that their stored procs execute true transactions, and they are protected from subversion by outside entities (any calling stored proc or app server). If they have done that, then those sprocs will run from any app server.

"Refactoring" is for the MS world, it is not required at all in the Sybase or Relational world. If you change the sprocs to remove the tight transaction control, the business will suffer: loss of data integrity; loss of referential integrity; lost updates; duplicate transactions; etc. If you are going to subvert the sprocs, or remove transaction control (as opposed to "refactor"), be warned that the consequence is enormous.

Clearly, JBoss is defaulting to either AUTOCOMMIT or SET CHAINED ON (because many people do not write true transactions, and these are the defaults for MS SQL ), and your previous Java (app) server did not do that.

Second, ODBC is very slow compared with a direct connection, so if you have not felt it yet, be aware that you will, very soon. Datasources aren't "implemented", they are merely configured (takes a few minutes). They use ODBC or JDBC. It is a FAT layer between, and places a small buffer between, the program and the database, and of course gives up all the control that you had and enjoyed before, when you had the native connection. I have seen it as much as twelve times slower.

Third, didn't anyone check JBoss out (a) before choosing it, for Sybase native connectivity (as opposed to generic, MS oriented), (b) during implementation and (c) during testing ?

If your connections are the problem, certainly, then just deal with the connection problem, and implement connection pooling (Java and Sybase have libraries for that), rather than reducing the quality and performance of your app, as well as the Consistency (that's the C in ACID) of the database.

EAServer (Sybase) and WebShpere (IBM) have no such problems; they perform connection pooling; and they use native connection to ASE (no ODBC or JDBC required).

This might help: http://forum.springsource.org/showthread.php?t=49398, 'Holly' appears to have solved the problem