20. Database Add-on

The database Add-on adds SQL database support. It allows you to log process data values into a SQL database via the SQL Data Logger and it provides support for executing SQL queries.

20.1. SQL Database Logger

The SQL database logger enables logging of process data into an SQL database. The logger is not intended to record process data at very high sample rates but to log important process data over a longer period of time to document experiments or automated processes in the laboratory.

The SQL database logger supports various databases such as SQLite, MySQL or PostgreSQL. It has been tested with SQLite and MySQL databases.

20.1.1. SQL Logger Configuration Dialog


When the data logging plug-in has been loaded, the toolbar will display two additional buttons for the configuration of the SQL database logger and to start/stop the logging process .


Click the Configure SQL Logger button to open the configuration dialog. The following configuration dialog will be displayed:


The configuration dialog contains the following elements:

  1. Object Tree - The Object Tree contains a tree of all objects and their properties that are present in the application. You can use various filters to filter the object tree for specific objects. By default, only devices and device properties are displayed.

  2. Logger Channels - lists all channels that may be recorded by the logger.

  3. Configure Database Connection - allows the user to configure the database settings such as database server and port.

20.1.2. Object Tree

In the Object Tree you will find a hierarchical list of all objects (e.g. devices) and their child objects (such as child devices):

  1. Object - An object can be, for example, a device (here Nemesys_M_1) or another application object. You will find the two elements Children and Properties in each object after expanding it in the next level.

  2. Children - The Children element groups all child objects of the parent object. In our example, these are all objects or devices that are subordinate to the device Nemesys_M_1 or belong to this device.

  3. Properties - The Properties element groups all properties of the parent object. In our example, these are all the properties of the Nemesys_M_1 device that can be recorded in the logger.

  4. Child Object - All child objects can be found in the Children group. In the example Nemesys_M_1, these are, for example, the digital and analogue inputs and outputs of the device, such as Nemesys_M_1_DigOUT1. These objects can in turn be expanded to display their child objects and properties.

  5. Property - In the Properties group you will find all properties of the parent object. In the example of Nemesys_M_1, these are, for example, the properties SyringeFillLevel or ActualFlow. You can simply drag and drop these properties into the channel list to record their values.

20.1.3. Filtering the Object Tree

Above the object tree you will find various filters with which you can filter the object tree according to certain criteria. The Devices Only checkbox is activated by default. This means that only devices that are managed by the internal device manager (Core.DeviceManager) are displayed in the object tree. If you deactivate this checkbox, other application objects are displayed in the object tree.

If the Devices Only checkbox is activated, a selection box is displayed with which you can filter the device tree according to a specific device type. In the illustration below, for example, the tree was filtered for syringe pumps:


In addition, you will find an input field directly above the object tree, with which you can filter the object tree according to a specific term, e.g. a device name or a device property. In the image below, for example, a filter has been set for the device property ActualFlow. This means that only objects or devices with this property are displayed in the object tree:


20.1.4. List of Logger Channels

The table Logger Channels shows the configuration of the logger.


The table contains the following columns:

  • Object - contains the name of the object from which the value of a certain property is to be recorded and the icon of the object.

  • Property - this is the name of the object property/process data value that will be recorded. Its type (numeric or boolean) can be identified by the displayed icon.


    Numeric value


    Boolean value


    Text value


    Data Array


    Data Structure



  • Interval (s) - the sampling interval in seconds. The minimum sample time is 1 seconds.

  • Label - allows you to define a customized description for the selected channel.

20.1.5. Database Settings

To configure the database settings, click the Configure Database Connection button in the configuration dialog.


Intially the database logger uses a SQLite database in the current project folder for logging. The default SQLite database file is located in:


With the following steps you can easily find the database file

  • from the application main menu select the menu item File → Browse Project Folder

  • the project folder will be opened in Windows file explorer

  • now open the Log folder

  • inside of the Log folder you should see the file ProcessDataDbLog.sqlite

If you open the database configuration for the first time, you should see this default configuration:


As soon as you click the Check Connection button you should see the green checkmark . Whenever you want to restore these default settings, just click the Restore default settings button .

If you use the SQLite database driver, you just need to select the database filename. If you select any other database driver, you need to provide additional database settings. The following picture shows the configurations settings for a MySQL database:


The following fields must be filled in:

  • Database Driver: the database driver that matches your database

  • Server Hostname or IP: the hostname or IP address of the server running the database. In this example the MySQL database runs locally and we use localhost

  • Port: the server port number to connect to database. In this example whe use localhost as hostname and therefore the port does not matter

  • Database Name: name of the database to use for data logging

  • User Name: database user to use for database access

  • Password: password to connect to database

