Calling a stored procedure that returns a result set in IBM WebSphere Cast Iron Studio

Learn how to access the results of a database stored procedure in IBM® WebSphere® Cast Iron Studio by using the CopyOf function and an XML schema.

Share:

Katherine Sanders , Lab Services Consultant, IBM China

Katherine Sanders Katherine Sanders is a Lab Services Consultant for IBM Software Services for WebSphere in Hursley, United Kingdom. She has worked at IBM for 5 years and is currently specializing in IBM WebSphere Cast Iron Cloud Integration. She also has a strong background in software development and the WebSphere product portfolio. Katherine holds a Bachelor of Science degree in Computer Science from the University of Nottingham.



02 May 2012

Also available in Chinese

Introduction

IBM WebSphere Cast Iron Studio (hereafter called Studio) is used to develop orchestrations that call database stored procedures. Stored procedures commonly return a result set containing the results of an SQL select statement without specifying the names and types of the individual fields. In these cases, the IBM WebSphere Cast Iron Cloud Integration (hereafter called Cast Iron) developer must create an XML schema to tell Studio the structure of the results. This tutorial explains how to create the XML schema and contains a step-by-step example.

Prerequisites

This tutorial is intended for application developers. It contains detailed steps for every stage of the sample application development, so even beginners can complete it. However, prior knowledge of the Extensible Markup Language (XML), XML Schema, and Extensible Stylesheet Language Transformations (XSLT) standards will increase your understanding of the material.

System requirements

This tutorial uses the following software:

  • IBM WebSphere Cast Iron Studio V6.1.0.3 or later
  • IBM DB2® V9.7 or later
  • Microsoft™ Windows™ V7 or later

Duration

This tutorial takes approximately 1 to 2 hours to complete.


Creating database objects

Before you can start developing the orchestration, you must create some sample objects to use in the database. They will be required in Studio during development. They will also contain some sample data to use for testing.

Create a user account

It is typical to create a dedicated user account for Cast Iron on each endpoint system it will connect to for audit purposes. DB2 uses the operating system to authenticate, so you must create a new Windows user:

  1. Create a test Windows user called "castiron" by selecting Start > Control Panel > Administrative Tools > Computer Management. Expand Local Users and Groups, then right-click Users > New User, as shown in Figure 1.
    Figure 1. Create a new user
    Create a new user
  2. Enter the user name, password, and password confirmation. Uncheck the "User must change password at next logon" box and click the Create button, as shown in Figure 2.
    Figure 2. New user configuration
    New user configuration

Create a database

A database is required to contain the stored procedure as well as some sample data for testing.

  1. Launch the DB2 Control Center from the Windows Start Menu. Create a test database called "CASTIRON" by right-clicking on All Databases > Create Database > Standard, as shown in Figure 3.
    Figure 3. Create a standard database
    Create a standard database
  2. Enter the database name as castiron, as shown in Figure 4, then click Finish.
    Figure 4. Specify a database name
    Specify a database name
  3. Wait for the database to be created, then grant connect and create table privileges to the user, castiron, by right-clicking the database CASTIRON > Authorities, as shown in Figure 5.
    Figure 5. Database CASTIRON authorities
    Database CASTIRON authorities
  4. Click the Add User button, select CASTIRON > OK, and then select CASTIRON. Set CONNECT to Yes and CREATETAB to Yes, as shown in Figure 6.
    Figure 6. Grant authorities to user CASTIRON
    Grant authorities to user CASTIRON

Create a stored procedure, table, and test data

A stored procedure is required to return the results of an SQL select statement. A table and some test data are also required for the SQL select statement to query.

The sample script provided with this tutorial completes the following tasks:

  • Connects to the database CASTIRON as user "CASTIRON" and prompts the user for a password.
  • Creates a table called CAST_IRON_FORM_FACTORS with three columns: ID, NAME and CREATE_DATE.
  • Inserts three rows of test data into the new table.
  • Creates a stored procedure called GET_CAST_IRON_FORM_FACTORS that returns the contents of the new table ordered by ID.

Run the script using the following steps:

  1. Open the Windows command prompt.
  2. Navigate to the directory containing the sample script using the cd command.
  3. Run the command db2cmd to open the DB2 command window.
  4. Run the command db2 -td@ -vf sample.sql in the DB2 command window to run the sample script.

Developing the Cast Iron orchestration

Now that the sample database table and stored procedure have been created, you can begin to develop the Cast Iron orchestration that invokes the stored procedure. The finished sample project is provided with this tutorial as well as the XML schema files that you need to create it.

