IBM WebSphere Adapter for JDBCQuick 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.

December 14, 2007 Page 1 of 31

Table of Contents

1.0 Introduction...........................................................................................11.1 Learning Objectives........................................................................................................11.2 Audience..........................................................................................................................11.3 Software prerequisites.....................................................................................................1

2.0 Prerequisites to run the scenario...........................................................22.1 Configuration prerequisites.............................................................................................2

2.1.1 Creating the authentication alias.................................................................................22.2 Creating the tables...........................................................................................................5

3.0 Creating the adapter project in WebSphere Integration Developer (WID)..............................................................................................................6

3.1 Configuration prerequisites.............................................................................................64.0 Scenario: Retrieving All Records Based On A User-Defined Query with a SQLServer Database............................................................................8

4.1 Configuring the adapter for outbound processing...........................................................84.1.1 Setting connection properties for the external service wizard..................................124.1.2 Selecting the business objects and services to be used with the adapter..................134.1.3 Generating business object definitions and related artifacts.....................................19

4.2 Deploying the module to the test environment.............................................................214.3 Testing the assembled adapter application....................................................................234.4 Clearing the sample content..........................................................................................26

5/19/2023 Page 2 of 31

Table of Figures

Figure 1: Choose Servers’ View.........................................................................................2Figure 2: Java Authentication and Authorization Service (JAAS)......................................3Figure 3: Create a New Authentication Alias with User ID, Password and Description....4Figure 4: Select Authentication Entries...............................................................................4Figure 5: Import the Adapter RAR File...............................................................................6Figure 6: The Connector RAR File Being Imported...........................................................7Figure 7: Listings of Connector’s Project............................................................................7Figure 8: Business Integration’s External Service...............................................................8Figure 9: The “Adapters” Option, for a New External Service...........................................9Figure 10: Select the Appropriate Adapter Type As Imported..........................................10Figure 11: JDBC Driver JAR Files Are Added.................................................................11Figure 12: The Adapter Style Options...............................................................................12Figure 13: Database Connection Properties......................................................................13Figure 14: Object Discovery and Selection.......................................................................14Figure 15: Query Properties...............................................................................................15Figure 16: Configuration Parameters for Parent BO.........................................................16Figure 17: Configuration Properties for a Child BO.........................................................17Figure 18: User-Defined Query.........................................................................................18Figure 19: Selected and Configured Objects To Be Imported..........................................18Figure 20: Configure Operations of Selected Business Objects........................................19Figure 21: Service Generation and Deployment Configuration........................................20Figure 22: Creating a New Test Module...........................................................................21Figure 23: Add and Remove Projects to the Server..........................................................22Figure 24: Add the Project to the Server...........................................................................23Figure 25: Assembly Diagram of SCA module for Newly Created External Service

Module.......................................................................................................................24Figure 26: A Test Client Page...........................................................................................25Figure 27: Unset Values on Fields of Business Object.....................................................26

5/19/2023 Page 3 of 31

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 ObjectivesAfter 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 AudienceThese tutorials are for integration developers who design, assemble, test, and deploy business integration solutions.

1.3 Software prerequisitesTo use these tutorials, you must have the following applications installed:

WebSphere Integration Developer, version 6.1WebSphere Process Server or WebSphere Enterprise Service Bus, version 6.1Respective JDBC Database driver

December 14, 2007 Page 1 of 31

2.0 Prerequisites to run the scenario

2.1 Configuration prerequisitesBefore 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

5/19/2023 Page 2 of 31

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.

5/19/2023 Page 3 of 31

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.

5/19/2023 Page 4 of 31

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.

5/19/2023 Page 5 of 31

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 prerequisitesThe 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

5/19/2023 Page 6 of 31

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.

5/19/2023 Page 7 of 31

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

5/19/2023 Page 8 of 31

3. Select the Business Integration’s External Service4. 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.

5/19/2023 Page 9 of 31

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”.

5/19/2023 Page 10 of 31

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.

5/19/2023 Page 11 of 31

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 type2. Database3. Host name4. Port number5. JDBC driver classname6. User name7. Password

For example, refer to the following figure as the next wizard page.

5/19/2023 Page 12 of 31

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

5/19/2023 Page 13 of 31

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.

5/19/2023 Page 14 of 31

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.

5/19/2023 Page 15 of 31

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 deletes2. Adding a stored procedure from a list to perform an operation.

None of these applies. Click OK to continue.

5/19/2023 Page 16 of 31

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:

1. to choose the parent table,2. setting its single cardinality, 3. select a column as the primary key if it’s not defined in the table schema, 4. building a foreign key relationship based on a common column between them.

Additional referential integrity between tables can be set based on the options presented such as “Parent object owns child object (cascade delete)”.

