Hi All
I am a newbie in Hibernate, I am using Hibernate 2.1.7 and jtds 1.2. I am using Microsoft SQLServer 2000 as the database.
The problem happend when I tried to update a record in database table throw hibernate. The error is: java.sql.SQLException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR. The connection configuration and the generated Exception are listed as following.
Can anyone help me solve this problem?

Hibernate version:2.1.7

Mapping documents:
.....
<property name="hibernate.connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>
<property name="hibernate.connection.password">mypass</property>
<property name="hibernate.connection.url">jdbc:jtds:sqlserver://myServer:1433/theInstance;instance=theInstance</property>
<property name="hibernate.connection.username">myUser</property>
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
.....


Full stack trace of any exception that occurs:
Jan 18, 2006 12:44:17 PM net.sf.hibernate.id.TableGenerator generate
SEVERE: could not read a hi value
java.sql.SQLException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:693)
at net.sf.hibernate.id.TableGenerator.generate(TableGenerator.java:94)
at net.sf.hibernate.id.TableHiLoGenerator.generate(TableHiLoGenerator.java:59)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:774)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:747)
at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1397)
at net.sf.hibernate.engine.Cascades$4.cascade(Cascades.java:114)
at net.sf.hibernate.engine.Cascades.cascade(Cascades.java:436)
at net.sf.hibernate.engine.Cascades.cascadeCollection(Cascades.java:526)
at net.sf.hibernate.engine.Cascades.cascade(Cascades.java:452)
at net.sf.hibernate.engine.Cascades.cascade(Cascades.java:503)
at net.sf.hibernate.engine.Cascades.cascade(Cascades.java:482)
at net.sf.hibernate.impl.SessionImpl.doUpdate(SessionImpl.java:1484)
at net.sf.hibernate.impl.SessionImpl.update(SessionImpl.java:1364)
...


Name and version of the database you are using: Macrosoft SQLServer 2000

Could you be changing your dialect elsewhere? Or maybe a different config file is being used? I use SQLServer 2000, with that dialect, and I have no problems. I don't see any reference to "for update" in the dialect anywhere; SQLServerDialect extends SybaseDialect which returns "" from getForUpdateString(), so what you're seeing shouldn't be happening.

Hi tenwit:

Thank you very much for your reply.
I am pretty sure that org.hibernate.dialect.SQLServerDialect is the only dialect I am using.
Can you tell me which version of hibernate and jtds you are using? I am using Hibernate 2.1.7 and jtds 1.2.

Thanks again.

Hi All:

I switched to Microsoft databse driver instead of using the jtds driver, but I still got exactly the same problem.
Can anyone help me out with this? I am really freaked out.
The new connection properties and the exceptions I got as listed as following

Connection properties:
...
<property name="hibernate.connection.driver_class">com.microsoft.jdbc.sqlserver.SQLServerDriver</property>
<property name="hibernate.connection.password">myPass</property>
<property name="hibernate.connection.url">jdbc:microsoft:sqlserver://myServer\myInstance:1433;database=theDatabase</property>
<property name="hibernate.connection.username">myUser</property>
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
...


Exceptions I got:
Jan 18, 2006 3:31:02 PM net.sf.hibernate.id.TableGenerator generate
SEVERE: could not read a hi value
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
at net.sf.hibernate.id.TableGenerator.generate(TableGenerator.java:94)
at net.sf.hibernate.id.TableHiLoGenerator.generate(TableHiLoGenerator.java:59)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:774)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:747)
...

I'm using H3.0.5, and I don't know what jtds is so I'm probably not using it at all :) The problem isn't with the driver: SQLServer doesn't understand "select .. for update" and for some reason, Hibernate is creating SQL with this in it. All I can suggest is to debug into your select statement, trace through the hibernate code, and find out where the "for update" is being added. If you put a breakpoint on the getForUpdateString() methods (in the various org.hibernate.dialect classes), that might speed the process up.

Dear All:
I fixed the problem after I upgrade my Hibrnate to 3.1. Thank you all for your help. ~_^