IDS Forum

Re: Passing LIST to SPL stored procedure or functi

Posted By: JACQUES RENAUT
Date: Monday, 29 July 2013, at 6:30 p.m.

In Response To: Passing LIST to SPL stored procedure or function (CANDY MCCALL)

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 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.

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

Messages In This Thread

• Passing LIST to SPL stored procedure or function
  CANDY MCCALL -- Monday, 29 July 2013, at 5:33 p.m.
  • Re: Passing LIST to SPL stored procedure or functi
    JACQUES RENAUT -- Monday, 29 July 2013, at 6:30 p.m.
  • Re: Passing LIST to SPL stored procedure or functi
    Art Kagel -- Monday, 29 July 2013, at 7:14 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.