5 Replies Latest reply on Jan 11, 2007 6:19 PM by Esteban Schifman

    SqlTableGatewayListener

    Murali Katipalli Newbie

      We would want to see the sql generated by the SqlTableGatewayListener. How do I turn that on? We tried changing in the log4j the following entry -

      <!-- Limit JBoss categories -->
       <category name="org.jboss">
       <priority value="DEBUG"/>
       </category>



      but did not seem to work. Its printed more debug stmts but not the sql.

      Please Help

        • 1. Re: SqlTableGatewayListener
          Esteban Schifman Newbie

          Hello,

          There are 4 sql statements in SqlTableGatewayListener that are composed by the following methods:

          scanStatement() (line 404) updateStatement() (line 433) selectForUpdStatement() (line 457) and deleteStatement() (line 480)

          If you just add a _logger.debug() statement just before these methods return the String value, you'll be able to see the generated SQL.

          I hope this is what you're looking for.

          Esteban.

          • 2. Re: SqlTableGatewayListener
            Murali Katipalli Newbie

            Thankyou Esteban for your quick response - that worked.
            Looks like SqlTableGatewayListener has the following drawbacks -
            1) Cannot specify custom SQL
            2) Forces to create an extra column in your table/schema for InProcess Field
            3) Cannot have multiple table select stmts..

            Your thoughts or ideas are welcome..

            Thanks
            Murali

            • 3. Re: SqlTableGatewayListener
              Esteban Schifman Newbie

              Hello Murali, and thank you for your comments.

              Many existing applications (legacy or third party) won't allow you to alter their DB in any way, not even adding your own triggers, let alone adding columns or manipulating content of tables. We have consequently been thinking of new ways to configure SQL gateways, in such a way that existing data models will remain completely untouched.

              The items you mention are in line with other users' comments, and we are planning to refactor the sql table gateway so:

              1) you won't need an extra column in any existing table, so you can leave your existing data model untouched. You will however need to establish some mechanism to determine if a row (in the rowset or view) has already been processed, is being processed, is 'available for process', or is 'in error' for gateway purposes.

              2) you'll be able to define your own SQL for the query that brings back a list of candidates to be processed (from a single table or a sophisticated view)

              This is our direction for sql gateways. Unfortunately I am currently unable to commit to a specific date for these changes to be included in our code.
              Nevertheless, If you decide to try to design and code a new sql gateway that meets other particular requirements (e.g. does not present the drawbacks you had mentioned), we will be happy to support you and contribute as much as possible to that initiative.

              Thanks.

              Esteban

              • 4. Re: SqlTableGatewayListener
                Murali Katipalli Newbie

                Could you pl. help me with this final one too. The SqlTableGatewayListener is giving me the following error -
                "FOR UPDATE can not be used in a SELECT which is not part of the declaration of a cursor or which is not inside a stored procedure."

                I cannot figure out why. Here's how my config file looks like -

                <esb-db-gateway
                target-service-category="local"
                target-service-name="closeq"
                gatewayClass="org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener"
                pollLatencySeconds="30"
                driver="com.sybase.jdbc2.jdbc.SybDriver"
                URL="jdbc:sybase:Tds:dummy:3025/Dummy"
                username="xxx"
                password="xx"
                tableName="ESB_CLOSE"
                selectFields="id,close_number,intab,inprocess"
                keyFields="close_number"
                inProcessField="inprocess"
                whereCondition="close_number='1'">
                </esb-db-gateway>

                'id' in the table is the primary sequence key. Any ideas?

                • 5. Re: SqlTableGatewayListener
                  Esteban Schifman Newbie

                  Hello again Murali,

                  My guess is that Sybase won't allow the 'FOR UPDATE' unless (as the error message reads) within a cursor or a stored procedure.
                  You could just try by a) eliminating the 'for update' in the selectForUpdStatement() method (line 470), in which case there would be danger of concurrent update by more than one thread, or b) try another RDBMS ( we know it will work with PostgreSQL or MySql)

                  Good luck !

                  Esteban