SQL Database
The SQL Database driver provides a method of monitoring any JDBC-compliant Database Management System (DBMS). Almost all modern database servers provide a Java Database Connectivity drivers, and thus may be monitored and controlled by Iotellect.
The driver has the following capabilities:
Checking database server availability and status;
Executing selection queries and providing access to query results;
Executing dynamically generated insert/update/delete queries.
 | It's necessary to add at least one "probe" query to ensure database operability checking. If no queries were added, the driver will establish connection upon the first synchronization cycle and do nothing during subsequent synchronizations, providing no reliable database availability and operability data. It's always possible to use a very simple test query, such as |
The driver uses connection pool to improve performance by allowing to execute several requests in parallel. See "Connection Pool ..." properties in the Database Connection Properties section below for details.
| It's possible to test pooled connections using the two advanced settings: Test Connection on Check-in and Idle Connection Test Period. These settings can be used either together or independently to minimize the possibility of having a stale or broken connection. The testing is switched off by default. Do not change these setting unless you really need to and you understand what you are doing. The Maximum Idle Time setting determines when a pooled idle connection is discarded. This setting should be set to a lower value than the wait_timeout setting of the corresponding SQL server (for MySQL, if wait_timeout is not explicitly stated in the configuration file (my.ini or my.cnf) its value is 8 hours by default). |
Adding Database JDBC Drivers
To allow Iotellect Server to load a third-party JDBC database driver supplied by your database server vendor, a JAR (Java Archive) file containing this driver must be added to the server classpath by putting it into /jar subdirectory of Iotellect Server installation or using Iotellect Server Launcher Properties File.
 | SQL Database driver can be also used to connect to Open Database Connectivity (ODBC) data sources via a JDBC-ODBC bridge that is a part of Iotellect Server installation. No additional drivers should be added in this case. The Database URL syntax to use is |
Java Version
Most Iotellect Server distributions include a bundled JVM. Any Java VMs installed in the OS are not used by default. Thus, any custom JDBC driver should be compiled for the version of Java used by the server. This version can be found in system requirements.
Driver Information
Driver Plugin ID: | com.tibbo.linkserver.plugin.device.database |
Global Settings
None defined.
User Level Settings
None defined.
Device Account Properties
Connection Properties
Property | Description |
Database URL | This is a database-specific string that defines the database type, the path (local or network) to a database to execute queries on and any additional options. To figure out the correct value for your selected JDBC database driver, please consult its documentation. |
Driver Class | Java class name of the JDBC driver. Can be found in JDBC driver documentation. Required only for drivers that are not JDBC4-compliant. |
Database Username | This property defines which username is used to log on to the database server. |
Database Password | This property defines which password is used to log on to the database server. |
Additional Properties | Table containing advanced properties of database connection. The table has two columns: Property and Value. The list of supported properties can be found in JDBC driver documentation. |
Request Pool Minimum Size | Minimum number of Connections the connection pool will maintain at any given time. |
Request Pool Maximum Size | Maximum number of Connections the connection pool will maintain at any given time. |
Request Pool Increment | Determines how many connections at a time the connection pool will try to acquire when the pool is exhausted. |
Connection Checkout Timeout | Sets the maximum time that the SQL Database Device waits for a new database connection. Default value is 0, which indicates infinite wait time. |
Unreturned Connection Timeout | Sets the maximum time that any database connection can be kept open. When timeout value is reached for a connection, it is forced to close. Default value is 0, indicating no limit on connection time. If a non-zero value is set, it should be longer than any database connection is expected to be in use, otherwise connections in active use can potentially be closed. |
Maximum Idle Time | The time before a pooled idle connection is discarded (0 means idle connections never expire). |
Test Connection on Check-in | If set to true, a connection test will be performed asynchronously at every connection check-in to verify that the connection is valid. |
Idle Connection Test Period | Pulled idle connections will be asynchronously tested every this period of time (0 means never). |
Queries
This is a tabular setting defining what queries will be executed using a database server.
Field | Description |
Name | Name of device setting variable containing query result. |
Description | Description of device setting variable containing query result. |
Expressions | Forces treating Read Query and Write Query fields as expressions. These expressions must resolve to actual SQL query strings. |
Read Query | Text of SQL selection query, or expression text if Expressions flag is set. Read query execution result will be accessible within Iotellect as a device setting variable. |
Write Query | Text of SQL insert/update/delete query, or expression text if Expressions flag is set. If this field is non-empty, variable containing Read Query execution result will be writable (editable). Its modification will trigger execution of the Write Query. If Write Query is an expression, it may refer to the Data Table containing Read Query result. This is a default table for references inside this expression. |
Device Assets
Assets are not supported by the driver.
Device Settings
Database device driver creates one device setting variable per Queries table record. Format of this variable is dynamic, depending on the result of Read Query execution.
In addition, Database Statistics variable is created with the following fields:
Database Name
Database Version
Database Driver Name
Database Driver Version
Device Operations
Execute Query
This action lets you execute an arbitrary query on the database. Both select and update queries are supported.
Function input fields:
Field | Description |
Query | Query text. May contain query parameter references inserted as |
Update | Flag defining whether the query is an update or a select query. |
Parameters | Query parameters table. Value of cell in first row and first column will substitute first query parameter (first occurrence of |
Device Events
No events provided by the driver.
Connection Handling
This driver makes the device Online if JBDC connection to the database was established successfully.
Data Types Conversion Rules
When executing a query JDBC Driver converts SQL data types to JDBC types, then Iotellect Server converts JDBC types to the Iotellect field types following the rules:
JDBC Type | Field Type |
TINYINT | Integer field |
SMALLINT | Integer field |
INTEGER | Integer field |
BIGINT | Long field |
VARCHAR | String field |
BOOLEAN | Boolean field |
BIT | Boolean field |
NUMERIC | Long field |
REAL | Float field |
DOUBLE | Double field |
DECIMAL | Float field |
TIMESTAMP | Date field |
LONGVARCHAR | Data Table field |
LONGNVARCHAR | Data Table field |
BLOB | Data Table field |
LONGVARBINARY | Data Block field |
Information about mappings between SQL data types and JDBC types can be found in the JDBC driver documentation. These correspondences affect result types when executing queries.
| In some cases a single SQL data type can be represented by different field formats in Iotellect. For example, JDBC Driver for Microsoft SQL Server represents |
Was this page helpful?