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";