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
I tried it out in a different way and found a solution.
I modified the procedure to accept a parameter just as a List, not a List{SET...
CREATE PROCEDURE test_hector
(
C LIST( CHAR(10) 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;
And executing it like this way.
EXECUTE PROCEDURE test_hector('LIST{''stspols'',''stsrepo''}');
Or this way
EXECUTE PROCEDURE test_hector3('LIST{"stspols","stsrepo"}');
And it worked like a charm.
