To ensure the physical and logical database consistency, you must write stored procedures using the solidDB® SQL stored procedure language. These procedures can use the parameter bulletin board to communicate with each other. In “INSERT_ORDER stored 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 may 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";