Informix procedure JDBC: How to call when first parameter is array of integers and second is out parameter

Question

What is the proper syntax to call the procedure ? It works if I remove the 2nd out parameter and used returning to return the result, but I have use case where there will be more than 1 different results to be returned by the procedure. I can't figure out other way to return different result from a single procedure call. Any help are deeply appreciated.

Tools

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

Stored procedure

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 code

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

Stacktrace

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)

Answer 1

Have you tried using the driver's PreparedStatement (IfmxPreparedStatement)?

The classes:

com.informix.lang.IfxToJavaType,
com.informix.lang.JavaToIfxType
com.informix.lang.IfxTypes

are very helpful.

And these ones:

com.informix.jdbc.IfxComplexInput
com.informix.jdbc.IfxComplexOutput

make calls to IfxTypes' method:

public static boolean isCollection(int ifxType) {
    ifxType &= 0xFF;
    if (ifxType == 21 || ifxType == 19 || ifxType == 20)
      return true; 
    return false;
 }

(The IfxType for SET is 19)