JDBC Connection

Databases supported by Gathr to create JDBC connections are, MySQL, PostgreSQL, Oracle, MSSQL, DB2 and custom databases.

SSL Security can be enabled on JDBC Databases.

System should be able to connect, read and write from SSL Secured JDBC.

If security is enabled, it will be configured in Connection and automatically propagated to the respective channel.

Connection Configuration #

Connection Name: Name of the connection to be created.

Connection Type: Option to choose between either connecting securely using SSH Tunnel or, proceed with a direct connection.

If Connection Type value is SSH Tunnel, additional fields will be displayed as given below:

To know more about SSH Tunneling with Gathr, see SSH Tunneling.

SSH Key: The SSH key should be provided to authenticate the associated SSH host and SSH user.

SSH Host: Public IP address of your SSH host.

SSH User: Default SSH username.

If Connection Type value is Direct Connection, then proceed by updating the following fields.

Database Type: Type of database that needs to be connected. The databases available are: MySQL, PostgreSQL, Oracle, MSSQL, DB2 and Custom.

The fields unique to the custom database type are explained in the section, Custom JDBC Connection Fields. The fields that are common to the custom JDBC connection are covered in this table itself.

Enable SSL: The checkbox can be selected if SSL is enabled on JDBC.

Upload Keystore File: If SSL is enabled, a keystore file has to be uploaded using this option.

Database Name: The name of the database to be connected.

Host: Host name of the relational database.

Port: Port number of the relational database.

Username: Username of the relational database account.

Password: Password of the relational database account.

Custom JDBC Connection Fields #

The configuration fields that are specific to the custom JDBC connection are described below.

Driver Class Name: The JDBC driver to be used for the connection should be provided.

Connection URL: JDBC Driver URL to be used for the connection should be specified.

Table List Query: A query can be provided to list the tables during JDBC source configuration.

The response of a table list query provided should be a single column that contains table names.

In case this field is left blank or your query cannot produce a single column result, then you can manually provide the required table name in the JDBC data source.

Column List Query: This query can be used to fetch columns from table. The placeholder that you provide here will replace with your selected table.

Inspect Query: A query should be provided to inspect data.

The query that you provide in the JDBC source will act as a sub-query for this field.

An inspect query for custom JDBC can be provided in the following format:

select \* from \<QUERY\> as alias LIMIT \<LIMIT\>

Enable Datatype conversion for Incremental Read: Select this option to provide custom expressions for converting incremental read offset strings to number, date, or timestamp. These expressions will be used while creating incremental read query.

Expression for Text to Number: Provide expression for converting string to number.

Expression for Text to Date: Provide expression for converting string to date.

Expression for Text to Timestamp: Provide expression for converting string to timestamp.

Upload Jar: Option to upload the JAR file that is required to connect to the data source.

After entering all the details, click on the TEST button.

If the connection service identification and authentication details are provided correctly, a success message stating “connection available” is generated. Click on the CREATE button to save the changes.

If the details are incorrect or the server is down, you will get a message “Connection unavailable”.