Create a project and an orchestration

All Studio development is done inside a project. Each project contains one or more orchestrations, and each orchestration contains a series of activities that define the flow of data. For this tutorial, you will create a new project using the following steps:

  1. Launch Studio.
  2. Click the Create Project link on the introduction pane, as shown in Figure 7.
    Figure 7. Create a new project from the introduction pane
    Create a new project from the introduction pane
  3. Enter the Project Name as StoredProcedureSample, select a Project Directory location, and click OK, as shown in Figure 8. The new project then opens with a new orchestration called "Orchestration".
    Figure 8. Enter the project name and directory
    Enter the project name and directory
  4. Rename the orchestration by right-clicking on it in the toolbox on the right side of the screen, and then select Rename, as shown in Figure 9.
    Figure 9. Rename the orchestration
    Rename the orchestration
  5. Enter the name CallStoredProcedure as shown in Figure 10 and press Enter.
    Figure 10. New orchestration name
    New orchestration name
  6. Save the project by pressing CTRL+s.

Create a database endpoint

A database endpoint encapsulates the connection details that Cast Iron will use to connect to the database. A new database endpoint is created using the following steps:

  1. Click on the New Endpoint button in the toolbox toolbar as shown in Figure 11.
    Figure 11. Create a new endpoint
    Create a new endpoint
  2. Click on Database in the context menu that appears, as shown in Figure 12.
    Figure 12. Create a new database endpoint
    Create a new database endpoint
  3. Select the Database Type as DB2 UDB and enter the name, server, port, user name, password, and package collection for your DB2 database, as shown in Figure 13.
    Figure 13. Enter the database connection details
    Enter the database connection details
  4. Click the Test Connection button at the bottom of the screen to confirm that you have entered the details correctly. Figure 14 shows a successful confirmation message.
    Figure 14. Test the database connection
    Test the database connection
  5. Close the Database endpoint by clicking the "x" in the tab at the top of the screen.
  6. Save the project by pressing CTRL+s.

Create a Call Procedure activity

You will now create a Call Procedure activity to invoke a database stored procedure.

  1. Ensure that the CallStoredProcedure orchestration is open.
  2. Drag the Call Procedure activity from the activities tab in the toolbox onto the Add Starter Activity section of the orchestration in the workspace in the center of the screen (see Figure 15). Note that the Call Procedure is not a starter activity, but you will add one later in the tutorial.
    Figure 15. Create a Call Procedure activity
    Create a Call Procedure activity
  3. Activities are configured using the configuration panes below the workspace that contain a checklist of tasks. There are initially two tasks with warning symbols next to them in the checklist for this activity. A warning symbol indicates that some information is missing or invalid. Click on the first task with a warning, the Pick Endpoint task as shown in Figure 16.
    Figure 16. Call Procedure activity checklist
    Call Procedure activity checklist
  4. Observe that there is a warning symbol next to the Database field because the value is missing. Click the Browse button to the right of the Database field as shown in Figure 17.
    Figure 17. Pick Endpoint task configuration
    Pick Endpoint task configuration
  5. Select the Database endpoint you created earlier and click OK as shown in Figure 18.
    Figure 18. Select the database endpoint
    Select the database endpoint
  6. Observe that the database connection details are loaded from the database endpoint you selected. Also the warning next to the Pick Endpoint task has been replaced with a green tick symbol to indicate that the configuration is valid as shown in Figure 19. Click on the Stored Procedure task in the activity checklist.
    Figure 19. Pick Endpoint task completed
    Pick Endpoint task completed
  7. Observe that there is a warning symbol next to the Stored Procedure field because the value is missing. Click the Browse button to the right of the Stored Procedure field.
    Figure 20. Stored Procedure task configuration
    Stored Procedure task configuration
  8. Ensure the User Schema CASTIRON is selected and click Search as shown in Figure 21. Observe that the GET_CAST_IRON_FORM_FACTORS stored procedure is displayed as shown in Figure 21.
    Figure 21. Browse stored procedures
    Browse stored procedures
  9. Ensure GET_CAST_IRON_FORM_FACTORS is selected and click OK as shown in Figure 22. Studio fetches the metadata about the stored procedure from the database and uses it to populate the input and output parameters list (in this case, there are none). Database objects used by Cast Iron must be created in advance so that they are available to browse from Studio.
    Figure 22. Stored procedure parameters
    Stored procedure parameters
  10. Observe the warning symbol next to the Stored Procedure task has been replaced with a green tick symbol and there are no more warning symbols left. Click on the Map Outputs task and observe that the stored procedure returns a recurring node with a recurring type called "resultSets". However, the details of the elements inside that node are not available.
    Figure 23. Observe the output type in the Map Outputs task
    Observe the output type in the Map Outputs task
  11. Save the project by pressing CTRL+s.

