Informix:如何在单个参数中传递，使用和执行多个值到存储过程 [英] Informix: How to pass, use and execute multiple values in a single parameter to a stored procedure

问题描述

如何将单个参数中的多个值传递给 Informix 中的存储过程??

这是一个常见的问题，但是我没有看到有关INFORMATIONIX的信息.

我找到了解决方案

将参数类型定义为适当的集合类型:LIST，SET，MULTISET(这是我在交叉引用问题的回答中所说的). /p>

• 什么对你不起作用?
• 您尝试了什么?
• 您收到的错误消息是什么?
• 您正在使用哪个版本的Informix服务器?
• 您使用哪个工具或API创建存储过程?
• 您使用哪个工具或API执行存储过程?
• 您在哪个平台上运行?
• 您如何调用该程序?

---

感谢扩大问题.你说:

EXECUTE PROCEDURE test_hector('{stspols,stsrepo}');

我收到错误消息[Informix][Informix ODBC Driver][Informix]Invalid collection literal value.

这可能是比表面上更容易解决的问题.该过程的输入类型应该是集合(实际上是SET值的列表)，其中每个值都是一个字符串.您可以将其写为:

EXECUTE PROCEDURE test_hector(LIST{SET{'stspols','stsrepo'}});

我创建了一个虚拟程序来测试此语法:

CREATE PROCEDURE test_hector(c LIST(SET (CHAR(10) NOT NULL ) NOT NULL))
    RETURNING CHAR(10) AS C, CHAR(10) AS CVE, CHAR(50) AS DESC;
    return "abc", "def", "ghi";
END PROCEDURE;

其输出符合预期:

abc   def   ghi

