Create IBM Data Web Services using IBM Database Add-Ins for Visual Studio

Learn how the IBM® Database Add-Ins for Microsoft® Visual Studio® tool set supports IBM data server Web services development. Walk through the creation of a data Web service, and see how it can be deployed to easily access IBM DB2® or IBM Informix® databases from your Web applications.

Share:

Farzad Farahbod, Senior Software Engineer, IBM

Farzad FarahbodFarzad Farahbod is a senior software engineer working at the IBM Silicon Valley Lab, providing tools for seamless integration with IBM servers. He is also the Visual Studio .NET XML tooling lead.



Ajay Aggrwal, Advisory Software Engineer, IBM

Ajay AggrwalAjay Aggrwal is an advisory software engineer with the IBM Database Add-ins for Visual Studio team.



02 February 2009

Also available in Chinese

With the bottom-up development approach, you start with one or more database objects to create and expose as Web service operations. Working with a table, a view, or a procedure, you can expose these objects as Web service operations directly from the Server Explorer in the Microsoft Visual Studio environment. This is easy-to-use functionality, as it involves a one-click approach in generating and exposing IBM Data Web Services.

Prerequisites

In order to create IBM data server Web service applications, you need to have the following installed:

  • Microsoft Visual Studio 2005/ Visual Studio 2008 Professional Edition
  • IBM database client V9.5 or later
  • IBM Database Add-Ins for Visual Studio, Version 9.5 or later
  • Access to any IBM DB2 or Informix database
  • Any of the following application servers:
    • Apache Tomcat V5.5
    • IBM WebSphere® Application Server Community Edition 1.1
    • IBM WebSphere Application 6.x

General familiarity with the IBM Database Add-ins for Visual Studio is also assumed. To become more familiar with the general features of the IBM Database Add-Ins for Visual Studio, read the "DB2 for Visual Studio 2005 developers, Part 1: Overview of IBM Database Add-ins for Visual Studio 2005" (developerWorks, December 2005) and the Develop proof-of-concept .NET applications tutorial series (developerWorks).

New users to IBM Data Web Services and .NET framework can get acquainted to this new technology by following this useful tutorial: "IBM Data Studio Data Web Services, Part 1: IBM Data Studio: Get started with Data Web Services" (developerWorks, November 2007).


Creating an IBM Data Web Service

This article covers operations permitted on different nodes under IBM Data Server connection that support IBM Data Server Web Services and the steps needed to run these operations. This article also covers the creation of a sample scenario in which you can create a Web service with multiple operations and how it can be deployed.

  1. To begin, launch Visual Studio 2005 or Visual Studio 2008, and create a connection to the database.
  2. From the pop-up menu, select Add Connection in Server Explorer, which launches the Add connection dialog, as shown in Figure1:
    Figure 1. Add connection
    Add connection
  3. In the "Data source" field, select IBM DB2, IDS and U2 Servers (IBM DB2, IDS, and U2 Data Provider for .NET Framework), and provide server name, user ID, and password.
  4. Click on the Test Connection button to verify if the connection has been made successfully, then click on the OK button.
    Figure 2. Add connection
    Add connection
    The Web Services node is shown under the connection. If there are any existing Web services, they are listed under the Web Services node.
    Figure 3. Server Explorer
    Server Explorer
  5. You can right-click on a table, view, or procedure in the Server Explorer and select Create and Deploy IBM Data Web Service from the context menu to create an IBM Data Web Service operation. Doing so launches the IBM Data Web Service Designer, allowing you to create a new data Web service.
    Figure 4. Creating an IBM Data Web Service
    Creating an IBM Data Web Service
    You can also launch the same designer (IBM Data Web Service Designer) from the Web Services node in the Server Explorer:
    Figure 5. Creating an IBM Data Web Service
    Creating an IBM Data Web Service
  6. You can edit an existing Web service to rename, add, delete, or modify a Web service operation. To do so, select the Web service in Server Explorer, right-click on the Web service, and select Open Definition, which launches the IBM Data Web Service Designer:
    Figure 6. Modify Web service
    Modify Web service
    Within the designer, you can edit different attributes of th Web service:
    Figure 7. Edit Web service
    Edit Web service
  7. To run a Web service, select Run from the context menu. Doing so launches the Web Service Method Invocation dialog, where you can select any operation, enter associated parameter values, and see the WSDL (Web Services Description Language) and the returned XML data.
    Figure 8. Run Web service
    Run Web service
  8. To delete a Web service and its related artifacts from the server, select Delete from the context menu.

IBM Data Web Service Designer

This designer allows you to create a Web service in one single step. You can also use this same designer to modify or edit a Web service. The designer consists of two views:

  • Web service information view (default view)
  • Show generated WSDL — In this view, you can modify the server parameter settings or point to a different server. Any action on this dialog is persisted, and you do not have to remember it again. When you launch this dialog for the first time, the default parameter for the server type is set for you, though you can change that at any time.
Figure 9. IBM Data Web Service Designer
IBM Data Web Service Designer

Sample scenario

Let's create a sample Web service for Employees table with the following table definition to store employee records:

Listing 1. Table definition
sqltype               sqlname.data
--------------------  ------------
CHARACTER             EMPNO
VARCHAR               FIRSTNME
CHARACTER             MIDINIT
VARCHAR               LASTNAME
CHARACTER             WORKDEPT
CHARACTER             PHONENO
DATE                  HIREDATE
CHARACTER             JOB
SMALLINT              EDLEVEL
CHARACTER             SEX
DATE                  BIRTHDATE
DECIMAL               SALARY
DECIMAL               BONUS
DECIMAL               COMM
VARCHAR FOR BIT DATA  PKEY
XML                   PROJECTS

