JDBC Drivers and Translators

Overview

In most cases, the default JDBC drivers and Translator settings in Ignition will not need to be modified. However, there are cases where drivers or translators may need to be modified.

JDBC Drivers and Ignition Upgrades

When upgrading Ignition, JDBC drivers are not modified during the upgrade process. This is to prevent database connection issues on upgrade: you should only need to upgrade your JDBC drivers when the database is updated, not when Ignition is upgraded.

New Ignition installations will always use the JDBC drivers that are included with the installer.

Thus running the installer/upgrader on a preexisting Ignition installation will not modify any JDBC drivers, even if newer drivers are included in the installer. This means that if a newer version of an Ignition installer contains a newer JDBC driver, the newer driver will not be used. In this case, you will need to manually update the JDBC drivers.

Adding a JDBC Driver

To add a new JDBC driver to Ignition, do the following steps:

1. In Gateway on the Configure section, click on Databases > Drivers.
   The Database Drivers & Settings page is displayed.
2. Find the blue arrow and click on the Create new JDBC Driver... link.
3. In the Name field, type the full name of the JDBC driver, see the manufacturer's documentation to get the name.
4. In the JAR File(s) field, specify the core Java JAR file that contains the driver, as well as any other required JARs.
5. Use the default settings for the following properties:

Driver Defaults and Instructions

Setting	Description
Driver Type	Is the brand of database. This is used for optimizations in the Gateway, if in doubt, select GENERIC.
URL Format	Is a default value for the connect URL. This provides a hint to the format of the connect URL that this driver requires while adding a datasource connection. For example, the hint for the format can be, jdbc:dbtype://host:port/database
URL Instructions	Free form instructions that are shown to help the user to create a connection.
Default Connection Properties	Any additional properties to add by default to the connection string.
Connection Properties Instructions	Tips about which connection properties might be useful.
Default Validation Query	The default query that is used to verify that the connection is available.

SQL Language Comaptibility

Setting	Description
Default Translator	The database translator that is used by default for connections from this driver.

6. Click the Create New JDBC Driver button, located at the very bottom of the page, to create the new driver.

Upgrading a JDBC Driver

In some cases you may need to upgrade a driver. The steps below detail where this would take place

1. You will need to obtain the new driver. These are typically provided by the same organization that made the database. The driver will be a JAR file.

2. Once you have the new driver, head to your Ignition gateway's Configure section, click on Databases > Drivers.

3. The Database Drivers & Settings page will be displayed. These are the currently configured JDBC drivers on the gateway, and can be modified from the Edit button. Click the Edit button for the driver you need to upgrade.

4. You will need to pass in the new driver to the JAR File(s) property. Click the Choose File button, navigate to the driver, and click Open.

5. [Optional] Update any other properties. In most cases, you may skip this step. However you may need to update some other properties when a new driver is in place. This step depends on the driver, and what it changes. Refer to the driver's documentation to determine if any connection properties need to be changed. For example, users upgrading to MySQL 8.0 from legacy versions will need to change the Default Connection Properties value from zeroDateTimeBehavior=convertToNull; to zeroDateTimeBehavior=CONVERT_TO_NULL;useSSL=false;allowPublicKeyRetrieval=true;

   Again, this step depends on the driver, and in some cases you may be able to skip it.

6. Click the Save Changes button at the bottom of the page.

Database Translators

Despite the presence of a SQL standard, many database system vary in how they implement or accomplish various tasks. The JDBC driver system tries to hide these differences as much as possible, but unfortunately some differences persist.

The database translator system in Ignition navigates these differences as they apply to the system. It provides a way to define certain key operations that are commonly different between database vendors, such as creating auto-incrementing index columns, and the keywords used for different data types.

Translator Management

Database translators are managed in the Gateway from the Databases > Drivers > Translators tab. Ignition comes pre-configured with translators for the major supported databases, but you can edit and remove them, as well as create new translators. It is necessary to create a new translator only when adding a new JDBC driver for a database that does not share syntax with any of the existing translators.

Creating a New Translator

To add a new database translator to Ignition, do the following steps:

1. In Gateway on the Configure page, click on Databases > Drivers.
   The Database Drivers & Settings page is displayed.
2. Go to the Translators tab, find the blue arrow, and click on the Create new Database Translator... link.
   The New Database Translator page is displayed showing a list of all the translator properties.
3. Define the tokens used with the translator properties on the New Database Translator page.
   For most of the properties, you need to define special token markers to indicate places where other values are placed. For example, the default Create Table Syntax entry looks as follows:

CREATE TABLE {tablename} ({creationdef}{primarykeydef})

Where tablename, creationdef, and primarykeydef are all tokens that are expanded. tablename is replaced directly with the table, creationdef is a list of columns, and primarykeydef is the phrase created by the Primary Key Syntax entry in the translator.

Possible Tokens

Token	Description
tablename	The name of the table being created.
indexname	The name of the index to create, when adding a column index to the table.
primarykeydef	A clause that defines a primary key for a new table.
creationdef	The list of columns to create in the table.
alterdef	A list of columns to add/remove/modify in the table.
columnname	The name of a column.
type	The data type of a column.
limit	The value of the limit clause.

Other Properties

Property	Description
Limit Position	Defines where the limit clause should be placed. Back, the limit is placed at the end of the query. Front, places it directly after the SELECT keyword.
Column Quote Character	All columns are created and accessed with the defined quote, which tells the database to use a specific casing, as well as avoiding collisions between the column name and database keywords.
Supports Returning Auto-generated Keys? / Fetch Key Query	Indicates whether the JDBC driver supports the return of generated keys. If the driver does not support this feature, the Fetch Key Query is used to retrieve the last key.

Data Type Mapping

Type	Description
All data types	The keywords that are used when creating columns of the given types.

4. Click the Create New Database Translator button, located at the very bottom of the page, to create the translator.