Database Listener

Description

The SQL Database listener runs SQL queries periodically and sends the output to 1Gateway as messages.

Prerequisites

The JDBC driver for the database has to be installed in the 1gateway data/lib directory (File Upload into System Maintenance - 1gateway restart is required).

Database	JDBC Driver Provider	JAR file name	Download
MySQL	Oracle Corporation	mysql-connector-java-VERSION.jar	Download JDBC Driver for MySQL
SQL Server	Microsoft Corporation	mssql-jdbc-8.2.2.jre8.jar	Download JDBC Driver for SQL Server
Oracle	Oracle Corporation	ojdbc6.jar, ojdbc7.jar, ojdbc8.jar	Download JDBC Driver for Oracle (login required)
PostgreSQL	The PostgreSQL Global Development Group	postgresql-VERSION.jar	Download JDBC Driver for PostgreSQL
SQLite	Xerial.org	sqlite-jdbc-VERSION.jar	Download JDBC Driver for SQLite

Installation

Make sure you have the Plugin zip file that has been provided to you with the installation files.

Login to 1gateway, and make sure you are in "Advanced mode". If this is the first time you log in, the default user and password are both 'admin' (without the quotes).

2 - System Maintenance

Click on the menu icon and choose the option "System maintenance"

3 - Upload file

In the System maintenance view you can either drag and drop the downloaded zip file or select it by clicking the "choose file" button.

Configuration

Open the main menu and click on "New plugin"

Select the plugin you want to configure. Use the Filter field if needed.

The Endpoint name is the identifier of the connected system.

There are two different profiles to configure:

Connection profile: includes database connection configuration
Query profile: configuration of the queries to run

Connection configuration

The username and password can be defined as part of the connectionString or in their own separate fields.

Field name	Supported values	Description	Example
Profile name	Any string	Name of the connection profile	mysql
Connection string	Valid connection string	Connection string to the database	jdbc:sqlite:C:\\Temp\metric.db jdbc:sqlserver://localhost:1433;user=sa;password=abc123;databaseName=TestDB; jdbc:oracle:thin:[<user>/<password>]@//HOSTNAME:PORT/SERVICENAME jdbc:oracle:thin:[<user>/<password>]@(description=(address=(host=HOSTNAME)(protocol=tcp)(port=PORT))(connect_data=(service_name=SERVICENAME)(server=SHARED)))
Database user	Any string	Database user	sa
Database password	Any string	Database user's password	abc123

Query configuration

The SQL query output contains a type column that specifies the message type. If no type is defined, the default message type field is applied. This message type is then used for the mapping (see Mappers).

Field name	Supported values	Description	Example
Profile name	Any string	Name of the query profile	cis
SQL query	Valid SQL query	Query to be executed	SELECT 'DatabaseCI' as type, e.* FROM `innhold bkmon_Ark1` e where lastUpdated > ?
Field parameter (optional)	Any string	Parameter in the query to run as the where field	lastUpdated
Field default value (only required when "Field parameter" is set)	Any string	Starting parameter value (needs to be valid)	2019-01-01 00:00:00.000
Database connection	Any string	Name of the connection to use (defined above)	mysql
Default message type	Any string	Default message type for incoming data	DatabaseCI
Run interval in seconds	Any integer	Polling interval in seconds (default is 300)	60

Field Parameter & Field default value

These two fields are used to check for only updates in a table and create 1Gateway Messages only for newer row entries in a SQL table.

If the "Field parameter" is set, the "Field default value" has to be set too. The first time the query will run, it will take the value of the "Field default value" to compare in the "Field parameter"(column). Afterwards it will take the latest entry in the "Field Parameter" defined column.

If a "last updated" column exists, it allows to poll only for rows in the database that have changed.

Example

First Run

1Gateway config: 
Select 'Alert' as type, * from NAS_ALARMS WITH (NOLOCK) WHERE time_arrival > '?' ORDER BY time_arrival

1Gateway executes the following Query: 
Select 'Alert' as type, * from NAS_ALARMS WITH (NOLOCK) WHERE time_arrival > '1990-01-01 00:00:00.000' ORDER BY time_arrival

SQL

Result:

It will select all rows from the database where the time_arrival is newer than 1990-01-01 00:00:00.000 and generate a 1Gateway message for each result row.

The last entry in this case has a "time_arrival" of 2020-08-24 08:22:42.000

All following runs

Select 'Alert' as type, * from NAS_ALARMS WITH (NOLOCK) WHERE time_arrival > '2020-08-24 08:22:42.000' ORDER BY time_arrival

SQL

From the second run on forward, 1Gateway will take the "time_arrival" time of the last entry and use it for the next query. So only new entries will create 1Gateway messages.