This plugin is part of the CoDaBix® Setup. Please consult CoDaBix® Setup and First Start for more information on how to install and uninstall this plugin.
The entire SQL Exchange Plugin configuration is located under the Node path /System/Exchange/SQL Exchange
.
The Node tree in the image above depicts the SQL Exchange Plugin's default Node tree. To set up one or more SQL Exchange Databases, add a Folder Node beneath the Node SQL Exchange / Databases
, or right-click on the SQL Exchange / Databases
Node and select Add Database
.
Name | Type | Description |
---|---|---|
Server Type | Enum | Defines the server type to which the database connection is established. Valid values: MySQL , Oracle , MSSQL , ODBC , SQLite |
Server | String | The hostname or IP address to which the connection shall be established. For SQLite, this is the file path to the database; other connection properties like Port will be ignored. |
Port | Integer | The port to which the connection shall be established. When using MSSQL as Server Type, you can specify 0 as port, which means a connection to the default SQL Server instance shall be established. |
Database Name | String | The name of the database/scheme. For ODBC, this is the data source name (DSN), and is the only field that needs to be specified. The DSN can be configured on Windows using the ODBC Data Sources app (User DSN or System DSN). Note that for Codabix (x86) you will need to use the 32-bit and for Codabix (x64) the 64-bit ODBC Data Sources app. |
Username | String | The username for the connection. |
Password | Password | The password. |
Connect Timeout | Integer | The timeout in seconds after an attempt to connect is canceled. |
Command Timeout | Integer | The timeout in seconds after a long-running command is canceled. |
After clicking “Save”, the database Node will be created. You can start it by selecting the database Node and then clicking the Start button:
To setup one or more tables for the database, you can right-click on the database Node and then use the Browse Tables
menu entry:
Alternatively, you can right-click on the Tables
Node and select Add Table
(to edit an existing table, right-click on the Table
Node and select Edit Table
):
Name | Type | Description |
---|---|---|
Table Name | String | The name of the table in the database. |
Read SQL Expression | String | An SQL string (e.g. a WHERE clause or an ORDER BY clause) that is applied when reading the columns. The first row returned by the database is read. You can specify a WHERE clause (e.g. WHERE "ID" = 123 ) to read only a specific column. The default is ORDER BY "ID" DESC to order the rows by ID descending, so that the row with the highest ID is used. |
Write SQL Expression | String | An SQL String (e.g. a WHERE clause) that is applied when writing the columns. You can specify a WHERE clause (e.g. WHERE "ID" = 123 ) to update an existing row. The default value is an empty string, which means a new row is inserted every time column Nodes are written. |
Every Node within the Columns
Node of a table Node can be assigned to a column of the table, using the Path
property.
Syntax:
<ColumnName> | Only the column name is specified. When reading or writing from / to it, the table's Read SQL or Write SQL expressions are used. Example: MyColumn1 |
<ColumnName>; <ReadWriteExpression> | The column name is specified, along with an expression that is used when reading or writing the column. Example: MyColumn1; WHERE ID = 123 |
<ColumnName>; <ReadExpression>; <WriteExpression | The column name is specified, along with an expression that is used when reading from the column, and a separate expression that is used when writing to the column. Example: MyColumn1; WHERE ID = 123; WHERE ID = 456 |
To monitor and diagnose the status of the different databases channels, take a look at the following image:
The image above depicts the database channel's Control Panel which displays all status relevant information. The control panel will automatically update its status information when a new status is available.
Status Circle
All database related status information is also logged into the database-specific log file stored in the [LoggingFolder]
. Each log file is named in the naming scheme SQL Exchange.<DatabaseName>.log
. The content of such a log file can look as follows:
... [14:55:34 25.07.2017] - Error (Severity=High): Code=[-1], Text=[Access denied for user 'abc'@'localhost' (using password: YES)], Details=[] ...
As each exchange plugin the SQL exchange plugin extends the basic CoDaBix Exchange Model.
The plugin's exchange type SqlExchange
also defines the SqlExchangeChannel
and therefore extends the basic CodabixExchange
and CodabixExchangeChannel
entities. While the SqlExchange
just represents a concretization of the CodabixExchange
, the SqlExchangeChannel
extends the CodabixExchangeChannel
with the SQL Table entities.
Each channel is handled by a channel worker which establishes a physical connection to the database.
For diagnostic purposes, the worker automatically checks the database connection every 10 seconds to update the status code of the Channel
and description to detect connection failures.
By default, the worker does not read any values. When a Client or plugin requests a synchronous read of the Channel
, the channel worker reads the variables in CoDaBix (e.g. using the CoDaBix Web Configuration's function “Read actual value”) from the database and then writes them into the corresponding CoDaBix Nodes.
Similarly, when a Client or plugin writes values into the channel's variables, the channel worker will write those values to the database.
To have an database variable being read steadily, you can edit the Node in the Configuration Web GUI and set “History Options” to Yes
(which will create an internal subscription), or you can use e.g. an OPC UA Client connected to the OPC UA Server plugin and create a subscription for the S7 variable Nodes. In these cases, the channel worker reads the variables from the database at a regular interval and, if the value of one of the variables has changed, writes the new value into the corresponding CoDaBix® Node.
Each table entity represents the information needed to access a database table. At table level, you can specify how to select the row which the values will be read from or written to.
Each column entity represents a single database table column.
Content | Path | Usage |
---|---|---|
AssemblyFolder | <CodabixInstallDir>/plugins/SqlExchangePlugin/ | Contains the plugin's assembly file. |
ConfigFolder | <CodabixDataDir>/plugins/SqlExchangePlugin/ | Contains the plugin's configuration file. |
LoggingFolder | <CodabixDataDir>/log/ | Contains the plugin's log files. |
Type | Path | Usage |
---|---|---|
Assembly | [AssemblyFolder]/CoDaBix.SqlExchangePlugin.dll | The plugin's assembly file. |
Logging | [LoggingFolder]/SQL Exchange.<DatabaseName>.log | The log file. |
Date | 2018-06-15 |
---|---|
Version | 1.1 |
Name | SQL Exchange Plugin |
---|---|
Node | /System/Exchange/SQL Exchange |
Version | 1.0.5 |
Name | CoDaBix.SqlExchangePlugin.dll |
---|---|
Date | 2019-02-04 |
Version | 1.0.5.0 |