JDBC未检测到存储过程异常

问题描述

我正在使用准备好的语句jdbc模板运行存储过程:

I am running a stored procedure using a prepared statement jdbc template:

conn = dbrm.getConnection(this.dataSources.get(aas.getArgumentValue("dataSource")));
Statement stmt = conn.createStatement();
try{
    boolean hasResultSet = stmt.execute(query);
catch(Exception e){
    // log and handle appropriately
}

我的存储过程基本上是一个存储过程，它调用了另外两个存储过程.

My stored procedure is basically one stored procedure calling two other stored procedures.

我遇到的问题是，如果存储过程的第一条语句之后出现异常，则该异常不会返回到jdbc模板，因此即使我的存储过程对我的Java代码也可以正常工作没有，这显然是有问题的.

The problem that I am having is that if there is an exception after the first statement of the stored procedure then the exception does not come back to the jdbc template so it appears that my stored procedure worked to my java code even if it didn't which is obviously problematic.

是否可以手动检查存储过程的输出或使所有可能的异常冒泡到Java?

Is there a way to manually check the output of a stored procedure or make all possible exceptions bubble up to the java?

推荐答案

似乎在执行存储过程时，引发的异常可能在成功结果之后被排队".为了检索"异常，我们可能必须使用 CallableStatement 对象的 getMoreResults 方法.

It seems that when a stored procedure is executed, exceptions that are raised may get "queued" behind a successful result. In order to "retrieve" the exception we may have to use the getMoreResults method of the CallableStatement object.

例如，给定存储过程

CREATE PROCEDURE [dbo].[Table1sp] AS
BEGIN
    SET NOCOUNT ON;
    SELECT 123;
    CREATE TABLE #Table1 (textcol VARCHAR(50) PRIMARY KEY);
    INSERT INTO #Table1 (textcol) VALUES (NULL);  -- error here
END

如果我们运行Java代码

If we run the Java code

String connectionUrl = "jdbc:sqlserver://localhost:52865;"
        + "databaseName=myDb;" + "integratedSecurity=true";
try (Connection conn = DriverManager.getConnection(connectionUrl)) {
    try (CallableStatement cs = conn.prepareCall("{call Table1sp}")) {
        cs.execute();
        ResultSet rs = cs.getResultSet();
        rs.next();
        System.out.println(rs.getInt(1));
        rs.close();
    }
} catch (Exception e) {
    e.printStackTrace(System.err);
}

然后我们只打印值 123 ，我们的代码就会继续进行，好像没有什么错.

then we just print the value 123 and our code carries on as if nothing was wrong.

但是，如果我们跟进 getMoreResults()调用...

However, if we follow-up with a getMoreResults() call ...

String connectionUrl = "jdbc:sqlserver://localhost:52865;"
        + "databaseName=myDb;" + "integratedSecurity=true";
try (Connection conn = DriverManager.getConnection(connectionUrl)) {
    try (CallableStatement cs = conn.prepareCall("{call Table1sp}")) {
        cs.execute();
        ResultSet rs = cs.getResultSet();
        rs.next();
        System.out.println(rs.getInt(1));
        rs.close();

        try {
            cs.getMoreResults();
        } catch (com.microsoft.sqlserver.jdbc.SQLServerException ex) {
            System.out.println("SQLServerException: " + ex.getMessage());
        }

    }
} catch (Exception e) {
    e.printStackTrace(System.err);
}

...然后捕获到异常:

... then the exception gets caught:

123
SQLServerException: Cannot insert the value NULL into column 'textcol', table 'tempdb.dbo.#Table1 ...

这篇关于JDBC未检测到存储过程异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！