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.
Database | Link | Notes |
---|
MySQL | https://dev.mysql.com/downloads/connector/j/ | - Select Your Operating system (or Platform Independent if you are on Windows).
- After the file has been downloaded, unzip the the archive. On Windows you can right-click and select the Extract All option.
- 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. |
Oracle | https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html | - You will need to create an Oracle account to download the JAR files.
- You can select the Unzipped version of the newest JAR.
- 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.
MySQL Translator and JDBC Driver Settings
MySQL connections can often use the included MariaDB translator, but we recommend using a separate MySQL 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.
Default MySQL Translator Settings
Main Properties |
---|
Name | MySQL |
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} NOT NULL AUTO_INCREMENT |
Alter Table Syntax | ALTER TABLE {tablename} {alterdef} |
Add Column Syntax | ADD COLUMN {columnname} {type} |
Primary Key Syntax | PRIMARY KEY ({columnname}) |
Limit Syntax | LIMIT {limit} |
Limit Position | Back |
Current Timestamp Query | SELECT 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 |
Boolean | int |
Datetime | datetime |
Float (R4) | float(10) |
Double (R8) | double |
String | varchar(255) |
Binary | varbinary |
Long Text | text |
Default JDBC Driver Settings
Main Properties |
---|
Name | MySQL |
Description | The official MySQL JDBC Driver, Connector/J. |
Classname | com.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 Type | MySQL |
URL Format | jdbc: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 Properties | zeroDateTimeBehavior=CONVERT_TO_NULL;connectTimeout=120000;socketTimeout=120000;useSSL=false;allowPublicKeyRetrieval=true; |
Connection Properties Instructions | There 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 Query | SELECT 1 |
SQL Language Compatibility |
---|
Default Translator | MySQL |