IBM Cloud Authors: Elizabeth White, Pat Romanski, Liz McMillan, Yeshim Deniz, Stefan Bernbo

Related Topics: IBM Cloud

IBM Cloud: Article

Creating Web Services from Stored Procedures Using WebSphere Studio

Creating Web Services from Stored Procedures Using WebSphere Studio

Web services are no longer a new concept. They are rapidly gaining acceptance and use in the development of e-business applications. By now, the benefits of using Web services are clear: they provide a modular, self-describing, and self-contained mechanism to share business logic over the Internet using standardized messaging protocols. Business logic is separated from the client code and the database and can be made available to numerous applications.

The payoffs are obvious. However, related standards such as SOAP, AXIS, UDDI, and JAX-RPC are evolving quickly and various components are needed to completely implement a Web service, including client code, message protocols, WSDL, security, registration, and deployment. It is challenging, and in some cases tedious, for developers to assemble the appropriate components from scratch while keeping pace with the changing technology and standards modifications.

IBM WebSphere Studio provides wizards that generate standard Web service code for developers, allowing them to focus on the code specific to their application instead of taking time to write the standard code used for Web service implementation. In this article we will describe how to create an inventory management Web service from a Java stored procedure using WebSphere Studio. We use a stored procedure in this application because the scenario requires multiple SQL statements to be executed based on a computation. By using a stored procedure, we reduce the number of calls made between the application and the database over the network, which can result in substantial performance gains. There are other benefits of using stored procedures in your application. Stored procedures make application updates and maintenance smoother by centralizing business logic. If you make changes to a stored procedure, the changes are immediately available to all client applications that use it. In addition, security and administration are easier when the logic is relegated to the database server since the database management system already takes care of these issues.

The Scenario
We will use an inventory management scenario to demonstrate how to create a Web service from a stored procedure. A satellite store checks to see if there is enough inventory in the warehouse to satisfy a customer order. If there is, inventory records need to be updated to remove the inventory from the warehouse database. A report containing a description of the item and an estimated arrival time is required by the satellite store as confirmation of the product availability.

The stored procedure logic is as follows:
1.   Query the inventory table using the item ID to retrieve the current available inventory.
2.   Compare the available quantity with the requested quantity.
3.   If there is enough quantity to satisfy this request, update the inventory table by subtracting the requested quantity from the available quantity.
4.   Query the inventory table to retrieve the product description and the projected delivery date for the item.

The stored procedure will take two parameters:

  • Quantity: The number of items requested by the satellite store
  • itemId: A unique ID that identifies the item

    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
    To create a stored procedure, we first need a connection to a database. The Connection wizard is used to establish a connection as shown in Figure 1. It prompts for the relevant database, JDBC information, and optional filters to subset the list of table definitions to import. Once the database artifacts are imported into a project, we launch the Java Stored Procedure wizard to build the Java stored procedure.

    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.

    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.


  • Web Services Object Runtime Framework (WORF) for DB2: www7b.software.ibm.com/dmdd/zones/webservices/worf
  • IBM WebSphere Application Server - Express: www-3.ibm.com/software/webservers/appserv/express/features/
  • IBM WebSphere Studio Application Developer: www-3.ibm.com/software/ad/studioappdev
  • DB2 Developer Domain: www7b.software.ibm.com/dmdd
  • More Stories By Christina Lau

    Christina Lau is a senior technical staff
    member at the IBM Toronto Lab. Christina is the architect and
    manager for WebSphere Studio's XML and data tools.

    More Stories By Joan Haggarty

    Joan Haggarty is a staff software
    developer at the IBM Toronto Lab. Joan is a lead developer for the
    XML and data tools in WebSphere Studio Application Developer.

    Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

    IoT & Smart Cities Stories
    The graph represents a network of 1,329 Twitter users whose recent tweets contained "#DevOps", or who were replied to or mentioned in those tweets, taken from a data set limited to a maximum of 18,000 tweets. The network was obtained from Twitter on Thursday, 10 January 2019 at 23:50 UTC. The tweets in the network were tweeted over the 7-hour, 6-minute period from Thursday, 10 January 2019 at 16:29 UTC to Thursday, 10 January 2019 at 23:36 UTC. Additional tweets that were mentioned in this...
    Artificial intelligence, machine learning, neural networks. We're in the midst of a wave of excitement around AI such as hasn't been seen for a few decades. But those previous periods of inflated expectations led to troughs of disappointment. This time is (mostly) different. Applications of AI such as predictive analytics are already decreasing costs and improving reliability of industrial machinery. Pattern recognition can equal or exceed the ability of human experts in some domains. It's devel...
    The term "digital transformation" (DX) is being used by everyone for just about any company initiative that involves technology, the web, ecommerce, software, or even customer experience. While the term has certainly turned into a buzzword with a lot of hype, the transition to a more connected, digital world is real and comes with real challenges. In his opening keynote, Four Essentials To Become DX Hero Status Now, Jonathan Hoppe, Co-Founder and CTO of Total Uptime Technologies, shared that ...
    The Japan External Trade Organization (JETRO) is a non-profit organization that provides business support services to companies expanding to Japan. With the support of JETRO's dedicated staff, clients can incorporate their business; receive visa, immigration, and HR support; find dedicated office space; identify local government subsidies; get tailored market studies; and more.
    After years of investments and acquisitions, CloudBlue was created with the goal of building the world's only hyperscale digital platform with an increasingly infinite ecosystem and proven go-to-market services. The result? An unmatched platform that helps customers streamline cloud operations, save time and money, and revolutionize their businesses overnight. Today, the platform operates in more than 45 countries and powers more than 200 of the world's largest cloud marketplaces, managing mo...
    At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
    When Enterprises started adopting Hadoop-based Big Data environments over the last ten years, they were mainly on-premise deployments. Organizations would spin up and manage large Hadoop clusters, where they would funnel exabytes or petabytes of unstructured data.However, over the last few years the economics of maintaining this enormous infrastructure compared with the elastic scalability of viable cloud options has changed this equation. The growth of cloud storage, cloud-managed big data e...
    Your applications have evolved, your computing needs are changing, and your servers have become more and more dense. But your data center hasn't changed so you can't get the benefits of cheaper, better, smaller, faster... until now. Colovore is Silicon Valley's premier provider of high-density colocation solutions that are a perfect fit for companies operating modern, high-performance hardware. No other Bay Area colo provider can match our density, operating efficiency, and ease of scalability.
    At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
    As you know, enterprise IT conversation over the past year have often centered upon the open-source Kubernetes container orchestration system. In fact, Kubernetes has emerged as the key technology -- and even primary platform -- of cloud migrations for a wide variety of organizations. Kubernetes is critical to forward-looking enterprises that continue to push their IT infrastructures toward maximum functionality, scalability, and flexibility. As they do so, IT professionals are also embr...