Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    1

    Question Unanswered: Returning rows from a stored Procedure (SPL)

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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.
    rws

  3. #3
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152

    Re: Returning rows from a stored Procedure (SPL)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •