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 1gateway zip file that includes the plugin files and valid licences information ready. This file 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.

4 - Restart

After the upload installation will be confirmed. Restart 1Gateway to ensure any locked files are updated.

Configuration

Open the main menu and click on "New plugin"

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

There are two different profiles to configure:

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

Connection configuration

If a connectionString is defined, the rest of the parameters don't have to be set, and will be ignored. If no Connection String is set, the rest of the parameters have to be defined.

Field name	Supported values	Description	Example
Database host	Any string	Host where the database is	localhost
Database port	Any string	Port number	1433
Database type	Any string	Database type	sqlserver, sqlite
Database user	Any string	Database user	sa
Database password	Any string	Database user's password	abc123
Database name	Any string	Database name	TestDB
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)))

Query configuration

The SQL query output must contain a Type column that specifies the message type. This message type is then used for the mapping (see Mappers).

Field name	Supported values	Description	Example
SQL query	Valid SQL query	Query to be executed	Select 'Alert' as type, * from NAS_ALARMS WITH (NOLOCK) WHERE time_arrival > '?' ORDER BY time_arrival
Field parameter (optional)	Any string	Parameter in the query to run as the where field	time_arrival
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	SCOMDB
Polling interval	Any integer	Polling interval in seconds (default is 300)	60

Field Parameter & Field default value

These 2 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.

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.