As soon as you have properly entered all fields, the Check Connection button will be enabled. Click this button, to check your database connection. If this check succeeds, you can click OK to accept the settings.


The Check Connection will be enabled only, if you have entered values in all required fields. The OK button will be enabled as soon as you have successfully checked your database connection.

20.1.6. SQL Logger Configuration

Step 1 - Add Channels

Drag-and-Drop the object property you want to record from the Object Tree into the Logger Channels list. The new channel is inserted in the line where you release the mouse button (see figure below).



To simplify the selection of an object property, you can filter the object tree according to various criteria.

Step 2 - Configure Sample Interval

You can set a different sample interval for each individual logger channel. The minimum sample time is 1 second. To configure the log interval double-click into the respective field within the column Interval (s) and enter the interval time.



Choose a log interval that is as large as possible and as small as necessary in order to minimize the amount of data that needs to be recorded and stored into the database.

Step 3 - Set Channel Label

In the column Label you can customize the description for each channel. You can use this column to add additional information, a meaningful name or a SI unit identifier.


To do this, double-click into the respective table cell within the column Label and enter the label text.


Upon choosing a new device property, a new channel description will be assigned automatically. That is, you should change the channel label only once the correct device property has been selected.

The object property and the label are separate columns in the SQL table

Deleting Channels

Highlight the desired channels using the mouse to delete one or more channels from the list, and then use either the Delete key or the Delecte Selection item of the right-click context menu:

delete_key delete_menu

To delete the entire channel list, use the context menu item Clear Logger.

20.1.7. Database Schema

The SQL logger uses the following database schema to store its data:


The schema consists of two tables. The first table is the process_data for storage of process data information. The following code is used to create this table:

   `name` varchar(64) NOT NULL,
   `label` varchar(64)

The name column stores the process data identifiers that are build from the device name and the selected property. The label column stores the value entered in the Label column of the Logger Channels table. The following picture shows the entered values in the Logger Channels table:


This configuration results in the following entries in the process_data table (screenshot from MySQL workbench):


Entries will never get removed from the process_data table. If an entry is missing, it will get added. Changing the label of a channel in the Logger Channels table, may result in a new entry in the process_data. The following example picture shows this:


The analog input Nemesys_S_1_AnIN1 property ActualValue (process data identifier Nemesys_S_1_AnIN1.ActualValue) was used to log different physical quantities in various experiments:

  • the temperature of a shaker unit in °C

  • the ozon concentration in %

  • and the value of a flowmeter in ml/s

This shows, that a change of the Label value results in different database entries.

The second table is the data_log which is used to store the actual values read from the device properties. This table is created with the following SQL code:

