Tibco Database Adapter is used for enabling communication between TIBCO processes and database system. There are two types of services that can be used with a database adapter:
- Publication Service
Adapter Publication service extracts data from the changed rows of a database table and publishes them on appropriate subject names which are then subscribed by adapter subscriber process starter.
- Subscription Service
Subscription service of a database adapter does opposite to a publication service. When running as a subscriber, database adapter listens on a subject, receives messages and updates the relevant tables in its associated database.
In this tutorial, we will be covering publication service of database adapter. If you are interested to know about TIBCO Adapter Subscription service, you can go to tutorial TIBCO Adapter: Step By Step Tutorial For Adapter Subscription Service
In this tutorial of Adapter Publication service, We will write a publication service that will look for any updates in a table TBL_EMPLOYEE in Oracle database and it will publish the updates on a subject. We will write a business process that will have Adapter Subscriber as start activity which will receive a message from the publication service of the specified adapter.
Step 1: Database Table Creation
Our adapter will be based on a table TBL_EMPLOYEE. We need to create the table using following query:
— Create table create table TBL_EMPLOYEE ( EMP_CODE VARCHAR2(20) not null, EMP_NAME VARCHAR2(30) not null, EMP_DESIGNATION VARCHAR2(30) not null, EMP_AGE VARCHAR2(10) )
Step 2: Create database Adapter
In our project AdapterTest, we create a new Active Database Adapter Configuration by choosing Add Resources–>Active Database Adapter–>Active Database Adapter Configuration. We specify database driver, Schema name, IP, Port, User, Password etc. in Design-time connection tab for it. We need to use Test Connection button on the bottom to ensure that connection is fine with database.
In Run-time Connection tab we need to specify ODBC DSN. ODBC DSN must be first created for this purpose. ODBC connection is created in Windows Environment in following way. Creating ODBC Connection for TIBCO adapter: Choose Start–>Control Panel–>Administrative Tools–>Data Sources (ODBC) in your windows machine.
Under System DSN tab, create a new data source and name it TIBCO. Specify database parameters of your TIBCO database in required fields.
Using Test Connect button, test if your database connection is successful by giving database username and password.
If you have configured your ODBC connection correctly, you should get a message “Connection Established” as shown below:
Now we have a working ODBC connection configured in our windows machine. So we can safely use this ODBC name in Run-time Connection tab of our adapter configuration. Creating Adapter Publication Service: Now we need to add a publication service in our adapter. In “Adapter Services” folder, add a new publication services by choosing Add Resource–>Active Database Adapter–>Publication Service Configuration tab of publication service should be configured as shown below:
In the Table Tab of publication service configuration, choose the table TBL_EMPLOYEE by clicking on Add Table button. It will look as below after adding the table:
In the publisher options tab, we have chosen Publish By Value as Storage Mode and Update as Update Mode. Note that we don’t create Publishing table. Its created automatically.
We don’t need to make any change in Advanced tab and it should look like this:
Now we are done with configuring our Adapter and making it ready.
Now is the time to move forward for crating a process for our adapter service.
Step 3: Create Adapter Subscriber Process
We create a process employeeSubscriber that will subscribe to the adapter we created in step 2. This process will have a Adapter Subscriber activity as its process starter. This process will write the information that it receives through adapter into a text file. Process will look like below:
In Adapter Subscriber Configuration tab, we choose our Adapter Service by using binocular. This will be the service for which this subscriber will be listening on a given subject.
In Transport Tab of adapter subscriber, subject, transport type etc. is specified. In this tutorial example, I have kept it to default without making any changes as shown below:
Adapter Subscriber Output tab shows the output of the subscriber activity. In body of its output, it will have all the columns of our publisher table as shown below:
We use confirm activity after Adapter Subscriber to confirm the receipt of message. The Confirm activity confirms any confirmable messages received by the process instance. In configuration of confirm activity, we specify confirm event which is adapter subscriber in our case.
We have used a Write File activity which will write Employee Details to a text file.
Now we are done with setting up all required bricks and blocks of our building and now is the time to test it.
Step 4: Test TIBCO database Adapter Publication Service
We have created one adapter publication service and one business process. So we need to use both Adapter Tester and Process Tester for testing our code.
We can open Adapter Tester by going through Tools Menu in Tibco designer as shown below:
After opening Adapter Tester, choose the adapter from the left side where all available adapter services are shown. Make sure that Working Directory points to the bin folder inside adapter installation path as shown below:
Using Start Buton, start the adapter. Once adapter is started, Start Button should change to Stop Button.
Now our adapter is up and running. We need to start our process as well so that any messages received by Adapter go to its subscriber.
Once we have our adapter as well as process ready in the tester, its time to play with our database table to fire the event.
We add a new record in the database table TBL_EMPLOYEE using below query:
INSERT INTO TBL_EMPLOYEE
As soon as value is inserted in TBL_EMPLOYEE, it gets published to Publisher table P_TIBL_EMPLOYEE with ADB_L_DELIVERY_STATUS as ‘N’:
Adapter Publication Service gets the information, process having adapter subscriber subscribes the request and process runs to its completion as shown below:
Now, if we check the same record in the table P_TBL_EMPLOYEE, its ADB_L_DELIVERY_STATUS has changed to C (Complete):
Now our last step in this tutorial is to check in the text file that we wrote in our process and verify that employee record has been written in the file:
Congratulations, we are now done with our tutorial TIBCO Database Adapter: Step By Step