Invoke the stored procedure to discover the results structure

An XML schema is needed to tell Studio the result set structure so that you can map the individual elements. To help you create this schema, invoke the Call Procedure activity to see the structure of the results that are returned.

  1. Right-click the Call Procedure activity in the workspace and select Verify Activity from the context menu that appears, as shown in Figure 24.
    Figure 24. Verify the Call Procedure activity
    Verify the Call Procedure activity
  2. The Create Assets window appears. By default, Cast Iron is configured to deliver messages to the stored procedure exactly once and a control table is required in the database for Cast Iron internal use. If you do not wish to create a control table in your database, you can change Deliver Message to "at least once" in the Delivery Rules task in the checklist. Select the checkbox next to the stored procedure and click Create, as shown in Figure 25.
    Figure 25. Create assets
    Create assets
  3. Click OK on the dialog (Figure 26) that confirms the asset was created successfully.
    Figure 26. Asset creation success dialog
    Asset creation success dialog
  4. Click Continue on the Create Assets dialog, then click on the Verify tab in the toolbox to view the results. Expand the Instance #1 tree to see the values of the variables used by the Map Outputs task of the Call Procedure activity. There is no Map Inputs in this case because the stored procedure does not have any input parameters. Click on the results variable (Figure 27) inside the Map Outputs task to display the XML that was returned by the stored procedure invocation. There is a row element for each database row with a numbered column for each database column. There are also the values that the SQL script inserted in the column elements.
    Figure 27. Verify results
    Verify results

Create the XML schemas

You must create an XML schema that exactly matches the structure returned by the stored procedure above to store the results from the database. However, to make the orchestration more maintainable in future, the column elements should have names that match the database columns. Therefore, you need to create another schema that replaces column1, column2, and column3 with more appropriate names that you can use in a later mapping.

  1. Open an external text editor and create a file called "results.xsd".
  2. Copy and paste the XML schema in Listing 1 that exactly matches the results structure into results.xsd.
    Listing 1. results.xsd
    <?xml version="1.0" encoding="UTF-8"?>
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="results" type="ResultsType"/>
      <xs:complexType name="ResultsType">
        <xs:sequence>
          <xs:element maxOccurs="1" minOccurs="1" name="result" type="ResultType"/>
        </xs:sequence>
      </xs:complexType>
      <xs:complexType name="ResultType">
        <xs:sequence>
          <xs:element maxOccurs="1" minOccurs="1" name="resultSets" 
           type="ResultSetsType"/>
        </xs:sequence>
      </xs:complexType>
      <xs:complexType name="ResultSetsType">
        <xs:sequence>
          <xs:element maxOccurs="1" minOccurs="1" name="resultSet" type="ResultSetType"/>
        </xs:sequence>
      </xs:complexType>
      <xs:complexType name="ResultSetType">
        <xs:sequence>
          <xs:element maxOccurs="unbounded" minOccurs="0" name="row" type="RowType"/>
        </xs:sequence>
      </xs:complexType>
      <xs:complexType name="RowType">
        <xs:sequence>
          <xs:element name="column1" type="xs:int"/>
          <xs:element name="column2" type="xs:string"/>
          <xs:element name="column3" type="xs:dateTime"/>
        </xs:sequence>
      </xs:complexType>
    </xs:schema>
  3. Create another file called "resultsWithColumnNames.xsd".
  4. Copy and paste the XML schema in Listing 2 that has the updated column element names into resultsWithColumnNames.xsd.
    Listing 2. resultsWithColumnNames.xsd
    <?xml version="1.0" encoding="UTF-8"?>
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="results" type="ResultsType"/>
      <xs:complexType name="ResultsType">
        <xs:sequence>
          <xs:element maxOccurs="1" minOccurs="1" name="result" type="ResultType"/>
        </xs:sequence>
      </xs:complexType>
      <xs:complexType name="ResultType">
        <xs:sequence>
          <xs:element maxOccurs="1" minOccurs="1" name="resultSets" 
           type="ResultSetsType"/>
        </xs:sequence>
      </xs:complexType>
      <xs:complexType name="ResultSetsType">
        <xs:sequence>
          <xs:element maxOccurs="1" minOccurs="1" name="resultSet" 
           type="ResultSetType"/>
        </xs:sequence>
      </xs:complexType>
      <xs:complexType name="ResultSetType">
        <xs:sequence>
          <xs:element maxOccurs="unbounded" minOccurs="0" name="row" 
           type="RowType"/>
        </xs:sequence>
      </xs:complexType>
      <xs:complexType name="RowType">
        <xs:sequence>
          <xs:element name="id" type="xs:int"/>
          <xs:element name="name" type="xs:string"/>
          <xs:element name="create_date" type="xs:dateTime"/>
        </xs:sequence>
      </xs:complexType>
    </xs:schema>
  5. Click on the Project tab in the Studio toolbox.
  6. Click on the Add Document button in the toolbox toolbar as shown in Figure 28.
    Figure 28. Add a document
    Add a document
  7. Click on XML Schema in the context menu that appears, as shown in Figure 29.
    Figure 29. Add an XML schema
    Add an XML schema
  8. Click the Browse button, select the location of the results.xsd file that you created above, and click OK as shown in Figure 30.
    Figure 30. Add results.xsd
    Add results.xsd
  9. Repeat Steps 6-8 for the resultsWithColumnNames.xsd file. Afterwards, both files will be listed in the XML schema section of the Project tab as shown in Figure 31.
    Figure 31. Imported XML schemas
    Imported XML schemas
  10. Save the project by pressing CTRL+s.

