使用 Java JDBC 调用包含选项参数的 SqlServer SPproc

发布于 2024-12-11 22:29:10 字数 1013 浏览 0 评论 0原文

我目前在使用 JDBC 调用 SQL Server 2008 存储过程时遇到问题。

该问题似乎与可选参数以及 SPproc 中参数的顺序有关。存储过程签名如下（名称/默认值已更改）：

@REQPARAM1 NVARCHAR(50),
@OPTPARAM2 NVARCHAR(50) = N'value',
@OPTPARAM3 NVARCHAR(50) = N'value',
@OPTPARAM4 NVARCHAR(21) = N'value',
@OPTPARAM5 NVARCHAR(50) = N'01234567890',
@OPTPARAM6 NVARCHAR(50) = N'01234567890',
@OPTPARAM7 NVARCHAR(50) = '[email protected]',
@OUTPARAM INT OUTPUT

我尝试使用的 Java 代码是

CallableStatement proc = connection.prepareCall("{call ProcName(?,?)}");
proc.setString("REQPARAM1", value);
proc.registerOutParameter("OUTPARAM", java.sql.Types.INTEGER);
proc.execute();
int result = proc.getInt("OUTPARAM");

但这不起作用，并返回

索引 8 超出范围

我似乎能够让它运行的唯一方法是声明所有可选参数，这破坏了它们是可选的对象。或者将 out 参数直接移到可选参数之前，这会在数据库中造成不一致（代码库主要是 .Net，这是完全可能的）。

所以，我想我的问题是是否可以以这种方式使用可选参数？

原文

I'm currently having an issue with calling a SQL Server 2008 Stored Procedure using JDBC.

The issue appears to be around optional parameters and the order of the parameters in the SProc. The stored proc signature is as follows (names/defaults are changed):

@REQPARAM1 NVARCHAR(50),
@OPTPARAM2 NVARCHAR(50) = N'value',
@OPTPARAM3 NVARCHAR(50) = N'value',
@OPTPARAM4 NVARCHAR(21) = N'value',
@OPTPARAM5 NVARCHAR(50) = N'01234567890',
@OPTPARAM6 NVARCHAR(50) = N'01234567890',
@OPTPARAM7 NVARCHAR(50) = '[email protected]',
@OUTPARAM INT OUTPUT

The Java code I am trying to use is

CallableStatement proc = connection.prepareCall("{call ProcName(?,?)}");
proc.setString("REQPARAM1", value);
proc.registerOutParameter("OUTPARAM", java.sql.Types.INTEGER);
proc.execute();
int result = proc.getInt("OUTPARAM");

But this doesn't work, and comes back with

The index 8 is out of range

The only way I seem able to get this to run is by declaring all of the optional parameters, which defeats the object of them being optional. Or by moving the out parameter directly before the optional ones, which creates inconsistency in the database (Code base is primarily .Net, where this is perfectly possible).

So, I guess my question is whether it is possible to use Optional Parameters in this way?

分享到QQ

分享到微博