Stored Procedures in SE 7.3
I am running a legacy MRP system on Informix SE 7.3 on SLES 10 Linux.
My end goal to write a stored procedure to be used by Lotus Enterprise
Integrator (LEI). I was unsuccessful on the previous setup which was
SE 7.2 on Solaris, but I had different problems.
On 7.2 on Solaris, I entered the following simple procedure using
dbaccess.
CREATE PROCEDURE maint_lines ()
RETURNING CHAR(10), INT, CHAR(16);
DEFINE p_sono char(10);
DEFINE p_soline int;
DEFINE p_product char(16);
select part
from bmdata
where cpart = "11-079P012010"
into temp t1;
select bmdata.part
from bmdata, t1
where bmdata.cpart = t1.part
into temp t2;
select bmdata.part
from bmdata, t2
where bmdata.cpart = t2.part
into temp t3;
select bmdata.part
from bmdata, t3
where bmdata.cpart = t3.part
into temp t4;
select part from t1
union
select part from t2
union
select part from t3
union
select part from t4
into temp t;
FOREACH
select sodetl.sono, sodetl.soline, sodetl.product
into p_sono, p_soline, p_product
from sodetl, t, inprod
where t.part = inprod.part
and inprod.product = sodetl.product
order by 1,2
return p_sono, p_soline, p_product
with resume; END FOREACH;
END PROCEDURE;
Potential problem #1:
On 7.2/Solaris, I could do "EXECUTE PROCEDURE maint_lines()" and it
would return all the lines as I expected. On 7.3/Linux I get error
684: Function (minx.maint_lines) returns too many values. I can'tfind a syntax reference for SE 7.3 to see if something changed. Any
ideas why the EXECUTE PROCEDURE worked on 7.2/Solaris doesn't on 7.3/
Linux?
Potential problem #2:
When this did run on 7.2/Solaris the column names were only
"(expression)". LEI didn't like this at all and I couldn't figure out
how to give names to the output. In other SQLs I think there is a way
to give names to the output values returned from a stored procedure,
though I haven't done it myself. Is there a way to name the return
values from a stored procedure, similar to making column aliases in
SELECT statement?
Thanks for any advice!
-Mark