Hibernate and MS SQL - Unsupported prepareStatement

Insert operation generated by Hibernate didn't work with MS SQL. I was using Hibernate 3.1.2, WAS 6.1, JVM 1.5. And I was testing two JDBC drivers.

For IBM ConnectJDBC for MS SQL,

java.sql.SQLException: [IBM][SQLServer JDBC Driver]Unsupported method: Connection.prepareStatement
at com.ibm.websphere.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.ibm.websphere.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.ibm.websphere.jdbc.base.BaseConnection.prepareStatement(Unknown Source)
at com.ibm.websphere.jdbcx.base.BasePooledConnection.prepareStatement(Unknown Source)
at com.ibm.websphere.jdbcx.base.BaseConnectionWrapper.prepareStatement(Unknown Source)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.pmiPrepareStatement(WSJdbcConnection.java:3980)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:3854)

For DirectData ConnectJDBC for MS SQL,

java.sql.SQLException: [DataDirect][SQLServer JDBC Driver]Unsupported method: Connection.prepareStatement(String, String[])
at com.ddtek.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.ddtek.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.ddtek.jdbc.base.BaseConnection.prepareStatement(Unknown Source)
at com.ddtek.jdbcx.base.BasePooledConnection.prepareStatement(Unknown Source)
at com.ddtek.jdbcx.base.BaseConnectionWrapper.prepareStatement(Unknown Source)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.pmiPrepareStatement(WSJdbcConnection.java:3980)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:3854)

There is a saying that the Connection.prepareStatement(String sql, String[] columnNames) is not supported. Workaround solution is to set the "hibernate.jdbc.use_get_generated_keys" to false in the hibernate settings, either in the properties or in the cfg.xml.

Here is the definition of the property.

Enable use of JDBC3 PreparedStatement.getGeneratedKeys() to retrieve natively generated keys after insert. Requires JDBC3+ driver and JRE1.4+, set to false if your driver has problems with the Hibernate identifier generators. By default, tries to determine the driver capabilites using connection metadata.

eg. true|false

Here is the quote from mdiamonte in DataDirect forum.

Against SQL Server 2000, the driver can not implement this method correctly in the general case because SQL Server 2000 does not allow you to return the value of an arbitrary column from an insert, update or delete statement. At least not with out making an extra round trip to the server, which negates the purpose of this method.

I have seen implementations of this method by other drivers where the value returned will be the value of the identity column regardless of which column was actually asked for. I believe that implementation is bad. I feel it is much worse for a driver to return incorrect information than it is to not be able to return the information. Assuming the id column in the statement is an identity column, then Hibernate may have just gotten lucky that it worked in the testing that they did.