Transforming ODBC data in CloverDX

CloverDX (formerly CloverETL) is an Extract, Transform, Load (ETL) application. ETL applications are mainly designed to transform and cleanse data from one source database to the format required by the target database.

CloverDX started in 2002 as an open-source project that was initially made available on Freshmeat (now known as Freecode) in October of that year. CloverDX's commercial life began in 2005.

CloverDX is written in Java and uses the JDBC API to communicate with databases. If a database has a driver supporting the JDBC API, CloverDX can unload data stored within a database table, or it can populate a database table with data from another source. If a database has a driver supporting the ODBC API, a piece of middleware known as a JDBC-ODBC Bridge is needed before CloverDX can work with that database.

Java used to include a JDBC-ODBC Bridge and previously Java applications such as CloverDX could uses this bridge driver to work with ODBC databases. Now that this JDBC-ODBC Bridge has been withdrawn, a bridge driver from a commercial vendor such as Easysoft is needed.

The Easysoft JDBC-ODBC Bridge is a client/server product that enables a Java application such as CloverDX to access an ODBC data source such as Microsoft Access. The client part of the Easysoft JDBC-ODBC Bridge is a JDBC driver. On Windows, the sever part is a Windows service that acts as an ODBC application.

The rest of this blog documents how to use the Easysoft JDBC-ODBC Bridge to work with data from an ODBC database in CloverDX.

1. Configure a system ODBC data source for the database that you want to connect to in CloverDX.

   To do this, use the 32-bit version of ODBC Data Source Administrator on your CloverDX machine. On some versions of Windows, this is located in Control Panel > Administrative Tools. On some version of Windows, you need to search for ODBC in the taskbar search box. The 32-bit version of ODBC Data Source Administrator should be clearly labelled. If in doubt, in the Windows Run dialog box, enter:

   %windir%\syswow64\odbcad32.exe

2. Download the Easysoft JDBC-ODBC Bridge.
3. Install and license the Easysoft JDBC-ODBC Bridge on the machine where CloverDX is installed.

   For installation instructions, refer to the Easysoft JDBC-ODBC Bridge documentation.

1. Open CloverDX Designer.
2. Create a new CloverDX project.
3. Copy the EJOB.jar file from the Easysoft distribution to the CloverDX project's $LIBDIR folder.
4. Create a new graph in the CloverDX project.
5. In the graph's Outline panel, right-click on connections and create a new DB connection.
6. In the Create DB connection dialog panel:
   1. Give the connection a name, say Easysoft JDBC-ODBC Bridge.
   2. Click + to add a driver, browse to the $LIBDIR directory, and then select the EJOB.jar file.
   3. Select easysoft.sql.jobDriver from the available drivers list.
   4. For URL, use jdbc:easysoft://hostname:8831/odbcdsnname.

      Specify the user name and password for the database, if required.

   5. In the Advanced tab, add properties for logonuser and logonpassword, specifying a user name and password for a Windows user on the machine where the Easysoft JDBC-ODBC Bridge server is running.
   6. Choose Validate to confirm the connection is defined correctly.
7. Add a new DatabaseReader component to the graph.
8. Double-click the DatabaseReader to edit its properties:
   1. For DBConnection, select the connection created above.
   2. For SQL Query, enter the desired SQL query.
   3. Close the dialog.
9. Right-click on DatabaseReader and select New Metadata > Extract Metadata.
10. Create a CloverDX metadata definition for the input data:
    1. Right-click on the DatabaseReader and select Extract Metadata.
    2. Complete the resulting dialog.
11. Add a FlatFileWriter component to the graph.
12. Double-click FlatFileWriter to edit its properties:
    • Set a name and location for the target output file.
13. Connect the DatabaseReader output port to the FlatFileWriter input port.
14. Drag the metadata created from the outline and drop it on the connector between DBInput and FlatFileWriter.
15. Choose Save & Run.