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 modification. 

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. Only new Ignition installations will always use the JDBC drivers that are included with the installer. 

Running the installer to upgrade a preexisting Ignition installation will not modify the installed JDBC drivers, even if newer drivers are included in the installer. 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.

On this page ...

Common Third Party Drivers

Commonly, you will have to download the official JAR file from the creator's website. We have a few links here to make it easy to find.

DatabaseLinkNotes
IBM DB2

https://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

MSSQL

https://github.com/microsoft/mssql-jdbc/releases

You only need the .jar file, so you can select just the mssql-jdbc-X.X.XX.jre11.jar file. The Xs will be replaced with the version numbers.

Ensure you download the jre11 version of the JAR. See the SQL Server docs for more details. 

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.

Bundled JDBC Drivers and Ignition Upgrades

Ignition installers come with the latest version of some JDBC drivers. During the installation process, the installer will use these drivers. However, during upgrade, the installer will not replace existing drivers with those in the installer. This is to preserve your existing connections, since newer drivers may not work with older database installations. 

The following feature is new in Ignition version 8.1.8
Click here to check out the other new features
As of 8.1.8, upgrading Ignition will create a directory at installDirectory/user-lib/jdbc-bundled and place updated JDBC drivers in the directory, allowing you to manually update your system's JDBC drivers at a later time without having obtain more recent drivers yourself. 


Database Translator and JDBC Driver Settings

In some cases, you may need to add your own JDBC Driver, or configure a Translator. However, you may need to check the JDBC driver's documentation for information on how to configure them. Below are some recommended settings, but the vendor's documentation should always supersede any suggestions here. 

IBM Translator and JDBC Driver Settings

Main Properties

Name

<Name of the Translator. The JDBC driver will reference the settings below by the name specified here>

Create Table Syntax

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

Create Sequence Syntax

<leave empty>

Create Trigger Syntax

<leave empty>

Create Index Syntax

CREATE INDEX {indexname} ON {tablename}({columnname})

Auto Increment Field Definition

{type} GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Alter Table Syntax

ALTER TABLE {tablename} {alterdef}

Add Column Syntax

ADD COLUMN {columnname} {type}

Primary Key Syntax

<leave empty>

Limit Syntax

FETCH FIRST {limit} ROWS ONLY

Limit Position

Back

Current Timestamp Query

values current timestamp

Column Quote Character

"

Supports Returning Auto-generated Keys?

False

Fetch Key Query

SELECT max({columnname}) FROM {tablename}

Table List Filter

<leave empty>

Data Type Mapping

Byte (I1)

int

Short (I2)

int

Integer (I4)

int

Long (I8)

bigint

Boolean

int

Datetime

timestamp

Float (R4)

float

Double (R8)

double

String

varchar(255)

Binary

blob

Long Text

clob(65536)

Main Properties

Name

IBM DB2

Description

The official IBM DB2 JDBC Driver.

Classname

com.ibm.db2.jcc.DB2Driver

JAR File(s)

<Click on the Choose File button to select and upload the JAR(s). This is the part where you upload the JDBC driver.>

Driver Defaults & Instructions

Driver Type

DB2

URL Format

jdbc:db2://localhost:50000/SAMPLE       OR
jdbc:db2://localhost:25000/SAMPLE

URL Instructions

<br/>The format of the DB2 connect URL is:<br><code>db2://<b>host</b>:<b>port</b>/<b>database</b></code><br>With the three parameters (in bold) <ul style="list-style-type:none;margin-left:10px;"><li><b>host</b>: The host name or IP address of the database server.</li><li><b>port</b>: The port that the database server is running on. DB2 default port is <b>50000</b>.</li><li><b>database</b>: The name of the logical database that you are connecting to on the DB2 server.</li></ul>

Default Connection Properties

<Leave Blank>

Connection Property Instructions

<Leave Blank>

Default Validation Query

select 1 from sysibm.sysdummy1

