Connecting to Databases

Connect Once

Many of the advanced features of Ignition, such as the Transaction Groups and Tags Historian require a connection to an external database and most databases require special permissions for each computer that wants to connect. Fortunately, Ignition takes care of all of this for us. You can create a connection to your database once and every system in Ignition will use that central connection. There's no need to worry about updating your database settings to add another client.

This central database connection also makes it easy to swap between databases or schemas. You can tell every query to use the default connection, then just change the default to update everything. Alternatively, you can force specific queries or systems to use a particular connection. Create as many database connections as you want and start designing using all of them.

Add a Database Connection

Now that we've installed your database, let's connect to it. You can find detailed descriptions for many database connections in this User Manual, however, they all include the same steps:

1. On the Gateway Webpage, go to the Gateway Config tab. Scroll down to the Databases > Connections section.

   

2. On at the Database Connections page, click on the Create new Database Connection... link at the bottom of the table.

   

3. The next step is to choose a JDBC Driver. Ignition ships with drivers for Microsoft SQL Server, MySQL, Oracle, and PostgreSQL. Pick the JDBC driver for your database, and click on the Next button.
   

   note

   Ignition connects to databases using JDBC drivers that are unique to each database. If a suitable driver is not available in the list, you need to add a new JDBC driver for other databases, like IBM DB2, see Adding a JDBC Driver.

4. After selecting the driver, you'll configure the settings for the connection. Some settings, such as the Connect URL are specific to the driver that you're using.

Main Database Connection Properties

Property	Description
Name	Each database connection needs a unique name, which consists of letters, numbers and underscores.
Description	A brief description of the database.
JDBC Driver	The JDBC driver dictates the type of database that this connection can connect to. It cannot be changed once created.
Connect URL	A string that instructs the driver how to connect to the database. This string is the server address, and may include the port, instance name, database name, and so on. The format and parameters depend on the driver being used.
Username	The username to use when connecting. Some databases support other authentication methods, such as Windows authentication, in which case this field is not used.
Change Password?	Check the box to change the existing password.
Password	Enter password.
Password	Re-type password for verification.
Extra Connection Properties	Depending on which database you are connecting to, there will be different default values placed in this box. MS SQL Server requires you to place your database name here, but for other databases you can usually leave this at its default values. Each database has its own set of available extra connection properties so you must refer to your Database documentation to determine what is valid here.
Enabled	Lets you to enable or disable a database connection.
Failover Datasource	The connection that is automatically used when this connection is not available.
Failover Mode	Lets you select how to handle the database connection failing and recovering. Database connections support failover. This means that the objects which use a database connection will use a different connection if the one they are using becomes unavailable. The Failover Datasource property determines which connection is used, and the Failover Mode determines when, if ever, the connection is switch back to the primary connection. There are two failover modes: STANDARD mode means that this datasource will fail over when a connection cannot be retrieved, but when connectivity is restored, connections will again come from this datasource. STICKY mode means that once this datasource fails over, connections will continue coming from the failover datasource until the failover datasource itself fails or the Gateway is restarted.
Slow Query Log Threshold	Queries that take longer than this amount of time, in milliseconds, will be logged. This helps to find queries that are not performing well. (default: 60,000)
Validation Timeout	The time in milliseconds between database validation checks. (default: 10,000)

Advanced Settings

There are many advanced settings, described below, that you don't need to change under normal circumstances.

SQL Compatibility

Property	Description
Translator	The SQL translator used to negotiate variances in syntax.
Include Schema in Table Name	When true, the schema name will be included in listing tables to create a fully-qualified name. Default is false.

Connection Pooling

Property	Description
Intial Size	The initial number of connections that are created when the pool is started. Default value is 0.
Max Active	The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit. Default value is 8.
Max Idle	The maximum number of connections that can remain idle in the pool without extra ones being released, or negative for no limit. Default value is 8.
Min Idle	The minimum number of connections that can remain idle in the pool without extra ones being created, or zero to create none. Default value is 0.
Max Wait	The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely. Default value is 5,000.

Connection Testing

Property	Description
Validation Query	A SQL query that will be used to validate connections from the pool. This query MUST be an SQL SELECT statement that returns at least one row. Default value is SELECT 1.
Test on Borrow?	When true, connections will be validated before being borrowed from the pool. If the connection fails to validate, it will be dropped from the pool, and we will attempt to borrow another. Default value is true.
Test on Return?	When true, connections will be validated before being returned to the pool. Default value is false.
Test While Idle?	When true, connections will be validated by the idle connection evictor. If a connection fails to validate, it will be dropped from the pool. Default value is false.
Eviction Rate	The number of milliseconds to sleep between runs of the idle connection evictor thread. When non-positive, no idle connection evictor thread will be run. Default value is -1.
Eviction Tests	The number of connections to examine during each run of the idle object evictor thread (if any). Default value is 3.
Eviction Time	The minimum amount of time in milliseconds a connection may sit idle in the pool before it is eligible for eviction by the idle object evictor (if any). Default value is 1,800,000.

Connection Initialization

Property	Description
Connection Initialization Commands	A set of commands (one per line) that will be executed each time a connection is pulled from the pool. Can be used to reset environment variables.
Default Transaction Isolation Level	The isolation to use for each connection.