如何使用JDBC从存储过程中获取* everything * [英] 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 object， - 我们可以使用
CallableStatement＃getUpdateCount（），或 - 抛出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从存储过程中获取* everything *的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
