Welcome!

IBM Cloud Authors: Zakia Bouachraoui, Elizabeth White, Yeshim Deniz, Pat Romanski, Liz McMillan

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.

    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

  • 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 platform combines the strengths of Singtel's extensive, intelligent network capabilities with Microsoft's cloud expertise to create a unique solution that sets new standards for IoT applications," said Mr Diomedes Kastanis, Head of IoT at Singtel. "Our solution provides speed, transparency and flexibility, paving the way for a more pervasive use of IoT to accelerate enterprises' digitalisation efforts. AI-powered intelligent connectivity over Microsoft Azure will be the fastest connected pat...
    There are many examples of disruption in consumer space – Uber disrupting the cab industry, Airbnb disrupting the hospitality industry and so on; but have you wondered who is disrupting support and operations? AISERA helps make businesses and customers successful by offering consumer-like user experience for support and operations. We have built the world’s first AI-driven IT / HR / Cloud / Customer Support and Operations solution.
    Codete accelerates their clients growth through technological expertise and experience. Codite team works with organizations to meet the challenges that digitalization presents. Their clients include digital start-ups as well as established enterprises in the IT industry. To stay competitive in a highly innovative IT industry, strong R&D departments and bold spin-off initiatives is a must. Codete Data Science and Software Architects teams help corporate clients to stay up to date with the mod...
    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...
    Druva is the global leader in Cloud Data Protection and Management, delivering the industry's first data management-as-a-service solution that aggregates data from endpoints, servers and cloud applications and leverages the public cloud to offer a single pane of glass to enable data protection, governance and intelligence-dramatically increasing the availability and visibility of business critical information, while reducing the risk, cost and complexity of managing and protecting it. Druva's...
    BMC has unmatched experience in IT management, supporting 92 of the Forbes Global 100, and earning recognition as an ITSM Gartner Magic Quadrant Leader for five years running. Our solutions offer speed, agility, and efficiency to tackle business challenges in the areas of service management, automation, operations, and the mainframe.
    The Jevons Paradox suggests that when technological advances increase efficiency of a resource, it results in an overall increase in consumption. Writing on the increased use of coal as a result of technological improvements, 19th-century economist William Stanley Jevons found that these improvements led to the development of new ways to utilize coal. In his session at 19th Cloud Expo, Mark Thiele, Chief Strategy Officer for Apcera, compared the Jevons Paradox to modern-day enterprise IT, examin...
    With 10 simultaneous tracks, keynotes, general sessions and targeted breakout classes, @CloudEXPO and DXWorldEXPO are two of the most important technology events of the year. Since its launch over eight years ago, @CloudEXPO and DXWorldEXPO have presented a rock star faculty as well as showcased hundreds of sponsors and exhibitors! In this blog post, we provide 7 tips on how, as part of our world-class faculty, you can deliver one of the most popular sessions at our events. But before reading...
    DSR is a supplier of project management, consultancy services and IT solutions that increase effectiveness of a company's operations in the production sector. The company combines in-depth knowledge of international companies with expert knowledge utilising IT tools that support manufacturing and distribution processes. DSR ensures optimization and integration of internal processes which is necessary for companies to grow rapidly. The rapid growth is possible thanks, to specialized services an...
    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...