How to call stored procedure taking array using odbc:param_query in Erlang

Question

I have a stored procedure in db2

create type intArray as integer array[100]@

create or replace procedure sum(in numList intArray, out total integer) 
begin 
    declare i, n integer;  

    set n = CARDINALITY(numList);

    set i = 1; 
    set total = 100; 

    while (i <= n) do 
    set total = total + numList[i]; 
    set i = i + 1; 
    end while;    
end@

I am trying to call through Erlang odbc:param_query.

odbc:param_query(Ref,  "CALL sum (?, ?)", [{sql_integer,[1]}, {sql_integer,out, [1]}]).

The above is giving me proper return as

{executed,1,[{101}]}

But when I pass multiple values as

 odbc:param_query(Ref,  "CALL sum (?, ?)", [{sql_integer,[1,2,3,4]}, {sql_integer,out, [1]}]).

It is throwing an exception

exception exit: {badarg,odbc,param_query,'Params'} in function odbc:decode/1 (odbc.erl, line 894)

Is there any other way to pass a list (Array) to the stored procedure?

Answer 1

I believe you need to have an equal amount of arguments in both lists of arguments, meaning add three 1s in your 2nd list or arguments.

odbc:param_query(Ref,  "CALL sum (?, ?)", [{sql_integer, [1,2,3,4]}, {sql_integer, out, [1,1,1,1]}]).