Introduction

Ignition supports many of the most popular databases in the industry like Microsoft SQL Server, MySQL, Oracle, and Postgres, so long as you provide a valid driver. However, you may come across alternative drivers or other types of databases that are not officially supported but still want to give them a try. Ignition provides the ability to test this out by taking advantage of our "JDBC Driver" and "Translator" settings found in the Gateway webservers Config tab > Drivers. 

Background

First, let's talk about what a JDBC driver is and how Ignition translators come into play. 

• JDBC stands for "Java Database Connectivity" and a JDBC driver is a piece of software written to standardize the way we access data using the Java programming language.
• In Ignition, a Database Translator is a system that navigates the differences between the provided and expected syntaxes of the database system in use, providing a way to define certain key operations that are commonly different between database vendors.

In this guide, we will be using the Snowflake database as an example. Snowflake requires a JDBC driver that is not included with your install of Ignition, requires a custom Translator, and a few other settings to establish a valid connection. The following information was tested using the Snowflake JDBC 3.12.17 driver on Ignition version 7.9.10 and 8.1.9. https://docs.snowflake.com/en/user-guide/jdbc.html.

The first step for setting up the database connection is to install the appropriate JDBC driver.

1. Navigate to the Gateway webservers Config tab > Databases >Drivers
2. Select "Create new JDBC Driver..."
3. Select "Choose File" for the JAR File(s) field and open the appropriate JDBC driver you have downloaded.
4. For the "Classname" field, this will depend upon the JDBC driver you are using and you will have to refer to the documentation provided by whoever developed the driver. For Snowflake databases, the class name is:

net.snowflake.client.jdbc.SnowflakeDriver

https://docs.snowflake.com/en/user-guide/jdbc-configure.html#jdbc-driver-class

5. For Snowflake, these are the only required fields to change, for now. We will eventually come back and assign a custom translator for the "Default Translator" field for convenience. 

It is possible that your 3rd party JDBC does not require or can take advantage of one of the pre-built translators provided by Ignition, but in the case that they do not, you will be required to create a custom translator. For Snowflake database connections, it is required to create a custom translator.

1. In the Gateway Webservers Config tab > Databases > Drivers, select the "Translators" tab.
2. Select "Create new Database Translator..."
3. Provide a value in the "Name" field that is unique and easily identifiable for you.
4. Fill out or customize all of the required fields per the guidance or documentation of whichever database system you are using. For Snowflake database connections, the following fields must be applied. Any fields not mentioned can be left default.

Snowflake Translator Settings:

• Create Table Syntax

CREATE TABLE IF NOT EXISTS {tablename} ({creationdef}{primarykeydef})

• Create Index Syntax

Snowflake is unique in that they do not have a traditional indexing system but Ignition translator settings require this field to be filled out so use the following:

ALTER TABLE IF EXISTS NO_OP_TABLE SET COMMENT='no-op table due to forced value for TRANSLATORS > ALTER TABLE SYNTAX';

NOTE: In the above Create Index Syntax for Snowflake, you should NOT have a Database Table called "NO_OP_TABLE". If you do, then you should change the above to a table name that does not exist. 

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

• String

string

• Binary

binary

Select "Create New Database Translator" to save and create the new translator configuration.

Now that you have created the custom translator, you can apply it as the default translator:

1. In the Gateway webservers Config tab > Databases > Drivers under the "JDBC Drivers" tab, select the "Edit" button for the JDBC driver you created earlier. 
2. Under "SQL Language Compatibility" select the dropdown for "Default Translator" and select the new translator configuration that was created above.
3. Select Save Changes to apply the new configuration.

We are now ready to create a database connection from Ignition to the database in question.

1. In the Gateway Webservers Config tab > Databases > Connections, select "Create new Database Connection..."
2. Select the new driver that you had created earlier and then hit the "Next" button.
3. Enter in the "Name" field a name that suits you.
4. In the "JDBC Driver" field dropdown, select the JDBC driver that was uploaded for your database of choice.
5. For the "Connect URL" field, refer to your JDBC drivers documentation, for Snowflake, the syntax is the following: 

jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?<connection_params>

Example: 

jdbc:snowflake://dua85002.snowflakecomputing.com/?role=SYSADMIN&db=IGNITIONTEST&schema=PUBLIC&warehouse=COMPUTE_WH&TRACING=ALL

https://docs.snowflake.com/en/user-guide/jdbc-configure.html#jdbc-driver-connection-string 

6. Enter the Username and Password for the database.

7. For Snowflake, the "Extra Connection Properties" field must be filled out as such:

CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=TRUE

8. Under "Show advanced properties", ensure the translator dropdown is using the translator required.

9. For Snowflake, there are no other settings to configure so you can hit the "Save Changes" button.

If all went well, you should have a "Valid" status for your new database connection, congratulations!