Creating the XML_CancelReservation message flow

Use the following instructions to create the XML_CancelReservation message flow. For more detailed instructions, click the links provided at the end of each step.

To create and configure the XML_CancelReservation message flow:

  1. Create a new message flow called XML_CancelReservation.
    For instructions, see Creating a message flow.
  2. In the Message Flow editor, add and rename the nodes listed in the following table.
    For instructions, see Adding a node.
    Palette drawers Type of node Name of node
    WebSphere MQ MQInput XML_CANCELRESERVATION_IN
    WebSphere MQ MQOutput XML_CANCELRESERVATION_OUT
    WebSphere MQ MQOutput XML_CANCELRESERVATION_FAIL1_1
    WebSphere MQ MQOutput XML_CANCELRESERVATION_FAIL1_2
    WebSphere MQ MQOutput XML_CANCELRESERVATION_FAIL2
    Transformation Compute DeleteReservation
    Transformation Compute IncrementSeat
    Construction Trace Trace
    Construction Trace Trace1
    Construction Trace Trace2
  3. Connect the nodes together as shown in the following table.
    For instructions, see Connecting nodes.
    To check that you have connected the nodes together correctly, see the figure in About the XML_CancelReservation message flow.
    Node name Terminal Connect to
    XML_CANCELRESERVATION_IN Out DeleteReservation
    Catch XML_CANCELRESERVATION_FAIL1_1
    DeleteReservation Failure Trace
    Out Trace1
    Trace Out XML_CANCELRESERVATION_FAIL1_2
    Trace1 Out IncrementSeat
    IncrementSeat Failure Trace2
    Out XML_CANCELRESERVATION_OUT
    Trace2 Out XML_CANCELRESERVATION_FAIL2
  4. Configure the node properties as shown in the following table. Accept the default values for all properties unless an alternative value is shown in the table.
    For instructions, see Configuring a node.
    Node name Page Property Value
    XML_CANCELRESERVATION_IN Basic Queue name XML_CANCELRESERVATION_IN
    (This is the local queue from which the message flow takes the message.)
    Input Message Parsing Message domain XML : For XML messages
    (This tells the broker to use the generic XML parser.)
    DeleteReservation Basic Data source RESERVDB
    (This is the database used by this node.)
    Basic ESQL module DeleteReservation
    (This is the name of the ESQL module used by this node during processing. The name must match the name in the CREATE COMPUTE MODULE statement in the ESQL file.)
    Basic Throw exception on database error Clear the check box so that you can specify in the ESQL how the message flow should deal with database errors. If the check box is selected, when there is a database error, the message flow, by default, throws an exception and passes the message to the FAIL queue.
    Trace Basic Destination Local Error Log
    (This instructs the node to write the trace information the local error log. On Windows, the local error log is Event Viewer; on Linux, the local error log is syslog.)
    Basic Pattern ==== Error from Delete Reservation node ==================
    ${ExceptionList}

    (This logs the ExceptionList tree part of the message.)
    Basic Message number 3051
    XML_CANCELRESERVATION_FAIL1_1 Basic Queue name
    (You do not need to specify the name of the queue manager because the queue is defined on the same queue manager as the broker.)
    XML_CANCELRESERVATION_FAIL1
    (This is the local queue on which the message flow puts the message if processing fails.)
    XML_CANCELRESERVATION_FAIL1_2 Basic Queue name
    (You do not need to specify the name of the queue manager because the queue is defined on the same queue manager as the broker.)
    XML_CANCELRESERVATION_FAIL1
    (This is the local queue on which the message flow puts the message if processing fails.)
    Trace1 Basic Destination Local Error Log
    (This instructs the node to write the trace information to the local error log. On Windows, the local error log is Event Viewer; on Linux, the local error log is syslog.)
    Basic Pattern ========== Message after Delete Reservation node ===================
    ${Root}
    ${Environment}

    (This logs the Message and Environment tree part of the message.)
    Basic Message number 3051
    IncrementSeat Basic Data source RESERVDB
    (This is the database used by this node.)
    Basic ESQL module IncrementSeat
    (This is the name of the ESQL module used by this node during processing. The name must match the name in the CREATE COMPUTE MODULE statement in the XML_CancelReservation.esql file.)
    Trace2 Basic Destination Local Error Log
    (This instructs the node to write the trace information the local error log. On Windows, the local error log is Event Viewer; on Linux, the local error log is syslog.)
    Basic Pattern ======== Error from Increment Seat node ===================
    ${ExceptionList}


    (This logs the ExceptionList tree part of the message.)
    Basic Message number 3051
    XML_CANCELRESERVATION_FAIL2 Basic Queue name
    (You do not need to specify the name of the queue manager because the queue is defined on the same queue manager as the broker.)
    XML_CANCELRESERVATION_FAIL2
    (This is the local queue on which the message flow puts the message if processing fails.)
    XML_CANCELRESERVATION_OUT Basic Queue name
    (You do not need to specify the name of the queue manager because the queue is defined on the same queue manager as the broker.)
    XML_CANCELRESERVATION_OUT
    (This is the local queue on which the message flow puts the message.)
  5. In the XML Airline Message Flows project, double-click the ESQL file to open it in the ESQL editor. Copy and paste the following ESQL code modules to the ESQL file, then save the file. For more information, see Developing ESQL.
    -- ************************************************
    -- * ESQL for the XML_CancelReservation message flow
    -- ************************************************
    
    CREATE COMPUTE MODULE DeleteReservation
    	CREATE FUNCTION Main() RETURNS BOOLEAN
    	BEGIN
    		DECLARE SQLState1 CHARACTER;
    		DECLARE SQLErrorText1 CHARACTER;
    		DECLARE SQLCode1 INTEGER;
    		DECLARE SQLNativeError1 INTEGER;
    
    		SET OutputRoot = InputRoot;
    		DECLARE cancel REFERENCE TO InputRoot.XML.CancelReservation.ListOfReservations;
    		DECLARE I INTEGER 1;
    		DECLARE J INTEGER CARDINALITY(cancel.ReservationNumber[]);
    		WHILE I <= J DO -- loop through the reservations to be cancelled
    			-- check that the requested reservation exists
    			SET Environment.ListOfReservations.Info[I] = 
    				THE(SELECT T.CLASSTYPE, T.FLIGHTNO, T.FLIGHTDATE 
    					FROM Database.XMLPASSENGERTB AS T 
    					WHERE T.RESERVATIONNO = cancel.ReservationNumber[I]);
    			IF (SELECT COUNT(*) FROM Environment.ListOfReservations.Info[I].*[] AS I1) IS NULL THEN
    				THROW USER EXCEPTION CATALOG 'MyCatalog' MESSAGE 1234 VALUES('No record to delete');
    			END IF;
    
    			-- delete the record from the database
    			DELETE FROM Database.XMLPASSENGERTB AS T WHERE T.RESERVATIONNO = cancel.ReservationNumber[I];
    			SET I = I + 1;
    
    			SET SQLCode1 = SQLCODE;
    			IF SQLCode1 <> 0 THEN
    				SET SQLState1 = SQLSTATE;
    				SET SQLErrorText1 = SQLERRORTEXT;
    				SET SQLNativeError1 = SQLNATIVEERROR;
    				THROW USER EXCEPTION MESSAGE 2950 VALUES('The SQL State' , SQLState1 , SQLCode1 , SQLNativeError1 , SQLErrorText1);
    			END IF;
    		END WHILE;
    		RETURN TRUE;
    	END;
    END MODULE;
    
    CREATE COMPUTE MODULE IncrementSeat
    	CREATE FUNCTION Main() RETURNS BOOLEAN
    	BEGIN
    		DECLARE SQLState1 CHARACTER;
    		DECLARE SQLErrorText1 CHARACTER;
    		DECLARE SQLCode1 INTEGER;
    		DECLARE SQLNativeError1 INTEGER;
    
    		SET OutputRoot = InputRoot;
    		DECLARE I INTEGER 1;
    		DECLARE J INTEGER CARDINALITY(Environment.ListOfReservations.*[]);
    		-- loop through the cancellations, updating the seat availability
    		WHILE I <= J DO
    			IF Environment.ListOfReservations.Info[I].CLASSTYPE = 'Y' THEN
    				UPDATE Database.XMLFLIGHTTB AS T 
    					SET ECONOMICCLASS = T.ECONOMICCLASS-1, TOTALECONOMIC = T.TOTALECONOMIC+1
    					WHERE T.FLIGHTNO = Environment.ListOfReservations.Info[I].FLIGHTNO
    					AND T.FLIGHTDATE = Environment.ListOfReservations.Info[I].FLIGHTDATE;
    			END IF;
    			IF Environment.ListOfReservations.Info[I].CLASSTYPE = 'F' THEN
    				UPDATE Database.XMLFLIGHTTB AS T 
    					SET FIRSTCLASS = T.FIRSTCLASS-1, TOTALFIRST= T.TOTALFIRST+1
    					WHERE T.FLIGHTNO = Environment.ListOfReservations.Info[I].FLIGHTNO
    					AND T.FLIGHTDATE = Environment.ListOfReservations.Info[I].FLIGHTDATE;
    			END IF;
    			SET I = I + 1;
    
    			SET SQLCode1 = SQLCODE;
    			IF SQLCode1 <> 0 THEN
    				SET SQLState1 = SQLSTATE;
    				SET SQLErrorText1 = SQLERRORTEXT;
    				SET SQLNativeError1 = SQLNATIVEERROR;
    				THROW USER EXCEPTION MESSAGE 2950 VALUES('The SQL State' , SQLState1 , SQLCode1 , SQLNativeError1 , SQLErrorText1);
    			END IF;
    		END WHILE;
    		RETURN TRUE;
    	END;
    END MODULE;
  6. Save the message flow.
You have now created the XML_CancelReservation message flow, which cancels reservations that the XML_Reservation message flow previously made.

Main Page icon   Back to Building the Airline Reservations sample.