Sql Server select error on sql-provider

[roar109]

Hi community, i try to make a listener to catch new rows on every insert in Sql Server 2005 table, but when the server start (JBoss ESB Server 4.9)  and deploy the esb throw a exception:

14:35:08,581 INFO  [MessageAwareListener] State reached : false

14:35:08,609 WARN  [SqlTableCourier] Exception during pickup

com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)

at org.jboss.internal.soa.esb.couriers.SqlTableCourier.tryToPickup(SqlTableCourier.java:368)

at org.jboss.internal.soa.esb.couriers.SqlTableCourier.pickup(SqlTableCourier.java:261)

at org.jboss.internal.soa.esb.couriers.TwoWayCourierImpl.pickup(TwoWayCourierImpl.java:228)

at org.jboss.internal.soa.esb.couriers.TwoWayCourierImpl.pickup(TwoWayCourierImpl.java:204)

at org.jboss.soa.esb.listeners.message.MessageAwareListener.waitForEventAndProcess(MessageAwareListener.java:297)

at org.jboss.soa.esb.listeners.message.MessageAwareListener.doRun(MessageAwareListener.java:253)

at org.jboss.soa.esb.listeners.lifecycle.AbstractThreadedManagedLifecycle.run(AbstractThreadedManagedLifecycle.java:115)

at java.lang.Thread.run(Thread.java:637)

14:35:08,581 INFO  [MessageAwareListener] State reached : false

14:35:08,609 WARN  [SqlTableCourier] Exception during pickup

com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)

at org.jboss.internal.soa.esb.couriers.SqlTableCourier.tryToPickup(SqlTableCourier.java:368)

at org.jboss.internal.soa.esb.couriers.SqlTableCourier.pickup(SqlTableCourier.java:261)

at org.jboss.internal.soa.esb.couriers.TwoWayCourierImpl.pickup(TwoWayCourierImpl.java:228)

at org.jboss.internal.soa.esb.couriers.TwoWayCourierImpl.pickup(TwoWayCourierImpl.java:204)

at org.jboss.soa.esb.listeners.message.MessageAwareListener.waitForEventAndProcess(MessageAwareListener.java:297)

at org.jboss.soa.esb.listeners.message.MessageAwareListener.doRun(MessageAwareListener.java:253)

at org.jboss.soa.esb.listeners.lifecycle.AbstractThreadedManagedLifecycle.run(AbstractThreadedManagedLifecycle.java:115)

at java.lang.Thread.run(Thread.java:637)

this is the jboss-esb.xml:

<jbossesb parameterReloadSecs="5"
 xmlns="http://anonsvn.labs.jboss.com/labs/jbossesb/trunk/product/etc/schemas/xml/jbossesb-1.0.1.xsd"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://anonsvn.labs.jboss.com/labs/jbossesb/trunk/product/etc/schemas/xml/jbossesb-1.0.1.xsd http://anonsvn.jboss.org/repos/labs/labs/jbossesb/trunk/product/etc/schemas/xml/jbossesb-1.0.1.xsd">
 <providers>
  <sql-provider driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" name="sqlProvider"
   password="pass" url="jdbc:sqlserver://192.168.1.73:1433;databaseName=test" username="user">
   <sql-bus busid="busIdSql">
    <sql-message-filter error-delete="true"
     insert-timestamp-column="tiempo" message-column="message"
     message-id-column="idprueba" post-delete="true"
     status-column="status" tablename="prueba"/>
   </sql-bus>
  </sql-provider>
           <jms-provider name="JBossMQ" 
                    connection-factory="ConnectionFactory">
                   <jms-bus busid="kafEsbChannel">
                       <jms-message-filter
                           dest-type="QUEUE"
                           dest-name="queue/kaf_Request_esb"
                       />
                   </jms-bus>
             </jms-provider>
 </providers>
 <services>
  <service category="replicateSqlService" description="descripcion" name="replicateService">
   <listeners>
    <sql-listener busidref="busIdSql" is-gateway="false"
     name="listenerSql" />
                <jms-listener name="kafJmsAction"
                              busidref="kafEsbChannel"
                />
   </listeners>
   <actions>
     <action class="com.kaf.esb.action.ReplicateSqlListenerAction"
               name="replicationAction" process="replicateData"/>
   </actions>
  </service>
 </services>
</jbossesb>

I don't know what happen, i been searching for that error on internet but i just found a problem with hibernate jars; but i don't use hibernate, don't know if internaly jboss esb use hibernate of something, the jboss esb server i use is with embebbed db.

I use sqljdbc.jar for driver  also test jdbc4.jar the same exception.

Thanks for any help.

[roar109]

I been searching for this issue and is because Sql Server 2005 don't support select for update, and seeing esb source code, for each db used append the for update sentence, so that's the cause why throw that exception.

I'll try solve in other way.