Let's also use a stored procedure called Bonus_Increase with the following parameters (these objects are available as part of the sample database).

Listing 2. Generated code
IN p_bonusFactor DECIMAL (3,2)             
IN p_bonusMaxSumForDept DECIMAL (9,2),                              
OUT p_deptsWithoutNewBonuses VARCHAR(255),                              
OUT p_countDeptsViewed INTEGER,                              
OUT p_countDeptsBonusChanged INTEGER,                              
OUT p_errorMsg VARCHAR(255)

Right-click on the procedure Bonus_Increase, and select Create and Deploy IBM Data Server Web Service. This launches the IBM Data Server Web Service Designer.

The default name (Webservice1) is assigned to the Web service and is shown in the name field of the designer.

Tooling automatically generates the Call operation with the operation text as CALL Bonus_Increase.

So far, only one Web service with one operation has been created. To add more operations for this simple service, click on the plus (+) button in front of the operation grid and specify names for the operations. For example, this sample uses getEmployee for the new operation being added. You can assign the operation in operation column, or you can click on the ellipses to bring up the Operation edit dialog, where you can type owner name. The editor lists all possible objects using intellisense. Select table name (Employees table for this sample), and click on OK. Since this is a table operation, tooling generates insert, update, and delete operations automatically. The tooling calls the employee insert operation insertEmployee and the update operation updateEmployee, and so on. For this sample service, you do not need the delete and update operations, so click on the rows in the operation grid for delete and update, and click on the minus (-) button in front of grid to remove those operations.

Listing 3 shows the SELECT statement for the getEmployee operation for the Employee table, as generated by the tooling:

Listing 3. SELECT statement
SELECT * FROM EMPLOYEE WHERE EMPNO = ?

Listing 4 shows the generated insert statement:

Listing 4. INSERT statement
INSERT INTO EMPLOYEE values (?, ?, ?, ?, 
                             ?, ?, ?,? , 
                             ?,? ,?,?, 
                             ?,?, ?, ?)

Since this is the first time the IBM Data Web Service Designer is launched, the Advanced Option dialog appears so you can enter application server deployment and configuration information. Since the tooling persists all changes to this Advanced Options dialog for later use, you only need to enter this information once.

Figure 10. Advanced Options
Advanced Option

Setting up an application server

The sample scenario in this article uses IBM WebSphere Application Server Community Edition 1.1.

  1. Start WebSphere Application Server Community Edition 1.1 server and launch its Administrative Console:
    Figure 11. Administrative Console
    Adminstrative Console
  2. Click on the Database Pools link from the Console Navigation panel (this launches Database Pools page):
    Figure 12. Administrative Console
    Adminstrative Console
  3. Click on the Using the Geronimo database pool wizard link:
    Figure 13. Database Pools
    Database Pools
  4. Enter Sample in the Name of Database Pool field, and click on Next:
    Figure 14. Database Pools
    Database Pools
  5. Select the appropriate database driver for type of DB2 server and fill out the rest of the fields, then enter sample for database field and click on Next:
    Figure 15. Database Pools
    Database Pools
  6. On the last page of the Database Pools wizard, click on Test Connection to ensure the connection information is correct and connection to your DB2 Sample database can be established:
    Figure 16. Database Pools
    Database Pools
  7. Once the test connection is successful, click on Deploy to create the database pool:
    Figure 17. Database Pools
    Database Pools
  8. After the database pool is created, close the Administrator Console page and shut down WebSphere Application Server Community Edition server.
  9. Go back to the IBM Web Service Designer in Visual Studio, and click on Advanced Options:
    Figure 18. Advanced Options
    Advanced Options
  10. In the Advanced Option dialog, select New, which launches the New Server dialog:
    Figure 19. New Server
    New Server
  11. Select IBM from the Vendor drop-down list, and click on Browse to specify you Web server installation directory.
  12. Point to you WebSphere Application Server Community Edition1.1 installation directory, then click OK to complete the new server setup:
    Figure 20. Advanced Options
    Advanced Options
  13. Change the name of the artifact.dataSourceGroupId file to the name of the database pool you created in the previous step in the Advanced Options console page, which was "sample".
  14. Check the Set As Default and Open Invoke Web Service Method dialog check boxes. The first check box allows tooling to reuse the same information for further Web service deployment, and the second check box launches Web Service test dialog after deployment is successful.
  15. Click OK in this dialog to persist this information:
    Figure 21. IBM Data Web Service Designer
    IBM Data Web Service Designer
  16. Once you're finished with the new server setup, click on the Save toolbar button in Visual Studio IDE to deploy the Web service.

The deployment process takes a minute for the first time, but any subsequent deployments are fast. IBM Web Service Designer prints out each deployment step into the Visual Studio Output window:

Figure 22. Deployment output messages
Deployment output messages

After successful deployment, Web Service Method Invocation dialog is launched, allowing you to test the Web service you just deployed:

Figure 23. Web Service Method Invocation
Web Service Method Invocation

You can also click on Show Script to see the WSDL generated for the Web service.


Conclusion

IBM Database Add-ins for Visual Studio provides support for IBM Data Web Services, which is the next generation Web service provider for IBM database servers. The tooling provides an easy way to create and expose Web service operations for the selected database objects. It supports the IBM family of database servers and many popular Web servers, such as Apache Tomcat V5.5 and IBM WebSphere Application Server V6.1 and Community Edition V1.1. The add-ins provide a framework to assist the users in creating Web services to meet business needs. As illustrated in this article, you can create, view, modify, and delete Web services without writing extensive code, and you can deploy these Web services with ease.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

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 Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=367816
ArticleTitle=Create IBM Data Web Services using IBM Database Add-Ins for Visual Studio
publish-date=02022009