webMethods JDBC Adapter tutorial — Create a JDBC Adapter Service
In this tutorial you will create a JDBC Adapter Service to select customer data from the Persons table in an SQL Server database.
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(
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.
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.
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
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:
Note: These values may be stored from previous sessions. In this case, select OK.
- 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:
Server that the DB is installed on: localhost, IP Address, or hostname
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
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.
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:
Note: You can specify a wide variety of database operations on the template selection list, including SQL, Stored Procedures, and Batch operations.
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:
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.
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:
Note: You can also create JDBC Adapter Services to insert, update and/or delete rows from the Persons table.
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.
Originally published at http://techcommunity.softwareag.com.