从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（）吗？的executeQuery（）？的executeUpdate（）？这些似乎都没有默认返回值，但我不确定如何达到它。

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

这里的关键部分是<$中？=前面的$ = c $ c> prepareCall 为返回值设置位置的函数和 registerOutputParameter 。它必须注册为Integer，因为返回值始终是int（至少在SQL Server中，可能在其他DB中不同）。因此，您必须使用 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屋！