SQL Database

Characteristics - SQL Database

• 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.

Information Model Requirements

Insert/Update

• The node after the root model node must be of type Event which represent a database table.

• In case of relational databases: Tables which are dependent on each other require a List .

• Columns of databases are represented by Variables .

Select

• The Command defines that after a request is made, a reply with a result is expected.

• 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 .

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 Username and Password or select it from the Credentials Manager.

Description of configuration properties:

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

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.

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.

Insert Statement

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

2. Check the Insert checkbox and enter the Table name. If required enter a Schema name.

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

4. Check the Assign database column checkbox and enter the Column name (Check the Insert auto generated key from parent checkbox if the column relates to its parent)

Note

Configuration of the column name is only necessary if the column name in the database is different compared to the variable defined in the Information Model.