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.

       

      jboss-esb.xml:

       

      <?xml version="1.0"?>

      <jbossesb parameterReloadSecs="5"

      xmlns="http://anonsvn.labs.jboss.com/labs/jbossesb/trunk/product/etc/schemas/xml/jbossesb-1.0.1.xsd"

      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">

      <providers>

        <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'"/>

         </sql-bus>

        </sql-provider>

      </providers>

      <services>

        <service category="SQLService"

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

         <listeners>

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

         </listeners>

         <actions mep="OneWay">

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

         </actions>

        </service>

      </services>

      </jbossesb>

       

       

       

             

       

       

       

       

       

       

      SQLListenerAction.java

       

       

      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;
      }

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

        logHeader();
        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());
        
        }
        System.out.println(results.toString());
        logFooter();

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

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

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

         throw new RuntimeException();
        }
      }

      // This makes it easier to read on the console
      private void logHeader()
      {
        System.out.println("\n&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&");
      }

      private void logFooter()
      {
        System.out.println("&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n");
      }

      }

       

       

       

       

       

       

       

       

       

      jbossesb-service.xml:

       

       

       

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

      <server>
         <mbean code="org.jboss.internal.soa.esb.dependencies.DatabaseInitializer"
             name="jboss.esb:service=OracleTransactedDatabaseInitializer">
            <attribute name="Datasource">java:/OracleDS</attribute>
            <attribute name="ExistsSql">select * from test_acn_region</attribute>
            <depends>jboss.jca:name=OracleDS,service=DataSourceBinding</depends>
         </mbean>
      </server>

        • 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.

           

          Thanks,

          Arshad