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

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.