CREATE TABLE `data_log` (
   `log_datetime` DATETIME NOT NULL,
   `process_data_id` int NOT NULL,
   `value` double NULL,
   `value_str` TEXT
   FOREIGN KEY (process_data_id) REFERENCES `process_data` (`id`)

CREATE INDEX `idx_data_log_process_data_id` ON `data_log` (`process_data_id` ASC);

CREATE INDEX `idx_data_log_log_datetime` ON `data_log` (`log_datetime` ASC);

The code creates the following table layout:

  • log_datetime: stores the date and time when the value was logged

  • process_data_id: is a foreign key into the process_data table to identify the process data that has been logged

  • value: the actual logged value if it is a numeric or boolean value


    Numeric value


    Boolean value

  • value_str: a string of the logged value - this can be used to record non-numeric values such as text, data arrays, data structures or any other data types.


    Text value


    Data Array


    Data Structure



You can use SQL query language to get the logged data that you need. The following example SQL statement shows, how to get all logged values from the process data labeled with Flowmeter (ml/s):

SELECT b.log_datetime, a.name, a.label, b.value
FROM data_log AS b
INNER JOIN process_data as a ON (b.process_data_id=a.id)
WHERE a.label LIKE '%Flowmeter%'

This is the resulting table from the given SQL statement:


20.2. Script Functions

To automate the data logging or to synchronize data logging with other processes, the SQL database logger can be started and stopped using script functions. The corresponding functions can be found in the Logging category in the list of the available script functions.


20.2.1. Start SQL Logger


This function is used to start the SQL logger with the currently configured settings and channels.

20.2.2. Stop SQL Logger


This function stops logging into SQL database.

20.2.3. Trigger SQL Data Logging


This function triggers the immediate logging of all channels of the SQL logger. Normally the data will be logged with the configured interval. If you would like to force the immediate logging of all channels, for example if you would like to capture the current state of all channels if a certain event occurs, then you can use this function.

20.3. JavaScript Database Access

The Database Add-on provides some functionality, to access SQL databases from JavaScript code. If you enter the help() command in th JavaScript Console, you should see the database objects such as QSqlDatabase or QSqlQuery.


20.3.1. Example 1 - SQLite Database Query

The following example code shows, how to use the database objects in a JavaScript function to execute a SQL query for an SQLite database:

function main() {
   db = new QSqlDatabase();
   db.createConnection("QSQLITE", "JsScript");
   path = ScriptEnv.projectPath(ScriptEnv.LocationLog) + "/ProcessDataDbLog.sqlite";
   q = db.createQuery();
   result = q.exec("SELECT * FROM tbl_process_dat");
   if (!result) {
      throw new Error(q.lastError());
   while (q.next()) {

20.3.2. Example 2 - Create SQLite Database Schema

The following example shows, how to create the following database schema in an SQLite database using JavaScript code:

function createSchema() {
db = new QSqlDatabase();
if (!db.createConnection("QSQLITE", "JsConsole")) {
   throw new Error(db.lastError);
if (!db.open()) {
   throw new Error(db.lastError());
q = db.createQuery();
result = q.exec("CREATE TABLE IF NOT EXISTS substance ( " +
   "name TEXT, " +
   "description TEXT)");
if (!result) {
   throw new Error(q.lastError());

result = q.exec("CREATE TABLE IF NOT EXISTS method (" +
   "name TEXT)");
if (!result) {
   throw new Error(q.lastError());

result = q.exec("CREATE TABLE IF NOT EXISTS experiment (" +
   "substance_id INTEGER, " +
   "method_id INTEGER, " +
   "FOREIGN KEY (substance_id) REFERENCES substance (id), " +
   "FOREIGN KEY (method_id) REFERENCES method (id))");
if (!result) {
   throw new Error(q.lastError());

   result = q.exec("CREATE TABLE IF NOT EXISTS experiment_log (" +
   "log_datatime TIMESTAMP, " +
   "event TEXT, " +
   "experiment_id INTEGER, " +
   "FOREIGN KEY (experiment_id) REFERENCES experiment (id))");
if (!result) {
   throw new Error(q.lastError());

   result = q.exec("CREATE TABLE IF NOT EXISTS ms_measurement (" +
   "data_file_path TEXT, " +
   "FOREIGN KEY (id) REFERENCES experiment_log (id))");
if (!result) {
   throw new Error(q.lastError());

return "";

20.4. JavaScript API Reference

20.4.1. QSqlDatabase

class CScriptQSqlDatabase : public QObject

Wrapper for QSQLDatabase object to use it in JavaScript.

The following code shows, how to open a database connection in JavaScript and how to execute a simple query

db = new QSqlDatabase();
db.createConnection("QSQLITE", "JsScript");
q = db.executeQuery("SELECT * FROM tbl_process_data");
while (q.next()) {

Public Functions

bool createConnection(const QString &type, const QString &connectionName)
QObject *createQuery(const QString &query = QString())
QString databaseName() const
QStringList drivers() const
QObject *executeQuery(const QString &query)
QStringList fields(const QString &tablename) const
QString hostName() const
bool isOpen() const
bool isValid() const
QString lastError() const
bool open()
QString password() const
int port() const
QSqlDatabase qSqlDatabase()

Returns the internal database object.

void setDatabaseName(const QString &name)
void setHostName(const QString &host)
void setPassword(const QString &password)
void setPort(int port)
bool setUrl(const QString &Url)
void setUserName(const QString &name)
QStringList tables() const
QString url() const
QString userName() const
virtual ~CScriptQSqlDatabase()

20.4.2. QSqlQuery

class CScriptQSqlQuery : public QObject

Wrapper for QSqlQuery to use it in JavaScript.

There are several options how to execute a query. The first option is, to call the executeQuery() function of the database object:

q = db.executeQuery("SELECT * FROM tbl_process_data");

The second option is, to create the query via the database object and to execute it later:

q = db.createQuery("SELECT * FROM tbl_process_data");

or you create the query object and the pass the actual query to the executeQuery() function

q = db.createQuery();
q.exec("SELECT * FROM tbl_process_data");

If you want to insert data into the database, then you can build the complete query string with JavaScript or you can use the bindValues() function with placeholders like in the code below:

q = db.createQuery("INSERT INTO person (id, forename, surename) VALUES (?, ?, ?)");
q.bindValues([1001, "Bart", "Simpson"]);

Public Functions

void bindValues(const QVariantList &Values)
CScriptQSqlQuery(const QSqlQuery &Query)
bool exec(const QString &query = QString())
QString lastError() const
bool next()
bool prepare(const QString &query)
QVariantMap record() const
QVariantList recordValues() const
virtual ~CScriptQSqlQuery()