Create a JDBC connection with Mysql Cache

When we need to integrate Caché/IRIS with other relational databases, one common question arises: “How do I set up the JDBC connection?”.
The official documentation doesn’t always provide a straightforward step-by-step guide, which can be frustrating, especially for beginners.

In this article, I’ll walk you through the entire process of configuring a JDBC connection with MySQL, from downloading the connector to linking tables in Caché/IRIS.

Note: JDBC connections in Caché/IRIS have some limitations, especially regarding caching and the use of ResultSet. In many cases, you may need to adapt queries manually and use the Gateway object to handle advanced scenarios.

1. Download the MySQL JDBC Connector

The first step is to download the official MySQL JDBC driver.
Go to: https://dev.mysql.com/downloads/connector/j/

2. Make the Connector Accessible

After downloading:

1. Extract the package.
2. Locate the .jar file (e.g., mysql-connector-j-9.4.xx.jar).
3. Copy the file to a folder accessible to Caché/IRIS, for example:

3. Configure the Connection in the Management Portal

Now, let’s set up the connection inside the Management Portal:

1. Open the Caché/IRIS Management Portal in your browser.
2. Navigate to Gateway Sql Connections
3. Click “Create New Connection”.

Create a new connection and configure it using the example provided.

,

4. Link External Tables

• Go to a SQL in Menu, assistant > link table
• Click Assistant > Link Table.
• Select your connection (MySQL_Conn).
• Choose the tables you want to link.
• Confirm.

And put the necessary settings, this part is easy and very suggestive, once this is done your jdbc connection is ready, you can see on the sql screen itself the bank, the table and can bring the data, remember that mysql uses backquotes to form select and the cache uses double quotes which can be a problem when doing some select

After mapping the tables, you will be able to view them under the Tables tab in the SQL screen.

5. SQL Syntax Consderations

One common issue:

• MySQL uses backticks (`) for table and column names.
• Caché/IRIS uses double quotes (") for identifiers.
• By selecting the appropriate options in the Gateway configuration screen (as shown in the screenshot), you can resolve this issue. However, you may also choose to leave it unchanged for any reason.

This can cause problems in queries. If needed, manually adjust the syntax when writing SQL statements.

Conclusion

And that’s it! You now have a working JDBC connection between Caché/IRIS and MySQL.

With this setup, you can:

• Query MySQL data directly from Caché/IRIS.
• Link tables to your namespace for easy integration.
• Automate data exchange between systems.

Even though JDBC in Caché/IRIS has some limitations, this configuration covers most integration needs in a practical way.