Click “Add” to select a preferred stored procedure to carry out an operation on a business object. For example, choose “CreateSP” to create a new Address.

5/19/2023 Page 17 of 31

Figure 18: User-Defined Query

In order to define a query, select “Query Statements” at the bottom of the left-window list of discovered objects and choose “Select Statement 1” which is based on the number of select queries as determined, a window prompt will appear to configure the query statement as shown above.

Figure 19: Selected and Configured Objects To Be Imported

5/19/2023 Page 18 of 31

The objects of interest will be discovered on the enterprise system by importing its schema and based on parameter configuration will determine the service interface mapping artifacts to be produced.

4.1.3 Generating business object definitions and related artifacts

Figure 20: Configure Operations of Selected Business Objects

By importing the business objects’ schema and configuring it, business object definition and related artifacts can be created as preferred.

By default, the operations to Create, Update, Delete, Retrieve, RetrieveAll, and ApplyChanges are selected for inclusion in the service interface artifact to be generated. Any of these operations can be excluded.

5/19/2023 Page 19 of 31

Figure 21: Service Generation and Deployment Configuration

The service interface artifact to be published can have its properties configured such as renaming the service operations’ names. Click “Edit Operations” to do the appropriate.

Deployment properties are specified as one of the following:

1. J2C Authentication Data Entry, as specified in the JAAS Alias entry creation.2. Database System Connection Information

If the choice is JAAS, check it out and enter the JAAS alias (node name / alias). Otherwise, specify the connection properties for the particular database, including the relevant password.

With the business objects’ definitions configured and artifacts ready for publishing, a new test module can be created to test it.

5/19/2023 Page 20 of 31

Figure 22: Creating a New Test Module

With a new module created for testing the business objects, the wizard comes to a finish.

4.2 Deploying the module to the test environment

The result of running the external service wizard is a Service Component Architecture (SCA) module that contains an EIS import or export. This SCA module will be tested with the WID test client.

5/19/2023 Page 21 of 31

1. Add the module created earlier to the server using the server panel in WID. Right-click on the server, and then select Add and remove projects.

Figure 23: Add and Remove Projects to the Server

2. Add the SCA module to the server.

5/19/2023 Page 22 of 31

Figure 24: Add the Project to the Server

Click Finish to end the wizard process and deploy the application. Immediately look at the Console window within WID for signs of successful completion. If not, troubleshoot any error message based on the suggested recommendation.

4.3 Testing the assembled adapter application

Test the assembled adapter application using the WebSphere Integration Developer integration test client by following these steps:

1. Based on the SCA module file, sca.module, double-click to display it as an assembly diagram and right click anywhere within the diagram to bring up the context menu for creating a test module, choose “Test Module”. Refer to the following figure.

5/19/2023 Page 23 of 31

Figure 25: Assembly Diagram of SCA module for Newly Created External Service Module

A test client will be created with input fields.

5/19/2023 Page 24 of 31

Figure 26: A Test Client Page

A test client page is automatically created and displayed which is ready for input values before the test execution. The first invocation is created by default. Subsequent invocation can be created

by clicking the “Invoke” button.

2. Determine the type of operation; the verb prefix reflects it. For example, one of the operation names for selection, “retrieveallRetrieveAllCustomerBG”, is a named combination of the verb prefix (retrieveall), business object name (RetrieveAllCustomer), and an abbreviation for “Business Graph” (BG).

3. Based on the type of operation, double-click on the field under Value column for the corresponding field under Name column to enter an appropriate value. Since a user-defined query has been created earlier, no input value is necessary. Simply, unset all existing Value fields of the relevant business object.

5/19/2023 Page 25 of 31

Figure 27: Unset Values on Fields of Business Object

4. To run the test client, click the “Continue” button on the top of the page.5. The result of the test execution will be displayed once completed.

4.4 Clearing the sample content

After a record has been created with the WID environment, it can be removed with the Delete operation, simply determine the key field that uniquely represent the record just created and enter its value.

5/19/2023 Page 26 of 31

5.0 Troubleshooting

Adapter fails to start up If the adapter fails to start up, refer the adapter log file to find the cause of failure. Here are some of the common reasons why the adapter would fail to start up.

1. Error: Driver class does not exist. Cause: The database driver jar does not exist in the runtimes lib folder.

2. Error: Logon error; invalid username/password.Cause: The authentication alias does not have the proper username/password that can connect to the database.

Failure during adapter processingIf the adapter fails during processing, refer to the adapter log file to find the cause of the failure. 1. Error: Primary key does not exist

Cause: The table does not have a primary key defined. Hence, the PrimaryKey ASI on the BO is not set to true.

2. Error: A record already exists Cause: A record with the primary key already exists in the database. Try inserting a record with a primary key that does not exist in the database.

5/19/2023 Page 27 of 31