• H2 String Collation Service Provider Installation
  • H2 console in server mode
    • Scripting H2 console with a loop
  • H2 DBeaver in server mode
    • Caveat: A working FWD H2 connection not working after restart of DBeaver.

H2 String Collation Service Provider Installation¶

H2 is an in-memory database used implicitly by the FWD runtime for legacy temp-table support and for other internal purposes. This means that even if the permanent database servers being used are not H2, a Java-based text collator used by H2 must be installed on the machine where the FWD Application Server is run. If a permanent database is H2 and is on a different machine, then it must be installed there, too.

• the Java Extension Mechanism when running a version of Java up to JDK8. It is deprecated and may be dropped in newer versions. For information on how this was done see String Collation Service Provider Installation. The name of the library was p2jspi.jar.
• the JDK's Supported Locales. This page will describe how to install and configure on JDKs starting with Java8. The new name of the library is fwdspi.jar.

Make sure that you have built FWD already, so that the fwdspi.jar is available.

On both Linux and Windows OSes, the java.locale.providers system property allows to specify the order in which the locations where the jars containing the locale providers are searched/loaded. Java will use the configured locations to scan for ALL jar and load all the implementation of CollatorProvider. To avoid heavy processing tens of jars which are found in lib/ folder at server startup, in FWD's standard installation we decided to move/copy this particular library to its own directory: lib/spi/, resulting in no additional jar to be processed.

First, the FWD's gradlew script will create the library in same directory with main jar library, adding only the necessary files/classes. Then, when the project's ant deploy.prepare target is executed, the fwdspi.jar must be copied to lib/spi/ in the deployment folder, unlike the other FWD jars and dependencies which are copied with the application's jar(s) to lib/. Here's how the ant target should look like:

   <target name="deploy.prepare" [...] >

      <!-- create the deploy lib folder -->
      <mkdir dir="${deploy.home}/lib"/>

      <!-- create and populate the extension lib folder -->
      <mkdir dir="${deploy.home}/lib/spi"/>
      <copy file="${p2jlib.home}/fwdspi.jar" todir="${deploy.home}/lib/spi"/>

      [...]
   </target>

If the process is successful, the file structure should look like this:

<AppRoot>
   + deploy
       + lib
       |  + spi
       |  |   + fwdspi.jar
       |  + p2j.jar
       |  + fwd-h2-1.4.200-6.jar
       |  [...]
       + server
       [...]

• static configuration (without server running). In cfg/p2j.cfg.xml, for each namespace which is designed to use H2 dialect, the following entry will be added:
  

        <namespace name="testDb" [...]>
           <dialect-specific name="h2">
              <parameter name="collation" value="en_US@cp1252_fwd_basic" />
           </dialect-specific>
        <metadata name="standard" />
  

  
  This is used mainly when the database is imported.
• server runtime configuration. In directory.xml, for each database, at /server/standard/database/<db-name>/p2j/, add the following:
  

                <node class="string" name="embedded-collation">
                  <node-attribute name="value" value="en_US@cp1252_fwd_basic"/>
                </node>
  

• the same value for the collation must be set for a specific database for both static and runtime configuration.
• the _temp database should also have the same collation set as expected in 4GL. The location in directory.xml is /server/standard/database/_temp/p2j/embedded-collation. This database does not have a static configuration.
• for any database, if no collation is set, the default en_US@iso88591_fwd_basic is used.

At runtime, the PrintSystemProp utility is used to detect the default java.ext.dirs value. When the server is launched, java.locale.providers is set to SPI,JRE to let the system know the locations and the order in which they must be processed, and the java.ext.dirs is updated to include both project's lib/spi (SPI) and the original location (JRE). This was fully supported for both Linux and Windows. The specific code is launch script is quite similar for both OSes:

spi="-Djava.locale.providers=SPI,JRE -Djava.ext.dirs=$(java ${cpath} com.goldencode.util.PrintSystemProp java.ext.dirs):../lib/spi" 
java $spi $cpath com.goldencode.p2j.main.ServerDriver [...]

