Passing LIST to SPL stored procedure or function
I have the need to pass an array to a stored procedure and have been slightly
successful but have run into an issue that I do not understand. Here is my
test stored procedure:
create procedure testlist(mylist LIST(ROW (invid VARCHAR(30),
category CHAR(8),qty DECIMAL(14,6)) NOT NULL));
define myrow ROW (invid VARCHAR(30),
category CHAR(8),
qty DECIMAL(14,6));
define p_invid VARCHAR(30);
SET DEBUG FILE TO "test.trace.log";
TRACE ON;
FOREACH
SELECT * INTO myrow FROM TABLE(mylist)
END FOREACH
end procedure
I call it with this call:
call testlist(LIST{ROW('9000','R',1.0),ROW('9001','A',2.0)});
If you look at the trace log, you see that 2 rows are accessed.
Now, I changed the call slightly where 9000 is now 9000A as in this:
call testlist(LIST{ROW('9000A','R',1.0),ROW('9001','A',2.0)});
When I try to run this call (in dbaccess), I get this:
674: Routine (testlist) can not be resolved.
Error in line 1Near character position 6
I'm at a loss as to why this second call doesn't work. It looks
like it is expecting the data passed to be the same number of characters.
I am testing with IDS 11.70.UC6E.
Any feedback is appreciated!
Thanks,
Candy
--
Candy McCall
ONLINE Computing, Inc.
Original post:
I have the need to pass an array to a stored procedure and have been slightly
successful but have run into an issue that I do not understand. Here is my
test stored procedure:
create procedure testlist(mylist LIST(ROW (invid VARCHAR(30),
category CHAR(8),qty DECIMAL(14,6)) NOT NULL));
define myrow ROW (invid VARCHAR(30),
category CHAR(8),
qty DECIMAL(14,6));
define p_invid VARCHAR(30);
SET DEBUG FILE TO "test.trace.log";
TRACE ON;
FOREACH
SELECT * INTO myrow FROM TABLE(mylist)
END FOREACH
end procedure
I call it with this call:
call testlist(LIST{ROW('9000','R',1.0),ROW('9001','A',2.0)});
If you look at the trace log, you see that 2 rows are accessed.
Now, I changed the call slightly where 9000 is now 9000A as in this:
call testlist(LIST{ROW('9000A','R',1.0),ROW('9001','A',2.0)});
When I try to run this call (in dbaccess), I get this:
674: Routine (testlist) can not be resolved.
Error in line 1Near character position 6
I'm at a loss as to why this second call doesn't work. It looks
like it is expecting the data passed to be the same number of characters.
I am testing with IDS 11.70.UC6E.
Any feedback is appreciated!
Thanks,
Candy
--
Candy McCall
ONLINE Computing, Inc.
Response:
I'll admit I haven't done a ton with LIST's, but what you are describing seem
like it could be a bug. However, I wonder if you could work around the
problem, by making the following changes.
First use a named row type rather then un-named:
create row type test_type (invid VARCHAR(30),category CHAR(8),
qty DECIMAL(14,6));
then change your spl to this:
create procedure testlist(mylist LIST(test_type not null) );define myrow test_type;
define p_invid VARCHAR(30);
SET DEBUG FILE TO "test.trace.log";
TRACE ON;
FOREACH
SELECT * INTO myrow FROM TABLE(mylist)
END FOREACH
end procedure;
Then last, change how you call the SPL to the following:
call testlist(LIST{ROW('9000','R',1.0)::test_type,
ROW('9001','A',2.0)::test_type});
call testlist(LIST{ROW('9000A','R',1.0)::test_type,
ROW('9001','A',2.0)::test_type});
I tested that on 11.70.FC7 and both calls worked. Again, not sure you should
have to do it that way, but if you can, it could be a work around to the issue.
I would think you could open a PMR with support and submit your small test
case and probably get defect entered as I don't have a very good explanation
as to why on the 2nd call to testlist it seems as though it's unable to
resolve the routine.
Jacques Renaut
IBM Informix Advanced Support
APD Team
The behavior you are seeing is confirmed in v12.10 as well. Both of the
first two columns of the ROW type have to have the same length throughout
the LIST that is passed into the function or the engine returns a -674
error.
There is a solution: Cast each argument to the correct type:
> execute procedure testlist(
list{row('Freddy'::varchar(30),'abc'::char(8),8),row('Claire'::varchar(30),'x'::char(8),9),row('defghijk'::varchar(30),'def'::
char(8),0)});
Routine executed.
You should be able to do it also by casting the ROW literal to the correct
row type, but I'm having trouble getting the syntax right and can't spend
any more time on this.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Mon, Jul 29, 2013 at 5:33 PM, CANDY MCCALL <candym@olcinc.com> wrote:
> I have the need to pass an array to a stored procedure and have been
> slightly
> successful but have run into an issue that I do not understand. Here is my
> test stored procedure:
>
> create procedure testlist(mylist LIST(ROW (invid VARCHAR(30),>
> category CHAR(8),qty DECIMAL(14,6)) NOT NULL));
>
> define myrow ROW (invid VARCHAR(30),
>
> category CHAR(8),
>
> qty DECIMAL(14,6));
>
> define p_invid VARCHAR(30);
>
> SET DEBUG FILE TO "test.trace.log";
>
> TRACE ON;
>
> FOREACH
>
> SELECT * INTO myrow FROM TABLE(mylist)>
> END FOREACH
>
> end procedure
>
> I call it with this call:
>
> call testlist(LIST{ROW('9000','R',1.0),ROW('9001','A',2.0)});
>
> If you look at the trace log, you see that 2 rows are accessed.
>
> Now, I changed the call slightly where 9000 is now 9000A as in this:
>
> call testlist(LIST{ROW('9000A','R',1.0),ROW('9001','A',2.0)});
>
> When I try to run this call (in dbaccess), I get this:
>
> 674: Routine (testlist) can not be resolved.
> Error in line 1> Near character position 6
>
> I'm at a loss as to why this second call doesn't work. It looks
> like it is expecting the data passed to be the same number of characters.
>
> I am testing with IDS 11.70.UC6E.
>
> Any feedback is appreciated!
>
> Thanks,
>
> Candy
> --
> Candy McCall
> ONLINE Computing, Inc.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c356e2591bd104e2aea775