JDBC Driver and Database Translator Settings

This page details various JDBC driver and translator settings. See the JDBC Drivers and Translators page 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/SAMPLE OR jdbc: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	Leave Blank
Connection Property Instructions	Leave 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	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

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

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	Where
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	leave empty

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

Changed in 8.1.33

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:

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

See the official Snowflake Knowledge Base 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 > 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	Leave Field 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 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