Database Module

The Database Module is the easiest, fastest and most convenient way to use SQL databases with Ignition.

The Database Module embeds a fast, pure Java SQL database engine called H2 in Ignition. In addition it provides a Database Editor project that is used to create/alter/drop database tables and view/query/edit data in database tables.

Upon installation the Database Module does the following things:

  1. Embeds the H2 database engine into Ignition.
  2. Automatically creates a default H2 datasource called "DefaultH2". This can be used immediately to create database-backed Ignition projects.
  3. If not already installed, installs the Database Editor project.

The Database Module runs where Ignition runs i.e. Windows, Linux, OS X etc.

If you encounter any issues or problems with the Database Module please contact support by emailing support@perfectabstractions.com.

The Database Module was created by and is maintained by Perfect Abstractions LLC.

H2 Database Engine

H2 is a very fast, easy to learn and use embedded SQL database. H2 is pure Java and runs in the Ignition Gateway. H2 works well with Ignition's built-in connection pooling functionality.

See H2's documentation about its features.

Datasources and Database Files

The DefaultH2 datasource stores the H2 database in the Ignition\data\db\userdbs directory.

Where an H2 database is stored is determined from the "Connect URL" field in the datasource configuration.

Any number of new H2 datasources can be made. Each one automatically creates a new database located at the place specified by the "Connect URL" field.

Screenshot

Database Compatibility Modes

H2 supports the ANSI SQL standard, and tries to be compatible with other databases. H2 also has compatibility modes to provide further compatibility to specific databases.

Compatibility modes include MySQL, Microsoft SQL Server, PostgreSQL, Oracle and DB2.

H2's database compatibility enables you to develop, test or start off using H2 for your applications and later switch to another database system if you want to.

See the H2 documentation for compatibility modes for more information.

The compatibility mode is set in the configuration of a datasource, in the Extra Connection Properties. Here is an image of what the configuration looks like:

Screenshot

MySQL Compatibility Mode

MySQL field names are case insensitive. In this mode H2 field names are case insensitive. In the Database Editor project all field and table names are lowercase.

More info.

Microsoft SQL Server Compatibility Mode

Table and field names can be quoted using square brackets, like this: [Test]. Text can be concatenated using '+'. Other features are supported.

More info.

Database Editor

The Database Editor project is a nice graphic user interface for creating/altering/deleting database tables and for viewing/querying/editing data in database tables. Since version 1.2 the Database Editor project supports H2 and its compatibility modes.

Older versions of the Database Editor (before version 1.2) do not support H2. If you have an existing Database Editor project you must remove or rename it before installing the Database Module. The Database Module will install the newer Database Editor project if a project with the same name does not already exist.

On the Table Editor screen each line in the query box is executed as an independent query. This is different than a MySQL datasource where all the lines in the query box are executed as one single query.

Click on the following image to see the Table Editor screen in full size.

Screenshot

See the documentation for the Database Editor project.

More Information

More information and videos can be found from the Database Module product webpage.