I found out today that MS Sql server seems to handle Unicode in a very special way. Instead of having some support a database or table level, each Unicode column have to be created as “national”. That is be either nchar, nvarchar or ntext.

Ms SQL Server 2005 seems to go one step further by announcing future deprecation for ntext, text and image types.

From Sql Server 2005 notes:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.”

When working with Hibernate it seems there is no dialect to handle Unicode integration properly. You have to get down and write a custom dialect that maps to the new data types.

/**
 * Unicode support in SQL Server
 *
 * @author icocan
 */
public class UnicodeSQLServerDialect extends SQLServerDialect {

    public UnicodeSQLServerDialect() {
        super();

        // Use Unicode Characters
        registerColumnType(Types.VARCHAR, 255, "nvarchar($l)");
        registerColumnType(Types.CHAR, "nchar(1)");
        registerColumnType(Types.CLOB, "nvarchar(max)");

        // Microsoft SQL Server 2000 supports bigint and bit
        registerColumnType(Types.BIGINT, "bigint");
        registerColumnType(Types.BIT, "bit");
    }
}

7 responses to “Hibernate, UTF-8 and SQL Server 2005

  1. When using the hibernate schema validation, I found that I had to use ntext, not nvarchar(max), for CLOB, the reason being that the driver returns ntext as the column type when nvarchar(max) was used to define it.

  2. Q1. When using nvarchar(max), what do you specify as the length of the column.

    Could someone please provide a mapping file example – this would be very helpful. We have several areas in our application, when the application value needs to resides in the database as nvarchar(max) to allow foreign language characters.

    Please help.

    Regards,
    Sapna

  3. to make hibernate work with nvarchar(max) add following method to your class:

    public String getTypeName(int code, int length, int precision, int scale) throws HibernateException {
    if(code != 2005) {
    String name = super.getTypeName(code, length, precision, scale);
    return name;
    } else {
    return “ntext”;
    }
    }

    and damn it works :)

Leave a Comment:

Your email address will not be published. Required fields are marked *