AppletTalk.com

Kathy · Guest

I am using a Callable Statement to run a stored procedure in the database.
The stored procedure takes an input parameter and passes an output parameter
of type int. The output parameter is used to perform a test to see if the
stored procedure worked properly, or passed some logic tests on the database
side. If the output parameter is 0, then I commit, otherwise I must perform
a rollback.

Now, the problem is, everything looks like it's running perfectly. I get no
SQL exceptions, my output parameter is 0, so I call Connection.commit() and
Connection.setAutoCommit(true). However, the database doesn't update, it's
as if the commit statement never gets run, but no exception is thrown.
Note: If I run this in autocommit mode it runs fine, but I am not able to
perform an explicit rollback when I get an incorrect output parameter, so my
data will change when it should NOT change.

Here is the code:

con.setAutoCommit(false);
CallableStatement cs = con.prepareCall("{call mysp (?,
?)}");
cs.registerOutParameter(2, java.sql.Types.INTEGER);
cs.setInt(1, 12);
cs.execute();
int myresult=cs.getInt(2);
System.out.println("Update done for: 12");
System.out.println("Result=" + myresult);
if (myresult!=0) {
System.out.println("Update failed!");
try {con.rollback();
con.setAutoCommit(true);
} catch (SQLException xcp)
{System.out.println("Exception: " + xcp.getMessage();}
} else {con.commit();
con.setAutoCommit(true);
}

Output when I test:
Update done for: 12
Result=0

The entire thing is surrounded with a SQLException handler etc, but this is
the core of the code which performs the function.

The only odd thing about the stored procedure, is that it uses a temp table
to do some data manipulation before it updates the data that I am affecting.

Any suggestions would be appreciated.

Thanks!
Kathy

Joe Weinstein · Guest

Kathy wrote:

Tim Jowers · Guest

Joe Weinstein <joeNOSPAM (AT) bea (DOT) com> wrote