Alternative Configurator by axtesys
This Configurator is an alternative way to configure the connector: axtesys SQL Connector Configurator
Workflow
Create the whole configuration consisting of database definition, table definitions and data points (columns), export the configuration in json file format, upload the configuration in the common configurator and deploy it to the application.
Connections
With our Configurator you always configure one connection at a time. For using multiple connections paste the connection configuration object of one export into another in the connections array. When configuring multiple connections please make sure that they are all given unique names.
Configuration
Database Server Information
-
Host server:
The host server is a url or dns entry being used to connect the Connector to the database.
-
Database name:
The name of the database to which the Connector should connect. The database must already be created and online so that the Connector can connect to it.
-
Port:
The port of the database.
-
Database Username:
The user to connect to the database.
-
Database Password:
The password to connect to the database.
-
Database Quoted Identifier active?
This encloses table and column names in quotation marks in all sql statements.
-
Heartbeat Rate
Specifies the interval in ms where the connector verifies the connection to the database.
-
Heartbeat startup delay
Specifies the delay after connector start until it starts performing connection checks to the database.
-
Fetchsize
The fetchsize is the number of rows that are read from the table. If the fetchsize is not filled in, all rows are read out.
Buffering Details
-
Buffer:
The buffer option is used to select between two insert modes. If this option is activated, a defined number of inserts will be collected before they are written to the database. If this option is not activated, buffering only takes place in the event of the connection to the database being lost.
-
Buffer size:
The buffer size is the number of inserts that are collected before they are transferred to the database.
Datapoints
The table displays all datapoints currently configured. You can create, edit and delete datapoints.
-
Datapoint Name
Each datapoint has its own name, which can be freely chosen. Later in the metadata each column (datapoint) gets its own ID (e.g. DP12).
-
Acquisition Cycle
The acquisition cycle is the interval in ms at which the database is read. This value can be entered for each column, but can only differ from table to table. If different values are configured for the same table, the top datapoint is always used as the configuration.
-
Access mode
Configures the access mode of the table. This value can be entered for each column, but can only differ from table to table. If different values are configured for the same table, the top datapoint is always used as the configuration.
-
Primary Sorting Attribute
Makes the selected column the column to be sorted by. This column must be a column through which the actuality of the data can be determined to guarantee the function of the "Newest" read mode. For each table, exactly one column must be marked as the order column. If none or more than one is marked, this table will not be read out by the SQL connector.
-
Identity Column
Specifies that this column is an AUTO INCREMENT column. The connector will not perform any inserts on columns set to Identity Columns no matter is being received on the write topic.
-
Operation Type
Two reading modes can be selected. One is the "Select" mode, in which the top fetchsize rows are always read out at a certain time interval. On the other hand, the "Newest" mode in which only the top fetchsize values that have not yet been sent are returned.
This value can be entered for each column, but can only differ from table to table. If different values are configured for the same table, the top tag is always used as the configuration.
-
Target Column
Name of the column. When entering the column name, pay attention to the case sensitivity otherwise the connector will not find the column name in the table for the validation and will set the whole table to invalid, which means it can neither be read from nor written to the table.
-
Target Table
Name of the table.
The table name must be entered for each column.
When entering the table name, pay attention to the case sensitivity otherwise the connector will not find the table name in the database for the validation and will set the whole table to invalid, which means it can neither be read from nor written to the table.
-
Data Type
The data type that the column has. Currently, only the following data types are supported:
| Common Payload | Datatype SQL Datatype |
|---|---|
| Bool | bit |
| Byte | tinyint |
| Word | int |
| DWord | bigint |
| Real | real |
| DateTime | datetime |
| Date | date |
| String | varchar |
If the acquisition mode is set to "Newest" and the column is used to determine the new rows by comparison, only the following data types are supported to select the function:
| Common Payload | Datatype SQL Datatype |
|---|---|
| Bool | bit |
| Byte | tinyint |
| Word | int |
| DWord | bigint |
| Real | real |
| DateTime | datetime |
| Date | date |
-
Where Freetext
Here you can enter free text for your SQL Query. This will be appended to a SQL statement at the WHERE part. You can use ORDER BY only if the primary sorting attribute is false.
Example Configuration for MSSQL DB
Download JSON
To download this configuration, please click on the link above. Another tab with the config.json file will open.
- Firefox:
Then click on the Save button in the left corner. Choose the location you want to save your file in and rename it if needed.
Afterwards, you can use our Configurator to upload the file there and change what you need. - Edge:
Please copy and paste the config.json file into a json-file you create at your specified location. Afterwards, you can use our Configurator to upload the file there and change what you need.
{
"configs": [
{
"$schema": "https://siemens.com/connectivity_suite/schemas/sqlc1/1.0.4/config.json",
"config": {
"connections": [
{
"parameters": {
"host": "localhost",
"database": "Connection_1",
"db_port": 1433,
"db_username": "admin",
"db_password": "adminpw",
"buffer_active": true,
"buffer_size": 50000,
"quoted_identifier": false,
"heartbeat_fixedRate_in_milliseconds": 500,
"heartbeat_initialDelay_in_milliseconds": 10000
},
"name": "ExampleDB",
"datapoints": [
{
"parameters": {
"table_name": "licht",
"data_type": "Real",
"fetchsize": 100,
"acquisition_mode": "Select",
"acquisition_cycle": 5000,
"access_mode": "rw",
"sort_by_column": true,
"custom_filter": "",
"sorting": "Ascending",
"column_name": "id",
"identity_column": false
},
"address": {},
"name": "id"
},
{
"parameters": {
"table_name": "licht",
"data_type": "Byte",
"fetchsize": 100,
"acquisition_mode": "Select",
"acquisition_cycle": 5000,
"access_mode": "rw",
"sort_by_column": false,
"sorting": "Ascending",
"column_name": "value",
"identity_column": false
},
"address": {},
"name": "value"
},
{
"parameters": {
"table_name": "licht",
"data_type": "DateTime",
"fetchsize": 100,
"acquisition_mode": "Select",
"acquisition_cycle": 5000,
"access_mode": "rw",
"sort_by_column": false,
"sorting": "Ascending",
"column_name": "created_at",
"identity_column": false
},
"address": {},
"name": "created_at"
}
]
}
]
}
}
]
}
