Error on calling stored procedure

[Bill Pearce]

Hi folks,

I'm getting a SQLException when running a stored procedure. The error description looks like it's maybe coming from the database, but it's rather vague (Not in a transaction) and I thought I would check to make sure there was nothing I was missing on my end.

The code for the stored procedure private class:

private class UpdateOwnerTeamProcedure extends StoredProcedure 
    {
        public UpdateOwnerTeamProcedure(JdbcTemplate template) {
            super(template, "updt_pref");
            setFunction(false);
            declareParameter(new SqlParameter("ownerNumber", Types.INTEGER)); 
            declareParameter(new SqlParameter("teamId", Types.INTEGER)); 
            compile();
        }

        public void execute(int ownerNumber, int teamId) 
        {
            HashMap input = new HashMap();
            input.put("ownerNumber", new Integer(ownerNumber));
            input.put("teamId", new Integer(teamId));
            execute(input);
        }

    }

I call with the following code:

UpdateOwnerProcedure proc = new UpdateOwnerProcedure(getJdbcTemplate());
        proc.execute(ownerNumber, teamId);

Running a unit test results in the following error:

org.springframework.jdbc.UncategorizedSQLException: (executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementCreatorImpl: sql=[{call updt_pref(?, ?)}]: params=[{ownerNumber=1, teamId=11}]]): encountered SQLException [Not in transaction.]; nested exception is java.sql.SQLException: Not in transaction.

Any suggestions appreciated. I tried using the CallableStatementCreator, but ran into different issues with that.

[Rod Johnson]

Bill

Are you in an app server using JTA? It's possible that the SQLException is coming from the app server as a result of a JTA misconfiguration, rather than from the RDBMS. If this is the case, can you try this outside the app server with a non JTA DataSource?

Rgds
Rod

[ArtVandelay]

Thanks Rod. I'm actually getting this error when running the JUnit test case from within the IDE. I have configured a DBCP BasicDatasource within Spring, injected into the DAO. I'll try again Monday using the CallableStatementCreator, and maybe get some DBA input. Just wanted to check (since all the other DAOs using JdbcTemplate work fine and this is the fist attempt at using the StoredProcedure) that I had not missed something.

[Bill Pearce]

Still having an issue with this stored procedure. I can call the stored procedure fine directly on the database using a database tool, providing the same SQL and parameters. I tried using the CallableStatementCreator and get the same error. The stack trace I get is:

org.springframework.jdbc.UncategorizedSQLException: (executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementCreatorImpl: sql=[{call updt_pref(?, ?)}]: params=[{ownerNumber=191, teamId=1123}]]): encountered SQLException [Not in transaction.]; nested exception is java.sql.SQLException: Not in transaction.
java.sql.SQLException: Not in transaction.
	at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:348)
	at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3000)
	at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3310)
	at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2263)
	at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2183)
	at com.informix.jdbc.IfxSqli.sendStatementExecute(IfxSqli.java:1149)
	at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2109)
	at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2046)
	at com.informix.jdbc.IfxResultSet.executeExecute(IfxResultSet.java:397)
	at com.informix.jdbc.IfxStatement.executeImpl(IfxStatement.java:897)
	at com.informix.jdbc.IfxPreparedStatement.execute(IfxPreparedStatement.java:299)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
	at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:655)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:630)
	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:653)
	at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:100)
	at com.xxx.AceTeamDAO$UpdateOwnerTeamProcedure.execute(MyDAO.java:45)
	at com.xxx.MyDAO.pushPrefs(MyDAO.java:80)
	at

To the console:

(support.SQLErrorCodeSQLExceptionTranslator 279 ) Unable to translate SQLException with errorCode '-255', will now try the fallback translator

I looked up Informix error 255 and find:

The database server cannot execute this COMMIT WORK or ROLLBACK WORK statement because no BEGIN WORK was executed to start a transaction. Because no transaction was started, you cannot end one. Any database modifications that were made are now permanent; they cannot be rolled back but do not need to be committed. Review the sequence of SQL statements to see where the transaction should have started.

This error can occur when you open a cursor for update and have not started a transaction yet.

As I say, I can call the SP from my database tool with no error. Is there anything suspect about the code below? I have not configured anything differently with regard to this DAO from all others, though this is the first SP I have tried to call with Spring.

[trisberg]

How about running this from plain JDBC? Try executing

{call updt_pref(?, ?)}

from a CallableStatement and see what happens.

[Bill Pearce]

Meant to try that this morning as well. That gives the same error. I checked with our DBA, and there appears to be a bug in the stored procedure. It looks as though it is actually calling COMMIT WORK before calling BEGIN. They are reviewing now. Thanks for the help and sorry for the bother.