检索结果集值时获取 IN 参数而不是行

Question

我正在尝试从我的Java代码中调用一个过程。我首先创建一个 CallableStatement，然后将我的参数与 setString(x， "value"( 相关联。

我的程序选择查询中的第一个结果。我可以毫无问题地从SQL Server执行该过程并获得结果。从我的 Java 代码中，除了我的 IN 参数之外，我无法获得任何东西，这些参数在我的 ResultSet 中......我不明白这怎么可能。

这是过程创建脚本：

CREATE PROCEDURE [dbo].[sp_getTask] 
-- Add the parameters for the stored procedure here
@actionTypeParam nvarchar(50) = '', 
@serverNameParam nvarchar(10) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @actionTypeParam, @serverNameParam
SELECT TOP 1 actionId, actionType, partner, fileProt FROM dbo.Ordonnancement WHERE actionType = @actionTypeParam AND serverName = @serverNameParam AND actionState = 'ACTIVE' ORDER BY actionRetry ASC, actionLocalDateTime ASC;
END
GO

这是我调用此过程的 Java 代码：

private static List<Action> getSyncTask() {
List<Action> tasks = new ArrayList<Action>();
Connection cnx;
ResultSet rs = null;
try {
cnx = centralDb.getConnection();
CallableStatement cstmt = cnx.prepareCall(PMASchedulerProcedures.GET_TASK);
cstmt.setString(1, "sync");
cstmt.setString(2, PMAUtils.getHostName());
cstmt.execute();
rs = cstmt.getResultSet();
} catch (SQLException e) {
logger.error("[" + String.format("%-25s","Sync program")+"    ] : " 
+ "Cannot get SQL connection  : " + e.getMessage());
}
try {
while (rs.next()) {
Action action = new Action();
action.setActionId(rs.getInt(1));
action.setActionType(ActionType.fromName(rs.getString(2)));
action.setPartner(rs.getString(3));
action.setFileProt(rs.getString(4));
tasks.add(action);
}
} catch (SQLException e) {
logger.error("[" + String.format("%-25s","Sync program")+"    ] : " 
+ "Cannot gather action informations  : " + e.getMessage());
}
return tasks;
}

我得到以下结果：

rs.getString(1) : sync
rs.getString(2) : PF11-40A

这是我在 CallableStatement 中设置的两个参数。

如果有人对这里发生的事情有所了解，我会很高兴听到它:)

Answer 1

我对 Java 一无所知，但您的 SQL 存储过程返回了两组结果。我怀疑第一个 SELECT，它只是获取传入的参数，是为某种类型的测试而设置的。 尝试摆脱参数的选择，如下所示：

CREATE PROCEDURE [dbo].[sp_getTask] 
-- Add the parameters for the stored procedure here
@actionTypeParam nvarchar(50) = '', 
@serverNameParam nvarchar(10) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT TOP 1 actionId, actionType, partner, fileProt FROM dbo.Ordonnancement WHERE actionType = @actionTypeParam AND serverName = @serverNameParam AND actionState = 'ACTIVE' ORDER BY actionRetry ASC, actionLocalDateTime ASC;
END
GO