Skip to main content
Version: 8.1

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​

PropertyValue
NameIBM DB2
DescriptionThe official IBM DB2 JDBC Driver.
Classnamecom.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​

PropertyValue
Driver TypeDB2
URL Formatjdbc:db2://localhost:50000/SAMPLE
OR
jdbc:db2://localhost:25000/SAMPLE
URL InstructionsThe 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 PropertiesLeave Blank
Connection Property InstructionsLeave Blank
Default Validation Queryselect 1 from sysibm.sysdummy1

SQL Language Compatibility​

PropertyValue
Default TranslatorThe Translator this driver should use. Default: IBM DB2

IBM Default Translator Settings​

Main Properties​

PropertyValue
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 Syntaxleave empty
Create Trigger Syntaxleave empty
Create Index SyntaxCREATE INDEX {indexname} ON {tablename}({columnname})
Auto Increment Field Definition{type} GENERATED ALWAYS AS IDENTITY PRIMARY KEY
Alter Table SyntaxALTER TABLE {tablename} {alterdef}
Add Column SyntaxADD COLUMN {columnname} {type}
Primary Key Syntaxleave empty
Limit SyntaxFETCH FIRST {limit} ROWS ONLY
Limit PositionBack
Current Timestamp Queryvalues current timestamp
Column Quote Character"
Supports Returning Auto-generated Keys?False
Fetch Key QuerySELECT max({columnname}) FROM {tablename}
Table List Filterleave empty

Data Type Mapping​

NameType
Byte (I1)int
Short (I2)int
Integer (I4)int
Long (I8)bigint
Booleanint
Datetimetimestamp
Float (R4)float
Double (R8)double
Stringvarchar(255)
Binaryblob
Long Textclob(65536)

MySQL​

MySQL Default JDBC Driver Settings​

Main Properties​

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

PropertyValue
Driver TypeMySQL
URL Formatjdbc:mysql://localhost:3306/test
URL InstructionsThe 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 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 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 QuerySELECT 1

SQL Language Compatibility​

PropertyValue
Default TranslatorMySQL

MySQL Default Translator Settings​

Main Properties​

PropertyValue
NameMySQL
Create Table SyntaxCREATE TABLE {tablename} ({creationdef}{primarykeydef})
Create Sequence Syntaxleave empty
Create Trigger Syntaxleave 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​

NameType
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

MSSQL​

MSSQL Default JDBC Driver Settings​

Main Properties​

PropertyValue
Name(Name of the driver, as you would like it to appear on the Gateway)
DescriptionEnter 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​

PropertyValue
Driver TypeMicrosoft SQL Server
URL Formatjdbc:sqlserver://localhost\SQLEXPRESS
URL InstructionsThe 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 PropertiesdatabaseName=test
Connection Properties InstructionsUse databaseName=YOUR_DATABASE to specify the database to connect to.
Default Validation QuerySELECT 1

SQL Language Compatibility​

PropertyValue
Default TranslatorThe Translator this driver should use.

MSSQL Default Translator Settings​

Main Properties​

PropertyValue
NameName 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 SyntaxBlank
Create Trigger SyntaxBlank
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 QueryBlank
Table List FilterBlank

Data Type Mapping​

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

Oracle Express​

Oracle Express Default JDBC Driver Settings​

Main Properties​

PropertyValue
NameName of the driver, as you would like it to appear on the Gateway.
DescriptionEnter 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​

PropertyValue
Driver TypeOracle
URL Formatjdbc:oracle:thin:@localhost:1521:test
URL InstructionsThe 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 PropertiesBlank
Connection Properties InstructionsBlank
Default Validation QuerySELECT 1 FROM DUAL

SQL Language Compatibility​

PropertyValue
Default TranslatorThe Translator this driver should use.

Oracle Express Default Translator Settings​

Main Properties​

PropertyValue
NameName 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 Filterleave empty

Data Type Mapping​

NameType
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

PostgreSQL​

PostgreSQL Default JDBC Driver Settings​

Main Properties​

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

PropertyValue
Driver TypePostgreSQL
URL Formatjdbc:postgresql://localhost:5432/test
URL InstructionsThe 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 PropertiesBlank
Connection Properties InstructionsNo extra connection parameters are recommended for PostgreSQL. For possible parameter values, see the documentation at the PostgreSQL JDBC driver website.
Default Validation QuerySELECT 1

SQL Language Compatibility​

PropertyValue
Default TranslatorThe Translator this driver should use.

PostgreSQL Default Translator Settings​

Main Properties​

PropertyValue
NameName 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 SyntaxBlank
Create Trigger SyntaxBlank
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 QueryBlank
Table List FilterBlank

Data Type Mapping​

NameType
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

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.

Main Properties​

PropertyValue
NameName of the driver, as you would like it to appear on the Gateway.
DescriptionEnter a useful description you would like to see next to the driver.
Classnamenet.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​

PropertyValue
Driver TypeGeneric
URL FormatBlank
URL InstructionsBlank
Default Connection PropertiesBlank
Connection Properties InstructionsBlank
Default Validation QuerySELECT 1

SQL Language Compatibility​

PropertyValue
Default TranslatorYour 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​

PropertyValue
NameName of the translator, as you would like it to appear on the Gateway.
Create Table SyntaxCREATE TABLE IF NOT EXISTS {tablename} ({creationdef}{primarykeydef})
Create Sequence SyntaxCREATE SEQUENCE IF NOT EXISTS {tablename}seq
Create Trigger SyntaxBlank
Create Index SyntaxALTER 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 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 CharacterLeave Field Blank
Supports Returning Auto-generated Keys?False
Fetch Key QuerySELECT NEXT_VALUE FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_NAME='{tablename}'
Table List FilterBlank

Data Type Mapping​

NameType
Byte (I1)int
Short (I2)int
Integer (I4)int
Long (I8)bigint
Booleanint
Datetimedatetime
Float (R4)float
Double (R8)double
Stringstring
Binarybinary
Long Texttext
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