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 in the IBM Integration Bus documentation.
  2. In the Message Flow editor, add and rename the nodes listed in the following table.
    For instructions, see Adding a message flow node in the IBM Integration Bus documentation.
    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 message flow nodes in the IBM Integration Bus documentation.
    To check that you have connected the nodes together correctly, see the diagram 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 message flow node in the IBM Integration Bus documentation.
    Node name Page Property Value
    XML_CANCELRESERVATION_IN Basic Queue name XML_CANCELRESERVATION_IN
    (This value is the local queue from which the message flow takes the message.)
    Input Message Parsing Message domain XMLNSC : For XML messages (namespace aware, validation, low memory use)
    (This value tells the integration node to use the optimized generic XML parser.)
    DeleteReservation Basic Data source RESERVDB
    (This value is the database that is used by this node.)
    Basic ESQL module DeleteReservation
    (This value is the name of the ESQL module that is 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 must deal with database errors. If the check box is selected, when there is a database error the message flow, by default, produces an exception and passes the message to the FAIL queue.
    Trace Basic Destination Local Error Log
    (This value 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 value logs the ExceptionList tree part of the message.)
    Basic Message number 3051
    XML_CANCELRESERVATION_FAIL1_1 Basic Queue name
    (You are not required to specify the name of the queue manager because the queue is defined on the same queue manager as the integration node.)     		XML_CANCELRESERVATION_FAIL1
    (This value is the local queue on which the message flow puts the message if processing fails.)
    XML_CANCELRESERVATION_FAIL1_2 Basic Queue name
    (You are not required to specify the name of the queue manager because the queue is defined on the same queue manager as the integration node.)     		XML_CANCELRESERVATION_FAIL1
    (This value is the local queue on which the message flow puts the message if processing fails.)
    Trace1 Basic Destination Local Error Log
    (This value 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 value logs the Message and Environment tree part of the message.)
    Basic Message number 3051
    IncrementSeat Basic Data source RESERVDB
    (This value is the database that is used by this node.)
    Basic ESQL module IncrementSeat
    (This value is the name of the ESQL module that is 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 value 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 value logs the ExceptionList tree part of the message.)
    Basic Message number 3051
    XML_CANCELRESERVATION_FAIL2 Basic Queue name
    (You are not required to specify the name of the queue manager because the queue is defined on the same queue manager as the integration node.)     		XML_CANCELRESERVATION_FAIL2
    (This value is the local queue on which the message flow puts the message if processing fails.)
    XML_CANCELRESERVATION_OUT Basic Queue name
    (You are not required to specify the name of the queue manager because the queue is defined on the same queue manager as the integration node.)     		XML_CANCELRESERVATION_OUT
    (This value 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 in the IBM Integration Bus documentation. 
    -- ************************************************
-- * 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.XMLNSC.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 created the XML_CancelReservation message flow, which cancels reservations that the XML_Reservation message flow previously made.

Back to Building the Airline Reservations sample