java -cp %lpath%\p2j.jar com.goldencode.util.PrintSystemProp java.ext.dirs > original-exts.txt
set /p originalSpiDir=<original-exts.txt
set spi=-Djava.locale.providers=SPI,JRE -Djava.ext.dirs=%originalSpiDir%;%lpath%\spi
java %spi% %cpath% com.goldencode.p2j.main.ServerDriver [...]

Note that the Windows script is a bit more complicated because its shell is not as powerful as Linux's.

Add similar code for import process. If the database is inspected using H2 console, the command to launch it should also be enhanced this way. For example, if the command is executed from lib/ directory, it would look like this:

java -Djava.locale.providers=SPI,JRE -Djava.ext.dirs=/usr/lib/jvm/java-8-openjdk-amd64/jre/lib/ext:/usr/java/packages/lib/ext:./spi -classpath spi/fwdspi.jar -jar fwd-h2-1.4.200-6.jar

H2 console in server mode¶

H2 in server mode is not officially supported, but can be helpful during testing and debugging, which is allowed.

To run H2 in server mode, easiest is to use AUTO_SERVER=TRUE everywhere.
Add ;AUTO_SERVER=TRUE to the URL in directory.xml, and use a full path, for example:

jdbc:h2:/v2/workspace/xfer_testc/testcases.7049a.tip/deploy/db/tstcasedata;DB_CLOSE_DELAY=-1;MV_STORE=FALSE;AUTO_SERVER=TRUE

Add ;AUTO_SERVER=TRUE to your H2 console URL as well, for example:

cd /v2/workspace/xfer_testc/testcases.7049a.tip/deploy/lib
TERM=xterm java -Djava.locale.providers=SPI,JRE -Djava.ext.dirs=/usr/local/lib/jvm/openlogic-openjdk-8u292-b10-linux-x64/jre/lib/ext/:./spi -classpath fwd-h2-1.24-trunk.jar:spi/fwdspi.jar org.h2.tools.Shell

Welcome to H2 Shell 1.4.200 (2019-10-14)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:/v2/workspace/xfer_testc/testcases.7049a.tip/deploy/db/tstcasedata;DB_CLOSE_DELAY=-1;MV_STORE=FALSE;AUTO_SERVER=TRUE
[Enter]   org.h2.Driver
Driver    
[Enter]   
User      fwd_user
Password  
Connected
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> select * from rock;
RECID | SINGER
25001 | abc++
(1 row, 15 ms)
sql>     

Scripting H2 console with a loop¶

You can also pass SQL from stdin , and create an infinite watcher script, like for example:

#!/usr/bin/env bash
cd /v2/workspace/xfer_testc/testcases.trunk.tip/deploy/lib
(while true; do sleep 1 ; printf ' trunk ' >&2 ; date >&2 ; echo 'select * from rock;' ; done ) | java -Djava.locale.providers=SPI,JRE -Djava.ext.dirs=/usr/local/lib/jvm/openlogic-openjdk-8u292-b10-linux-x64/jre/lib/ext/:./spi -classpath fwd-h2-1.31-trunk.jar:spi/fwdspi.jar org.h2.tools.Shell -url "jdbc:h2:/v2/workspace/xfer_testc/testcases.trunk.tip/deploy/db/tstcasedata;DB_CLOSE_DELAY=-1;MV_STORE=FALSE;AUTO_SERVER=TRUE" -user fwd_user -password user

H2 DBeaver in server mode¶

Apply the directory.xml change mentioned in "H2 console in server mode" above.

Start DBeaver, go to Database -> Driver Manager, and create a copy of H2 Embedded V.2 , and name it H2 Embedded V.2 FWD

Add the FWD H2 driver inside the lib folder to 'Libraries'.

Go to the Classpath of the driver, and add the FWD spi jar, as well as the jre/lib/ext jar files of your jre.

Now you can connect, via this new driver, via the same URL as your directory.xml, and view the data:

Caveat: A working FWD H2 connection not working after restart of DBeaver.¶

It seems DBeaver has issues with the SPI customizations after restart, as of 2023Q4.
You may get the following error:

Connection to 'H2 7049a' was lost and cannot be re-established.
What do you want to do?

Reason:
org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.RuntimeException: en_US_iso88591_fwd_basic" [50000-200]

The workaround is simple: Go to the driver manager, edit the H2 FWD driver, go to Classpath, immediately hit Apply, OK, and the connection works again.