DB2Dialect override for substring hides DB2 method in Hibernate

Environment

• Red Hat JBoss Enterprise Application Platform (EAP)
  • 7
  • 6
• Hibernate
  • 5
  • 4
• DB2
  • 10.5
  • 10.1
  • 9.7

Issue

• Creating an HQL query (e.g. from Employee e where e.name = (SUBSTRING('Johnny', 1, 4, OCTETS)))
• The DB2 driver raises com.ibm.db2.jcc.am.SqlSyntaxErrorException: ... SQLCODE=-104, SQLSTATE=42601, SQLERRMC=OCTETS ... during query execution.

Resolution

• This is a known issue - Content from issues.jboss.org is not included.JBEAP-12906 - which will be addressed in a future release.

• To work around this limitation in HQL queries, one of the following may be used:

  • A native query (which is not translated by Hibernate) can be used instead of an HQL based query.
  • A Content from docs.jboss.org is not included.custom dialect such as the below can be used

      package org.hibernate.dialect;
    
      import org.hibernate.dialect.function.StandardSQLFunction;
      import org.hibernate.type.StandardBasicTypes;
  
      public class MyDB2Dialect extends DB2Dialect {
              public MyDB2Dialect() {
                      super();
                      registerFunction( "substring", new StandardSQLFunction( "substring", StandardBasicTypes.STRING ) );
              }
      }
  

References

• DB2 substring API in Content from www.ibm.com is not included.10.5 | Content from www.ibm.com is not included.10.1 | Content from www.ibm.com is not included.9.7
• DB2 substr API in Content from www.ibm.com is not included.10.5 | Content from www.ibm.com is not included.10.1 | Content from www.ibm.com is not included.9.7

Root Cause

The DB2Dialect has an Content from github.com is not included.override for the substring method that translates the method (when included in HQL) to substr.

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.