It explains how you can use a table in the db as a message channel (bus).
darn the answer is longer then the question
Go to the wiki and follow the link to the quickstart overview.
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?
It is possble force the ESB to insert "status" E = error in some cases?
Joao, what cases are you trying to insert status "E" for?
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.
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 :