Connectors / Service / JDBC Client

JDBC Client

JDBC Client

An interface for connecting to SQL databases.

Overview

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

Currently, the JDBC Client supports:

  • MySQL
  • PostgreSQL
  • Oracle
  • Snowflake
  • Microsoft SQL (for SQL Server)

Authentication

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:

DatabaseURL
MySQLjdbc:mysql://<host>:3306/<database>
PostgreSQLjdbc:postgresql://<host>:5432/<database>
Oraclejdbc:oracle:thin:@<host>:1521/<database>
Snowflakejdbc:snowflake://<account_name>.snowflakecomputing.com/?db=<database>
Microsoft SQLjdbc:sqlserver://<host>:1433;database=<database>

Where host is the address of the server where your database is hosted, and database is the name of your database.

To determine the account_name for Snowflake, visit Snowflake's documentation on configuring JDBC here.

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

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

TRAY POTENTIAL: Tray.io is extremely flexible. By design there is no fixed way of working with it - you can pull whatever data you need from other services and work with it using our core and helper connectors. This demo which follows shows only one possible way of working with Tray.io and the JDBC Client connector. Once you've finished working through this example please see our Introduction to working with data and jsonpaths page and Data Guide for more details.

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

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:

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

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.

JSONPATHS: For more information on what jsonpaths are and how to use jsonpaths with Tray.io, please see our Intro page and Data Guide for more details.
CONNECTOR-SNAKE: The simplest and easiest way to generate your jsonpaths is to use our feature called the Connector-snake. Please see the main page for more details.

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

3 - Get Author

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:

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

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

BEST PRACTICES: Whenever you do decide to create your own workflow, please make sure you take a look at our Managing data best practices guide.

All Operations

Latest version:

2.1