请注意，Informix支持以{开头并以后一个}结尾的注释样式.但是，当{之前的关键字是SET，MULTISET或LIST之一时，该注释样式将被抑制(是的，这确实使它很难解析！).您可以通过在哪里可以在上述SQL中添加{}的位置"获得巨大的乐趣(如果变态了).而不改变其含义. API可能会有外部机会识别Informix {}注释，但不能识别collection-exception.在这种情况下，您可能会返回语法错误(因为如果将第一个{解释为开始注释符号，则不会出现第二个}).在这种情况下，请使用以下表示法之一.

集合(SET，MULTISET，LIST)文字的表示法随时间而发展.这种替代表示法也可以使用(并且与您最初尝试的内容更紧密相关，并且是最初记录的内容):

EXECUTE PROCEDURE test_hector('LIST{SET{''stspols'',''stsrepo''}}');

SET中的字符串必须用引号引起来，但是整个文字本身就是字符串，因此您需要将嵌入的引号加倍.您还可以作弊"并使用双引号和单引号:

EXECUTE PROCEDURE test_hector('LIST{SET{"stspols","stsrepo"}}');
EXECUTE PROCEDURE test_hector("LIST{SET{'stspols','stsrepo'}}");

---

从下面的讨论中，如替代答案中所述，该问题现在似乎与嵌套集合有关. LIST{SET{"str1", "str2"}}是一个有序列表(其中有一个条目)；该条目本身就是一组(不同的)字符串，没有特定的顺序.如果需要能够重复字符串，则可以使用MULTISET(但是顺序并不重要).您使用LIST很重要(顺序很重要(并且列表中允许重复)).

听起来好像您真的只需要选择参数类型，这样它才更简单.您应该能够有效地使用任何一种收集类型；我可能会提名SET以便您不必处理列表中的重复字符串，但是MULTISET或LIST也是有效的选项.将过程重命名为test_3():

CREATE PROCEDURE test_3(c SET(CHAR(10) NOT NULL))
    RETURNING CHAR(10) AS r;
    DEFINE r CHAR(10);
    FOREACH SELECT * INTO r FROM TABLE(c)
        RETURN r WITH RESUME;
    END FOREACH;
END PROCEDURE;

我能够执行以下两条语句，并显示结果:

+ EXECUTE PROCEDURE test_3(SET{'stspols','stsrepo'});
stspols
stsrepo
+ EXECUTE PROCEDURE test_3('SET{''stspols'',''stsrepo''}');
stspols
stsrepo

这是使用ESQL/C接口.您应该能够使用ODBC.第一种可能会导致-201语法错误.

如果您希望将LIST设置为SET，则在上面的代码中将SET更改为LIST:

+ CREATE PROCEDURE test_3(c LIST(CHAR(10) NOT NULL))
    RETURNING CHAR(10) AS r;
    DEFINE r CHAR(10);
    FOREACH SELECT * INTO r FROM TABLE(c)
        RETURN r WITH RESUME;
    END FOREACH;
END PROCEDURE;
+ EXECUTE PROCEDURE test_3(LIST{'stspols','stsrepo'});
stspols
stsrepo
+ EXECUTE PROCEDURE test_3('LIST{''stspols'',''stsrepo''}');
stspols
stsrepo

How I Can pass multiple values in a single parameter to a stored procedure in Informix?.

This is a frequent question but I have seen noyhing about informix.

I found a post, but it is not working for me, should be the version of the DB or am I missing something?

I'm trying this to perform a WHERE X IN (SELECT Y FROM TABLE(PARAM))

Edit:

This is an example of what Im trying to do.

CREATE PROCEDURE test_hector
(
    C LIST( SET (CHAR(10) NOT NULL ) NOT NULL)
)
RETURNING CHAR(10) AS C, CHAR(10) AS CVE, CHAR(50) AS DESC;

DEFINE vColumna like tclaves.columna;
DEFINE vClave like tclaves.clave;
DEFINE vdescve like tclaves.descve;

FOREACH
select columna, clave, descve
INTO vColumna, vClave,vdescve
from tclaves
where columna in (SELECT * FROM TABLE(C))
RETURN vColumna, vClave,vdescve WITH RESUME;
END FOREACH
END PROCEDURE;

I'm trying to execute it but I think I'm having sintax problems

EXECUTE PROCEDURE test_hector( '{stspols,stsrepo}');

I'm getting the error message [Informix][Informix ODBC Driver][Informix]Invalid collection literal value.

I executed this function execute function se_release() to obtain the informix version this is what I got.

column1
Spatial DataBlade Release 8.21.FC4R1 (Build 238)                 Compiled on Thu Aug 26 19:42:55 CDT 2010 with:                      IBM Informix Dynamic Server Version 10.00.FC7                    glslib-4.00.UC10

I'm using Aqua Data Studio 8.0.22 to create and execute the procedure. Runinng on Windows 7 Ultimate 32-Bits

Thanks in advance. For any help

解决方案

Define the parameter type as an appropriate collection type: LIST, SET, MULTISET (which is what I said in the answer to the cross-referenced question).

• What isn't working for you?
• What did you try?
• What is the error message you get?
• Which version of Informix server are you using?
• Which tool or API are you using to create the stored procedure?
• Which tool or API are you using to execute the stored procedure?
• Which platform are you running on?
• How are you invoking the procedure?

---

Thanks for amplifying the question. You say:

EXECUTE PROCEDURE test_hector('{stspols,stsrepo}');

I'm getting the error message [Informix][Informix ODBC Driver][Informix]Invalid collection literal value.

This may be a simpler problem to fix than it appears on the surface. The input type for the procedure is supposed to be a collection — in fact, a LIST of SET values — where each value is a character string. You can write that as:

EXECUTE PROCEDURE test_hector(LIST{SET{'stspols','stsrepo'}});

I created myself a dummy procedure to test this syntax:

CREATE PROCEDURE test_hector(c LIST(SET (CHAR(10) NOT NULL ) NOT NULL))
    RETURNING CHAR(10) AS C, CHAR(10) AS CVE, CHAR(50) AS DESC;
    return "abc", "def", "ghi";
END PROCEDURE;

And the output from it was as expected:

abc   def   ghi

Note that Informix supports a comment style started by { and ended by the first following }. However, that comment style is suppressed when the keyword before the { is one of SET, MULTISET or LIST (and yes, that does make it really hard to parse!). You can have immense (if perverted) fun with "where can you add the {} in the SQL above" without changing its meaning. There's an outside chance that an API might recognize Informix {} comments but not recognize the collection-exception. In that case, you'd probably get back a syntax error (because the second } is not expected if you interpret the first { as a start comment symbol). In that case, use one of the notations below.

The notation for collection (SET, MULTISET, LIST) literals evolved over time. This alternative notation also works (and is more closely related to what you tried originally, and is what was documented originally):

EXECUTE PROCEDURE test_hector('LIST{SET{''stspols'',''stsrepo''}}');

The strings within the SET must be enclosed in quotes, but the whole literal is itself a string, so you need to double the embedded quotes. You could also 'cheat' and use double quotes and single quotes:

EXECUTE PROCEDURE test_hector('LIST{SET{"stspols","stsrepo"}}');
EXECUTE PROCEDURE test_hector("LIST{SET{'stspols','stsrepo'}}");

---

From the discussion below, and as described in the alternative answer, the problem now seems to be related to the nested collections. A LIST{SET{"str1", "str2"}} is an ordered list (with one entry in it); that entry is itself a set of (distinct) strings, which has no particular order. You'd use a MULTISET if you needed to be able to repeat strings (but the order isn't important). You use a LIST is order is important (and duplicates are allowed in a list).

It sounds as if you really only need to choose the argument type so that it is simpler. You should be able to use any one collection type effectively; I'd probably nominate SET so that you don't have to deal with repeated strings in the list, but MULTISET or LIST are also valid options. With a procedure renamed to test_3():

CREATE PROCEDURE test_3(c SET(CHAR(10) NOT NULL))
    RETURNING CHAR(10) AS r;
    DEFINE r CHAR(10);
    FOREACH SELECT * INTO r FROM TABLE(c)
        RETURN r WITH RESUME;
    END FOREACH;
END PROCEDURE;

I was able to execute both the statements following, with the results shown:

+ EXECUTE PROCEDURE test_3(SET{'stspols','stsrepo'});
stspols
stsrepo
+ EXECUTE PROCEDURE test_3('SET{''stspols'',''stsrepo''}');
stspols
stsrepo

This was using an ESQL/C interface. You should be able to get the second to work with ODBC; the first may cause a -201 syntax error.

If you prefer LIST to SET, then change SET to LIST in the code above:

+ CREATE PROCEDURE test_3(c LIST(CHAR(10) NOT NULL))
    RETURNING CHAR(10) AS r;
    DEFINE r CHAR(10);
    FOREACH SELECT * INTO r FROM TABLE(c)
        RETURN r WITH RESUME;
    END FOREACH;
END PROCEDURE;
+ EXECUTE PROCEDURE test_3(LIST{'stspols','stsrepo'});
stspols
stsrepo
+ EXECUTE PROCEDURE test_3('LIST{''stspols'',''stsrepo''}');
stspols
stsrepo

这篇关于Informix:如何在单个参数中传递，使用和执行多个值到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！