Oninit® SQLMorph — sp_validcache

Source: examples/sp_validcache.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 sp_validcache(50.0)
-- @test EXECUTE FUNCTION sp_validcache(-5.0)
-- @test EXECUTE FUNCTION sp_validcache(150.0)
CREATE PROCEDURE sp_validcache(p_cache SMALLFLOAT)
RETURNING SMALLFLOAT;

    DEFINE l_sqlerr    INTEGER;
    DEFINE l_isamerr   INTEGER;
    DEFINE l_str       CHAR(256);

    ON EXCEPTION SET l_sqlerr, l_isamerr, l_str
        INSERT INTO errorlog
            VALUES(CURRENT, l_sqlerr, l_isamerr, "sp_validcache:" || DBINFO('sessionid') || ":"|| TRIM(p_cache) ||":" || TRIM(l_str));
        RAISE EXCEPTION l_sqlerr;
    END EXCEPTION

    IF p_cache < 0 THEN
        LET p_cache = 0;
    END IF
    IF p_cache > 100 THEN
        LET p_cache = 100;
    END IF

    RETURN p_cache;

END PROCEDURE;

C UDR — sp_validcache.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"

UDREXPORT mi_real sp_validcache(mi_real p_cache, MI_FPARAM *fparam)
{
    mi_integer l_sqlerr;
    mi_integer l_isamerr;
    mi_string * l_str = NULL;
    MI_CONNECTION *conn = NULL;
    mi_integer rc;
    mi_integer _exc_sqlcode = 0;
    mi_integer _exc_isamerr = 0;
    const char *_exc_msg = "";

    if ((conn = (MI_CONNECTION *)mi_fp_funcstate(fparam)) == NULL )
    {
        if ((conn =  mi_open(NULL, NULL, NULL )) == (MI_CONNECTION *) NULL )
            ONIERR_002("mi_open() failed");
        mi_fp_setfuncstate(fparam, (void *)conn);
    }
    DBDK_TRACE_ENTER(__FUNCTION__);
    if ((p_cache < 0)) {
        p_cache = 0;
    }
    if ((p_cache > 100)) {
        p_cache = 100;
    }
    DBDK_TRACE_EXIT(__FUNCTION__);
    if (mi_fp_funcstate(fparam) == NULL && conn) mi_close(conn);
    return p_cache;

    _exc_handler:;
    l_sqlerr = (mi_integer)_exc_sqlcode;
    l_isamerr = (mi_integer)_exc_isamerr;
    l_str = (mi_string *)(_exc_msg ? (char *)_exc_msg : "");
    /*
     * INSERT INTO errorlog
     *             VALUES(CURRENT, l_sqlerr, l_isamerr, "sp_validcache:" || DBINFO('sessionid') || ":"|| TRIM(p_cache) ||":" || TRIM(l_str))
     */
    ONI_EXEC(conn, oni_clean_sql("INSERT INTO errorlog\n            VALUES(CURRENT, l_sqlerr, l_isamerr, \"sp_validcache:\" || DBINFO('sessionid') || \":\"|| TRIM(p_cache) ||\":\" || TRIM(l_str))"), "insert failed", 0, 0);
    ONIERR_004(l_sqlerr, "raised by SPL");
}

Install SQL — sp_validcache_install.sql

-- sqlmorph — Informix install SQL for sp_validcache. 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 PROCEDURE IF EXISTS sp_validcache;

CREATE PROCEDURE sp_validcache(p_cache SMALLFLOAT) RETURNING SMALLFLOAT
EXTERNAL NAME '$INFORMIXDIR/extend/sqlmorph/sqlmorph.so(sp_validcache)'
LANGUAGE C
NOT VARIANT;