solidDB Help : Replication : Advanced Replication : Using Advanced Replication with applications : Implementing Intelligent Transactions : Creating stored procedures
  
Creating stored procedures
To ensure the physical and logical database consistency, you must write stored procedures by using the solidDB SQL stored procedure language. These procedures can use the parameter bulletin board to communicate with each other. In the following procedure, the account balance update must not be made if the insert of the new order fails.
"CREATE PROCEDURE INSERT_ORDER
(ORDER_ID VARCHAR, CUST_ID VARCHAR, ...) BEGIN
  DECLARE ORDER_FAILED VARCHAR ;
  DECLARE CUST_OK INTEGER ;
  DECLARE STATUS VARCHAR ;
ORDER_FAILED := 'N' ;
  STATUS := 'OK' ;
-- Validate the order
-- For instance, it must have a valid customer
EXEC SQL PREPARE CHECK_CUST
CALL CHECK_CUSTOMER (?) ;
EXEC SQL EXECUTE CHECK_CUST
USING (CUST_ID)
INTO (CUST_OK) ;
IF CUST_OK = 0 THEN
ORDER_FAILED := 'Y' ;
STATUS := 'FAIL' ;
END IF ;
-- Other validation checking should go here...
-- ...
-- End of validation
-- If the validation fails, put a parameter to the bulletin board to
-- inform subsequent stored procedures about the validation failure.
IF ORDER_FAILED = 'Y'
THEN PUT_PARAM('ORDER_FAILED', 'Y');
END IF;
-- Insert the order row into the database. The STATUS value in the
-- row can be either 'OK' or 'FAIL'.
EXEC SQL PREPARE INS_ORD
INSERT INTO CUST_ORDER (ORD_ID, CUST_ID, STATUS, ...)
VALUES (?,?,? ...);
EXEC SQL EXECUTE INS_ORD
USING (ORD_ID, CUST_ID, STATUS...);
EXEC SQL CLOSE INS_ORD;
EXEC SQL DROP INS_ORD;
END";
The following procedure updates the balance of a given account:
"CREATE PROCEDURE UPDATE_CUST_CREDIT (ACC_NUM VARCHAR, AMOUNT FLOAT)
BEGIN
  DECLARE ORDER_FAILED VARCHAR;
-- Check from the bulletin board, whether the order
-- was inserted/modified successfully.
-- In case of failure, do not update the account balance
ORDER_FAILED := GET_PARAM('ORDER_FAILED');
IF ORDER_FAILED = 'Y' THEN
  RETURN
END IF;
EXEC SQL PREPARE UPD_CREDIT
  UPDATE ACCOUNT
  SET BALANCE = BALANCE + ?
WHERE ACC_NUM = ?;
EXEC SQL EXECUTE UPD_CREDIT USING (AMOUNT, ACC_NUM);
EXEC SQL CLOSE UPD_CREDIT;
EXEC SQL DROP UPD_CREDIT;
END";
Go up to
Implementing Intelligent Transactions