-
1. Re: SqlTableGatewayListener
estebanschifman Jan 9, 2007 6:18 PM (in response to katipamk)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
katipamk Jan 11, 2007 9:44 AM (in response to katipamk)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
estebanschifman Jan 11, 2007 10:10 AM (in response to katipamk)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
katipamk Jan 11, 2007 5:02 PM (in response to katipamk)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
estebanschifman Jan 11, 2007 6:19 PM (in response to katipamk)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