是什么会导致SQL Server JDBC错误“未为输出参数访问设置参数号0的值"? [英] What can cause the SQL Server JDBC error 'The value is not set for the parameter number 0' for an output parameter access?

问题描述

我有一些访问SQL Server 2005的Java代码，如下所示:

I have some Java code that accesses SQL Server 2005 which looks something like this:

CallableStatement cstmt = ...;
... // Set input parameters
cstmt.registerOutParameter(11, Types.INTEGER);
cstmt.execute();
int out = cstmt.getInt(11);

最后一行抛出以下异常:

And the following exception is thrown from the last line:

com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set 
     for the parameter number 0.
   at com.microsoft.sqlserver.jdbc.SQLServerException.
     makeFromDriverError(Unknown Source)
   at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
     skipOutParameters(Unknown Source)
   at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
     getOutParameter(Unknown Source)
   at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
     getterGetParam(Unknown Source)
   at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
     getInt(Unknown Source)
   at org.jboss.resource.adapter.jdbc.WrappedCallableStatement.
     getInt(WrappedCallableStatement.java:192)

被调用的存储过程看起来像这样:

The stored procedure being called looks something like this:

CREATE PROCEDURE dbo.stored_proc ( -- 10 input parameters
                                 , @out_param INT OUTPUT) AS

-- Variable declarations 

SET @out_param = 0

-- Do processing...

SET @out_param = 1

由于在存储过程进入时将输出参数设置为零，在什么情况下不能设置该值?还是我误解了错误消息?

此错误可通过以下方式重现:

This error is reproducible with:

• SQL Server JDBC驱动程序1.2
• SQL Server 2005(64位)Service Pack 2
• SQL Server 2005(64位)Service Pack 3

更新:似乎是由于存储过程的-- Do processing...部分导致的.删除它可以消除错误.这里要复制的代码太多，我想要一些指向可能原因的指针，以缩小可能的候选对象的范围.

Update: It seems to be occuring as a result of the -- Do processing... part of the stored procedure. Removing this eliminates the error. There's too much code to reproduce here, what I'd like is some pointers to possible causes to narrow down likely candidates.

更新:向存储过程的-- Do processing...部分注入错误(例如，除以零)不会引发此异常(相反，如预期的那样) ，execute()调用将失败，并显示相应的错误消息.

Update: Injecting errors (e.g. divide by zero) into the -- Do processing... part of the stored procedure does not cause this exception to be thrown (instead, as expected, the execute() call fails with an appropriate error message).

更新:反编译com.microsoft.sqlserver.jdbc.SQLServerCallableStatement类建议参数编号0"是存储过程返回值.

Update: Decompiling the com.microsoft.sqlserver.jdbc.SQLServerCallableStatement class suggests that 'parameter number 0' is the stored procedure return value.

更新:我无法通过直接通过Management Studio调用存储过程来重现此问题.

Update: I have been unable to reproduce this by calling the stored procedure directly through Management Studio.

更新:此错误的最终原因似乎是存储过程中的僵局.但是，通常，死锁会导致execute()调用失败，并出现SQLException包装SQL Server错误代码1205 ...

Update: The ultimate cause of this error seems to be a deadlock in the stored procedure. Usually, however, deadlocks cause the execute() call to fail with a SQLException wrapping SQL Server error code 1205...

推荐答案

以防万一有人在SQL Server 2008上仍然遇到此问题.

In case anyone still getting this problem on SQL server 2008.

我在尝试为DateTime字段设置时间戳的更新语句中遇到了相同的问题.违规字段位于where子句中，该子句的索引与所报告的索引不同.

I had the same problem on an update statement with trying to set a Timestamp for a DateTime field. The offending field was in a where clause at a different index from the reported one.

这篇关于是什么会导致SQL Server JDBC错误“未为输出参数访问设置参数号0的值"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！