JDBC Client

2.1

An interface for connecting to SQL databases.

Overview

Copy

JDBC (short for Java Database Connectivity) is an interface that makes it easy to connect to a wide variety of SQL databases and perform queries and updates on them. Users can connect to MySQL, PostgreSQL, Oracle, and more - all in the same connector.

Supported databases

Copy

Currently, the JDBC Client supports:

• MySQL

• PostgreSQL

• Oracle

• Snowflake

• Microsoft SQL (for SQL Server)

Authentication

Copy

When using the JDBC Client connector, the first thing you will need to do is go to your Tray.io account page, and select the workflow you wish to work on. Once in the workflow builder itself, search and drag the JDBC Client connector from the connectors panel (on the left hand side) onto your workflow.

With the new JDBC Client connector step highlighted, in the properties panel on the right, click on the Authenticate tab and 'Add new authentication' (located under the 'Authentication' field).

This will result in a Tray.io authentication pop-up window. The first page will ask you to name your authentication, and state which type of authentication you wish to create ('Personal' or 'Organisational').

As you can see, the next page asks you for the type of database you are using (MySQL, PostgreSQL, or Oracle), and your URL, username, and password.

How the URL is formatted will depend on the type of database you are using. The typical formatting for the following services is as follows:

Once you have added these fields to your Tray.io authentication popup window, click on the 'Create authentication' button. Go back to your settings authentication field (within the workflow builder properties panel), and select the recently added authentication from the dropdown options now available.

Your connector authentication setup should now be complete.

Available Operations

Copy

The examples below show one or two of the available connector operations in use.

Please see the Full Operations Reference at the end of this page for details on all available operations for this connector.

Example Usage

Copy

Below is an example of a way in which you could potentially use the JDBC Client connector, to retrieve a number of rows from a database, each containing a foreign key, loop through them, and then retrieve the data from a separate table that the foreign key refers to.

In this example, we'll be retrieving data from a table of posts to a hypothetical social media platform. Each post has an author, which links to a table of users.

The steps will be as follows:

1. Setup using a manual trigger and create an JDBC Client connector step in order to gather the available posts.

2. Add a Loop Collection step to iterate through each post found.

3. Add a second JDBC Client connector step, this time to retrieve the author information for each post.

The final outcome should look like this:

1 - Setup Trigger & Get Posts

Copy

Once you have clicked 'Create new workflow' from your main Tray.io dashboard named it, select the Manual trigger from the trigger options available:

After you have been redirected to the Tray.io workflow dashboard, from the connectors panel on the left, add a JDBC Client connector to your second step. Set the operation to 'Execute Query', and write out the SQL query you want to perform in the 'Template' field.

In this example, the following was used:

1
SELECT id, content, published, author FROM posts LIMIT 5;

Feel free to re-name your steps as you go along to make things clearer for yourself and other users.

Now, when the workflow is run, the JDBC Client will retrieve the first 5 rows from the posts table in our database.

2 - Add Loop Collection

Copy

Next, search for the Loop collection connector within your connector panel, and drag it into your workflow as your next step. Set your operations to 'Loop list'.

The Loop Collection connector allows you to iterate through a list of results. In this example, we will use it to iterate through the data found within the previous JDBC Client connector step.

In order to specify the list you want to loop through, start by using the 'List' mapping icon (found next to the list input field, within the properties panel) to generate the connector-snake.

While hovering over the 'Get posts' step (with the tail end of the connector-snake), select data from the list of output properties displayed. This will auto-populate a jsonpath within your 'List' input field, and update the type selector to jsonpath.

For more clarification on the pathways you have available, open the Debug panel to view your step's Input and Output.

Now, the workflow will iterate through the results from your database. But currently, it doesn't do anything with them.

3 - Get Author

Copy

Add another JDBC Client step inside the Loop Collection, and once again set the operation to 'Execute Query'.

In this example, because we want to retrieve data based on the author field in the posts table, we need to specify a parameter to be inserted before the query is executed. The process for this is:

1. Write out the SQL query in the 'Template' field, using ? where as-yet-unspecified values should go.

2. Specify what these values should be in the 'Parameters' list.

For our 'author' field, we need to use the connector-snake to retrieve the value property from the Loop Collection. However, the value property refers to the entire row, so we need to add .author at the end to retrieve the author's ID specifically.

When setting parameters, make sure that you are using the correct type for your query. In this case, our ID is an integer, so we set the type to 'number'.

The query used here is as follows:

1
SELECT id, username, first_name, first_name FROM users WHERE id=?;

Now, the workflow will retrieve the user associated with each post in turn.