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.