Set up a connection to an application or database via JDBC

JDBC (Java Database Connectivity) is a Java-based API for connecting to relational databases, executing SQL queries, and managing transactions. It can also be extended to connect with non-relational data sources, offering versatility in data interaction within Java applications.

This generic JDBC connection is to be used only with the on-premise agent. Use this connector to connect to MySQL, Microsoft SQL, or any other database that uses OpenJDK or Oracle Java 17.

Note: We've tested the connection with MySQL 8.0.32 and MS SQL 11.2.1 drivers. You can use this connection to connect to any application that supports JDBC and uses Java 17.
As you use the connector, if you've got an improvement idea, you're welcome to submit it on the Product portal, or if you've got a connection-related query, reach out via a community post.

Contents

• 1. Set up a JDBC connection
• 2. Edit the general JDBC settings
• 3. Edit the JDBC settings for authentication
• 4. Edit the advanced JDBC settings for customization
• 5. Test the connection

1. Set up a JDBC connection

Start establishing a JDBC connection in either of the following ways:

• From the Resources menu, select Connections. Then, click + Create connection at the top right.
      – or –
• While working in a new or existing integration, you can add an application to a flow simply by clicking Add source or Add destination.

In the resulting Application list, click JDBC.

2. Edit the general JDBC settings

Get started by naming your connection and setting up the agent. 

Name (required): Enter a clear and distinguishable name for your connection.
Throughout integrator.io imports and exports, you will have the option to choose this new connection. A unique identifier will prove helpful later when selecting it from a list of the connections in your account.

Application: JDBC is automatically populated based on your earlier selection.

Agent (required): Select an agent from the list that you have created inside integrator.io. If you have not yet created an agent, see Integrate data through firewall with on-premise agent. To connect to an on-premise application, integrator.io requires that you install an agent on a networked computer. Note that the minimum version of the agent must be 3.3.0.
An agent is a small application that allows you to connect to data behind your firewall. When installing an agent, you will specify a unique access token, which then populates the Agent drop-down list. The installed agents connect to integrator.io and establish a reverse SSH tunnel that allows secure communication without the need to whitelist integrator.io’s IP addresses in your firewall settings. Multiple connections can use the same agent.

Tip: A single agent can be used by multiple connections.

3. Edit the JDBC settings for authentication

Now, provide the required application settings for authentication.

Connection string (required): Enter the connection string/URL in the following format to connect to the database.

Example (MySQL)

[protocol]://[host][:port][/database][?properties]

jdbc:mysql://host1:33060/Test?encrypted=true

• protocol: A set of rules to communicate data. Specify the driver and the type of database. For example, jdbc:mysql:// or jdbc:mssql://
• host: The name and port number of the computer that hosts your database. For the host, specify either the name or URL. (If not specified, for MySQL, the default values of the host and port are 127.0.0.1 and 3306, respectively.)
• database: The name of the database. If not specified, a connection is made with no default database.
• properties: Include ?before you provide the properties, and specify them as propertyName=propertyValue.
  When you provide many properties in your connection string,
  • separate them with an ampersand (&) for MySQL
  • separate them with a comma (,) for MS SQL

Note: For connection string details, check the documentation of the database or application that you're connecting to.

JDBC driver directory path (required): Specify the path where you have installed the Java driver files. See connection pre-requisites.

Username: Enter your user name for database authentication.

Password: Enter your password for database authentication.

4. Edit the advanced JDBC settings for customization

Edit these advanced settings to configure additional properties or the concurrency level based on your specific requirements.

Configure properties: Define additional configurations for the database connection. For example, you can configure the connection timeout if the server you are trying to connect to is slow.

Borrow concurrency from: Select another connection from the list. By default, all data flowing through a connection record is submitted to the respective endpoint application at the Concurrency level configured for that connection record. There are cases, however, where multiple integrator.io connections need to share the same concurrency level, and this field allows you to specify which connection you would like to borrow from. With borrowed concurrency, the data flowing through both connections will be submitted to the endpoint application together, via a shared concurrency model.

Auto-recover rate limit errors: This checkbox is selected by default in new connections. The setting automatically retries requests to recover from rate limit errors. See also, Connection setting – Auto-recover rate limit errors.

Target concurrency (required if Auto-recover rate limit errors is enabled): Enter a value based on the rate limit policy of the endpoint that you're connecting to. The connection will send API requests in parallel based on the value provided here. If a rate limit error occurs, the Celigo platform will start sending requests starting with 1 request and then slowly increment it till it reaches the number specified here.

Concurrency level (required if Auto-recover rate limit errors is not enabled): Set this field to limit the number of concurrent HTTP requests (up to 25) allowed by the connection resource at any one time, or leave this field blank to use burst mode. With burst mode, integrator.io will make HTTP requests as fast as possible, with high levels of concurrency.

Configure encrypted fields: Store all sensitive fields required by your imports and exports to access the app you are connecting to. Click the Expand window (  ) button to modify the JSON sent to the app in the Encrypted Advanced Field Editor. For example, enter {"secretKey": "someSecretKeyValue"}. (You can also retrieve these sensitive values elsewhere, such as How to test this connection?, via the example handlebars format {{{connection.http.encrypted.secretKey}}}.) Multiple layers of protection are in place, including AES 256 encryption, to keep your connection’s encrypted fields safe. When editing this form later, you must enter this value again; it is stored only when the connection is saved and never displayed as text.

5. Test the connection

Once you have configured the Oracle Autonomous Database / NSAW connection, click one of the following options:

• Save – click this button to test the connection, and commit the new connection so that it will be available to all integrations for your account
• Save & close – click to test and save the connection and exit the Create connection pane
• Cancel – click to exit without saving any new changes
• Test connection – click this button to verify that your new connection is free of errors

When you test or save the connection, you will receive the following message:

The connection is added to your account.

• You can see it listed under Resources > Connections.
• If a connection is part of an integration, you can see it listed under Home > Integration name > Connections, and you may proceed to register the connection.