---
title: "JDBC Driver and Database Translator Settings"
description: "This page details various JDBC driver and translator settings."
---

# JDBC Driver and Database Translator Settings

> This page details various JDBC driver and translator settings.

This page details various JDBC driver and translator settings. See the [JDBC Drivers and Translators page](platform\database-connections\connecting-to-databases\jdbc-drivers-and-translators\jdbc-drivers-and-translators.md#add-a-new-jdbc-driver) for information on adding and configuring JDBC drivers and translators.

## IBM
### IBM Default JDBC Driver Settings

#### Main Properties

|Property|Value|
|---|---|
|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

|Property|Value|
|---|---|
|Driver Type|DB2|
|URL Format|jdbc:db2://localhost:50000/SAMPLEORjdbc:db2://localhost:25000/SAMPLE|
|URL Instructions|The format of the DB2 connect URL is:db2://**host**:**port**/**database**With the three parameters (in bold)**host**: The host name or IP address of the database server.**port**: The port that the database server is running on. DB2 default port is **50000**.**database**: The name of the logical database that you are connecting to on the DB2 server.|
|Default Connection Properties|Blank|
|Connection Property Instructions|Blank|
|Default Validation Query|select 1 from sysibm.sysdummy1|

#### SQL Language Compatibility

|Property|Value|
|---|---|
|Default Translator|The Translator this driver should use. Default: IBM DB2|

### IBM Default Translator Settings

#### Main Properties

|Property|Value|
|---|---|
|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|Blank|
|Create Trigger Syntax|Blank|
|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|Blank|
|Limit Syntax|`FETCH FIRST {limit} ROWS ONLY`|
|Limit Position|Back|
|Current Timestamp Query| The value's current timestamp.|
|Column Quote Character|"|
|Supports Returning Auto-generated Keys?|False|
|Fetch Key Query|`SELECT max({columnname}) FROM {tablename}`|
|Table List Filter|Blank|

#### Data Type Mapping

|Name|Type|
|---|---|
|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)|

## MySQL

### MySQL Default JDBC Driver Settings

#### Main Properties

|Property|Value|
|---|---|
|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

|Property|Value|
|---|---|
|Driver Type|MySQL|
|URL Format|jdbc:mysql://localhost:3306/test|
|URL Instructions|The format of the MySQL connect URL is:jdbc:mysql://**host**:**port**/**database**With the three parameters (in bold) **host**: The host name or IP address of the database server.**port**: The port that the database server is running on. MySQL default port is **3306**.**database**: The name of the logical database that you are connecting to on the MySQL server.|
|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 [the documentation](https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties/) for a table describing all connection properties.A default `serverTimezone` 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

|Property|Value|
|---|---|
|Default Translator|MySQL|

### MySQL Default Translator Settings

#### Main Properties

|Property|Value|
|---|---|
|Name|MySQL|
|Create Table Syntax|`CREATE TABLE {tablename} ({creationdef}{primarykeydef})`|
|Create Sequence Syntax|Blank|
|Create Trigger Syntax|Blank|
|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|Blank|
|Table List Filter|Blank|

#### Data Type Mapping

|Name|Type|
|---|---|
|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|

## MSSQL

### MSSQL Default JDBC Driver Settings

#### Main Properties

|Property|Value|
|---|---|
|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.|
|Classname|com.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

|Property|Value|
|---|---|
|Driver Type|Microsoft SQL Server|
|URL Format|jdbc:sqlserver://localhost\SQLEXPRESS|
|URL Instructions|The format of the SQL Server connect URL is:jdbc:sqlserver:// **host** \ **instanceName** [:**port**] With the three parameters (in bold) **host**: The host name or IP address of the database server.**instanceName**: (optional) the instance to connect to on the host. If not specified, a connection to the default instance is made.**port**: (optional) the port to connect to. The default is **1433**. If you are using the default, you can omit the port and the preceding ':'.  For SQL Server, you specify the **database name** to connect to using the `databaseName` property in the **Extra Connection Properties**.|
|Default Connection Properties|databaseName=test|
|Connection Properties Instructions|Use `databaseName=YOUR_DATABASE` to specify the database to connect to.|
|Default Validation Query|SELECT 1|

#### SQL Language Compatibility

