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

   • 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

   • Set the Maximum pool size

   • Specify the Alive check interval

   • Specify a Reconnection interval

Description of configuration properties:

Select Statement

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

2. Check the Custom Query checkbox

   • Enter the SQL Query

   • (Optional) Check the Set empty variables to NULL if emtpy variables should be set to NULL in the SQL statement

   • (Optional) Check the Log query checkbox to explicitly allow logging of the defined SQL statement to the database.

   • (Optional) Check the Log result checkbox to explicitly allow logging the entries (result) the query returned.

3. Each variable under Parameters and Reply needs to be assigned to a corresponding database column. To configure this, select the variable node under Parameters and then choose the appropriate option in the tree structure.

4. Check the Assign database column checkbox

   • Enter the Column name as it is defined in the used database

   • (Optional) Check Not used to exclude the variable from the database operation

Insert Statement

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

2. Check the Insert checkbox

   • Enter the Table name

   • (Optional) If required enter a Schema name

   • (Optional) Enter the Primary key column name

   • (Optional) Check Log query to explicitly allow logging of the defined SQL statement to the database. This is recommended during the configuration and testing of the communication instance. In production mode, this should be disabled.

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

4. Check the Assign database column checkbox

   • Enter the Column name

   • (Optional) Check the Insert auto generated key from parent checkbox if the column relates to its parent

   • (Optional) Check Not used to exclude the variable from the database insert operation