Creating stored procedures for populating master and replica databases
Write operations in the synchronized architecture are typically implemented by using stored procedures. Stored procedures are useful for implementing business logic in the transactions in order to handle possible conflicts without violating the data integrity or business rules of the application. In the evaluation sample, stored procedures are used to insert and update data in both the master and replica databases.
The following sample scripts contain SQL statements to create stored procedures for inserting and updating data in the master and replica databases.
The procedure logic in the scripts is designed to be as simple as possible. The only logic for handling conflicts is implemented in the update procedure. If the update fails, then the row is inserted with status -1. Other values for status field are 1 for a tentative write at the replica database and 2 for the official data accepted by the master database.
Sample script: proced1.sql
If you are using the working directories and sample scripts in the samples\smartflow\ directory, go the solidDB installation root directory and run the following commands on both the master and replica database servers:
▪ -O eval.out is an optional parameter that defines the output file for results,
▪ "tcp 1315" is the network protocol and address of the server (you might need to customize this part of the command),
▪ dba and dba_password are user name and password respectively.
You can view the results in eval.out with any text editor.
--********************************************************* -- 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);
EXEC SQL CLOSE SYNCDEMO_INS; EXEC SQL DROP SYNCDEMO_INS;
END"; COMMIT WORK;
Sample script: proced2.sql
--********************************************************* -- 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 = ? ;