Returning rows from a stored Procedure (SPL)

xanana · #1 (**permalink**) 08-30-02, 00:29

I am trying to return a resultset from a stored procedure and
currently have this working:

Code:

    CREATE FUNCTION findAllUsers()
       RETURNING char(10),
                 char(12);

       DEFINE u char(10);
       DEFINE p char(12);

       FOREACH cur1 WITH HOLD FOR 
         SELECT user_id, user_password INTO u, p FROM user where user_id IN ('gavinb','manglu')
         RETURN u, p WITH RESUME;
       END FOREACH

    END FUNCTION;

The only problem is that the columns returned do not retain
their table column names. e.g.

Code:

> EXECUTE FUNCTION findAllUsers();

(expression) (expression)

gavinb       bong
manglu       ganesh

2 row(s) retrieved.

How can I modify my stored procedure to return a normal resultset
with metadata intact ... ?

Thanks

Xanana

Roelwe · #2 (**permalink**) 08-30-02, 03:36

Hi,

I'm sorry but I don't think this is possible. Why would you want to do that? Normally is your client application you would define a cursor and fetch each row. Row per Row, without column headings.
I assume the output you got was from dbaccess.
In dbaccess, the output is always formatted with headings, that's why you see (expression) as heading.

eherber · #3 (**permalink**) 08-31-02, 03:53

Quote:

Originally posted by xanana
I am trying to return a resultset from a stored procedure and
currently have this working:

Code:

CREATE FUNCTION findAllUsers() RETURNING char(10), char(12); DEFINE u char(10); DEFINE p char(12); FOREACH cur1 WITH HOLD FOR SELECT user_id, user_password INTO u, p FROM user where user_id IN ('gavinb','manglu') RETURN u, p WITH RESUME; END FOREACH END FUNCTION;

The only problem is that the columns returned do not retain
their table column names. e.g.

Code:

> EXECUTE FUNCTION findAllUsers(); (expression) (expression) gavinb bong manglu ganesh 2 row(s) retrieved.

How can I modify my stored procedure to return a normal resultset
with metadata intact ... ?

Thanks

Xanana

This is not possible with the current versions of the informix databaseserver (9.3x, 7.31x).

AFAIK it is on the list of the most wanted features for future relases.

However I did not see it in the 9.40-Beta feature description. Sorry.

Maybe Jonathan Leffler from IBM (Informix) has more information on that, you should post your
query to "comp.databases.informix".

Best regards

Eric
--
IT-Consulting Herber
www.herber-consulting.de

Internet Services

Web Development

Digital Marketing

Consumer Tech