Finish the Call Procedure activity

The XML schemas for the results have now been imported into Studio. You can modify the Call Procedure activity to map the results into an XML variable with child elements that you can manipulate in later activities. You must use the CopyOf function to perform this mapping because you cannot access the child elements of the resultSets on the left side. It copies the entire contents of the source node to the destination node.

  1. Click on the Call Procedure activity in the workspace.
  2. Click on the Map Outputs task in the checklist.
  3. Click the Select Outputs button on the right side as shown in Figure 32.
    Figure 32. Select outputs
    Select outputs
  4. Click the New button to create a new variable for the results as shown in Figure 33.
    Figure 33. Create a new output variable
    Create a new output variable
  5. Select the results type under the results XML schema and click the Next button as shown in Figure 34.
    Figure 34. Create a variable with type results from the results schema
    Create a variable with type results from the results schema
  6. Enter the name results and click the Finish button as shown in Figure 35.
    Figure 35. Create a variable called results
    Create a variable called results
  7. Select the new results variable and click OK, as shown in Figure 36.
    Figure 36. Select the new results variable
    Select the new results variable
  8. Figure 37 shows the full results structure is available in the Map Outputs task.
    Figure 37. The results variable in Map Outputs
    The results variable in Map Outputs
  9. Click on the Functions tab of the toolbox and drag the CopyOf function from the Miscellaneous section to the white box in the middle of the Map Outputs task, as shown in Figure 38.
    Figure 38. Add the CopyOf function to Map Outputs
    Add the CopyOf function to Map Outputs
  10. Drag the results variable on the left side of the mapping to the CopyOf function to set the input parameter for the function, as shown in Figure 39.
    Figure 39. Set the CopyOf function input parameter
    Set the CopyOf function input parameter
  11. Drag the CopyOf function to the results variable on the right side of the mapping to output the results of the function to that variable, as shown in Figure 40.
    Figure 40. Set the CopyOf function output parameter
    Set the CopyOf function output parameter
  12. Complete the mapping by right-clicking the CopyOf function and selecting Apply Function Graph, as shown in Figure 41.
    Figure 41. Apply the function graph
    Apply the function graph
  13. Figure 42 shows that the mapping is displayed as a magenta line. This color means the mapping is not guaranteed because the source and destination types do not match. This is expected and you can ignore it.
    Figure 42. Mapping is not guaranteed
    Mapping is not guaranteed
  14. Save the project by pressing CTRL+s.

Create a Map Variables activity to set the column names

