Sample Script for Custom Attached Data

This page presents a sample SQL script to create a custom dispatcher stored procedure—gudCustDISP1 or gudCustDISP2—and a custom attached data storage table in your Interaction Database (IDB) schema.

The sample script in this appendix is for Microsoft SQL Server (MSSQL). After you install the Interaction Concentrator (ICON) application, sample scripts for each supported RDBMS type–SampleProc_<db_type>.sql–are available in the scripts subfolder in the directory to which you installed the application.

Tip

Carefully verify the syntax and operation of your modified gudCustDISP1 or gudCustDISP2 stored procedure. Any types of errors or RDBMS violations that the custom dispatcher stored procedure produces can affect ICON processing of all other attached data for voice calls and multimedia interactions.

The following MSSQL sample script illustrates how you can create a custom attached data storage table (G_SAMPLE_CUST_ADATA) and modify the gudCustDISP1 or gudCustDISP2 stored procedure in the CoreProcedures_<db_type>.sql script. The modified stored procedure stores arguments in the G_SAMPLE_CUST_ADATA table.

Sample Custom Dispatcher
/*==============================================================*/
/* Table: G_SAMPLE_CUST_ADATA                                   */
/*==============================================================*/
create table G_SAMPLE_CUST_ADATA (
   ID                   numeric(16)          identity,
   CALLID               varchar(50)          not null,
   CALL_TS              int                  not null,
   SWITCH_ID            int                  not null,
   TENANT_ID            int                  not null,
   C_INT_1              int                  null,
   C_INT_2              int                  null,
   ...
   C_INT_34             int                  null,
   C_STR_1              varchar(10)          null,
   C_STR_2              varchar(10)          null,
   ...
   C_STR_34             varchar(10)          null,
   GSYS_DOMAIN          int                  null,
   GSYS_PARTITION       int                  null,
   GSYS_SYS_ID          int                  null,
   GSYS_SEQ             bigint               null,
   GSYS_USEQ            bigint               null,
   GSYS_TS              datetime             null,
   GSYS_TC              int                  null,
   GSYS_EXT_VCH1        varchar(255)         null,
   GSYS_EXT_VCH2        varchar(255)         null,
   GSYS_EXT_INT1        int                  null,
   GSYS_EXT_INT2        int                  null,
   constraint PK_G_SAMPLE_CUST_ADATA primary key  (ID)
)
go

/*==============================================================*/
/* Index: IDX_G_SAMPLE_CDATA_TS                                 */
/*==============================================================*/
create   index IDX_G_SAMPLE_CDATA_TS on G_SAMPLE_CUST_ADATA (
CALL_TS ASC
)
go

-- ============================================================= --
-- Name:   -- gudCustDISP1 --
-- Group:  User data related procedures
-- Brief:  -- gudCustDISP DISPATCH --
-- ============================================================= --
DROP PROCEDURE gudCustDISP1
go

CREATE PROCEDURE gudCustDISP1 
        @GROUPID                INTEGER,
        @CALLID                 VARCHAR(64),
        @P_CALL_TS              INTEGER,
        @SWITCHID               INTEGER,
        @TENANTID               INTEGER,
        @TS_S                   INTEGER,
        @TS_MS                  INTEGER,
        @P_STR_1                VARCHAR(255),
        @P_STR_2                VARCHAR(255),
        ...
        @P_STR_17               VARCHAR(255),
        @P_INT_1                INTEGER,
        @P_INT_2                INTEGER,
        ...
        @P_INT_17               INTEGER,
        @P_STR_RES1             VARCHAR(255),
        @P_STR_RES2             VARCHAR(255),
        @P_STR_RES3             VARCHAR(64),
        @P_STR_RES4             VARCHAR(255),
        @P_INT_RES1             INTEGER,
        ...
        @P_INT_RES7             INTEGER,
        @DOMAIN                 INTEGER,
        @PARTITION              INTEGER,
        @SYS_ID                 INTEGER,
        @SYS_SEQN               NUMERIC(20,0),
        @SYS_TS                 DATETIME
AS
BEGIN

    -- Insert first portion in the long table --
    IF (@GROUPID = 1)
    BEGIN
        INSERT INTO G_SAMPLE_CUST_ADATA(
                CALLID,
                CALL_TS,
                SWITCH_ID,
                TENANT_ID,
                C_INT_1,
                C_INT_2,
                ...
                C_INT_17,
                C_STR_1,
                C_STR_2,
                ...
                C_STR_17,
                GSYS_DOMAIN,
                GSYS_PARTITION,
                GSYS_SYS_ID,
                GSYS_SEQ,
                GSYS_USEQ,
                GSYS_TS)
        VALUES (
                @CALLID,
                @P_CALL_TS,
                @SWITCHID,
                @TENANTID,
                @P_INT_1,
                @P_INT_2,
                ...
                @P_INT_17,
                @P_STR_1,
                @P_STR_2,
                ...
                @P_STR_17,
                @DOMAIN,
                @PARTITION,
                @SYS_ID,
                @SYS_SEQN,
                0,
                @SYS_TS)

    END

    -- Update record and specify more fields --
    ELSE
    IF (@GROUPID = 2)
    BEGIN
        UPDATE G_SAMPLE_CUST_ADATA SET
                C_INT_18                 = @P_INT_1,
                ...
                C_INT_34                 = @P_INT_17,
                C_STR_18                 = @P_STR_1,
                C_STR_19                 = @P_STR_2,
                ...
                C_STR_34                 = @P_STR_17,
                GSYS_USEQ                = @SYS_SEQN
        WHERE CALLID = @CALLID AND CALL_TS = @P_CALL_TS


    END
END
go