-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Trailing spaces with SQL Server
PostPosted: Thu Jan 08, 2009 4:15 pm 
Newbie

Joined: Thu Jan 08, 2009 4:09 pm
Posts: 1
I'm debugging a system built on Hibernate 3.1.3 and SQL Server 2005.

I sometimes get the following error:

Code:
java.lang.NullPointerException
  at org.hibernate.engine.CollectionLoadContext.addCollectionToCache(CollectionLoadContext.java:306)
  at org.hibernate.engine.CollectionLoadContext.endLoadingCollection(CollectionLoadContext.java:203)
  at org.hibernate.engine.CollectionLoadContext.endLoadingCollections(CollectionLoadContext.java:268)
  at org.hibernate.engine.CollectionLoadContext.endLoadingCollections(CollectionLoadContext.java:249)
  at org.hibernate.loader.Loader.endCollectionLoad(Loader.java:866)
  at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:853)
  at org.hibernate.loader.Loader.doQuery(Loader.java:717)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
  at org.hibernate.loader.Loader.loadCollection(Loader.java:1919)
  at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:36)
  at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:520)
  at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60)
  at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1676)
  at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:344)
  at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:86)
  at org.hibernate.collection.PersistentList.toArray(PersistentList.java:123)
  ...

Why?

It appears to be due to SQL Server's ANSI_PADDING rule which ignores trailing spaces on comparison. Thus 'X' = 'X '. Our keys are from user-provided strings and they occasionally get trailing spaces. Somehow the spaces gets truncated in certain circumstances (haven't figured out how) and that causes SQL Server to return a matching collection that Java considered to be not matching.

To get around this issue, I created the following UserType for the keys:

Code:
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

/**
* Removes trailing spaces to be consistent with how SQL Server does varchar
* comparisons
*/
public class TrimmedString implements UserType {

  /**
   * Returns the string without trailing spaces
   */
  private static String trimmed(String original) {
    int length = original.length();
    while (original.charAt(length - 1) == ' ') {
      length--;
    }
    return original.substring(0, length);
  }

  public Object assemble(Serializable cached, Object owner)
      throws HibernateException {
    return cached;
  }

  public Object deepCopy(Object value) throws HibernateException {
    return value;
  }

  public Serializable disassemble(Object value) throws HibernateException {
    return (Serializable) value;
  }

  public boolean equals(Object x, Object y) throws HibernateException {
    if (x == y)
      return true;
    if (null == x || null == y)
      return false;
    if (x instanceof String && y instanceof String)
      return trimmed((String) x).equals(trimmed((String) y));
    return x.equals(y);
  }

  public int hashCode(Object x) throws HibernateException {
    if (x instanceof String) {
      return trimmed((String) x).hashCode();
    } else {
      return x.hashCode();
    }
  }

  public boolean isMutable() {
    return false;
  }

  public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner)
      throws HibernateException, SQLException {
    String value = resultSet.getString(names[0]);
    if (null == value)
      return null;

    return trimmed(value);
  }

  public void nullSafeSet(PreparedStatement st, Object value, int index)
      throws HibernateException, SQLException {
        st.setString(index, (String)value);
  }

  public Object replace(Object original, Object target, Object owner)
      throws HibernateException {
    return original;
  }

  public Class returnedClass() {
    return String.class;
  }

  public int[] sqlTypes() {
       return new int[] { Types.VARCHAR };
  }

}


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.