Creating stored procedures for populating master and replica databases

Write operations in the synchronized architecture are typically implemented using stored procedures. Stored procedures are useful for implementing business logic in the transactions to handle possible conflicts without violating the application’s data integrity and business rules. In the evaluation sample, stored procedures are used to insert and update data in both the master and replica databases.

.\bin\solsql -O eval.out "tcp 1315" dba dba_password .\samples\ replication\eval_setup\proced1.sql

.\bin\solsql -O eval.out "tcp 1315" dba dba_password .\samples\ replication\eval_setup\proced2.sql

--*********************************************************
-- proced1.sql
-- Creates a procedure SYNCDEMO_INSERT for inserting data
-- into the sample table SYNCDEMO and inserts one row.
--
-- Execute in both the MASTER and REPLICA DBs.
--
-- Note: This has no validation rules. Duplicates are handled
-- by ignoring the duplicate insert. For simplicity, there
-- is no processing for errors. An actual application should
-- have logic for handling unique key constraint violations
-- and other possible error situations.
--*********************************************************
SET CATALOG sync_demo_catalog ;
"CREATE PROCEDURE SYNCDEMO_INSERT
  (MACHINEID INTEGER,
  ID INTEGER,
  INTDATA INTEGER,
  TEXTDATA CHAR(20),
  UPDATETIME TIMESTAMP,
  TARGETDB CHAR(1))
RETURNS
  (SUCCESS INTEGER, ROWS_AFFECTED INTEGER)
BEGIN
  DECLARE STATUS INTEGER ;
  IF TARGETDB = 'R' THEN
    STATUS := 1 ;
      ELSE IF TARGETDB = 'M' THEN
      STATUS := 2;
    ELSE
      STATUS := -1;
    END IF;
  END IF;

  EXEC SQL PREPARE SYNCDEMO_INS
  INSERT INTO SYNCDEMO
  (REPLICAID,ID,STATUS,INTDATA,TEXTDATA,UPDATETIME)   VALUES(?,?,?,?,?,?);

  EXEC SQL EXECUTE SYNCDEMO_INS USING   (MACHINEID,ID,STATUS,INTDATA,TEXTDATA,UPDATETIME);

  SUCCESS := SQLSUCCESS;
  ROWS_AFFECTED := SQLROWCOUNT;

  EXEC SQL CLOSE SYNCDEMO_INS;
  EXEC SQL DROP SYNCDEMO_INS;

END";
COMMIT WORK;

--*********************************************************
-- proced2.sql
-- Creates a procedure SYNCDEMO_UPDATE for updating data of
-- the sample table SYNCDEMO and updates one row.
--
-- Execute in both the MASTER and REPLICA DBs.
-- Validation rule: If the timestamp has changed, the update
-- is changed to insert with parameter 'targetdb' as 'f'.
-- this will result in an insert with the status of -1 in
-- the called procedure syncdemo_insert.
--
-- An actual application needs to handle an almost
-- unlimited number of conflicts as well as other
-- transaction validation errors.
--
-- The procedure takes a parameter value for the column ID
-- instead of using a sequence value. Also, a parameter
-- value is provided for the database ID. In an actual
-- application, the column ID should contain a sequence value.
--*********************************************************
SET CATALOG sync_demo_catalog ;
"CREATE PROCEDURE SYNCDEMO_UPDATE
(
MACHINEID INTEGER,
ID INTEGER,
INTDATA INTEGER,
TEXTDATA CHAR(20),
UPDATETIME TIMESTAMP,
TARGETDB CHAR(1)
)
RETURNS
(SUCCESS INTEGER, ROWS_AFFECTED INTEGER)
BEGIN
DECLARE TMPSTR VARCHAR;
DECLARE TNOW TIMESTAMP;
DECLARE DSTAT INTEGER;
DECLARE STATUS INTEGER;
IF TARGETDB = 'R' THEN
STATUS := 1;
ELSE
STATUS := 2;
END IF;
TNOW := NOW();
TMPSTR := 'R';
DSTAT := -1;

EXEC SQL PREPARE SYNCDEMO_UPD
UPDATE SYNCDEMO SET
  STATUS = ?,
  INTDATA = ?,
  TEXTDATA = ?,
  UPDATETIME = ?
WHERE
  REPLICAID = ? AND
  ID = ? AND
  UPDATETIME = ? ;

EXEC SQL EXECUTE SYNCDEMO_UPD USING
  (STATUS, INTDATA, TEXTDATA, TNOW, MACHINEID, ID, UPDATETIME);
  SUCCESS := SQLSUCCESS ; ROWS_AFFECTED := SQLROWCOUNT;

IF (SUCCESS = 1) AND (ROWS_AFFECTED = 0) THEN
  TMPSTR := 'F' ;

  EXEC SQL PREPARE SYNC_UPD1 CALL SYNCDEMO_INSERT(?,?,?,?,?,?);
  EXEC SQL EXECUTE SYNC_UPD1 USING
    (MACHINEID, ID, INTDATA, TEXTDATA, TNOW, TARGETDB);
  EXEC SQL FETCH SYNC_UPD1 ;
  SUCCESS := SQLSUCCESS;
  ROWS_AFFECTED := SQLROWCOUNT;
END IF ;

END";
COMMIT WORK;