HSQL DB custom function to mimic the DB function that HSQL not support (compatibility issue)

It is a solution say when we do integration test using Hyper SQLDB, if the SQL statement have functions which in-memory DB not support, we can use Java Language Routines to mimic the logic the specific-DB-function performs.

An examples is if the specific DB is SQL Server, and the SQL statement has LEN() function which will return the length of the varchar, in SQL Server, however, HSQLDB not support LEN() function (only support length()), and also we will not write a specific SQL for HSQLDB for testing purpose, in this case, we can use Java Language Routines which is a static method of a Java class, specified with a fully qualified method name in the routine definition.

Step 1: define a Java class and a static method to mimic the function you need

               For this case, I need a method by providing a string and return the length of string, so the method may look like below:

   public class JavaRoutinesForHSql {

   public static int stringLength(String chars){
      if (chars!=null)
         return chars.trim().length();
      return 0;
   }
}

Step 2: define the routine in sql script

             Before the SQL statement is called, create the function as below:     

Step 3:  you can drop the custom routine if needed:

               DROP SPECIFIC ROUTINE LEN;

For more information, please refer to:  http://hsqldb.org/doc/guide/sqlroutines-chapt.html#src_jrt_routines