External Source

Required knowledge:

• adding database driver and extending docker image (article)

External Source in parameters configuration allows defining another database source for parameter entries besides standard matrix which is kept in the main database. 

To use an external source first you need to include a database driver and define connection properties in the application.properties file for Hyperon Studio as in any applications using runtime library (e.g. hyperon-runtime-rest).

Example configuration (before version 2.0.0):

datasource.postgres.url=jdbc:postgresql://localhost:5432/hyperon
datasource.postgres.username=hyperon
datasource.postgres.password=hyperon 

datasource.h2.url=jdbc:h2:/home/user/hyperon.mv.db;
	AUTO_SERVER=TRUE;IFEXISTS=FALSE
datasource.h2.username=hyperon
datasource.h2.password=hyperon 

mpp.external.datasources=postgres,h2

For version 2.0.0 and higher please use updated properties as in example below:

hyperon.runtime.external-datasource.sql.postgres.url=jdbc:postgresql://localhost:5432/hyperon
hyperon.runtime.external-datasource.sql.postgres.username=hyperon
hyperon.runtime.external-datasource.sql.postgres.password=hyperon
hyperon.runtime.external-datasource.sql.h2.url=jdbc:h2:/home/user/hyperon.mv.db;
	AUTO_SERVER=TRUE;IFEXISTS=FALSE
hyperon.runtime.external-datasource.sql.h2.username=hyperon
hyperon.runtime.external-datasource.sql.h2.password=hyperon
hyperon.runtime.external-datasource.sql.names=postgres,h2

(the full list of updated properties names is included here )

‍

This configuration allows you to select “Postgres” or “H2” as an external data source on the parameter configuration screen. After selecting one of the data sources, SQL queries can be written to fetch data from any table in the selected database.

In-memorySQL template vs. Non-memory SQL template

In-memory index = true

Entries from an external source will be fetched once and stored in memory. In that case, SQL query should contain all columns defined in parameter (IN and OUT). On the first request, an in-memory index will be built using all specified columns so that subsequent requests are served faster.

This option does not allow specifying SQL where clause with values from IN columns, plain SQL should be defined as follows:

select id, code, createdate, description from systemrolejpa where id <= 8

In-memory index = false

Another option is to uncheck the “In-memory index” checkbox so that every time parameter is called a request to the database will be issued to fetch fresh data.

This option allows writing more flexible queries as SQL is sent straight to the database engine. By default argument for SQL queries should be defined as in the example below:

select code, createdate, description from systemrolejpa where id<= :id

If there is a need to define the custom, non-default symbol for where clause arguments, construction as below can be specified where '%' can also be replaced with almost any character, e.g., @, #, $, &:

arg % select code, createdate, description from systemrolejpa where id <= %id

Table structure:

Let’s now use the tester to call defined parameter. When id equals 7 is specified it returns HYPERON_SUPERPACK_IMPORT role from table“systemrolejpa” from the database specified as the external source on the parameter screen.

Read more about external source functionality here.