We are currently trying to get the eg demo project up and running with MS Sql Server 2000, and it is failing on the first 'insert' call.

Initially, we tested using the hypersonic DB, and after fighting things for awhile figured out that we needed to grab the hypersonic JAR file from the Hibernate CVS repository and not from the Hypersonic CVS repository - so now we have the 'eg' project working with hypersonic.

When we switch to using the JTDS driver (w/ MSSQL Server 2000), the Hibernate system is able to create the appropriate tables, but then fails on the following SQL statement (taken from the Ant logs):

[java] Hibernate: insert into AuctionUser (userName, [password], email, firstName, [initial], lastName) values (?, ?, ?, ?, ?, ?) select SCOPE_IDENTITY()


The error is (Sorry about the long post...):

[java] net.sf.hibernate.JDBCException: could not insert: [org.hibernate.auction.User]
[java] at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:557)
[java] at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:432)
[java] at net.sf.hibernate.impl.ScheduledIdentityInsertion.execute(ScheduledIdentityInsertion.java:29)
[java] at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:906)
[java] at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:839)
[java] at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:757)
[java] at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:720)
[java] at org.hibernate.auction.Main.createTestAuctions(Main.java:291)
[java] at org.hibernate.auction.Main.main(Main.java:365)
[java] Caused by: java.sql.SQLException: Was expecting a result set
[java] at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(PreparedStatement_base.java:214)
[java] at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:508)
[java] ... 8 more




The hibernate.properties configuration is as follows:

hibernate.connection.driver_class net.sourceforge.jtds.jdbc.Driver
hibernate.connection.url jdbc:jtds:sqlserver://localhost:1433/testDB;SelectMethod=cursor
hibernate.jdbc.use_scrollable_resultset false
hibernate.connection.username sa
hibernate.connection.password
hibernate.dialect net.sf.hibernate.dialect.SQLServerDialect



Hibernate is ver 2.1.2
JTDS is 0.6

If anyone could give me some pointers, I would really appreciate it.

Thanks!

- Kevin

Probably a driver problem. To workaround, try creating your own dialect by extending SqlServerDialect and overriding appendIdentitySelectToInsert() to return null. Probably ask the JDTS guys about that problem.

Interesting - that definitely fixes the issue, and I really appreciate the help.

From looking at the source, I guess the thing I need to tell the JTDS folks about is that their driver does not appear to support the SCOPE_IDENTITY() modifier.

If we don't specify that modifier, then Hibernate uses the GetGeneratedKeysHelper class to extract the keys from the prepared statement instead of from the result set?

Do I have that right?

Thanks!

- K

Try the TDS=8.0 argument in the JDBC URL. OInly SQL server 2000 support scope identity

I'm running SQL Server 2000, so it should work... I'm thinking that JTDS's support for returning a record set with the scope identitiy has a problem...

- K

TDS=7.0 (default value) is 7.0 and Sybase compatible
TDS=8.0 is 2000 specific. They may have add it as a support of the 8.0

Sorry - that was silly of me.

I've dug into the code a bit more, and the problem is with JTDS. They are not properly supporting the executeQuery() of PreparedStatement().

JTDS bug # is 899735 if anyone is interested.

- K

Guys,

Let me disagree with you. I am one of the developers of jTDS and the way I see things Hibernate is the ptoblem here.

My understanding of the JDBC spec and API documentation is that executeQuery() should only be used for queries that return one ResultSet (e.g. SELECT queries) and no other results (including update counts). executeUpdate() should similarly be used for queries that return one update count or nothing (e.g. INSERT, UPDATE). For all other queries there is execute()/getMoreResults().

It's true that most JDBC drivers ignore the spec and skip over update counts when executeQuery() is used, but that seems very wrong to me.

As you also probably know, the JDBC spec is not very clear about all this, so please let me know if I'm wrong. I would very much like to eliminate (one way or another) this incompatibility between Hibernate and jTDS.

Alin.

I should have followed up with a clarification in this newsgroup - my apolgies - I have opened an issue regarding this:

http://opensource.atlassian.com/project ... key=HB-724

It has been assigned, but I haven't heard back from the assignee yet.

You can get my current thoughts on the issue in the issue notes. After several emails back and forth with Alin, and a lot of spec reading, I am inclined to agree that jTDS is adhering properly to the JDBC specification (personally, I think the spec was written incorrectly, but once it's written, we kind of have to live with it...).

Also, for those of you who are fighting this issue, the recommended workaround via custom SQL flavor does work.

Sorry about not following up and posting the link to the opened issue.

Regards,

Kevin

I am not a JDBC spec expert, but I am inclined to believe Alin, and have therefore added a patch to the issue.

Michael,

I'm not sure you got the patch right. There are two results generated by that query: an update count and a ResultSet. In order to get to the ResultSet you must first call getMoreResults() (if you call getResultSet directly, the spec says you will get null if the first result was an update count).

My suggestion of doing this and keeping the code compatible with any driver is to check the return value of execute(). If it's false (as it will be with jTDS) then do a getMoreResults() first to move on to the ResultSet. If it's true (as it might be with other drivers) then skip the getMoreResults() and go straight to the getResultSet(). This should make Hibernate work with any driver, regardless of how it implements the spec.

Alin.

Ah, thanks for the notice, I really shoud have read that javadoc :) Let's see if i got it right this time ...

Yeap, much better. ;o)