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

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.

CodePudding user response：

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.