从 JDBC MSSQL 获取返回值 [英] Getting the Return Value from JDBC MSSQL

问题描述

我使用 Microsoft SQL Server JDBC Driver 2.0 通过 Java 连接到 SQL Server (2005).

I'm connecting to SQL Server (2005) through Java using the Microsoft SQL Server JDBC Driver 2.0.

如何从存储过程中获取返回值?我正在做类似的事情:

How do I get the return value from a stored procedure? I'm doing something like:

Connection connection = dataSource.getConnection()
CallableStatement proc = connection.prepareCall("{ call dbo.mySproc() }");
proc.execute();

我应该使用execute()吗?执行查询()?执行更新()?默认情况下，这些似乎都没有返回返回值，但我不确定如何获得它.

Should I be using execute()? executeQuery()? executeUpdate()? None of these seem to return a return value by default but I'm not really sure how to get to it.

编辑 1:要清楚，我知道如何调用存储过程.这个问题特别是关于如何获得返回值(而不是结果集).返回值是一个整数，通常在您执行没有结果集的查询时生成，或者如果您在 SQL 中明确声明了诸如 RETURN 0 之类的内容.

EDIT 1: To be clear, I know how to call stored procedures. This question is specifically about how to get the RETURN VALUE (as opposed to a Result Set). The Return Value is an integer that is usually generated when you execute a query with no Result Set or if you specifically state something like RETURN 0 in your SQL.

编辑 2:executeUpdate() 返回一个 int 但这个 int 与返回值不同.此外，OUT 参数与返回值不同.

EDIT 2: executeUpdate() returns an int but this int is not the same as the Return Value. Also, an OUT parameter is not the same as a return value.

推荐答案

Bozho 的第二次修订答案很接近，但还不够.不过，它确实让我找到了答案.

Bozho's 2nd revised answer was close but not quite there. It did lead me to the answer though.

以我开始的代码示例为例:

Taking the code example I started with we end up with:

CallableStatement proc = connection.prepareCall("{ ? = call dbo.mySproc() }");
proc.registerOutParameter(1, Types.INTEGER);
proc.execute();
int returnValue = proc.getInt(1);

这里的关键部分是 prepareCall 函数中调用"前面的?="，它为返回值和 registerOutputParameter 设置了一个位置.它必须注册为整数，因为返回值始终是整数(至少在 SQL Server 中，可能在其他数据库中有所不同).因此，您必须使用 getInt 来获取它.我测试了这个方法，它确实有效.

The key pieces here are the "? =" in front of the "call" in the prepareCall function which sets up a place for the return value and the registerOutputParameter. It has to be registered as an Integer, as the return value is always an int (at least in SQL Server, maybe it's different in other DBs). You therefore have to get it using getInt. I tested this method and it does work.

这篇关于从 JDBC MSSQL 获取返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！