Database Servers in TransSECS (Devices) and MIStudio/MIX use the same code base and therefore are configured and used in much the same way. Database servers can be used in MIStudio (graphically connecting from data sources to save values in database tables), in scripting (adding scripts to servers in MIStudio or TransSECS, or using the JavaScript bean in MIStudio), and by using the Devices Node (adding a Database Device Server to the Devices node in TransSECS (Devices) or in MIStudio. Examples of each of these are provided in the links below.

For all examples, a Database Connection Manager (DatabaseConnectionManager) is used to connect to the database itself. Some DatabaseConnectionManagers are set up for specific databases (MySQL/MariaDB, HSQLDB, and SQLite) but the generic DatabaseConnectionManager can be configured to use any JDBC database, including Microsoft SQL Server and PostgreSQL. The DatabaseConnectionManager specifies the driver class (the JDBC connection Driver), the url to the database (where the database is located and its name), a login name and a password. The database will need to be installed and set up before the DatabaseConnectionManager can be used to connect to it.

To use any of the database servers, a JDBC Driver jar specific to the database must be added to the project. For MIStudio, use the Drivers node of the project, right click and browse for this jar on your computer system. When you deploy to MIX from MIStudio, this jar will be added to the MIX deployment. For TransSECS this jar must be added to a resources directory of your project. When you build your TransSECS project this jar will be added to the deployment.

To read data from the database or to write data to the database, there are a variety of database server “beans” which, at a minimum, need to be configured to use a table name and a SQL statement, as well as selecting a pre-configured DatabaseConnectionManager. These beans include DatabaseRawWrite (write one value to the database table), DatabaseRawRead (read one value from the database table), DatabaseArrayRawLookup (read an array of values from the table), DatabaseRowLookup (read an array of values from a single row in the table), and DatabaseRawTableLookup (read a 2D array of values from the database, as a whole table). In addition there is a “historical” database bean which writes a whole row of timestamped data to the table when it is triggered, the TriggeredHistorical bean. See: Reporting and Historical Servers

Using the Database Servers in MIStudio Graphically

This shows you some examples of setting up a DatabaseConnectionManager in MIStudio and using it to read and write data to a table in the database.

Using the Database Servers in MIStudio and TransSECS Devices Node

Database Connection Managers can be added to and configured in the Devices Node of TransSECS and MIStudio.

Using the Database Servers in Scripts for MIStudio and TransSECS

Using scripts in your project can provide you with a lot of flexibility in your back-end logic.