RAISERROR-Java客户机中SQLException行为的差异

Question

null

1.存储过程：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;

从SQLCMD执行SP时，它会生成以下错误消息：

测试1：密钥冲突

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).

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

然而，我的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();
        }
    }
}

测试1：密钥冲突（行为与预期一致）

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).

测试2：除以零（不会被困住）

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

谁能向我解释一下，为什么SQL Server中捕获并在SP Catch块中处理的被零除错误没有被Java代码捕获？

Answer 1

谢谢你https://stackoverflow.com/users/7297700/David-Browne-Microsoft

我开始发布Github问题，他们请求的输出之一是JDBC跟踪。

当我这样做并比较2个跟踪输出时：

null

null

在另一篇文章中读到当SP返回resultset时RAISERROR的行为不同（在我的例子中是无意的），我更改了uspTestRE存储过程中的以下代码：

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

到

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

这解决了我的问题。