MSSQL Driver throws "ResultSet can not re-read row"

dep4b · **Joined:** Sat Dec 06, 2003 5:38 pm **Posts:** 27

We have a problem with the Microsoft JDBC driver throwing a "ResultSet can not re-read row data for column 51." exception. We have discovered that it only appears if the resultset contains a NTEXT, TEXT or IMAGE column. The driver then imposes the limitation that columns must be read in increasing column indexes and that they may not be re-read. This limitation is permitted by the JDBC spec:

Quote:

For maximum portability, columns within a row should be read
in left-to-right order, and each column should only be read once. This
reflects implementation limitations in some underlying database protocols

However, Hibernate uses column names instead of indexes and loads then in whatever order it wants to load the data in from the ResultSet, versus left to right order.

Additionally, I had this same problem in 2.0.3, however I was able just to reimplement the Subclass class since it was getting the order of columns to load by first loading the subclass and then the super class. By swapping them, it worked:

Code:

public Iterator getPropertyClosureIterator() {
      return new JoinedIterator( new Iterator[] {
         getSuperclass().getPropertyClosureIterator(),
         getPropertyIterator()
          }
      );
   }

Unfortunantly the same swap doesn't work for 2.1.1. I tried downloading the snapshot from last night but no joy.

Any ideas on strategies to take?

Eric

dep4b · **Joined:** Sat Dec 06, 2003 5:38 pm **Posts:** 27

Here is the most stripped down example I could come up with.

My mapping file:

Code:

<class name="com.upstate.catalog.om.product.HibernateProduct" table="PROD_Products" dynamic-update="true">
    <id name="catalogNumber" type="string" column="catalogNumber" unsaved-value="null">
      <generator class="assigned"/>
    </id>

    <property name="priceUS" column="Price$US" type="string"/>
      <joined-subclass name="com.upstate.catalog.om.product.AntibodyProduct" table="PROD_Characteristics">
        <key column="CatalogNumber"/>
      <property name="igType" column="IgType" type="string"/>
      <property name="purification" column="Purification" type="string"/>
      </joined-subclass>
  </class>

And the SQL generate:

Code:

select hibernatep0_.catalogNumber as catalogN1_0_, case when hibernatep0__1_.CatalogNumber is not null then 1 when hibernatep0_.catalogNumber is not null then 0 end as clazz_0_, hibernatep0_.Price$US as Price$US0_0_, hibernatep0__1_.IgType as IgType1_0_, hibernatep0__1_.Purification as Purifica3_1_0_ from PROD_Products hibernatep0_ left outer join PROD_Characteristics hibernatep0__1_ on hibernatep0_.catalogNumber=hibernatep0__1_.CatalogNumber where hibernatep0_.catalogNumber=@P1

and lastly the exception:

Code:

Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]ResultSet can not re-read row data for column 3.
  at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
  at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
  at com.microsoft.jdbc.base.BaseResultSet.validateColumnIndex(Unknown Source)
  at com.microsoft.jdbc.base.BaseResultSet.getString(Unknown Source)
  at com.microsoft.jdbc.base.BaseResultSet.getString(Unknown Source)
  at net.sf.hibernate.type.StringType.get(StringType.java:18)
  at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
  at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
  at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:66)
  at net.sf.hibernate.loader.Loader.hydrate(Loader.java:610)
  at net.sf.hibernate.loader.Loader.loadFromResultSet(Loader.java:551)
  at net.sf.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:510)

dep4b · **Joined:** Sat Dec 06, 2003 5:38 pm **Posts:** 27

For those coming after me, here are my results. One was that by applying to the Subclass.java the swap for the property interators that helps a lot:

public Iterator getPropertyClosureIterator() {
System.out.println("Hello there!");
return new JoinedIterator( new Iterator[] {
getSuperclass().getPropertyClosureIterator(),
getPropertyIterator()}
);
}

However, that is only half the story. The other thing is that if you object is loading up lots of other objects in the same query, for instance if you have man-to-one mappings, then often they will trigger the reread error. So if you specify a proxy for them, then they won't load as part of the initial query, which gets you around the re-read issue. Same for marking collections lazy. Of course this is at the cost of extra queies etc. Really, what we need is to use positional indexes for getting the values instead of column names (which is faster as well). The way I see it is to figure out when hibernate generats the select clause, and do an index to column name map. Then use that map to read back the values instead of using the order of the columns in the propertyTypes.. Of course that is way beyond my Hibernate skills...

dep4b · **Joined:** Sat Dec 06, 2003 5:38 pm **Posts:** 27

I also found these issues as well.

1) Any one-to-one's need to be marked as contrained=true:

Code:

<one-to-one name="publishing" constrained="true" cascade="none"/>

2) Moving all properties up to the beginning of your <class> section seemed to help, but that might also just have been me fixing the various

Code:

<many-to-one>

with proxies...

micho2001 · **Posted:** Sat Dec 27, 2003 4:21 pm

uhm... I had the same error (not through hibernate, but with jasperreports)

The error went away when I took of the "selectmethod=cursor" parameter from the DB connection URL.

Drop a line if it works.

P.D: My guess this is a mere workaround, I dont remember where but Im sure I read that Hibernate and MSSQL server should be used with this selectmethod=cursor parameter set.

P.D.D: Do you know if you have any locks in your query tables?

max · **Posted:** Sat Dec 27, 2003 4:49 pm

This is due to a very weird and (unfortunately) JDBC API compliant limitation in MS SQL JDBC Drivers.

Please use a better driver ;)

Look at HB-484 for more details

baliukas · **Posted:** Sat Dec 27, 2003 5:30 pm

It must be trivial to implement decorator for driver to handle this limitation.

max · **Posted:** Sat Dec 27, 2003 5:46 pm

baliukas wrote:

It must be trivial to implement decorator for driver to handle this limitation.

yup - and that is what one of the bug reporters have done ;)

gavin · **Posted:** Sun Dec 28, 2003 7:21 am

Note that this problem only surfaces in the very latest version of the MS driver; it did not occur in the previous sp. Note also that none of the good commercial SQL Server drivers suffer this problem.

dep4b · **Joined:** Sat Dec 06, 2003 5:38 pm **Posts:** 27

Thanks for all the help, I'll try one of the recommended workarounds. If someone can share a ResultSet decorator, I'll add to the WIKI some information about it, as I am sure there are quite a few folks with this problem.

As for the version, I tried it with the older version and the latest and greatest SP and had the same issue.

dep4b · **Joined:** Sat Dec 06, 2003 5:38 pm **Posts:** 27

If you have this problem, the solution is here: http://msjdbcproxy.sourceforge.net

skwee · **Joined:** Fri Apr 02, 2004 8:09 pm **Posts:** 1

As it seems this is this most active thread on this topic:
has anyone tried the hibernate unit tests using the jdbcproxy and the mssql driver ? And does this make sense anyway ?

I tried it with an earlier version and the number of unpassed tests using the proxy was much bigger than the number without the proxy driver.

pstruhos · **Joined:** Fri Nov 21, 2003 10:23 am **Posts:** 81

Is there any plan to fix this hibernate bug?

christian · **Posted:** Mon Sep 20, 2004 11:35 am

What bug?

gavin · **Posted:** Mon Sep 20, 2004 6:17 pm

I see a SQL server JDBC driver bug...