Re: Stored Procedures in SE 7.3

"Mark Conrad" <mac@pt.com> wrote in message news:f6c2419e-8ed5-4ee7-b097-948a39b05fae@56g2000hsm.googlegroups.com... > 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't> find 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 #1: I thought this error related to trying to use a multi-row or multi-column procedure as a function in a larger SQL statement, which won't work. You need to recheck how you are using the procedure at run-time. #2: I don't think you can fix this on Informix 7. From 9.4, you can either name return values within the procedure or wrap it as a table at run-time: CREATE PROCEDURE maint_lines () RETURNING CHAR(10) AS sono, INT AS soline, CHAR(16) AS product; ... SELECT * FROM TABLE(FUNCTION(maint_lines())) AS x1 (sono, soline, product); Upgrade your version! Regards, Doug Lawry