Creating the Stored Procedure
Building and deploying a stored procedure can be a daunting task. WebSphere Studio provides an integrated set of tools to make it much easier. Using the stored procedure tooling in WebSphere Studio, you can:

• Create a new stored procedure
• Modify an existing stored procedure
• Build a stored procedure and register it with the DB2 server
• Run a stored procedure
• Debug a stored procedure
• Drop a stored procedure from the DB2 server

Our Java stored procedure is composed of three SQL statements: a SELECT statement to query the inventory available, an UPDATE statement to update the table if there is enough inventory, and another SELECT statement to obtain the product description and an estimated delivery date to return to the client. In the Java Stored Procedure wizard, we will indicate that we want to generate multiple SQL statements as shown in Figure 2. We add the three SQL statements and complete each one using the SQL wizard launched from the SQL Assist button.

Once we are finished with the Java Stored Proce-dure wizard, a file containing the Java stored procedure will be created. The generated code makes a connection to the database and then executes the three SQL statements. We must customize the generated Java code to add in the logic that performs the availability check and that will update the table to reflect used inventory only if there is enough inventory available to fulfill the request. Listing 1 shows the modified Java stored procedure.

Building and Running the Stored Procedure
The next step is to build the stored procedure and register it with the DB2 server. To build the stored procedure, select the UPDATEINVENTORY stored procedure in the Data Definition view and choose Build. The Java source code is compiled and the stored procedure is registered with the database server if the compilation is successful. You can view the progress of the build process and the messages in the DBOutput view as shown in Figure 3. If there are errors in the Java stored procedure, the lines containing errors will also be indicated in the DB Output view.

The DB Output view shows the processes that have been run on the left and contains three tabs on the right: Messages, Parameters, and Results. The Messages tab shows console output returned from the database server. This tab is useful if there is a failure since it often provides an error code or trace of some kind. The Parameters tab shows both input and output parameters and their values. The Results tab shows the result set returned. If there are multiple result sets, you can use the arrow key to page through the multiple result sets.

To run the stored procedure, select the Run action in the pop-up menu for the stored procedure. Figure 4 shows the wizard that will prompt you for the input parameter values. In this example, we entered 12 for the quantity required and 100 for the item ID. The results of the run action are shown in the DB Output view described above.

The Web Services Object Runtime Framework
Now that we have successfully created and tested our stored procedure, it is time to wrap it as a Web service so that it can be invoked over the Internet. To run our stored procedure as part of a Web service, we will need to use the Web Services Object Runtime Framework (WORF). WORF is included in WebSphere Studio and is also available as a separate download. It provides an environment to create XML-based Web services that access DB2. WORF uses SOAP and a Document Access Definition Extension file (DADX) that describes the set of SQL operations that can be invoked over the Internet.

WORF supports both HTTP GET and POST operations in addition to a SOAP request. On a service request, WORF will load the DADX file specified in the request, connect to DB2, run the SQL statement, and commit the database transaction. It will format the result into XML, converting data types as necessary, and return the response to the requester. Optionally, you can also use WORF to generate WSDL, XML Schema, documentation, or a test page.

Creating a DADX Group
The DADX file used by WORF is deployed as part of a DADX group located in the WEB-INF/classes/groups directory of your Web application. The DADX group contains information, such as database connection parameters, that is shared between DADX files within the DADX group.

To create a DADX group that will hold the Web services that access the database, we use the Web Services DADX Group Configuration wizard shown in Figure 5. We will change the DB URL to jdbc:db2:SPDEMO so that the correct database association is made with the group. The wizard stores the information in the group.properties file in the directory created for this group. The wizard also updates the Web application deployment descriptor, web.xml, to store the appropriate information, such as servlet mapping for this WORF-based Web application.

Generating the DADX File
Now it is time to create the DADX file. WebSphere Studio provides a DADX wizard that you can use to generate the DADX file. Using the DADX wizard, SQL statements or DAD files can be selected to wrap as a Web service. The corresponding DADX operation wrapper is automatically generated by the wizard. Listing 2 shows the DADX file for invoking the UPDATEINVENTORY stored procedure we created earlier.

The <dadx:SQL_call> tag contains the actual call to the stored procedure UPDATEINVENTORY. The <dadx:parameter> tags define the two parameters for the stored procedure. The <dadx:result_set> tags define the two result sets returned by this stored procedure.

The <dadx:result_set_metadata> tags define the result set metadata, including column data types and names. Since the JDBC metadata for a CALL statement does not include the result set metadata, it must be defined explicitly in the DADX file. Our UPDATEINVENTORY stored procedure returns two result sets, and both of them are defined in the Inventory.dadx file.

Creating a Web Service from a DADX File
With the DADX group and DADX file in hand, we can now launch the Web Service wizard to create the Web service from the DADX file. Figure 6 shows the Web Service wizard. Note that this is the same wizard used when creating a Web service from a JavaBean or an Enterprise JavaBean. Make sure to select DADX Web Service as the Web service type. In the following page, we specify the Inventory.dadx file created earlier for the stored procedure. We check the "Test the generated proxy" option on the Web Service Test page to generate a set of JSPs that can be used to test the DADX Web service.

Testing the Web Service
Once the generation is completed, the test client shows a list of methods that can be used to test the Web service. We select the UpdateInventory operation. The input pane prompts us to specify the parameters for calling the stored procedure. Once we enter the value for the quantity and the item ID, the stored procedure UPDATEINVENTORY is called, and the result sets are returned in XML format and displayed in the result pane of the test client.

Summary
We have shown you how to use WebSphere Studio to easily create a stored procedure to access and update a database and how to create a Web service from the stored procedure. Using the WORF framework and a DADX file, you can create XML-based Web services that access DB2 data and stored procedures using a call operation in the DADX file to call a stored procedure. You can also specify operations such as update or query to invoke SQL statements directly to insert, update, delete, or query your data. Operations such as storeXML and retrieveXML can also be included in the DADX file if you want to use the DB2 XML Extender to store or retrieve XML documents to or from your database.

References