Oninit® SQLMorph — iter_orders

Source: examples/sp_iter_orders.sql. Original SPL above, sqlmorph-generated Informix C UDR in the middle, and the CREATE FUNCTION script that registers the UDR under the same procedure name at the bottom.

SPL

-- @test EXECUTE FUNCTION iter_orders(5)
CREATE FUNCTION iter_orders(month_num INT) RETURNING INT;
  DEFINE Num INT;
  DEFINE ord_num INT;
  LET Num = 1;
  FOREACH cursor1 FOR
    SELECT order_num INTO ord_num FROM orders
       WHERE month(order_date) = month_num
    RETURN Num WITH RESUME;
    LET Num = Num + 1;
  END FOREACH;
END FUNCTION;

C UDR — iter_orders.c

/* sqlmorph — Informix C UDR emitter. Built by Oninit LLC. */

#include <mi.h>
#include <miami.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include "standard.h"

typedef struct {
    mi_integer phase;
    MI_CONNECTION *conn;
    mi_integer Num;
    mi_integer ord_num;
    MI_STATEMENT *_cur1_st;
} iter_state_t;

UDREXPORT mi_integer iter_orders(mi_integer month_num, MI_FPARAM *fparam)
{
    iter_state_t *state;
    mi_integer rc;

    switch (mi_fp_request(fparam))
    {
        case SET_INIT:
            DBDK_TRACE_ENTER(__FUNCTION__);
            state = (iter_state_t *)mi_alloc((mi_integer)sizeof(iter_state_t), PER_COMMAND);
            if (state == NULL) ONIERR_002("iterator state alloc failed");
            memset(state, 0, sizeof(iter_state_t));
            if ((state->conn =  mi_open(NULL, NULL, NULL )) == (MI_CONNECTION *) NULL )
                ONIERR_002("mi_open() failed");
            state->Num = 1;
            state->_cur1_st = mi_prepare(state->conn, oni_clean_sql("SELECT order_num FROM orders\n       WHERE month(order_date) = month_num"), NULL);
            ONI_EXEC_PREP(state->conn, state->_cur1_st, 0, MI_SEND, 0, NULL, NULL, NULL, NULL, 0, NULL, "iterator cursor exec failed", 0);
            mi_fp_setfuncstate(fparam, (void *)state);
            return 0;

        case SET_RETONE:
            state = (iter_state_t *)mi_fp_funcstate(fparam);
            if (state->phase >= 1)
            {
                state->Num = (state->Num + 1);
            }
            if (mi_get_result(state->conn) != MI_ROWS)
            {
                if (state->_cur1_st) mi_close_statement(state->_cur1_st);
                mi_fp_setisdone(fparam, MI_TRUE);
                return 0;
            }
            {
                MI_ROW *_row = mi_next_row(state->conn, &rc);
                if (_row == NULL)
                {
                    if (state->_cur1_st) mi_close_statement(state->_cur1_st);
                    mi_fp_setisdone(fparam, MI_TRUE);
                    return 0;
                }
                state->ord_num = *((mi_integer *) mi_value(_row, 0, NULL, NULL));
                state->phase = 1;
                return state->Num;
            }

        case SET_END:
            DBDK_TRACE_EXIT(__FUNCTION__);
            state = (iter_state_t *)mi_fp_funcstate(fparam);
            if (state && state->_cur1_st) mi_close_statement(state->_cur1_st);
            return 0;

        default:
            ONIERR_002("unknown iterator request flag");
            return 0;
    }
}

Install SQL — iter_orders_install.sql

-- sqlmorph — Informix install SQL for iter_orders. Built by Oninit LLC.
--
-- Drops the existing SPL of the same name (if any) and registers the
-- C UDR shared library in its place. All UDRs in a directory link into
-- one shared object (sqlmorph.so by default). Fetch the canonical
-- Makefile via:
--     wget https://www.oninit.com/sqlmorph/Makefile
-- then run:
--     make                                  # builds sqlmorph.so
--     make IFXDB=yourdb install             # registers this procedure
--
-- Override the library name with LIBNAME on the make command line if you
-- want a different .so name; if you do, update the EXTERNAL NAME below
-- to match.

DROP FUNCTION IF EXISTS iter_orders;

CREATE FUNCTION iter_orders(month_num INT) RETURNING INT
WITH (ITERATOR)
EXTERNAL NAME '$INFORMIXDIR/extend/sqlmorph/sqlmorph.so(iter_orders)'
LANGUAGE C
NOT VARIANT;