SQL Language Compatibility

Default Translator

<The Translator this driver should use> Default: IBM DB2

MySQL Translator and JDBC Driver Settings

Main Properties
NameMySQL
Create Table SyntaxCREATE TABLE {tablename} ({creationdef}{primarykeydef})
Create Sequence Syntax<leave empty>
Create Trigger Syntax<leave empty>
Create Index SyntaxCREATE INDEX {indexname} ON {tablename}({columnname})
Auto Increment Field Definition{type} NOT NULL AUTO_INCREMENT
Alter Table SyntaxALTER TABLE {tablename} {alterdef}
Add Column SyntaxADD COLUMN {columnname} {type}
Primary Key SyntaxPRIMARY KEY ({columnname})
Limit SyntaxLIMIT {limit}
Limit PositionBack
Current Timestamp QuerySELECT CURRENT_TIMESTAMP
Column Quote Character`
Supports Returning Auto-generated Keys?True
Fetch Key Query<leave empty>
Table List Filter<leave empty>
Data Type Mapping
Byte (I1)int
Short (I2)int
Integer (I4)int
Long (I8)bigint
Booleanint
Datetimedatetime
Float (R4)float(10)
Double (R8)double
Stringvarchar(255)
Binaryvarbinary
Long Texttext
Main Properties
NameMySQL
DescriptionThe official MySQL JDBC Driver, Connector/J.
Classnamecom.mysql.cj.jdbc.Driver
JAR File(s)<Click on the Choose File button to select and upload the JAR(s). This is the part where you upload the JDBC driver.>
Driver Defaults & Instructions
Driver TypeMySQL
URL Formatjdbc:mysql://localhost:3306/test
URL Instructions<br/>The format of the MySQL connect URL is:<br><code>jdbc:mysql://<b>host</b>:<b>port</b>/<b>database</b></code><br>With the three parameters (in bold) <ul style="list-style-type:none;margin-left:10px;"><li><b>host</b>: The host name or IP address of the database server.</li><li><b>port</b>: The port that the database server is running on. MySQL default port is <b>3306</b>.</li><li><b>database</b>: The name of the logical database that you are connecting to on the MySQL server.</li></ul>
Default Connection PropertieszeroDateTimeBehavior=CONVERT_TO_NULL;connectTimeout=120000;socketTimeout=120000;useSSL=false;allowPublicKeyRetrieval=true;
Connection Properties InstructionsThere is an extensive list of extra connection properties available for MySQL Connector/J. See <a href='the'>http://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html'>the documentation</a> for a table describing all connection properties.<br>A default <tt>serverTimezone</tt> value (taken from the Gateway) will be appended to the connection string if one is not specified.
Default Validation QuerySELECT 1
SQL Language Compatibility
Default TranslatorMySQL

MSSQL Translator and JDBC Driver Settings

Main Properties
Name<Name of the Translator. The JDBC driver will reference the settings below by the name specified here>
Create Table SyntaxCREATE TABLE {tablename} ({creationdef}{primarykeydef})
Create Sequence Syntax<Blank>
Create Trigger Syntax<Blank>
Create Index SyntaxCREATE INDEX {indexname} ON {tablename}({columnname})
Auto Increment Field Definition{type} IDENTITY(1,1)
Alter Table SyntaxALTER TABLE {tablename} ADD {alterdef}
Add Column Syntax{columnname} {type}
Primary Key SyntaxPRIMARY KEY CLUSTERED ({columnname})
Limit SyntaxTOP {limit}
Limit PositionFront
Current Timestamp QuerySELECT CURRENT_TIMESTAMP
Column Quote Character"
Supports Returning Auto-generated Keys?True
Fetch Key Query<Blank>
Table List Filter<Blank>
Data Type Mapping
Byte (I1)int
Short (I2)int
Integer (I4)int
Long (I8)bigint
Booleanint
Datetimedatetime
Float (R4)float(10)
Double (R8)double precision
Stringvarchar(255)
Binaryvarbinary
Long Textnvarchar(max)
Main Properties
Name<Name of the driver, as you would like it to appear on the Gateway>
Description<Enter a useful description you would like to see next to the driver>
Classnamecom.microsoft.sqlserver.jdbc.SQLServerDriver
JAR File(s)<Click on the Choose File button to select and upload the JAR(s). This is the part where you upload the JDBC driver. >
Driver Defaults & Instructions
Driver TypeMicrosoft SQL Server
URL Formatjdbc:sqlserver://localhost\SQLEXPRESS
URL Instructions<br/>The format of the SQL Server connect URL is:<br/><code>jdbc:sqlserver://<b>host</b>\<b>instanceName</b>[:<b>port</b>]</code><br/>With the three parameters (in bold) <ul style="list-style-type:none;margin-left:10px;"><li><b>host</b>: The host name or IP address of the database server.</li><li><b>instanceName</b>: (optional) the instance to connect to on the host. If not specified, a connection to the default instance is made.</li><li><b>port</b>: (optional) the port to connect to. The default is <b>1433</b>. If you are using the default, you can omit the port and the preceding ':'.</li></ul><br/>For SQL Server, you specify the <i>database name</i> to connect to using the <code>databaseName</code> property in the <i>Extra Connection Properties</i>.
Default Connection PropertiesdatabaseName=test
Connection Properties InstructionsUse <i>databaseName=YOUR_DATABASE</i> to specify the database to connect to.
Default Validation QuerySELECT 1
SQL Language Compatibility
Default TranslatorThe Translator this driver should use. If you're adding a new Driver, then you may

Oracle Express Translator and JDBC Driver Settings

Main Properties
Name<Name of the Translator. The JDBC driver will reference the settings below by the name specified here>
Create Table SyntaxCREATE TABLE {tablename} ({creationdef}{primarykeydef})
Create Sequence SyntaxCREATE SEQUENCE {tablename}seq START WITH 1 INCREMENT BY 1
Create Trigger SyntaxCREATE TRIGGER {tablename}trig BEFORE INSERT ON {tablename} REFERENCING NEW AS NEW FOR EACH ROW BEGIN select {tablename}seq.nextval INTO :NEW.{columnname} FROM dual; END;
Create Index SyntaxCREATE INDEX {indexname} ON {tablename}({columnname})
Auto Increment Field Definition{type} NOT NULL
Alter Table SyntaxALTER TABLE {tablename} ADD ({alterdef})
Add Column Syntax{columnname} {type}
Primary Key SyntaxPRIMARY KEY ({columnname})
Limit Syntaxrownum<={limit}
Limit PositionWhere
Current Timestamp QuerySELECT CURRENT_TIMESTAMP FROM DUAL
Column Quote Character"
Supports Returning Auto-generated Keys?False
Fetch Key QuerySELECT {tablename}SEQ.CURRVAL FROM DUAL
Table List Filter<leave empty>
Data Type Mapping
Byte (I1)int
Short (I2)int
Integer (I4)int
Long (I8)int
Booleanint
Datetimetimestamp
Float (R4)float
Double (R8)double precision
Stringvarchar2(255)
Binaryvarbinary
Long Textnclob
Main Properties
Name<Name of the driver, as you would like it to appear on the Gateway>
Description<Enter a useful description you would like to see next to the driver>
Classnameoracle.jdbc.driver.OracleDriver
JAR File(s)<Click on the Choose File button to select and upload the JAR(s). This is the part where you upload the JDBC driver. >
Driver Defaults & Instructions
Driver TypeOracle
URL Formatjdbc:oracle:thin:@localhost:1521:test
URL Instructions<br/>The format of the Oracle connect URL is:<br/><code>jdbc:oracle:thin:@<b>host</b>:<b>port</b>:<b>SID</b></code><br/>With the three parameters (in bold) <ul style="list-style-type:none;margin-left:10px;"><li><b>host</b>: The host name or IP address of the database server.</li><li><b>port</b>: The port that the database server is running on. Oracle's default port is <b>1521</b>.</li><li><b>SID</b>: the system ID that identifies the database to connect to.</li></ul>
Default Connection Properties<Blank>
Connection Properties Instructions<Blank>
Default Validation QuerySELECT 1 FROM DUAL
SQL Language Compatibility
Default TranslatorThe Translator this driver should use. If you're adding a new Driver, then you may

PostgreSQL Translator and JDBC Driver Settings

Main Properties
Name<Name of the Translator. The JDBC driver will reference the settings below by the name specified here>
Create Table SyntaxCREATE TABLE {tablename} ({creationdef}{primarykeydef})
Create Sequence Syntax<Blank>
Create Trigger Syntax<Blank>
Create Index SyntaxCREATE INDEX {indexname} ON {tablename}({columnname})
Auto Increment Field DefinitionSERIAL NOT NULL
Alter Table SyntaxALTER TABLE {tablename} {alterdef}
Add Column SyntaxADD COLUMN {columnname} {type}
Primary Key SyntaxPRIMARY KEY ({columnname})
Limit SyntaxLIMIT {limit}
Limit PositionBack
Current Timestamp QuerySELECT CURRENT_TIMESTAMP
Column Quote Character"
Supports Returning Auto-generated Keys?True
Fetch Key Query<Blank>
Table List Filter<Blank>
Data Type Mapping
Byte (I1)int
Short (I2)int
Integer (I4)int
Long (I8)bigint
Booleanint
Datetimetimestamp
Float (R4)float
Double (R8)double precision
Stringvarchar(255)
Binarybytea
Long Texttext
Main Properties
Name<Name of the driver, as you would like it to appear on the Gateway>
Description<Enter a useful description you would like to see next to the driver>
Classnameorg.postgresql.Driver
JAR File(s)<Click on the Choose File button to select and upload the JAR(s). This is the part where you upload the JDBC driver. >
Driver Defaults & Instructions
Driver TypePostgreSQL
URL Formatjdbc:postgresql://localhost:5432/test
URL Instructions<br/>The format of the PostgreSQL connect URL is:<br/><code>jdbc:postgresql://<b>host</b>:<b>port</b>/<b>database</b></code><br/>With the three parameters (in bold) <ul style="list-style-type:none;margin-left:10px;"><li><b>host</b>: The host name or IP address of the database server.</li><li><b>port</b>: The port that the database server is running on. PostgreSQL default port is <b>5432</b>.</li><li><b>database</b>: The name of the logical database that you are connecting to on the PostgreSQL server.</li></ul>
Default Connection Properties<Blank>
Connection Properties InstructionsNo extra connection parameters are recommended for PostgreSQL. For possible parameter values, see the documentation at <a href='the'>http://jdbc.postgresql.org'>the PostgreSQL JDBC driver website</a>.
Default Validation QuerySELECT 1
SQL Language Compatibility
Default TranslatorThe Translator this driver should use. If you're adding a new Driver, then you may


Add a New JDBC Driver

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

  1. On the Gateway Webpage Config section, click on Databases > Drivers.



    The Database Drivers & Settings page is displayed.

  2. Click on the Create new JDBC Driver... link at the bottom of the page. 

    Note: Ignition comes preconfigured with some popular JDBC drivers already. In some cases you may only need to provide a JAR file to an existing driver configuration, instead of creating a new driver configuration. See the Common Third Party Drivers table for a list of preconfigured drivers that need a JAR file. 

  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 Defaults and 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
    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 Compatibility

    Default TranslatorThe 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.


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 Config 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.
    1. Click the Choose File button
    2. 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 the Gateway Config section, 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.

    The possible tokens are as follows:

    Token

    Description

    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

    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.

    Date Type Mapping

    All data typesThe 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.


In This Section ...


  • No labels