如何使用 JDBC 从存储过程中取回*一切* [英] How to get *everything* back from a stored procedure using JDBC
问题描述
在使用 JDBC 处理 SQL Server 存储过程时，我偶尔会遇到两种形式的奇怪行为:
I occasionally encounter two forms of strange behaviour when using JDBC to work with SQL Server stored procedures:
问题 1: 我在 SQL Server Management Studio (SSMS) 中运行了一个存储过程，它返回一个结果集.但是，当我尝试
Issue 1: I run a stored procedure in SQL Server Management Studio (SSMS) and it returns a result set. However, when I try
try (CallableStatement cs = conn.prepareCall("{call dbo.TroublesomeSP}")) {
ResultSet rs = cs.executeQuery();
我得到了异常
com.microsoft.sqlserver.jdbc.SQLServerException: 语句没有返回结果集.
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
问题 2:我在 SSMS 中运行了一个存储过程，它引发了一个错误，但是当我使用 JDBC 来 .execute 存储过程没有抛出异常.
Issue 2: I run a stored procedure in SSMS and it raises an error, but when I use JDBC to .execute the stored procedure no exception is thrown.
为什么会出现这些问题，我该如何避免?
Why do these issues occur and how can I avoid them?
推荐答案
当我们在 JDBC 中执行存储过程时，我们会返回一系列零个或多个结果".然后我们可以通过调用 CallableStatement#getMoreResults() 顺序处理这些结果".每个结果"可以包含
When we execute a stored procedure in JDBC we get back a series of zero or more "results". We can then process those "results" sequentially by calling CallableStatement#getMoreResults(). Each "result" can contain
- 我们可以使用
ResultSet对象检索零或多行数据， - 我们可以使用
CallableStatement#getUpdateCount()或 检索的 DML 语句(INSERT、UPDATE、DELETE)的更新计数 - 引发 SQLServerException 的错误.
- zero or more rows of data that we can retrieve with a
ResultSetobject, - an update count for a DML statement (INSERT, UPDATE, DELETE) that we can retrieve with
CallableStatement#getUpdateCount(), or - an error that throws an SQLServerException.
对于问题 1"，问题通常是存储过程不是以 SET NOCOUNT ON; 开头，而是在执行 SELECT 以生成结果集之前执行 DML 语句.DML 的更新计数作为第一个结果"返回，数据行卡在它后面"，直到我们调用 getMoreResults.
For "Issue 1" the problem is often that the stored procedure does not begin with SET NOCOUNT ON; and executes a DML statement before doing a SELECT to produce a result set. The update count for the DML is returned as the first "result", and the data rows are "stuck behind it" until we call getMoreResults.
问题 2"本质上是相同的问题.在错误发生之前，存储过程会产生一个结果"(通常是一个 SELECT，或者可能是一个更新计数).该错误在后续的结果"中返回，并且在我们使用 getMoreResults检索"它之前不会导致异常.
"Issue 2" is essentially same problem. The stored procedure produces a "result" (usually a SELECT, or possibly an update count) before the error occurs. The error is returned in a subsequent "result" and does not cause an exception until we "retrieve" it using getMoreResults.
在许多情况下，只需添加 SET NOCOUNT ON; 作为存储过程中的第一个可执行语句即可避免该问题.然而，对存储过程的更改并不总是可能的，事实仍然是，为了从存储过程中取回一切，我们需要不断调用 getMoreResults 直到，如Javadoc 说:
In many cases the problem can be avoided by simply adding SET NOCOUNT ON; as the first executable statement in the stored procedure. However, a change to the stored procedure is not always possible and the fact remains that in order to get everything back from the stored procedure we need to keep calling getMoreResults until, as the Javadoc says:
There are no more results when the following is true:
// stmt is a Statement object
((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))
这听起来很简单，但像往常一样，魔鬼在细节中"，如以下示例所示.对于 SQL Server 存储过程...
That sounds simple enough but as usual, "the devil is in the details", as illustrated by the following example. For a SQL Server stored procedure ...
ALTER PROCEDURE dbo.TroublesomeSP AS
BEGIN
-- note: no `SET NOCOUNT ON;`
DECLARE @tbl TABLE (id VARCHAR(3) PRIMARY KEY);
DROP TABLE NonExistent;
INSERT INTO @tbl (id) VALUES ('001');
SELECT id FROM @tbl;
INSERT INTO @tbl (id) VALUES ('001'); -- duplicate key error
SELECT 1/0; -- error _inside_ ResultSet
INSERT INTO @tbl (id) VALUES ('101');
INSERT INTO @tbl (id) VALUES ('201'),('202');
SELECT id FROM @tbl;
END
... 以下 Java 代码将返回所有内容...
... the following Java code will return everything ...
try (CallableStatement cs = conn.prepareCall("{call dbo.TroublesomeSP}")) {
boolean resultSetAvailable = false;
int numberOfResultsProcessed = 0;
try {
resultSetAvailable = cs.execute();
} catch (SQLServerException sse) {
System.out.printf("Exception thrown on execute: %s%n%n", sse.getMessage());
numberOfResultsProcessed++;
}
int updateCount = -2; // initialize to impossible(?) value
while (true) {
boolean exceptionOccurred = true;
do {
try {
if (numberOfResultsProcessed > 0) {
resultSetAvailable = cs.getMoreResults();
}
exceptionOccurred = false;
updateCount = cs.getUpdateCount();
} catch (SQLServerException sse) {
System.out.printf("Current result is an exception: %s%n%n", sse.getMessage());
}
numberOfResultsProcessed++;
} while (exceptionOccurred);
if ((!resultSetAvailable) && (updateCount == -1)) {
break; // we're done
}
if (resultSetAvailable) {
System.out.println("Current result is a ResultSet:");
try (ResultSet rs = cs.getResultSet()) {
try {
while (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLServerException sse) {
System.out.printf("Exception while processing ResultSet: %s%n", sse.getMessage());
}
}
} else {
System.out.printf("Current result is an update count: %d %s affected%n",
updateCount,
updateCount == 1 ? "row was" : "rows were");
}
System.out.println();
}
System.out.println("[end of results]");
}
... 产生以下控制台输出:
... producing the following console output:
Exception thrown on execute: Cannot drop the table 'NonExistent', because it does not exist or you do not have permission.
Current result is an update count: 1 row was affected
Current result is a ResultSet:
001
Current result is an exception: Violation of PRIMARY KEY constraint 'PK__#314D4EA__3213E83F3335971A'. Cannot insert duplicate key in object 'dbo.@tbl'. The duplicate key value is (001).
Current result is a ResultSet:
Exception while processing ResultSet: Divide by zero error encountered.
Current result is an update count: 1 row was affected
Current result is an update count: 2 rows were affected
Current result is a ResultSet:
001
101
201
202
[end of results]
这篇关于如何使用 JDBC 从存储过程中取回*一切*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