|Property|Value|
|---|---|
|Default Translator|The Translator this driver should use.|

### MSSQL Default Translator Settings

#### Main Properties

|Property|Value|
|---|---|
|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|Blank|
|Create Trigger Syntax|Blank|
|Create Index Syntax|`CREATE INDEX {indexname} ON {tablename}({columnname})`|
|Auto Increment Field Definition|`{type} IDENTITY(1,1)`|
|Alter Table Syntax|`ALTER TABLE {tablename} ADD {alterdef}` |
|Add Column Syntax|`{columnname} {type}`|
|Primary Key Syntax|`PRIMARY KEY CLUSTERED ({columnname})`|
|Limit Syntax|`TOP {limit}`|
|Limit Position|Front|
|Current Timestamp Query|`SELECT CURRENT_TIMESTAMP`|
|Column Quote Character|"|
|Supports Returning Auto-generated Keys?|True|
|Fetch Key Query|Blank|
|Table List Filter|Blank|

#### Data Type Mapping

|Name|Type|
|---|---|
|Byte (I1)|int|
|Short (I2)|int|
|Integer (I4)|int|
\Long (I8)|bigint|
|Boolean|int|
|Datetime|datetime|
|Float (R4)|float(10)|
|Double (R8)|double precision|
|String|varchar(255)|
|Binary|varbinary|
|Long Text|nvarchar(max)|

## Oracle Express

### Oracle Express Default JDBC Driver Settings

#### Main Properties

|Property|Value|
|---|---|
|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.|
|Classname|oracle.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

|Property|Value|
|---|---|
|Driver Type|Oracle|
|URL Format|jdbc:oracle:thin:@localhost:1521:test|
|URL Instructions|The format of the Oracle connect URL is:jdbc:oracle:thin:@ **host** : **port** : **SID** With the three parameters (in bold) **host**: The host name or IP address of the database server.**port**: The port that the database server is running on. Oracle's default port is **1521**.**SID**: the system ID that identifies the database to connect to.|
|Default Connection Properties|Blank|
|Connection Properties Instructions|Blank|
|Default Validation Query|SELECT 1 FROM DUAL|

#### SQL Language Compatibility

|Property|Value|
|---|---|
|Default Translator|The Translator this driver should use.|

### Oracle Express Default Translator Settings

#### Main Properties

