webMethods JDBC Adapter tutorial - Create a JDBC Adapter Service

Knowledge base
, , ,
1

Duration:  30 minutes

In this tutorial you will create a JDBC Adapter Service to select customer data from the Persons table in an SQL Server database.

Prerequisites #

1.   MS SQL Server 2008 or 2012 (any type, including the free Express) is installed and running on the local computer or one that is network accessible to the host that the webMethods ESB (Integration Server) free download is installed on.  Refer to the MS SQL Server installation instructions that come with the MS product.

2.   The Persons table is created and at least one row is entered in the table:

a.    Create the Persons table by executing the following SQL in your SQL Server database.

CREATE TABLE Persons(

PersonID int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255));

b.    Run the following INSERT statement to add a row to the Persons table:

INSERT INTO Persons VALUES(1, ‘Doe’, ‘John’, ‘100 Main St’, ‘Leesburg’);

 3.   Put the appropriate MS SQL Server JDBC Driver jar file in the <Software AG Installation Directory>\IntegrationServer\lib\jars\custom directory and restart the Integration Server (IS).

4.   webMethods Integration Server & Software AG Designer are installed.

5.   webMethods Integration Server is up and running.  Instructions on how to start the IS are found in the Prerequisites part of the 1. Create an IS Package and Folders FLOW tutorial.

6.   You must import the package: Start JDBC_Tutorial.zip using these instructions:Import an IS Package

For this tutorial, it helps to have a basic knowledge of the Software AG Designer and the SQL Database language.  Also, any JDBC Compliant DBMS can be used for this exercise.  This tutorial assumes MS SQL Server.  If another one is used, you would have to download the appropriate JDBC Driver from your database vendor and put it in the directory specified in prerequisite 3 above.

Step Outline #

You create a JDBC Adapter Service with the following steps:

  • Update a JDBC Adapter Connection
  • Create the JDBC Adapter service
  • Test the  JDBC Adapter service 

Step 1:  Update a JDBC Adapter Connection #

In this step:  You update a JDBC Adapter Connection for a SQL Server database by logging into the Integration Server Administrator.

To log into the Integration Server from Firefox:

  • select the Firefox browser icon next to the Start menu or select it from the Start Menu or open another browser:

  • Use the following values to log into the Integration Server:

Field Value
Username: Administrator
Password: manage (default password for IS)

Note:  These values may be stored from previous sessions.  In this case, select OK.

  • Firefox displays the Integration Server Administrator page: 

  • select JDBC Adapter from the Adapters menu: 

  • The Integration Server displays the JDBC Adapter Administration page.
  • The JDBC Adapter Administration page lists the JDBC adapter connections configured for this server.
  • select the Edit icon for the JDBC_Tutorial:MyConnection adapter connection to update it based on your database credentials:

  • Add your environment specific information to connect to your SQL Server database and save the JDBC_Tutorial:MyConnection by clicking Save Changes:

Parameter Value
serverName Server that the DB is installed on: localhost, IP Address, or hostname
user Database username (schema name) used to login to the DB
password and Retype password Database password associated with the user above to login to the DB
databaseName Database name that you created the Person table in

Note: If you use another DB Vendor, you will have to use the appropriate DataSource Class and portNumber.

image.jpg510×585 48.2 KB

  • On the next page select the No link in the Enabled column to enable the JDBC_Tutorial:MyConnection JDBC Adapter Connection:

  • If the JDBC Adapter Connection enables successfully, you can move forward to Step 2.  If not, read the error message that displays and fix the issue before moving forward.

Step 2:  Create a JDBC Adapter Service #

In this step:  You create a new JDBC Adapter service using the New Adapter Service wizard in the Software AG Designer application.

To create the JDBC Adapter service:

  • Start Software AG Designer from the Start Menu -> All Programs -> Software AG -> Tools -> Software AG Designer 9.5.
  • In the Software AG Designer Welcome page, click on the Open the Service Development Perspective link:

  • In the Package Navigator view, expand Default -> JDBC_Tutorial -> JDBC_Tutorial  to view the package’s contents:

  • Right click on the JDBC_Tutorial folder and select New -> Adapter Service

  • Enter the Element name of selectPersonByID and click Next>:

image.jpg526×639 40.4 KB

  • In the New Adapter Service dialog, select JDBC Adapter and click the Next > button.

  • Select your JDBC_Tutorial:MyConnection JDBC Adapter Connection that you updated in Step 1 and click Next >:

 

  • Click the SelectSQL template to create an adapter service based on a database SELECT query and select the Finish button:

Note:  You can specify a wide variety of database operations on the template selection list, including SQL, Stored Procedures, and Batch operations.

  • Designer creates the new JDBC Adapter service based on the SelectSQL template and opens it in the editor:

  • You can now configure the adapter service. 

Step 2:  Configure the JDBC Adapter Service #

In this step:  You configure the JDBC Adapter service to return all the rows in the Persons database table.

To configure the JDBC Adapter service:

  • Click inside the cell under the Table Name column in the t1 row and select the button that appears in the cell with the ellipsis on it:

  • In the Adapter Tree Chooser dialog expand <current catalog> -> <current schema>, select the Persons table and click OK:

Note:  Designer displays a drop-down a list of all the SQL Server databases and tables that the user defined in the Adapter Connection has the authority to access.

  • Your Adapter Service should look like the following:

  • Click the SELECT tab and the Fill in all rows to the table
      button.  Your completed Adapter Service should look as follows:

  • Click the Save button in the toolbar of Designer to save your work:

Note:  You can delete columns from the query by highlighting the row and selecting the Remove Row

button.  Also, you can specify a where clause in the WHERE tab to limit the rows returned from the table.

You have configured the JDBC Adapter service, and can now test the service.

Step 3:  Test the JDBC Adapter Service #

In this step:  You test the JDBC Adapter service by running the service to return all the rows from the Persons table.

To test the JDBC Adapter service:

  • Right-click on the selectPersonByID adapter service in the Package Navigator view and select Run As -> 2 Run Service:

  • Designer prompts you for any optional inputs.  Click the OK button to test your adapter service:

  • You should see all the rows in the Persons database table in the Results view:

Note: You can also create JDBC Adapter Services to insert, update and/or delete rows from the Persons table.

Conclusion #

You have updated a JDBC Adapter Connection to connect to your database, created a JDBC adapter service based on the SelectSQL template that returns all rows from the Persons table, and tested it.

To import the solution of this tutorial download End JDBC_Tutorial.zip and follow the directions in the Import an IS Package tutorial.  You will still have to enter the DB credentials and enable the JDBC Adapter Connection in the solution import according to Step 1 in this tutorial. 


Read in this series: