Datasource returned from Informix Stored Proc

Question

I have an Informix stored procedure that returns two columns and multiple rows. I can use "EXECUTE FUNCTION curr_sess(2009,'SP')" fine, but how do I get the results into a temp table.

EDIT: We are on version 10.00.HC5

---

Testing Jonathan Leffler's idea didn't work.

EXECUTE FUNCTION curr_sess(2009,'SP')

works fine. Then I did

CREATE TEMP TABLE t12(yr smallint, sess char(4));

But when I try

INSERT INTO t12 EXECUTE FUNCTION curr_sess(2009,'SP');

It doesn't work, I get a " Illegal SQL statement in SPL routine." error.

---

The source for curr_sess

begin procedure

DEFINE _yr smallint;
DEFINE _sess char(4);

SELECT
    DISTINCT
    sess_vw.yr,
    sess_vw.sess,
    sess_vw.sess_sort
FROM
    sess_vw
ORDER BY
    sess_vw.sess_sort DESC
INTO temp tmp_sess WITH NO LOG;

SELECT
    FIRST 1
    tmp_sess.yr,
    tmp_sess.sess
FROM
    tmp_sess
WHERE
    tmp_sess.sess_sort = sess_sort(iYear,sSess)
INTO temp tmp_final WITH NO LOG;

FOREACH cursor1 FOR
    SELECT
        tmp_final.yr,
        tmp_final.sess
    INTO
        _yr,
        _sess
    FROM
         tmp_final
    RETURN _yr, _sess WITH RESUME;
END FOREACH;

DROP TABLE tmp_sess;
DROP TABLE tmp_final;

end procedure

---

EDIT: sess_sort() does a lookup.

I have tried to rewrite the function as one query. Here is next_sess:

SELECT
    FIRST 1
    sess_vw.sess_sort
FROM
    sess_vw
WHERE
    sess_vw.sess_sort > sess_sort(2009,'SP')
ORDER BY
    sess_vw.sess_sort ASC

Someone from IBM emailed me and suggested using something like this:

SELECT
    *
FROM
    TABLE(next_sess(2009,'SP'))

But that still didn't work.

Answer 1

One possibility is a stored procedure. Another (tested on IDS 11.50.FC1), which I wasn't sure would work, is:

CREATE PROCEDURE r12() RETURNING INT, INT;
   RETURN 1, 2 WITH RESUME;
   RETURN 2, 3 WITH RESUME;
END PROCEDURE;

CREATE TEMP TABLE t12(c1 INT, c2 INT);

INSERT INTO t12 EXECUTE PROCEDURE r12();

The last line is the important one.

---

Given the observation that the stored procedure cannot be executed as shown just above (because it contains some non-permitted SQL statement), then you need to use stored procedures another way - illustrated by this test code (which works: worked first time, which pleasantly surprised me):

CREATE TEMP TABLE t12(yr smallint, sess char(4));

CREATE PROCEDURE curr_sess(yearnum SMALLINT, sesscode CHAR(2))
    RETURNING SMALLINT AS yr, CHAR(4) AS sess;
    RETURN yearnum, (sesscode || 'AD') WITH RESUME;
    RETURN yearnum, (sesscode || 'BC') WITH RESUME;
END PROCEDURE;

CREATE PROCEDURE r12(yearnum SMALLINT, sesscode CHAR(2))
    DEFINE yr SMALLINT;
    DEFINE sess CHAR(4);
    FOREACH EXECUTE PROCEDURE curr_sess(yearnum, sesscode) INTO yr, sess
    INSERT INTO t12 VALUES(yr, sess);
    END FOREACH;
END PROCEDURE;

EXECUTE PROCEDURE r12(2009,'SP');

SELECT * from t12;

You could incorporate the creation of the temp table into the stored procedure; you could even arrange to drop a pre-existing table with the same name as the temp table (use exception handling). Given that you're using IDS 10.00, you are stuck with a fixed name for the temp table. It would be possible, though not recommended (by me) to use the dynamic SQL facility in 11.50 to name the temp table at runtime.

Be aware that stored procedures that access temporary tables get reoptimized when reused - the table that is used is not the same as the last time (because it is a temporary) so the query plan isn't all that much help.

Answer 2

That fails possibly because the 'drop table' is not valid statment in a procedure that is used in this context? http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids_sqs_1755.htm#ids_sqs_1755