1 Reply Latest reply on Aug 21, 2011 5:59 PM by Arshad Syed

    SQLListener not responding.

    Arshad Syed Newbie

      Sorry if I double posted. Couldn't find my initial post.


      I am trying to get the QuickStart example for SQL Listener working with a Oracle datasource. I insert rows into the table but nothing seems to happen in the corresponding function. Here are the configurations I am using. The displayMessage function below just will not trigger. Thanks in advance for you help.




      <?xml version="1.0"?>

      <jbossesb parameterReloadSecs="5"


      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://anonsvn.labs.jboss.com/labs/jbossesb/trunk/product/etc/schemas/xml/jbossesb-1.0.1.xsd http://anonsvn.jboss.org/repos/labs/labs/jbossesb/trunk/product/etc/schemas/xml/jbossesb-1.0.1.xsd">


        <sql-provider datasource="java:/OracleDS" name="GatewaySQLprovider" url="jdbc:oracle:thin:xxx">

         <sql-bus busid="helloSQLChannel">

          <sql-message-filter insert-timestamp-column="INSERT_DT"

           message-column="ACN_REGION_DESCR" message-id-column="ACN_REGION_CD"

           post-delete="false" status-column="ACN_REGION_CD"

           tablename="TEST_ACN_REGION" where-condition="ACN_REGION_CD = 'N'"/>





        <service category="SQLService"

         description="Hello World TX SQL Action (esb jdbc listener)" name="myTxListener">


          <sql-listener busidref="helloSQLChannel" is-gateway="true" name="SqlGateway"/>


         <actions mep="OneWay">

          <action class="com.ncs.esbtest.SQLListenerAction" name="action1" process="displayMessage"/>


















      package com.ncs.esbtest;

      import java.util.Map;

      import org.jboss.soa.esb.actions.AbstractActionLifecycle;
      import org.jboss.soa.esb.helpers.ConfigTree;
      import org.jboss.soa.esb.message.Message;

      public class SQLListenerAction extends AbstractActionLifecycle
      private boolean fail;

      protected ConfigTree _config;

      public SQLListenerAction(ConfigTree config)
        _config = config;

      public Message noOperation(Message message)
        return message;

      public Message displayMessage(Message message) throws Exception
        System.out.println("DATA READ:  ");
        boolean problem = false;

        Map<String, Object> rowData = (Map) message.getBody().get();
        StringBuffer results = new StringBuffer();
        for (Map.Entry<String, Object> curr : rowData.entrySet())
         results.append("column " + curr.getKey() + " = <" + curr.getValue() + ">");

         System.out.println("DATA READ: " + curr.getValue());

        // Set message properties and message body so that SystemPrintln will
        // display message
        message.getProperties().setProperty("jbesbfilename", "helloworldTxSQlAction.log");

        if (problem)
         System.out.println("Will rollback transaction. Expect to see record again!");
         System.out.println("Will commit transaction. Will not see record again!");

        if (!problem)
         return message;
         System.out.println("BAD READ ON DATA!");

         throw new RuntimeException();

      // This makes it easier to read on the console
      private void logHeader()

      private void logFooter()















      <?xml version="1.0" encoding="UTF-8"?>

         <mbean code="org.jboss.internal.soa.esb.dependencies.DatabaseInitializer"
            <attribute name="Datasource">java:/OracleDS</attribute>
            <attribute name="ExistsSql">select * from test_acn_region</attribute>

        • 1. Re: SQLListener not responding.
          Arshad Syed Newbie

          Never mind. I downloaded the source code and figured it out. Each time a row is added to the table, the Status column needs to have a value of 'P'. It adds this to your filter for the where clause. How is anyone supposed to figure this out??? Download source code and play around, I guess. Anyway, the output with a debug statement was showing as follows:


          7:32:11,311 INFO  [EsbDeployment] Starting ESB Deployment 'Quickstart_helloworld_TX_SQL_action.esb'
          17:32:11,342 INFO  [InquiryHelper] uddi:juddi.apache.org:671bc988-b9b8-4e85-b1a1-1ecb59fd5369 is modified Sun Aug 21 17:23:47 EDT 2011 1313961827450
          17:32:11,342 INFO  [STDOUT] SQL to Poll: select * from TEST_ACN_REGION where ACN_REGION_CD like 'N%' and  upper(ACN_REGION_CD) like 'P%'


          Also, the other thing is your timestamp column should be of type Varchar.We have it defined as timestamp in Oracle and that is causing an issue.