如何使用odbc:param_query在Erlang中调用存储过程获取数组 [英] How to call stored procedure taking array using odbc:param_query in Erlang
问题描述
我在db2中有一个存储过程
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@
我试图通过Erlang odbc:param_query。
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]}]).
正在抛出异常
异常退出:{badarg,odbc,param_query,'params'}
在函数odbc:decode / 1(odbc.erl,第894行)
exception exit: {badarg,odbc,param_query,'Params'} in function odbc:decode/1 (odbc.erl, line 894)
有没有其他方法可以将列表(Array)传递给存储过程?
Is there any other way to pass a list (Array) to the stored procedure?
推荐答案
对于整数列表,看起来没有OBDC数据类型(至少有一个相应的Erlang一个)(参见 erlang obdc文档)。我不知道最终查询应该如何(int数组的语法),但我认为可以通过以字符串形式创建查询来实现所需要的:
It looks like there is no OBDC data type (at least with a corresponding Erlang one) for a list of integer (see erlang obdc documentation). I don't know how the the final query should look like (the syntax for int array) but I think that you can achieve what you want by creating your query as a string:
Query = io_lib:format(CALL sum(〜p,〜p),[int_array_syntax([1,2,3,4]),1])
,然后使用 odbc:sql_query(Ref,Query)
。
这篇关于如何使用odbc:param_query在Erlang中调用存储过程获取数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!