MariaDB and MySQL are relational databases that follow the same paradigm when it comes to setting up connections with SortCL-driven Voracity component jobs designed in IRI Workbench. Both JDBC (for IRI Workbench interaction) and ODBC (for data movement) must be set up before SortCL can perform CoSort, FieldShield, NextForm, or RowGen jobs such as data transformation, obfuscation, migration, synthesis, reporting, etc. Note that if you are using DarkShield, only the JDBC connection is required.

Since MariaDB is a fork based on MySQL, the setups will be very similar, though there are some minor differences that this article will mention.  

Note: Due to how similar both databases are to each other, you can reuse drivers (ODBC/JDBC) for either database. This means that you can use a MySQL JDBC driver to connect to MariaDB but you have to make sure that the version you are using is compatible with the version of the database.

Follow along with our YouTube video!

ODBC Connection

First, download the ODBC driver for either MariaDB or MySQL. Make sure to download the driver that is compatible with your OS version and that the bit version (32 bit or 64 bit) is the same as the CoSort bit version.

If you do not know which CoSort (SortCL) bit version you have installed, open Workbench, select the Help tab, select Version Information and here you will see which bit version you have. The example below shows a 32 bit version.Run the ODBC .msi file you just downloaded and follow the instructions of the wizard. Once that is complete, you can create an ODBC connection to either database. Use the Windows search function to open the ODBC Data Source Administrator (64-bit) or (32-bit) to create the connection.Select the Add… button and search for the data source you want to connect to. Once you click on Finish, another dialog will appear to input the connection credentials for your database. MySQL and MariaDB are slightly different, so the first connection shown below will be for MariaDB, and then followed by MySQL.

The first page for MariaDB is asking for a name for the ODBC connection and a description (optional). Once you’ve given a name select Next >.

On the next page, you will specify the server name and the port on which it is listening; the default is port 3306. In the middle of the dialog, you can add the credentials to connect to your database and test to see if the connection is working.

If you select the Test DSN, a successful connection will look like this:

The following pages allow you to set up optional configurations such as:

• TLS Settings
• SQL Queries 
• Server Sessions
• Cursor/Results

Once everything is set up to your liking and the test DSN is successful, click the Finish button to make your ODBC connection ready for use.

MySQL will be the same except that its dialog and optional configuration is limited and simple. Again you will need the following information: a name for the connection, the server name or IP address, the port number, and credentials to connect to the database.

Test the connection to ensure that everything is working properly and then click OK to save the configuration. The next step is to create a JDBC connection inside of  Workbench.

JDBC Connection

As with ODBC, you need to download the JDBC driver for either MariaDB or MySQL. However, instead of being used for data movement between the database and SortCL ¹, the JDBC driver enables table interactivity and DDL metadata interchange in IRI Workbench. Again be sure to download the version compatible with your O/S. 

In the Data Source Explorer section, select the new connection profile icon . 

The New Connection Profile dialog will appear. You can select the MySQL profile for either MySQL or MariaDB. Overwrite the default name for the profile to one that gives context to what you are connecting to.

Select Next > and an empty page will appear. To the right of the Drivers drop-down menu, click on the wheel icon called New Driver Definition.

This will open a new dialog to select a driver version; look for the one that corresponds to your database version or use the latest version. You can name the driver version to add context if the driver is connecting to MariaDB or MySQL.

Select the JAR List tab and here is where you can add the path to the JDBC jar file you downloaded. As the image below shows, there is already a jar file with no path which you can simply remove or edit to the path of your .jar file.

Next, select the Properties tab, and here is where MariaDB and MySQL differ. For MySQL, all you need to do is change the Connection URL to contain the server name or IP address, the port number if it’s not the default 3306, and the database name.

Syntax

jdbc:mysql://<serverName/IpAddress>:portNumber/databaseName

Below the connection URL, add the name of the database. Add the credentials to connect to the database at the bottom.

For MariaDB the connection URL and the driver class are different. Edit the connection URL to start with jdbc:mariadb:// and the rest is the same syntax as above. Next change the driver class to org.mariadb.jdbc.Driver for the connection to work. The same goes for MySQL; you need to add the credentials and specify the database name.

Select OK and now you are back to the connection profile. Ensure that the information is populating correctly with the name of the database, the correct connection URL, and the credentials.

Select the Save password to avoid having to give the password every time you need to connect to your data source and finally select the Test Connection at the lower right section to ensure everything is working correctly.

Click on Finish if everything is correct and now you have a JDBC connection ready for use. If you run into errors, verify the connection URL is in the correct format for MariaDB or MySQL. The last step is to set up the database connection registry which maps the JDBC connection to the ODBC connection.

Database Connection Registry

From the Workbench top toolbar, click on the twistie (down arrow) next to the IRI Menu, which is located at the top, just under the Project menu. Select the IRI Preferences… to set up the connection registry.

Select the twistie next to IRI to see more information, then click the twistie next to Connection Registry, and then select Database Connection.

There is a Default bit version: it can be 32 or 64. Make sure that the bit version corresponds to the bit version of CoSort (the SortCL executable) and the ODBC connection you just created. Click the Refresh button to ensure Workbench can see all your available ODBC connections.   

Double-click on the ODBC connection you created to edit the registry. The Database Connection Dialog will appear and at the top, you will see the ODBC information. In the middle make sure that the bit version is correct and below that, credentials to connect the database are specified.

To the right of both user and password, you can encrypt your credentials. It is recommended you at least encrypt your password. If you do not, when Workbench builds applicable SortCL jobs, your database credentials in the /INFILE or /OUTFILE section of script will be in plain text ².

At the bottom of the dialog is where you can select the JDBC connection to map to. Select the drop-down menu and the JDBC connection you just created. Once you are done, the registry view should appear like this:

Click OK and make sure that the connection is active. Again it should look like the image below:

You have successfully set up Workbench (and the SortCL data processing program) to interact with your data source. If you need any help, please email support@iri.com.

1.  Note that the JDBC driver is also used for data movement when using IRI DarkShield for masking. See this FAQ to understand the differences between masking RDB data with FieldShield/SortCL vs. DarkShield.
2. See this article on using DSN files to alternatively hide (and centralize) ODBC connection credentials.