Configuring the message flow

Configure a mapping node to use the stored procedure.

1. In the IBM App Connect Enterprise Toolkit, expand the Stored_Procedures application and, in the Flows folder, double-click StoredProcedures_Flow.msgflow to open the message flow.
2. Click the HTTP Input node, and view its basic properties.
   • On the Basic tab, Path suffix for URL is set to /scenario. The entry cannot be left blank but, because you will use the Flow Exerciser to test the message flow, the path is not used.
   • On the Input Message Parsing tab, Message domain is set to XMLNSC.
3. From the Transformation drawer, drag a Mapping node onto the canvas, and double-click the node.
4. On the "Specify a new message map file" window, click Next.
5. For both map inputs and map outputs, fully expand StoredProcedures_Lib.
   1. For input, select the Employee schema.
   2. For output, select the EmployeeData schema.
   
6. Click Next, and then Finish.
   The map opens.
7. Connect Employee to EmployeeData.
   A local map is created.
8. Click Local map.
9. Right-click a blank area of the canvas, and select Database > Call Database Routine.
10. In the opened editor, if the CLIENTS database is not displayed, click Add database and select it from the data design projects. Then, click Make available, and then OK.
    
11. In the opened editor, expand Stored Procedure, and then click EMPLOYEEDETAILS(2).
    Notice the following points:

    • The four result sets (cursors) that are defined in the procedure are now shown in the Result sets list.
    • For each result set, you can select the table columns to be defined in the set.
    • You can reorder result sets.

    
12. You now define the columns that ResultSet1 returns.
    The columns that you select correspond to the columns that are declared in the cursors in the stored procedure.
    1. Click ResultSet1.
    2. Expand the EMPLOYEE table, and select the EMPNO, FIRSTNME, LASTNAME, and WORKDEPT columns.
    3. In the Description field, enter Select from EMPLOYEE.
    Your screen looks as shown in this screen capture:

    
13. Configure ResultSet2 as follows:
    • Expand table DEPARTMENT, and then select columns DEPTNO and DEPTNAME.
    • In the Description field, type Select from DEPARTMENT.
14. Configure ResultSet3 as follows:
    • Expand table EMPPROJACT, and then select column PROJNO.
    • In the Description field, type Select from EMPPROJACT.
15. Configure ResultSet4 as follows:
    • Expand table PROJECT, and then select column PROJNAME.
    • In the Description field, type Select from PROJECT.
16. Click OK.
    You return to the map, which now contains Database Routine and Return submaps.
17. Connect EMPL_NUM to Database Routine, and connect Return to EmployeeData, as shown in the following figure.
    The Database Routine nested map runs if the Mapping node successfully calls the database to run the stored procedure. You use the Return nested map to map the information that the stored procedure returns into your message. If the call to the database fails, the Mapping node throws an exception that includes the returned database error.

    
18. Click Database Routine, and connect EMPL_NUM to EMPLIKENOIN (the input parameter for the routine).
19. Click the yellow arrow to go up a level.
20. Click the Return transform to navigate into the nested return mapping, and expand the result sets.
    The result sets contain the columns that you selected in steps 12 to 15.
21. Make the following mappings.

    • DESCRIPTIONOUT -> ProcedureOutput

    • EMPNO -> EmployeeNo

    • FIRSTNME -> FirstName

    • LASTNAME -> LastName

    • DEPTNAME -> Department

    • PROJNO -> ProjectNo

    • PROJNAME -> ProjectName

    When you finish, your screen looks like the following image.

    
22. Optional: Click the light bulb against each mapping, and select Set cardinality to first index to remove the warning triangles.
    The warnings appear because the mapper does not know the limitations on the database rows. For example, in the output, a single value is expected for EmployeeNo, but the stored procedure could return multiple rows of the mapped input column EMPNO. The design of the stored procedure ensures that only one row is returned, and so you can set the cardinality to 1.
23. Save and close the map.
24. Wire the HTTP Input node and the HTTP Reply node to the Mapping node.
    1. Connect the HTTP Input node Out terminal to the Mapping node In terminal.
    2. Connect the Mapping node Out terminal to the HTTP Reply node In terminal.
    The flow looks like the following image.

    
25. Save the message flow.