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.
To ensure that each H2 database collates its string data in the same manner as Progress, a custom implementation ofjava.text.spi.CollatorProvider
is used. This resource is installed and made available to the FWD runtime using:
- 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 wasp2jspi.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 [...]To enable a specific collation for the database the following configurations should be done:
- 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 indirectory.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:
Linux | Windows |
---|---|
|
|
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.