Callable or PreparedStatement and transactions

Question

I have a prepared statement call in java that calls a procedure that commits or rolls back a sybase transaction. Is there anyway I can check from my java calls the result of that transaction, ie. success for commit or failure for rollback? Alternatively, I want to check that the procedure completed without errors - whats the best way of doing that? Thanks

Answer 1

I think you're using the wrong approach. The client (the JAVA code in this case) should be committing or rolling back, not the sybase stored procedure. I assume here that by "calling a procedure" you mean a stored procedure that commits or rolls back.

If you use the approach I just mentioned, then you can put everything in a try/catch block and handle the commit/rollback accordingly.

try {
    PreparedStatement ps = create prepared statement;
    ps.execute();

    // nothing went wrong, commit
} catch (SqlException e) {
    // something wen't wrong, rollback
}

If by "calling a procedure" you mean just another JAVA method, then the I don't know why you would have a separate method to do a commit or rollback, you can handle it all in the try/catch block as shown above.

Answer 2

If the rollback or commit happens within the stored procedure, then the only way to know the success is to have the stored procedure return its success status. Then, Java will have access to this result.

Answer 3

A stored procedure should return some kind of status or error code.
Put that in an OUT parameter and read that from the proc.

     //Calling a stored procedure which takes in 2 parameters for addition
     /*
     --EXECUTE ADDITION 10,25,NULL
     ALTER PROCEDURE ADDITION
     @A INT
     , @B INT
     , @C INT OUT
     AS
     SELECT @C = @A + @B
     */
     CallableStatement cs2 = con.prepareCall("{call ADDITION(?,?,?)}");
     cs2.registerOutParameter(3,java.sql.Types.INTEGER);
     cs2.setInt(1,10);
     cs2.setInt(2,25);
     cs2.execute();
     int res = cs2.getInt(3);
     System.out.println(res);