Database Sender

Description

The SQL Database sender sends messages (events and metrics) from 1Gateway into a database table that is already defined, using a JDBC connection. The messages will appear as rows in a database table. To insert messages in multiple tables, more than one instantiation of the SQL Database sender plugin is needed.

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

The database table into which the data is imported must exist. If it does not exist, it must be created.

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.

Configuration

Open the main menu and click on "New plugin"

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

Field name	Supported values	Description	Required	Example
Connection string	Valid connection string	Connection string to the database	No, if other fields defined	jdbc:sqlserver://localhost:1433;user=sa;password=abc123;databaseName=TestDB; jdbc:sqlite:C:\\Temp\metric.db jdbc:mysql://sarah@localhost:3306/1gateway
Database name	Any string	Database name	No, if connection string is defined	TestDB
Database type	Any string	Database type	No, if connection string is defined	sqlserver, sqlite
Database user	Any string	Database user	No, if connection string is defined	sa
Database password	Any string	Database user's password	No, if connection string is defined	abc123
Database port	Any string	Port number	No, if connection string is defined	1433
Databse host	Any string	Host where the database is	No, if connection string is defined	localhost
Database table name	Any string	Database table name	Yes	TestTable
Script to run before insert	Filename	Filename of script to run before inserting message into database	No	beforeScript.sql
Script to run after insert	Filename	Filename of script to run after inserting message into database	No	afterScript.sql

If a connectionString is defined, the rest of the parameters don't have to be set, and will be ignored. If no connectionString is set, the rest of the parameters have to be defined. To properly configure the database sender, a mapper is needed. The mapper needs to include mappings to convert fields from normalized messages to field names that match the table column names.

Example of a script file to run before inserting message:

CREATE DATABASE 1gateway;
use 1gateway;
CREATE TABLE 1gateway.metrics(
   device VARCHAR(255),
   ci VARCHAR(255),
   timestamp VARCHAR(255),
   value VARCHAR(255),
   metrictype VARCHAR(255)
);
select * from 1gateway.metrics;

SQL