Skip to main content
Version: 8.3 Beta 🚧

JDBC Drivers and Translators

In most cases, the default JDBC drivers and Translator settings in Ignition will not need to be modified. However, there are cases where existing drivers or translators may need modification. Additionally, you may need to install new drivers to connect to your desired database.

More information on the default JDBC Driver Modules included with the Ignition installation can be found on here.

JDBC Drivers and Ignition Upgrades​

When upgrading Ignition, manually created 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. In addition, restoring a Gateway backup from an older version will replace any new versions of the drivers with the versions from the backup. In this case, you will need to manually update the JDBC drivers.

Refer to the JDBC Driver Modules page for the upgrade process for the drivers shipped with Ignition.

Common Third-Party Drivers​

If you need to install a new driver, you will most likely have to download the official JAR file from the creator's website. The table below describes this process for common third-party drivers. Note that MySQL and MSSQL will be both be available already upon install, so you'll only need to follow the steps for these types if you need to override the existing driver files.

DatabaseLinkNotes
IBM DB2https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads
  1. Select the GA version of your desired driver.
  2. Select your desired package.
  3. Log in to your IBM account and complete your package download.
MySQLhttps://dev.mysql.com/downloads/connector/j/
  1. Select Your Operating system (or Platform Independent if you are on Windows).
  2. After the file has been downloaded, unzip the the archive. On Windows you can right-click and select the Extract All option.
  3. The location of the JAR we need should be in the extracted folder under mysql-connector-java-X.X.XX where the Xs are the version number. You are looking for a file that is named like mysql-connector-java-X.X.XX.jar.

Compatibility with different JRE versions can be found in the MySQL connector docs.
MSSQLhttps://github.com/microsoft/mssql-jdbc/releases

You only need the .jar file, so you can select just the mssql-jdbc-X.X.XX.jreX.jar file. The Xs will be replaced with the version numbers of both the jdbc driver and jre used.

Check the SQL Server docs for more details which jdbc drivers contain jre versions compatible with your Ignition server's embedded Java version.
Oraclehttps://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html
  1. You will need to create an Oracle account to download the JAR files.
  2. You can select the Unzipped version of the newest JAR.
  3. You are looking for a file that is named like odbcX.jar. Where X is the version number.

Database Translator and JDBC Driver Settings​

When adding your own JDBC driver or configuring a Translator, be sure to check the JDBC driver's documentation for information on how to configure them. The JDBC Driver and Database Translator Settings page contains some recommended settings, but the vendor's documentation should always supersede any suggestions here.

Add a New JDBC Driver​

Ignition comes preconfigured with select JDBC drivers already. In some cases you may only need to provide a JAR file by editing an existing driver configuration, instead of creating a new driver configuration. To add a new JDBC driver to Ignition, complete the following steps:

  1. On the Gateway Webpage Connections section, click on Databases > Settings.

    " "

    The Database Settings page is displayed.

  2. On the Drivers tab, click on Create Driver + at the top right of the JDBC Drivers list.

    " "

  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 JAR file that contains the driver, as well as any other required JARs. If you do not have the JAR file needed, see above for download links.

  5. Use the default settings for the following properties:

    Driver and Defaults Instructions

    Driver TypeIs the brand of database. This is used for optimizations in the Gateway, if in doubt, select GENERIC.
    URL FormatIs 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 InstructionsFree form instructions that are shown to help the user to create a connection.
    Default Connection PropertiesAny additional properties to add by default to the connection string.
    Connection Properties InstructionsTips about which connection properties might be useful.
    Default Validation QueryThe default query that is used to verify that the connection is available.

    SQL Language Compatibility

    Default TranslatorThe database translator that is used by default for connections from this driver.

  6. Click Create JDBC Driver located at the bottom of the form to create the new driver.

Upgrade 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 Connections section, click on Databases > Settings.

  3. The Database Settings page will open and display the currently configured JDBC drivers. Click the three dots menu icon and select Edit for the driver you need to upgrade. Note that if the driver name needs updating, you must select the Rename option.

  4. You will need to pass in the new driver to the JAR File(s) section.

    1. Click the Choose File button
    2. Navigate to the driver, and click Open.

  5. [Optional]: Update any other properties. In most cases, you can skip this step. However, you may need to update some 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.

  6. Click Save Changes at the bottom of the Edit panel.

Database Translators​

Despite the presence of a SQL standard, many database systems 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 applicable. 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 Connections > Databases > Settings page on the 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, complete the following steps:

  1. In the Gateway Connections section, click on Databases > Settings. The Database Settings page is displayed.

  2. Go to the Translators tab and click Create Translator +. The Create Translator form pops up to show all of the translator properties.

  3. Define the tokens used with the translator properties on the Create Translator form.

    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.

    The possible tokens are as follows:

    TokenDescription
    tablenameThe name of the table being created.
    indexnameThe name of the index to create, when adding a column index to the table.
    primarykeydefA clause that defines a primary key for a new table.
    creationdefThe list of columns to create in the table.
    alterdefA list of columns to add/remove/modify in the table.
    columnnameThe name of a column.
    typeThe data type of a column.
    limitThe value of the limit clause.

    Other Properties​

    TokenDescription
    Limit PositionDefines 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 CharacterAll 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 QueryIndicates 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.

    Date Type Mapping​

    TokenDescription
    All data typesThe keywords that are used when creating columns of the given types.

  4. Click Create Translator located at the bottom of the form to create the translator.

Last updated on by ia-nboehnke