IBM WebSphere Adapter for JDBC
Quick Start Tutorial V 6.1
Creating a business object for a user-defined query and using the RetrieveAll operation for retrieval from a SQLServer database.
Table of Contents
1.0 Introduction 1
1.1 Learning Objectives 1
1.2 Audience 1
1.3 Software prerequisites 1
2.0 Prerequisites to run the scenario 2
2.1 Configuration prerequisites 2
2.1.1 Creating the authentication alias 2
2.2 Creating the tables 5
3.0 Creating the adapter project in WebSphere Integration Developer (WID) 6
3.1 Configuration prerequisites 6
4.0 Scenario: Retrieving All Records Based On A User-Defined Query with a SQLServer Database. 8
4.1 Configuring the adapter for outbound processing 8
4.1.1 Setting connection properties for the external service wizard 12
4.1.2 Selecting the business objects and services to be used with the adapter 13
4.1.3 Generating business object definitions and related artifacts 19
4.2 Deploying the module to the test environment 21
4.3 Testing the assembled adapter application 23
4.4 Clearing the sample content 26
Table of Figures
Figure 1: Choose Servers’ View 2
Figure 2: Java Authentication and Authorization Service (JAAS) 3
Figure 3: Create a New Authentication Alias with User ID, Password and Description 4
Figure 4: Select Authentication Entries 4
Figure 5: Import the Adapter RAR File 6
Figure 6: The Connector RAR File Being Imported 7
Figure 7: Listings of Connector’s Project 7
Figure 8: Business Integration’s External Service 8
Figure 9: The “Adapters” Option, for a New External Service 9
Figure 10: Select the Appropriate Adapter Type As Imported 10
Figure 11: JDBC Driver JAR Files Are Added 11
Figure 12: The Adapter Style Options 12
Figure 13: Database Connection Properties 13
Figure 14: Object Discovery and Selection 14
Figure 15: Query Properties 15
Figure 16: Configuration Parameters for Parent BO 16
Figure 17: Configuration Properties for a Child BO 17
Figure 18: User-Defined Query 18
Figure 19: Selected and Configured Objects To Be Imported 18
Figure 20: Configure Operations of Selected Business Objects 19
Figure 21: Service Generation and Deployment Configuration 20
Figure 22: Creating a New Test Module 21
Figure 23: Add and Remove Projects to the Server 22
Figure 24: Add the Project to the Server 23
Figure 25: Assembly Diagram of SCA module for Newly Created External Service Module 24
Figure 26: A Test Client Page 25
Figure 27: Unset Values on Fields of Business Object 26
12/14/2007 Page 27 of 30
1.0 Introduction
The JDBC Resource Adapter (RA) enables the bi-directional connectivity for integration to any database application. The exchange of data for such applications happens at the database level. Updates to database may need to be applied to another Enterprise Information System (EIS) and changes in an EIS may need to be applied to a database. The JDBC RA can integrate with any database, as long as there is a JDBC driver that supports the JDBC 2.0 or higher Specification, available for the database. Examples of such databases include Oracle, Microsoft SQLServer, DB2, Sybase, Informix etc.
The document demonstrates one scenario:
To retrieve all records matching a user-defined query associated with selected Business Objects (BO) that has a parent-child relationship. The adapter will execute the query with a prepared SQL statement.
To gain practical knowledge in setting up and deploying the adapter, complete one or more of the tutorials. Everything you need to complete each tutorial is contained in the tutorial. If you have performed the prerequisite tasks, you can complete each tutorial in under an hour.
1.1 Learning Objectives
After completing a tutorial, you should be able to perform the following tasks:
· Create an adapter project in WebSphere Integration Developer.
· Discover services and associated business objects from the enterprise information system (EIS) and make them part of the adapter project.
· Create a deployable module that you install on WebSphere Process Server or WebSphere Enterprise Service Bus.
· Test the module and validate the results.
1.2 Audience
These tutorials are for integration developers who design, assemble, test, and deploy business integration solutions.
1.3 Software prerequisites
To use these tutorials, you must have the following applications installed:
WebSphere Integration Developer, version 6.1
WebSphere Process Server or WebSphere Enterprise Service Bus, version 6.1
Respective JDBC Database driver
2.0 Prerequisites to run the scenario
2.1 Configuration prerequisites
Before doing any tutorial testing, complete the following tasks:
Creating the authentication alias
Using an authentication alias eliminates the need to store the password in clear text in an adapter configuration property, where it might be visible to others. The authentication alias needs to be set since the adapter uses the username/password to connect to the database. Here are the steps to set the authentication alias in WPS admin console. This authentication alias will be used later when generating the artifacts for the module.
1. In WebSphere Integration Developer, switch to the Servers View by selecting Windows > Show View > Servers.
Figure 1: Choose Servers’ View
2. To set the authentication alias, we need to start the server. In the Servers tab in the lower-right pane of the WebSphere Integration Developer screen, right-click on the server, and then select Start
3. When the server status is “Started”, right-click on the server, and then select “Run administrative console”.
4. Log in to the administrative console by clicking the “Log in” button. The User ID is optional and is meant for recalling the session configuration settings.
5. In the administrative console, click Security → Secure administration, applications, and infrastructure. Under Authentication, click Java Authentication and Authorization Service → J2C Authentication data.
Figure 2: Java Authentication and Authorization Service (JAAS)
a. In the list of J2C authentication aliases that is displayed, click New.
b. In the Configuration tab, type the name of the authentication alias in the Alias field.
c. Type the user ID and password that are required to establish a connection to the database.
d. Optionally type a description of the alias.
e. Click OK. The newly created alias is displayed. Note the full name of the alias, which includes the node name. This full name will be used in subsequent configuration windows.
f. Click Save.
Figure 3: Create a New Authentication Alias with User ID, Password and Description
6. A message appears asking to Save changes to the master configuration. Click on the Save link.
Figure 4: Select Authentication Entries
7. Save all changes made.
Note: We have created an authentication alias that will be used to configure the adapter properties. Restart the server for the changes to take effect.
Creating the tables
The following tables need to be created in the SQLServer database to run the scenario.
a. Script for creating the Customer and Address tables
CREATE TABLE CUSTOMER (
PKEY VARCHAR(10) NOT NULL PRIMARY KEY,
FNAME VARCHAR(20) ,
LNAME VARCHAR(20) ,
CCODE VARCHAR(10) ) ;
CREATE TABLE ADDRESS (
ADDRID VARCHAR(10) NOT NULL PRIMARY KEY,
CUSTID VARCHAR(10) ,
CITY VARCHAR(20) ,
ZIPCODE VARCHAR(10) ) ;
2.2 Extracting the Sample Files
Replicas of the artifacts that were created when using the external service wizard are provided as sample files for your reference. Use these files to verify that the files you create with the external service wizard are correct.
Go to the “Sample” folder and unzip the zip file into a directory of your choice (you may want to create a new directory).
Below is a table of file contents that accompany this tutorial.
Tutorial / Folders / Descriptions1 / BOCustAddrSQLSvr / Generated artifacts, such as XML Schema Definitions (xsd) and Web Services Definition Language (WDL).
BOCustAddrSQLSvrApp / EAR application artifacts, such as deployment descriptors.
BOCustAddrSQLSvrEJB / EJB module artifacts.
3.0 Creating the adapter project in WebSphere Integration Developer (WID)
This tutorial demonstrates how WebSphere Adapter for JDBC 6.1 is used to
populate customer information into an application’s database. This tutorial specifically demonstrates how to populate customer information in the database where customer and address tables have a parent child relationship. A stored procedure is used to populate the address (child) information.
3.1 Configuration prerequisites
The following tasks must be completed for this tutorial:
Create an adapter project by importing the resource adapter archive (.RAR) file into the Connector Projects folder in WebSphere Integration Developer. The following steps describe how to do this.
1. Launch WebSphere Integration Developer by going through the menu on Windows, Start > Programs > IBM WebSphere > Integration Developer V6.1.
Alternatively, run wid.exe within the installed folder.
2. In WebSphere Integration Developer, switch to the J2EE perspective. To do this, click Window > Open perspective > Other, from the menu if not immediately available. In the Select perspective screen, select “Show all,” then select J2EE from the list and click OK.
3. In the J2EE view, import the adapter’s RAR file by right-clicking, and select Import > RAR file.
Figure 5: Import the Adapter RAR File
4. In the Connector Import screen, click the Browse button next to the “Connector file” field, then navigate to the adapter .RAR file.
Figure 6: The Connector RAR File Being Imported
Based on the imported file, the connector module’s name is defined and will determine the project’s name, herewith CWYBD_JDBC. The targeted runtime is WebSphere Process Server v6.1, one of the software requirements
5. Accept all other defaults, then click Finish. A new connector project named CWYBC_JDBC appears under the Connector Projects folder.
Figure 7: Listings of Connector’s Project
A successful import of the adapter will enable us to run the Enterprise Service Discovery (ESD) wizard which assists with the scenario task at hand.
4.0 Scenario: Retrieving All Records Based On A User-Defined Query with a SQLServer Database.
4.1 Configuring the adapter for outbound processing
Run the external service wizard to specify business objects, services, and configuration to be used in this tutorial.
The wizard will guide the user to do the following steps:
1. Setting connection properties for the enterprise service discovery wizard
2. Selecting the business objects and services to be used with the adapter
3. Generating business object definitions and related artifacts
Follow these instructions to launch the Enterprise Service Discovery (ESD) wizard.
1. Make sure you are in the Business Integration perspective. To do this, select Window > Open Perspective > Other. In the Select Perspective screen, select Business Integration (default).
2. In WebSphere Integration Developer, launch the External Service wizard by selecting File -> New -> Other -> External Service
Figure 8: Business Integration’s External Service
3. Select the Business Integration’s External Service
4. Click the Next > button.
Figure 9: The “Adapters” Option, for a New External Service
The Adapters’ option is the choice for connecting to an external system or data source such as a relational database system and by relying on adapter to exchange information with WebSphere Process Server.
Figure 10: Select the Appropriate Adapter Type As Imported
Based on the type of adapter imported, the adapter is selected under the appropriate type.
In this case, “IBM WebSphere Adapter for JDBC (IBM : 6.1) is the type expected. Select the adapter by its name, “CWYBC_JDBC”.
Figure 11: JDBC Driver JAR Files Are Added
If the JDBC Driver JAR files were not found on the project’s classpath, a window prompt would appear to have the necessary files added. Each type of database server requires its own JDBC driver jar files to carry out its operations. The drivers are shipped with the database server, locate the files and add them to the project. Java-based Type 4 database connectivity is recommended and works with these jar files. If Type 2 connectivity is required, locate the native system libraries and add them to the project.
Figure 12: The Adapter Style Options
Based on the type of processing the adapter is required to perform either Inbound or Outbound, this step is as shown in the Figure above.
4.1.1 Setting connection properties for the external service wizard
To connect to the preferred database, select the database type and version and set the following properties:
1. JDBC driver type
2. Database
3. Host name
4. Port number
5. JDBC driver classname
6. User name
7. Password
For example, refer to the following figure as the next wizard page.
Figure 13: Database Connection Properties
Select the type of database server and specify the connection configuration properties, a database connection will be established to retrieve the database schema.
4.1.2 Selecting the business objects and services to be used with the adapter
Figure 14: Object Discovery and Selection
In order to discover the business objects and services on the database, a query will be specified to be run. By specifying a query filter, only relevant items are discovered for selection.
Figure 15: Query Properties
Besides specifying a pattern for schema name as a filter on the supported database object types, additional options are available to configure it. The option “Prompt for additional configuration settings when adding business object” is available which will later determine the relationship between business objects. The second option “Create a query business object to build user-defined select statements” allows a user-defined query to be created with selected business objects.
Figure 16: Configuration Parameters for Parent BO
With Customer as the parent BO, a wizard page will be displayed based on the selected option to prompt for additional configuration settings when adding business objects. Since Customer is the first and only chosen BO, there are 2 configuration settings that are displayed:
1. Name of the column used to perform logical deletes
2. Adding a stored procedure from a list to perform an operation.
None of these applies. Click OK to continue.
Figure 17: Configuration Properties for a Child BO
The decision to select the prompt for additional configuration settings would display the above wizard page for the selected child object. For example, if “Customer” is the first selection, followed by “Address”, the wizard would prompt for the options: