Databases

InfluxDB

1. Select the root model node in the tree on the left.

2. Configure the InfluxDB

   • Enter the URL to the database

   • Enter the database name

   • Enter the database user name and the password

3. Click the Apply button and save the Channel by clicking the Save button on the upper right corner.

SQL Database

Required model node types in the Information Model

Use Case: Insert/Update

• The node after the root model node must be an Event which represent a database table

• In case of relational databases: Any other database tables which are dependent on each other require a List

• Columns of databases are represented by Variables

Use Case: Select

• The Command represents e.g., a select statement

• Parameters within a Command represent a collection of query parameter - query parameters are defined as Variables

• Reply within a Command represents the result of the Command - results are defined as Variables

Characteristics

• The SQL Channel can be configured for the following two scenarios:

  • Inserting data

  • Updating data

  • Retrieving data

• When inserting values into the database please note that “infinity” values are converted automatically into “null” values

How to configure the SQL-Database

1. Select the root model node in the tree on the left.

2. Configure the database connection

   • Select the database type.

   • Specify a reconnection interval.

   • Enter the database connection url for the specific database type.

     • DB2: jdbc:db2:server:port/database

     • HSQLDB: jdbc:hsqldb:file:databaseFileName;properties

     • ORACLE: jdbc:oracle:thin:prodHost:port:sid

     • PostgreSQL: jdbc:postgresql://host:port/database

     • SQLServer: jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

     • MariaDB: jdbc:(mysql|mariadb):[replication:|loadbalance:|sequential:|aurora:]//<host>[:<portnumber>]/[database][?<key1>=<value1>[&<key2>=<value2>]]

   • Enter the database user name and password.

Description of database configuration properties

Property	Description	Mandatory	Example
Type	Type of the database	Yes	MariaDB, SQLServer, ORACLE, HSQLDB, DB2, PostgreSQL
ReconnectInterval	Time to reconnect if connection fails	Yes	10 (in milliseconds)
JdbcUrl	Url to connect to database	Yes	SQLServer: jdbc:sqlserver://<serverName:1433;databaseName=unifier MariaDB: jdbc:mariadb://localhost:3306/unifier?connectTimeout=5000 DB2: jdbc:db2://127.0.0.1:50000/TESTDB HSQLDB: jdbc:hsqldb:file:$dbFileName;shutdown=true ORACLE: jdbc:oracle:thin:@localhost:1521/MYCDB PostgreSQL: jdbc:postgresql://127.0.0.1:5432/postgres
Username and password	Credentials of the database	Yes

Note

The configuration of specific information model nodes differs whether you want to perform an insert or an select statement on the database. Inserting data into the database requires an event node whereas selecting data requires a command node in the Information Model.

Select Statement

3. Select the command node in the tree on the left.

4. Check the custom query checkbox and enter the sql query.

Insert Statement

3. Select the event node in the tree on the left.

4. Check the insert checkbox and enter the table name. If required enter a schema name.

5. Each variable under Parameters and Reply needs to be assigned to a database column. Select the variable node under Parameters and in the tree select what needs to be configured.

6. Check the assign database column checkbox and enter the column name as it is defined in the used database.