|Property|Value|
|---|---|
|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|`CREATE SEQUENCE {tablename}seq START WITH 1 INCREMENT BY 1`|
|Create Trigger Syntax|`CREATE 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 Syntax|`CREATE INDEX {indexname} ON {tablename}({columnname})`|
|Auto Increment Field Definition|`{type} NOT NULL`|
|Alter Table Syntax|`ALTER TABLE {tablename} ADD ({alterdef})`|
|Add Column Syntax|`{columnname} {type}`|
|Primary Key Syntax|`PRIMARY KEY ({columnname})`|
|Limit Syntax|`rownum<={limit}`|
|Limit Position|Wrap|
|Current Timestamp Query|`SELECT CURRENT_TIMESTAMP FROM DUAL`|
|Column Quote Character|"|
|Supports Returning Auto-generated Keys?|False|
|Fetch Key Query|`SELECT {tablename}SEQ.CURRVAL FROM DUAL`|
|Table List Filter|Blank|

#### Data Type Mapping

|Name|Type|
|---|---|
|Byte (I1)|int|
|Short (I2)|int|
|Integer (I4)|int|
|Long (I8)|int|
|Boolean|int|
|Datetime|timestamp|
|Float (R4)|float|
|Double (R8)|double precision|
|String|varchar2(255)|
|Binary|varbinary|
|Long Text|nclob|

## PostgreSQL

### PostgreSQL Default JDBC Driver Settings

#### Main Properties

|Property|Value|
|---|---|
|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.|
|Classname|org.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

|Property|Value|
|---|---|
|Driver Type|PostgreSQL|
|URL Format|jdbc:postgresql://localhost:5432/test|
|URL Instructions|The format of the PostgreSQL connect URL is:jdbc:postgresql://**host**:**port**/**database**With the three parameters (in bold) **host**: The host name or IP address of the database server.**port**: The port that the database server is running on. PostgreSQL default port is **5432**.**database**: The name of the logical database that you are connecting to on the PostgreSQL server.|
|Default Connection Properties|Blank|
|Connection Properties Instructions|No extra connection parameters are recommended for PostgreSQL. For possible parameter values, see the documentation at the [PostgreSQL JDBC driver website](http://jdbc.postgresql.org).|
|Default Validation Query|SELECT 1|

#### SQL Language Compatibility

|Property|Value|
|---|---|
|Default Translator|The Translator this driver should use. |

### PostgreSQL Default Translator Settings

#### Main Properties

|Property|Value|
|---|---|
|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|Blank|
|Create Trigger Syntax|Blank|
|Create Index Syntax|`CREATE INDEX {indexname} ON {tablename}({columnname})`|
|Auto Increment Field Definition|SERIAL NOT NULL|
|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|Blank|
|Table List Filter|Blank|

#### Data Type Mapping

|Name|Type|
|---|---|
|Byte (I1)|int|
|Short (I2)|int|
|Integer (I4)|int|
|Long (I8)|bigint|
|Boolean|int|
|Datetime|timestamp|
|Float (R4)|float|
|Double (R8)|double precision|
|String|varchar(255)|
|Binary|bytea|
|Long Text|text|

## Snowflake

### Snowflake Default JDBC Driver Settings

:::note
Many of the fields for the Snowflake JDBC driver can be left blank if your custom Snowflake translator is set up properly and referenced in the "Default Translator" field.
:::

 

Due to security updates in Java 17, Snowflake JDBC drivers may not interact correctly with Ignition. This can be fixed by adding the following Java argument to the [Gateway Configuration File](appendix\reference-pages\gateway-configuration-file-reference\gateway-configuration-file-reference.md#changing-java-additional-parameters):

```
wrapper.java.additional.1=--add-opens=java.base/java.nio=ALL-UNNAMED
```

See the official [Snowflake Knowledge Base](https://community.snowflake.com/s/article/JDBC-Driver-Compatibility-Issue-With-JDK-16-and-Later) for more information about this issue.

#### Main Properties

|Property|Value|
|---|---|
|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.|
|Classname|net.snowflake.client.jdbc.SnowflakeDriver|
|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

|Property|Value|
|---|---|
|Driver Type|Generic|
|URL Format|Blank|
|URL Instructions|Blank|
|Default Connection Properties|Blank|
|Connection Properties Instructions|Blank|
|Default Validation Query|SELECT 1|

#### SQL Language Compatibility

|Property|Value|
|---|---|
|Default Translator|Your custom Snowflake Translator.|

### Snowflake Default Translator Settings

:::note
If you have a database table called "NO_OP_TABLE", you should use a different table name for the "Create Index Syntax" field.
:::

#### Main Properties

|Property|Value|
|---|---|
|Name|Name of the translator, as you would like it to appear on the Gateway.|
|Create Table Syntax|`CREATE TABLE IF NOT EXISTS {tablename} ({creationdef}{primarykeydef})`|
|Create Sequence Syntax|`CREATE SEQUENCE IF NOT EXISTS {tablename}seq`|
|Create Trigger Syntax|Blank|
|Create Index Syntax|`ALTER TABLE IF EXISTS NO_OP_TABLE SET COMMENT='no-op table due to forced value for TRANSLATORS &lt; ALTER TABLE SYNTAX;`|
|Auto Increment Field Definition|`{type} NOT NULL AUTOINCREMENT`|
|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|Blank|
|Supports Returning Auto-generated Keys?|False|
|Fetch Key Query|`SELECT NEXT_VALUE FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_NAME='{tablename}'`|
|Table List Filter|Blank|

#### Data Type Mapping

|Name|Type|
|---|---|
|Byte (I1)|int|
|Short (I2)|int|
|Integer (I4)|int|
|Long (I8)|bigint|
|Boolean|int|
|Datetime|datetime|
|Float (R4)|float|
|Double (R8)|double|
|String|string|
|Binary|binary|
|Long Text|text|

:::note
In addition to a custom JDBC driver and custom translator, you may need to also configure the `Connection Initialization Commands` property in your Snowflake [database connection](platform\database-connections\database-connections.md) to properly store Tag History. Add the following values to the `Connection Initialization Commands` property:
* USE DATABASE ignition_db
* USE SCHEMA ignition_db.time_series
:::