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

Question

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

Answer 1

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

Answer 2

The problem was (and actually, still is) on a higher level.

A big part of our business code is made withing stored procedures, and transactions are handled at this level (it wasn't designed to work with an application server in the first place).

As such, the error messages are only consequences, of an incompatibility between the way these stored procedures work, and the connection pooling possibilities given by JBoss. It is likely that most people experiencing this kind of messages should take a look at the bigger picture, as the problem lies more in this incompatibility.

For now, we will default to the previous way to handle connection, with our existing connection pool, since implementing a datasource will require a deeper refactoring in the existing code.

Answer 3

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