1 Reply Latest reply on Oct 18, 2010 1:48 PM by Hector Mendoza

    Sql Server select error on sql-provider

    Hector Mendoza Newbie

      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.