RAISERROR - differences in SQLException behaviour in Java Client

Viewed 414

I'm seeing differences in behaviour in how Java handles SQL Server errors thrown by RAISERROR, depending upon what the actual error is, in that:

  • Unique Constraint Violation: The SQL Exception is trapped and handled by the Java Catch block
  • Divide by Zero: The SQL Exception is not trapped

To demonstrate:

1. Stored Procedure: uspTestRE

ALTER PROCEDURE demo.uspTestRE (
  @inKey    VARCHAR(5),    --Primary Key value in TestRE table
  @inDBZ    VARCHAR(1)     --Y/N flag to trigger Divide By Zero error
)
AS
BEGIN

  SET XACT_ABORT, NOCOUNT ON;

  BEGIN TRY
    BEGIN TRANSACTION

      --Insert record into TestRE table
      INSERT INTO TestRE (tREKey) VALUES (@inKey);

      --Trigger DBZ error
      IF @inDBZ = 'Y'
       BEGIN
         SELECT 1/0;
       END;

    --Transaction is in a state to be committed
    IF (XACT_STATE())=1
      BEGIN
        COMMIT TRANSACTION;
      END;
  END TRY

  BEGIN CATCH
    DECLARE
      @errorMessage  NVARCHAR(MAX) = ERROR_MESSAGE(),
      @errorState    INT           = ERROR_STATE(),
      @errorSeverity INT           = ERROR_SEVERITY();

    --Transaction is uncommitable so rollback
    IF (XACT_STATE()) = -1
      BEGIN
        ROLLBACK TRANSACTION;
      END;

    RAISERROR(@errorMessage,@errorSeverity,@errorState);

    RETURN 23;
  END CATCH

END;

When executing the SP from SQLCMD, it generates the following error messages:

Test 1: Key Violation

Msg 50000, Level 14, State 1, Server WW-HV5FQV2, Procedure demo.uspTestRE, Line 44
Violation of PRIMARY KEY constraint 'PK__TestRE__0FEE01A3EEF4E2AE'. Cannot insert duplicate key in object 'dbo.TestRE'. The duplicate key value is (UKV).

Test 2: Divide by Zero

Msg 50000, Level 16, State 1, Server WW-HV5FQV2, Procedure demo.uspTestRE, Line 42
Divide by zero error encountered.

i.e. expected behaviour for both.

However, my Java program doesn't give the same behaviour.

2. Java Program: uspTestRE.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;

public class uspTestRE {

    // Connect to DEMO_INST database as svcDemoInst Login
    public static void main(String[] args) {
        String connectionUrl =
                "jdbc:sqlserver://localhost:1433;"
                + "database=DEMO;"
                + "user=svcDemoLogin;"
                + "password=Demo;"
                + "trustServerCertificate=true;"
                + "loginTimeout=30;";

        ResultSet resultSet = null;
        CallableStatement cstmt = null;

        try {cstmt = DriverManager.getConnection(connectionUrl).prepareCall("{? = call demo.uspTestRE(?,?)}");
             int i = 0;
             cstmt.registerOutParameter(++i,java.sql.Types.INTEGER);

             cstmt.setString(++i,args[0]);
             cstmt.setString(++i,args[1]);
             cstmt.execute();

             int returnCode = cstmt.getInt(1);
             System.out.println("Success: Stored Procedure Return Status: " + returnCode);
        }
        catch (SQLException e) {
              System.out.println("Error Code: " + e.getErrorCode() +", Error Message: "+e.getMessage());
              // e.printStackTrace();
        }
    }
}

When I run the same test cases here, I get the following:

Test 1: Key Violation (Behaves as Expected)

C:\>java uspTestRE UKV N
Error Code: 50000, Error Message: Violation of PRIMARY KEY constraint 'PK__TestRE__0FEE01A3EEF4E2AE'. Cannot insert duplicate key in object 'dbo.TestRE'. The duplicate key value is (UKV).

Test 2: Divide by Zero (Does not get trapped)

C:\>java uspTestRE DBV Y
Success: Stored Procedure Return Status: 23

Can anyone explain to me why the Divide By Zero error - which is being trapped in SQL Server and handled in the SP Catch block - is not being trapped by the Java code?

The only differences I can see between the 2 errors is that the Divide by Zero error is Severity 16.

1 Answers

Thank you https://stackoverflow.com/users/7297700/david-browne-microsoft

I started to post the Github issue and one of the outputs they request is a JDBC trace.

When I did this and compared the 2 trace outputs for:

  1. Unique Key Violation
  2. Divide By Zero error

the following lines were present for the Divide By Zero test case:

  • Jun 10, 2020 5:28:50 PM com.microsoft.sqlserver.jdbc.SQLServerResultSet
  • FINE: SQLServerResultSet:1 created by (SQLServerCallableStatement:1)

Having read in another post that RAISERROR behaves differently when the SP returns a resultset (unintentionally in my case), I changed the following code in the uspTestRE Stored Procedure:

IF @inDBZ = 'Y'
  BEGIN
    SELECT 1/0;
  END;

to

IF @inDBZ = 'Y'
  BEGIN
    SET @dbz = 1/0;
  END;

This solved my problem.

Now, I just need to work out what changes to make to trap Errors where Results Sets are returned in the Stored Procedure

Related