Informix JDBC调用首参为整数集合、第二参为out参数的存储过程方法

调用该存储过程的正确语法是什么？若移除第二个out参数、仅使用returning返回结果时程序可正常运行，但业务场景要求存储过程返回多个不同结果，目前未找到单次调用存储过程返回多组不同结果的其他方式，寻求解决方案。

环境依赖 #

IBM Informix Dynamic Server Version 12.10.FC13, 14.10.FC4W1DE  
JDBC 4.10.14, 4.50.7  
Java version "1.7.0_60"

存储过程定义 #

create procedure sp_demo_set_arg5(
    arg1 set(integer not null),
    out arg2 integer
)
returning integer as retval;

define p_arg1 integer;
define p_arg2 integer;
define p_retval integer;

begin

let p_arg2 = 0;
let p_retval = 0;

foreach
    select *
    into p_arg1
    from table(arg1)

    let p_arg2 = p_arg2 + p_arg1;
    let p_retval = 1;

end foreach ;

return p_retval;

end
end procedure;

现有Java代码 #

String dbUrl = "jdbc:informix-sqli://someserver:28090/demodb:INFORMIXSERVER=ol_informix1410";
String user = "someuser";
String password = "somepass";
String sql = "{ ? = call sp_demo_set_arg5(?::SET(integer not null), ?) }"; 

HashSet<Integer> arg1 = new HashSet<>();
Integer intObject;
int i;
for (i=1; i <= 1; i++)
{
    intObject = new Integer(i);
    arg1.add(intObject);
    arg1.add(i);
}

Connection conn = null;

try {
    conn = Database.getConnection(dbUrl, user, password);
    CallableStatement stmt = conn.prepareCall(sql);
    
    int arg2 = 0;
    
    stmt.setObject(1, arg1);
    stmt.registerOutParameter(2, Types.INTEGER );
    ResultSet rs = stmt.executeQuery();

    arg2 = stmt.getInt(2);
    while (rs.next()) {
        System.out.println("retval = " + rs.getInt(1) + " , arg2 = " + arg2);
    }           
    
    return;

} catch (SQLException e) {
    e.printStackTrace();
}

报错信息 #

java.sql.SQLException: Illegal attempt to convert a collection type into another type.
    at com.informix.util.IfxErrMsg.buildExceptionWithMessage(IfxErrMsg.java:422)
    at com.informix.util.IfxErrMsg.buildIsamException(IfxErrMsg.java:401)
    at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3022)
    at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3273)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2269)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2194)
    at com.informix.jdbc.IfxSqli.executeFastPath(IfxSqli.java:4862)
    at com.informix.jdbc.IfxResultSet.executeFastPath(IfxResultSet.java:251)
    at com.informix.jdbc.IfxCallableStatement.executeFastPath(IfxCallableStatement.java:1470)
    at com.informix.jdbc.IfxCallableStatement.executeQuery(IfxCallableStatement.java:233)

报错为集合类型转换错误，触发原因有两个：

1. 参数索引顺序绑定错误：你定义的SQL语句{ ? = call sp_demo_set_arg5(?::SET(integer not null), ?) }中，三个占位符的顺序依次为：存储过程return返回值、输入SET参数、输出out参数。原代码将Java HashSet直接绑定到了第一个占位符（返回值位置），触发类型转换冲突。
2. Java HashSet不能直接映射为Informix原生SET类型，需要通过Informix JDBC提供的IfxSet类显式构造对应类型对象。

步骤1：导入Informix JDBC专属集合类 #

import com.informix.jdbc.IfxSet;

步骤2：修正参数绑定逻辑 #

try {
    conn = Database.getConnection(dbUrl, user, password);
    CallableStatement stmt = conn.prepareCall(sql);
    
    // 构造Informix原生SET类型对象
    IfxSet informixArg1 = new IfxSet("integer not null", conn);
    for (Integer num : arg1) {
        informixArg1.add(num);
    }

    // 注册第一个占位符：return返回的retval
    stmt.registerOutParameter(1, Types.INTEGER);
    // 绑定第二个占位符：输入SET参数
    stmt.setObject(2, informixArg1);
    // 注册第三个占位符：输出参数arg2
    stmt.registerOutParameter(3, Types.INTEGER);

    ResultSet rs = stmt.executeQuery();

    // 遍历结果集取return返回值，遍历完成后再取out参数
    while (rs.next()) {
        int retval = rs.getInt(1);
        int arg2 = stmt.getInt(3);
        System.out.println("retval = " + retval + " , arg2 = " + arg2);
    }           
    
    return;

} catch (SQLException e) {
    e.printStackTrace();
}

多结果返回实现方式 #

如果需要单次调用返回多组不同结果，可在存储过程中使用return with resume语句返回多行结果集，同时搭配out参数返回全局统计类结果，完全满足业务需求，不需要调整现有存储过程的参数结构。 #

内容的提问来源于stack exchange，提问作者user3737906