Solved: Stored procedures produce no output
Environment: IBMRSk, AIX 4.1.5, Informix SE 7.1, SCO Vision Middleware, MS
VC++ 4.2.
My original posting queried why code like:
create procedure foo(){
select * from customer}
end procedure;
execute procedure foo()
produced no output. Thanks to help from Paul Kapp, Mark D Stock, Tin Zaw,
Richard Thomas and Saulius Raudys, I have an answer.
1) Braces. The procedure body was one big comment. (Duh)
2) No RETURN statements. This was what tripped me up - I am porting an MS
SQL Server 6.0 application, whose SPs return values as default behaviour.
So, the new code looks like:
> create procedure foo()
> returning int,char(50);> define id int;
> define name char(50);
> foreach
> select cstid,cstname
> into id,name
> from customer
> return id,name with resume;> end foreach;
> end procedure
Which seemed to me to be a bit complicated just to return values from a
query (the real query is more complex). I finally decided that, for my
application, a VIEW fitted the requirements more closely. This also has the
advantage of allowing VC++ to generate a CRecordset class automatically (I
had previously had to hack one around to get the required output fields
from it).
Thanks again to everyone that lent a hand :)
Ian