Oninit® SQLMorph — sp_metricid

Source: examples/sp_metric1.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

CREATE PROCEDURE sp_metricid(p_server CHAR(128), p_metric CHAR(20), p_mode SMALLINT)
RETURNING INT

    DEFINE l_id         INTEGER;
    DEFINE l_serverid   INTEGER;
    DEFINE l_metricid   INTEGER;
    DEFINE l_str        LVARCHAR;
    DEFINE l_desc       VARCHAR(128);

    --SET DEBUG FILE TO "/tmp/sp_metric.dbg" WITH APPEND;
    --TRACE ON;

    SELECT id
        INTO l_serverid
        FROM oni_server
            WHERE nagios = p_server;

    IF DBINFO('sqlca.sqlerrd2') = 0 THEN
        RAISE EXCEPTION -746, 0, "ServerID not found";
    ELSE
        SELECT oni_metric.id, NVL(oni_perf.id, 0)
            INTO l_metricid, l_id
            FROM oni_metric, outer(oni_perf)
            WHERE oni_perf.metricid = oni_metric.id
                AND oni_metric.metric = p_metric
                AND serverid = l_serverid;
        IF l_metricid is NULL THEN
            IF p_mode = 0 THEN
            RAISE EXCEPTION -746, 0, "Metric not found: " || TRIM(p_metric);
            ELSE
                INSERT INTO oni_metric
                    VALUES(0,p_metric,p_metric);
                LET l_id = DBINFO('sqlca.sqlerrd1');
            END IF
        END IF
        IF l_id = 0 THEN
            TRACE l_metricid || ":" || l_serverid || ":" || p_metric;
            INSERT INTO oni_perf(id, metricid, serverid, series)
                VALUES(0, l_metricid, l_serverid, TSCreate("ts_5min", "2018-01-01 00:00:00.00000", 0, 0, 0, "metric_rt"));
            LET l_id = DBINFO('sqlca.sqlerrd1');
        END IF
        RETURN l_id;
    END IF

END PROCEDURE;

C UDR — sp_metricid.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_integer sp_metricid(mi_string *p_server, mi_string *p_metric, mi_smallint p_mode, MI_FPARAM *fparam)
{
    mi_integer l_id;
    mi_integer l_serverid;
    mi_integer l_metricid;
    mi_lvarchar * l_str = NULL;
    mi_string * l_desc = NULL;
    MI_CONNECTION *conn = NULL;
    mi_integer rc;

    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__);
    /*
     * SELECT id
     *         INTO l_serverid
     *         FROM oni_server
     *             WHERE nagios = p_server
     */
    ONI_EXEC(conn, oni_clean_sql("SELECT id\n        INTO l_serverid\n        FROM oni_server\n            WHERE nagios = p_server"), "select_into failed", 0, 0);
    if ((DBINFO("sqlca.sqlerrd2") == 0)) {
        ONIERR_004((-746), "ServerID not found");
    } else {
        /*
         * SELECT oni_metric.id, NVL(oni_perf.id, 0)
         *             INTO l_metricid, l_id
         *             FROM oni_metric, outer(oni_perf)
         *             WHERE oni_perf.metricid = oni_metric.id
         *                 AND oni_metric.metric = p_metric
         *                 AND serverid = l_serverid
         */
        ONI_EXEC(conn, oni_clean_sql("SELECT oni_metric.id, NVL(oni_perf.id, 0)\n            INTO l_metricid, l_id\n            FROM oni_metric, outer(oni_perf)\n            WHERE oni_perf.metricid = oni_metric.id\n                AND oni_metric.metric = p_metric\n                AND serverid = l_serverid"), "select_into failed", 0, 0);
        if ((l_metricid == NULL)) {
            if ((p_mode == 0)) {
                ONIERR_004((-746), oni_concat((const char *)("Metric not found: "), (const char *)(TRIM(p_metric))));
            } else {
                /*
                 * INSERT INTO oni_metric
                 *                     VALUES(0,p_metric,p_metric)
                 */
                ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_metric\n                    VALUES(0,p_metric,p_metric)"), "insert failed", 0, 0);
                l_id = DBINFO("sqlca.sqlerrd1");
            }
        }
        if ((l_id == 0)) {
            DPRINTF("oninit", 11, ("%s: %d:%d:%s", __FUNCTION__, l_metricid, l_serverid, p_metric));
            /*
             * INSERT INTO oni_perf(id, metricid, serverid, series)
             *                 VALUES(0, l_metricid, l_serverid, TSCreate("ts_5min", "2018-01-01 00:00:00.00000", 0, 0, 0, "metric_rt"))
             */
            ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf(id, metricid, serverid, series)\n                VALUES(0, l_metricid, l_serverid, TSCreate(\"ts_5min\", \"2018-01-01 00:00:00.00000\", 0, 0, 0, \"metric_rt\"))"), "insert failed", 0, 0);
            l_id = DBINFO("sqlca.sqlerrd1");
        }
        DBDK_TRACE_EXIT(__FUNCTION__);
        if (mi_fp_funcstate(fparam) == NULL && conn) mi_close(conn);
        return l_id;
    }
    DBDK_TRACE_EXIT(__FUNCTION__);
    if (mi_fp_funcstate(fparam) == NULL && conn) mi_close(conn);
}

Install SQL — sp_metricid_install.sql

-- sqlmorph — Informix install SQL for sp_metricid. 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_metricid;

CREATE PROCEDURE sp_metricid(p_server CHAR(128), p_metric CHAR(20), p_mode SMALLINT) RETURNING INT
EXTERNAL NAME '$INFORMIXDIR/extend/sqlmorph/sqlmorph.so(sp_metricid)'
LANGUAGE C
NOT VARIANT;