Using H2 Engine to test, invoke, ...

The H2 Database Engine shipped with the designer and runtime has a one column one row table named dual.

It can be very usefull for testing, or for some use cases of integration.

For example, a source is needed to invoke a Web Service, even if we want to invoke it only to retrieve the result, and we are not using the data from the source.

It can be confusing to need a table for that, but it's how it works actually: the node of the Web Service must be mapped from a source table, to have the Integration Template available for selection.

So here comes the tip of this article. To avoid having to create or use a table just for that you can use the embedded H2 Engine.

The H2 Database Engine

You can download an example of metadata using the H2 Database Engine here.

It is using a metadata query, available since S18.0.3

Note : A metadata query is a SQL query, which can be reversed and used like a datastore.

The advantage of this solution is that the H2 Engine metadata can be configured to use an in memory H2 Database. It will only be present during the execution of the Session.
And you can use this metadata query in any other mapping which need a source just for the invocation, or testing with only one row, ...

The H2 "in-memory" database does not need the Runtime's internal H2 database to be started. It is directly available in the Runtime or in any "startdelivery" Stambia session even if the Runtime is not running at execution time.

To accomplish that:

1. A JDBC url similar to jdbc:h2:mem:${/CORE_SESSION_ID}$ must be used.
2. Create a query which is a select on the H2 dual table. select * from dual or select 1 field from dual for example.

Then you can use this in mappings.

Example :

The oneline query will be generated only during the execution of the mapping.