Why does TRY CATCH successfully update rows on SSMS but not when run from Tomcat application?

gordon613 0
Viewed 30

I have a TSQL procedure, on SQL for Azure. Here is a minimal reproducible example.

CREATE PROCEDURE MyPROC
AS
BEGIN
    BEGIN TRY
        SELECT 1/0
    END TRY
    BEGIN CATCH
        UPDATE error_log SET error_desc='test'
    END CATCH
END

When run directly from SSMS, then error_log is successfully updated.

When run from my Tomcat application (using the Tomcat user) then the procedure is executed but error_log is not updated.

Tomcat executes the procedure using the following Java code

    DBConnection dbConn  = DBConnection.getInstance();
    Connection conn = null;
    try {
        conn = dbConn.getConnection();
        conn.setAutoCommit(false);
        CallableStatement stmt = conn.prepareCall("{CALL MyPROC}");
        stmt.execute();
        result = stmt.getInt(fields.size());
        if (result == DBProcedures.RESULT_FAILED) { // error
            conn.rollback();
        } else { // ok
            conn.commit();
        }
    } catch (SQLException e) {
        result = DBProcedures.RESULT_FAILED;
        try {
            conn.rollback();
        } catch (SQLException e1) {
            System.out.println("Exception on " + this.toString() + " " + e.toString());
        }
        System.out.println("Exception on " + this.toString() + " " + e.toString());
    } finally {
        try {
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            System.out.println("Exception on " + this.toString() + " " + e.toString());
        }
        dbConn.returnConnection(conn);
    }

I have checked that the Tomcat user has permissions on the error_log table.

What am I missing? I understand that not all errors are caught by TRY CATCH and I understand that sometimes the transaction cannot execute any Transact-SQL statements that would generate a write operation. However my example does not seem to fall under either of these two categories. Further it does work under SSMS.

error-handling java sql-server
1 Answers

This is caused by the fact that auto_commit is set to off, which means that the driver executes the command SET IMPLICIT_TRANSACTIONS ON;

You can see this in action in this fiddle.

To get around it, you can explicitly rollback the original transaction first. You should also ideally use XACT_ABORT to prevent hanging transactions, and use THROW; to rethrow the original error.

CREATE PROCEDURE MyPROC
AS
BEGIN
    SET XACT_ABORT ON;

    BEGIN TRY
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK;

        UPDATE error_log SET error_desc = 'test';

        THROW;  -- only if you want the error to propagate back to Tomcat
    END CATCH;
END;

To be brutally honest, I do not recommend using BEGIN CATCH to log errors, as not all errors are catchable. Instead, use the SQL Server error log, or an XEvent session, to log errors.

Charlieface0
Related