The Call Procedure activity configuration is now complete. It invokes the stored procedure and stores the output in a results variable that includes all the child elements. Now you can add a Map Variables activity to map column1, column2, and column3 in the results to elements with more appropriate names to make it easier to identify which column element contains data from which database column.

  1. Click on the Activities tab in the toolbox and expand the Transform section to display the Map Variables activity, as shown in Figure 43.
    Figure 43. Transform activities
    Transform activities
  2. Drag the Map Variables activity after the Call Procedure activity in the orchestration in the workspace, as shown in Figure 44.
    Figure 44. The orchestration after adding a Map Variables activity
    The orchestration after adding a Map Variables activity
  3. The Map Variables activity only has one task so there is no checklist in the configuration pane. Click the Select Inputs button on the left side as shown in Figure 45.
    Figure 45. Select inputs
    Select inputs
  4. Select the results variable and click OK, as shown in Figure 46.
    Figure 46. Select the results variable
    Select the results variable
  5. Click the Select Outputs button on the right side, as shown in Figure 47.
    Figure 47. Select outputs
    Select outputs
  6. Click the New button to create a new variable for the results with column names, as shown in Figure 48.
    Figure 48. Create a new variable
    Create a new variable
  7. Select the results type under the resultsWithColumnNames XML schema and click the Next button as shown in Figure 49.
    Figure 49. Create a new variable of type results from the resultsWithColumnNames schema
    Create a new variable of type results from the resultsWithColumnNames schema
  8. Enter the name resultsWithColumnNames and click the Finish button as shown in Figure 50.
    Figure 50. Create variable resultsWithColumnNames
    Create variable resultsWithColumnNames
  9. Select the new resultsWithColumnNames variable and click OK as shown in Figure 51.
    Figure 51. Select output variable resultsWithColumnNames
    Select output variable resultsWithColumnNames
  10. Drag the column1 element to the id element, the column2 element to the name element, and the column3 element to the create_date element, as shown in Figure 52. These mappings are displayed in green because the source and destination types match.
    Figure 52. Map the variables
    Map the variables
  11. Save the project by pressing CTRL+s.

Create a starter activity

So far you have invoked the stored procedure and stored the results in a variable with element names that correspond to the database columns. However, there is still a warning symbol next to the orchestration in the Project tab of the toolbox because every valid orchestration must start with a special type of activity called a "starter activity". You will use a Schedule Job activity that starts a job (a runtime instance of an orchestration) regularly at a specified interval.

  1. Click on the Activities tab in the toolbox and expand the Utilities section to display the Schedule Job activity, as shown in Figure 53.
    Figure 53. The Utilities activities
    The Utilities activities
  2. Drag the Schedule Job activity before the Call Procedure activity in the orchestration in the workspace, as shown in Figure 54.
    Figure 54. The orchestration after adding a Schedule Job activity
    The orchestration after adding a Schedule Job activity
  3. Change the interval to every 5 seconds in the Configure task of the checklist in the configuration pane as shown in Figure 55. This means you will only have to wait 5 seconds for the orchestration to run when you are testing.
    Figure 55. Set the time interval
    Set the time interval
  4. Click the Validate Project button in the toolbar below the menu bar as shown in Figure 56.
    Figure 56. Validate the project
    Validate the project
  5. Observe that the project is now valid.
    Figure 57. Validation success
    Validation success
  6. Save the project by pressing CTRL+s.

Unit test the full orchestration

Now you have a starter activity, you can unit test the full orchestration using the Verify functionality that is built into Studio.

  1. Click on the Verify tab in the toolbox then click the Start Orchestration button in the toolbar as shown in Figure 58.
    Figure 58. Start the orchestration
    Start the orchestration
  2. Click the Continue button on the Create Assets dialog because you already created the control table when you verified the Call Procedure activity. After 5 seconds, the orchestration will start running.
  3. After it has completed, expand Instance #2 to see the results in more detail as shown in Figure 59. Click on the resultsWithColumnNames variable in the Map Variables activity to see that the column elements have been successfully renamed.
    Figure 59. Verify full orchestration results
    Verify full orchestration results

Next steps

In a real scenario, you would modify this orchestration to do something with the results once they have been retrieved from the database. For example, they can be written to a flat file using the WriteXML and FTP Put File activities. It is good practice to add a Try activity around the Call Procedure activity to handle the error if the database endpoint is not available, or if an error occurs while invoking the procedure.

Additionally, you should publish the orchestration to a runtime environment and test it with a larger input data. Note that the database endpoint is currently configured to use the server localhost, which will not work after deployment. You should convert the database connection parameters to configuration properties so you can modify them from the Web Management Console (WMC) after deployment without needing to change the project in Studio. If a different database is used, you need to recreate the assets from the WMC too. However, all these steps are beyond the scope of this tutorial. See the Resources section for further reading.


Conclusion

This tutorial has demonstrated how you can use WebSphere Cast Iron Studio to develop an orchestration that runs every 5 seconds to invoke a stored procedure and to store the results in a variable with meaningful element names.


Download

DescriptionNameSize
Code sampleSampleApplication.zip22KB

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=812453
ArticleTitle=Calling a stored procedure that returns a result set in IBM WebSphere Cast Iron Studio
publish-date=05022012