JDBC adapter notification implementation

• Active UM(Universal Messaging) connection.
• Active JDBC connection.

The process of configuring the JDBC connection is thoroughly detailed in a dedicated article. As a result, we won’t be delving into JDBC configuration within this particular article. If you’re interested in exploring the JDBC configuration procedure, feel free to check out the article by clicking here.

JDBC Adapter Notification Implementation :

• Insert Adapter Notification :
  
  • Create a package with below package structure.
    

    

     

  • In the adapter folder, right-click to create a new adapter notification named “insertNotification.” While creating this notification, make sure to select the “webMethods adapter for JDBC” as the adapter type, and choose the appropriate JDBC connection you’ve set up for database communication. For the template, select “InsertNotification.”
    
  • As mentioned in the previous article, the creation of the adapter notification in webMethods server will automatically generate the buffer table and essential triggers. However, for a basic notification, the DBA also has the option to configure this manually.
  • Now open the “insertNotification” and navigate to the “Configure Notification” tab. Here, you’ll find the buffer table and trigger name displayed. Additionally, you can verify your database, you’ll notice that these components have been successfully created.

    

     

    
  • Once you create the adapter notification you will observe that an adapter notification and a publishable document that have been generated in the adapter folder.
    

     

  • Now open the publishable document, you’ll notice that it only contains the “env” document.
    

     

  • Let’s open “insertNotification.” In the “table” tab, pick the table you want to monitor for insert event. Then, in the “SELECT” tab, set up the fields you want to fetch when something gets inserted and save the adapter.

    

     

    

     

  • Once you’ve saved the adapter notification, revisit the publishable document. You’ll see that the field you specified to retrieve details from has now been included in the document.
    

     

  • Now we need to sync the publishable document with UM so right click on the publishable document and sync it.

    

     

    

     

  • Open the JDBC adapter page in your integration server and go to the “Pooling notification” page you will notice that the “insertNotification” which we have created is now visible but it is in disable state.
    

     

  • Click on the “Edit Schedule” option for the notification. Here, you can set up the time interval for the integration server to check if any insert events have been triggered in the database table and save it.
    

     

  • Now enable the notification by selecting “Enabled” status from the “JDBC Polling Notifications” page.
    

     

  • Next, to process the records retrieved from the database table, it’s essential to create a service. Within this service, we’ll implement the logic to handle the fetched records. Additionally, set up a trigger that will subscribe to the events and pass the same to the processing service.
  • Go to the services folder and make a new flow service called “processInsertedRecord.” In the input section, drag the publishable document and name it with full path of the publishable document type.
    

     

     

  • Inside the processing service, our aim is to create the logic that logs the records received from the insert event. To achieve this, we’ll call the “pub.flow:debugLog” service and within its message variable, insert the following hardcoded content: “Insert Event : Employee ID : [%AdapterNotificationDemo.adapter:insertNotificationPublishDocument/EmpId%] , EmployeeName : [%AdapterNotificationDemo.adapter:insertNotificationPublishDocument/EmpName%] and EmployeePhone Number : [%AdapterNotificationDemo.adapter:insertNotificationPublishDocument/EmpPhoneNo%]”. Remember to ensure that the variable substitution option is enabled.
    

     

  • Now it’s time to create a trigger which will subscribe the events and call our processing service, so right click on the trigger folder and create “webmethods messaging Trigger” and give it an appropriate name.
    

     

  • Now click on the icon above to the document type and select the publishable document.
    

     

  • In the Service section, pick our processing service.
    

     

  • Now the setup is completed, go to your database and insert one record to the table, for this demo I will use “INSERT INTO Employee (EmpId,EmpName,EmpPhoneNo) VALUES (106, ‘HarmoniGate’,’99999999′)” query to insert the record to table and same should be visible to us in the server log.
  • Congratulations our insert notification is working as expected😀.

    

     

• As we have discussed in the previous article for the basic notification we need to create the buffer table and the necessary trigger so click below download button to get the query for the same.

  

  Note : We are using MySQL server for this demo.

   

   

• Within the adapter folder, create a new subfolder named “basicnotification.” Inside this folder, generate an adapter notification by selecting the basic notification template.
• In the “Table” tab, pick the table you wish to monitor, and in the “SELECT” tab, choose the fields you want to retrieve.
  
  

  

   

  
• Now enable the adapter notification by setting the interval to 5 second.
  

   

• Now  right-click on the publishable document and synchronize it with UM, following the same procedure as in the previous demonstration.
• In the service folder, make a new flow service called “processBasicNotification.” Inside the service’s input section, add the basic notification’s publishable document and use its full name, similar to what we did in the previous demo.
  
  

   

• Now, add a “pub.flow:debugLog” step and set the message as: “BasicNotification: EmployeeID: [%AdapterNotificationDemo.adapter.basicNotification:BasicNotificationPublishDocument/EmpId%], EmployeeName: [%AdapterNotificationDemo.adapter.basicNotification:BasicNotificationPublishDocument/EmpName%], and EmployeePhoneNumber: [%AdapterNotificationDemo.adapter.basicNotification:BasicNotificationPublishDocument/EmpPhoneNo%].” Remember to check the option for variable substitution. This way, the service will log the details of the employee inserted into the database via the basic notification.
• Next, set up a “webMethods Messaging Trigger” and configure it by assigning the processing service and the publishable document, just as we did in the previous steps.
  
  

   

• We have now completed the setup , let’s insert one record into the table and see the out come.
• Congratulation!!! We did it!!😀.
  
  
  
  
• Note: Basic notification is capable of capturing Insert, Update, and Delete events, but it’s important to ensure that you have the required triggers set up accordingly.

Important Note: The process for creating other notifications follows the exact same steps as the Insert notification. Therefore, we won’t be discussing those in this article. If you’re interested in a dedicated article for those, please leave a comment on the article, and we’ll respond with an article addressing the topic.