H2 DB mapping to IBM DB2 database UDF for missing function: STRIP-CodePudding

I am trying to setup a unit test environment for a large codebase. For these unit tests I use H2 database instead of the IBM DB2 database, which is used in production and I already implemented a few UDF's to map IBM DB2 related functions to the H2 database. Some more Details about the project:

Java 8 JDK 321 64 Bit
DB2 12, DSN 12015
H2 version 2.1.212 with <MODE=DB2;DEFAULT_NULL_ORDERING=HIGH;

I am trying to implement the IBM Db2 function: strip() Reference Doc - IBM. This function is called as part of a larger select statement with the clause: STRIP(T2.ITEM_TYPE_NAME_GER, B, ' '). While I can map the first and last input parameter to a Java function and call this function as an ALIAS in H2, I was not able to manage to get the 2nd parameter interpreted in the correct way as a String or Expression. The JDBC/H2 engine always tries to map it to a table column:

 
    org.h2.jdbc.JdbcSQLSyntaxErrorException: Feld "B" nicht gefunden
    Column "B" not found; SQL statement:
    SELECT T1.ITEM_TYPE_KEY,T1.SUPER_ITM_TYPE_KEY,T2.ITM_TYPE_KEY_TRANS,T2.ITEM_TYPE_NAME,T2.COMPLEX_FLAG,T2.ITEM_CATEGORY,T2.HEADER_FLAG,T2.HEADER_NO ,T2.LEVEL_NO,strip(T2.ITEM_TYPE_NAME_GER, B, ' ') ,T2.LEVEL1_DISPLAY FROM      public.AA752T      T1,   public.AA743T            T2 WHERE          T1.ITEM_TYPE_KEY NOT IN ('F4CO', 'F4CB', 'F4RB', 'F4SO', 'F4SB', 'F4RO') AND T1.ITEM_TYPE_KEY = T2.ITEM_TYPE_KEY ORDER BY T2.HEADER_NO,T2.HEADER_FLAG DESC,T2.LEVEL_NO,T1.SUPER_ITM_TYPE_KEY,T2.LEVEL_PRIORITY [42122-212]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
        at org.h2.message.DbException.get(DbException.java:223)
        at org.h2.message.DbException.get(DbException.java:199)
        at org.h2.expression.ExpressionColumn.getColumnException(ExpressionColumn.java:244)
        at org.h2.expression.ExpressionColumn.optimizeOther(ExpressionColumn.java:226)
        at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:213)
        at org.h2.expression.function.JavaFunction.optimize(JavaFunction.java:59)
        at org.h2.command.query.Select.prepareExpressions(Select.java:1170)
        at org.h2.command.query.Query.prepare(Query.java:218)
        at org.h2.command.Parser.prepareCommand(Parser.java:574)
        at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:631)
        at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:554)
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116)
        at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:92)
        at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
        at com.db.cib.gbd.gps.pbs.pricing.StaticItemDetails.retriveItemDisplayDetails(StaticItemDetails.java:920)

This is my Java UDF:


public static String strip(String s, Expression loc, String trimConstant) {
        
    if (loc.toLowerCase() == "b" || loc.toLowerCase() == "both") {
        s = s.replaceAll("^["   trimConstant   "] |[ \t] $", "");
    } else if (loc.toLowerCase() == "l" || loc.toLowerCase() == "leading") {
        s = s.replaceAll("^["   trimConstant   "] ", "");
    } else if (loc.toLowerCase() == "t" || loc.toLowerCase() == "trailing") {
        s = s.replaceAll("["   trimConstant   "] $", "");
    }
    return s;
}

Is there a possibility to get the mapping of the column in the correct way, or can you suggest either a SQL function, which is usable as UDF alias (how is this usable?) or a way to solve this error? To avoid this question: I cannot change the existing sql statement. I have to find an alias for this function.

CodePudding user response：

It isn't possible to create a user-defined function with special arguments in H2 and most likely in all or almost all other database systems. User-defined functions accept only plain comma-separated arguments with literals or expressions in them.

(You also cannot declare a parameter as org.h2.expression.Expression.)

The proper solution here is to use the TRIM function from the SQL Standard:

https://h2database.com/html/functions.html#trim

https://www.ibm.com/docs/en/db2-for-zos/11?topic=functions-trim

TRIM(BOTH ' ' FROM T2.ITEM_TYPE_NAME_GER)

Please note that B, L, and T acronyms is a DB2-specific extension, in H2 you can use only standard BOTH, LEADING, and TRAILING.

If you cannot change your query you can only modify sources of H2 and compile its own version with support of the STRIP. But actually you can run into some other issue immediately. When you want to use multiple database systems at once you need to realize that they all are very different from each other. H2 provides compatibility modes for others, but even in these modes there is a very limited compatibility. It means you need to avoid usage of vendor-specific functions and other grammar elements and in some cases different SQL for different systems may be required.

You also can try to create constants B, BOTH, L, LEADING, T, and TRAILING with some values

CREATE CONSTANT B VALUE 1;
CREATE CONSTANT BOTH VALUE 1;
CREATE CONSTANT L VALUE 2;
…

and create a function with three arguments, second argument will be of type int (or other, if you'll decide to choose values of some other data type). But names of constants may conflict with column names, so this workaround is far from being perfect and may not work at all in some queries.