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.
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