8 Replies Latest reply on Apr 24, 2014 2:28 PM by Tom Cunningham

    helloworld_sql_action

    S K Newbie

      What does this example actually explain?

        • 1. Re: helloworld_sql_action
          Kurt Stam Master

          It explains how you can use a table in the db as a message channel (bus).

          • 2. Re: helloworld_sql_action
            Kurt Stam Master

            darn the answer is longer then the question

            • 3. Re: helloworld_sql_action
              Mark Little Master

              Go to the wiki and follow the link to the quickstart overview.

              • 4. Re: helloworld_sql_action
                Barry Sheward Newbie

                The link http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossESBQuickStart clearly states:

                helloworld_sql_action - demonstrates the use of the SQL listener/poller

                So, the SQL Listener is configured using the following XML:

                 <sql-provider name="SQLprovider"
                 url="jdbc:sqlserver://localhost\SQLEXPRESS;initialCatalog=dbo;databaseName=dbo;"
                 driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
                 username="sa"
                 password="password"
                 >
                 <sql-bus busid="vioeTaskSQLChannel" >
                 <sql-message-filter
                 tablename="dbo.task_queue"
                 status-column="details"
                 message-id-column="id"
                 />
                 </sql-bus>
                 </sql-provider>
                


                The source code can be found at trunk/product/rosetta/src/org/jboss/soa/esb/listeners/gateway/SqlTableGatewayListener.java

                The SqlTableGatewayListener periodically polls the database using a SQL statement similar to:

                select _selectFields from _tableName where _where and _status like ' _sLike%' order by _orderBy


                The SQL statement is build as:

                _selectFields is optional and can be defined using the selectFields attribute in the sql-message-filter.
                _tableName is from the XML configuration's tablename attribute, in the case of the XML config above, "dbo.task_queue".
                _where is optional and not shown in the XML configuration above. It can be defined using the whereCondition attribute in the sql-message-filter.
                _status it expects to find in the column defined as status-column, in this case "details".
                _sLike is optional and not shown in the XML configuration above. It can be defined using the inProcessValues attribute in the sql-message-filter. By default is looking for "P", the default possible states are:
                P - Pending
                W - Working
                E - Error
                D - Done
                _orderBy is optional and not shown in the XML configuration above. It can be defined using the orderBy attribute in the sql-message-filter.

                Whatever results are returned from the select, these are put into a message.

                Are there any plans for a JBoss ESB Listener Configuration Guide, similar to the Message Action Guide?

                • 5. Re: Re: helloworld_sql_action
                  Joao Silva Newbie

                  It is possble force the ESB to insert "status" E = error in some cases?

                  • 6. Re: helloworld_sql_action
                    Tom Cunningham Master

                    Joao, what cases are you trying to insert status "E" for? 

                    • 7. Re: helloworld_sql_action
                      Joao Silva Newbie

                      Hi Tom,

                       

                      i have a few doubts...

                       

                      1 - when the status is changed to 'D' when all actions are done? ( in my code looks like is in the begin action )
                      2- 'E' in my case i use a load contra table that have the information that i want copy to other DB. so all the logic is in the action.
                      in the cases that the data is already in the DB for example i want set the status 'E', then i know that we have some problems with that row.

                       

                      thanks

                      • 8. Re: helloworld_sql_action
                        Tom Cunningham Master

                        Joao, I don't think there's an easy way to do this.    The statuses represent the success or failure of the gateway, not the processing of the bus.   I think you are going to have to handle whatever custom checks you're proposing here with a custom action.

                         

                        It sounds like you're doing a bit of database processing in ESB, might be worth it to suggest taking a look at the BreakingWoods dbutils extension, see if that helps you any :

                         

                        https://code.google.com/p/breakingwoods/