Issue

• We are using MS JDBC Driver for Sql Server.We have a JBoss application that is using JTDS JDBC driver to access our databases, and we need to change the driver to use the MS SQL Server JDBC driver. This is where we are having a problem, because after the configurations we are getting errors for two specific data types:

XML:  javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: -16
DateTimeOffset:  javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: -155

The following are the steps we took to configure our JBoss App to use the new MS JDBC driver:

• Downloaded MS SQL Server JDBC Driver from Microsoft (SQLJDBC4.jar), and copied it in \modules\com\Microsoft\main folder for the server.
• Created a module.xml file, and put it in the same directory (\modules\com\Microsoft\main).
• The following is a copy of the module.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.1" name="com.microsoft.JDBC">
  <resources>
    <resource-root path="sqljdbc4.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>  
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

• Configured the data source in the server configuration file (i.e. standalone-full-ha.xml).
• Copied SQLJDBC_Auth.dll to the servers dll folder.
• We are using a persistence.xml file to hold the hibernate properties.The config in the file looks like the following :

 <persistence-unit name="testPU">
<jta-data-source>java:/testDS</jta-data-source>
    <properties>
      <property name="hibernate.archive.autodetection" value="class, hbm"/>
      <property name="hibernate.connection.driver_class" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
      <property name="hibernate.default_schema" value="dbo" />
      <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect" />
      <property name="hibernate.jdbc.batch_size" value="40" />
      <property name="hibernate.order_inserts" value="true" />
      <property name="hibernate.order_updates" value="true" />
      <property name="hibernate.connection.release_mode" value="after_statement" />
      <property name="hibernate.c3p0.acquire_increment" value="4"/>
      <property name="hibernate.c3p0.min_size" value="8"/>
      <property name="hibernate.c3p0.max_size" value="256"/>
      <property name="hibernate.c3p0.timeout" value="180"/>
      <property name="hibernate.c3p0.acquireRetryAttempts" value="30"/>
      <property name="hibernate.c3p0.acquireRetryDelay" value="2000"/>
      <property name="hibernate.c3p0.max_statements" value="200"/>
      <property name="hibernate.c3p0.idle_test_period" value="100"/>
    </properties>
  </persistence-unit>

• At this point the JBoss Application should be configured to use the MS JDBC driver, but it is failing with the following code.

try {
            Query query = em.createNativeQuery("{call test.StoredProcedure (:Value1,: Value2)}");
            query.setParameter("Value1", size);
            query.setParameter("Value2", id);   
            return query.getResultList();
} catch (Exception e) {
            String errorMsg = "Failed executing " + e.getMessage();
            if(log.isDebugEnabled()) e.printStackTrace();
            log.error(errorMsg);
}

• It always fails on query.getResultList(), and we have identified that it fails on either the XML field being returned or the DateTimeOffset field that is being returned.

• We have also attempted to extend the SQLServer2008Dialect, but we do not know how to configure it for XML and DateTimeOffset field. We have validated that when we extended the dialect class that it was being used since we were able to stop at a break point in that class.

• The following is an example of the extended dialect class :

import java.sql.Types;
import org.hibernate.dialect.SQLServer2008Dialect;

public class SQLServer2008DialectExtension extends SQLServer2008Dialect {
    public SQLServer2008DialectExtension() {
        super();
        registerColumnType( Types.LONGNVARCHAR, "text" );
    }
}

• Looking at the SQLJDBC4.jar file we see a class in the jar file called DateTimeOffset$1.class which leads me to believe that the data type DateTimeOffset should be supported, but with all the steps taken above we are still getting errors.