Re: Stored Procedures in SE 7.3
- From: "Doug Lawry" <lawry@xxxxxxxxxxxxx>
- Date: Fri, 25 Jul 2008 19:29:08 +0100
"Mark Conrad" <mac@xxxxxx> wrote in message news:f6c2419e-8ed5-4ee7-b097-948a39b05fae@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- Stored Procedures in SE 7.3
- From: Mark Conrad
- Stored Procedures in SE 7.3
- Prev by Date: Re: ontape restore error
- Next by Date: Re: ontape restore error
- Previous by thread: Stored Procedures in SE 7.3
- Next by thread: Raw Device IO Error
- Index(es):
Relevant Pages
|
