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.

1 - Login

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

RequiredExample
Connection stringValid connection stringConnection string to the databaseNo, 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 definedTestDB

Database type

Any string

Database type

No, if connection string is definedsqlserver, sqlite

Database user

Any string

Database user

No, if connection string is definedsa

Database password

Any string

Database user's password

No, if connection string is definedabc123

Database port

Any string

Port number

No, if connection string is defined1433

Databse host

Any string

Host where the database is

No, if connection string is definedlocalhost

Database table name

Any string

Database table name

YesTestTable
Script to run before insertFilenameFilename of script to run before inserting message into databaseNo

beforeScript.sql

Script to run after insertFilenameFilename of script to run after inserting message into databaseNoafterScript.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

See also