[esb-issues] [JBoss JIRA] Commented: (JBESB-1071) SqlTableGatewayListener code supports 'where' clause, but it is missing in jbossesb-1.0.1.xsd

[Tom Fennelly (JIRA)]

    [ http://jira.jboss.com/jira/browse/JBESB-1071?page=comments#action_12380361 ] 
            
Tom Fennelly commented on JBESB-1071:
-------------------------------------

Just as a matter of interest... could this be supported in the existing codebase by adding the where clause as a <property> on the listener config?  I mean this as a short term solution for Bernard while it's being sorted out properly.

To add it as a property, just check the code and see what attribute name is used to query the where clause from the ConfigTree.  Then use that name as the <property> name attribute, with the where clause in the <property> value.

> SqlTableGatewayListener code supports 'where' clause, but it is missing in jbossesb-1.0.1.xsd
> ---------------------------------------------------------------------------------------------
>
>                 Key: JBESB-1071
>                 URL: http://jira.jboss.com/jira/browse/JBESB-1071
>             Project: JBoss ESB
>          Issue Type: Feature Request
>      Security Level: Public(Everyone can see) 
>          Components: Rosetta
>    Affects Versions: 4.2, 4.2.1
>         Environment: OSX-10.4.10
> Dual 2GHz PowerPC G5, 3GB
>            Reporter: Bernhard Gass
>         Assigned To: Tom Cunningham
>             Fix For: 4.2.1
>
>
> SqlTableGatewayListener auto created a prepared sql statement from the information given in the *-esb.xml file defining your services. A where condition is supported in the code
> SqlTableGatewayListener - Line 344
> ListenerTagNames - Line 111
> but 'whereCondition' or 'where-condition' is not supported in jbossesb-1.0.1.xsd.
> Asessment:
> If my understanding is correct the generated prepared statement is called for every pull, but only one message is processed. 
> Every fetch loads ALL messages from the DB every cycle which leads to a problem specially when the source database has many rows. A static where clause can solve some of the above, but most likeley a parameterized 'where clause' is desireable (which leads to the question where the parameters come from).
> Additional considerations:
> - Processing speed can be improved considerably by fetching several rows and  deliver them for processing one by one or better process them in batches all together (given the business case allows that).
> - Some mechanism is needed to parameterize the fetching mechanism. A rudimentary mechanism can be realized using a plain 'where' clause
> - In a production scenario additional issues play a role. 
>    - Messages most of the times have a sequence they need to be processed in.
>    - A specific business case might or might not allow gaps in the sequence of transaction id's. A sequence of transaction id's without gaps often is used to assure no messages are missing.
>    - Even using something like an Oracle sequence is not a guaranty for a sequence witout gaps (sequence gaps can e.g. occure after a Oracle CPU flush).
>     - It might not be possible or desireable to change the table containing the payload. In that case you need a second table containing  'transactions id's', 'prcessing status', 'processing instructions' etc.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira