MuleSoft: Connect to a Flat File

In order to modernize the legacy application, Max decided to provide an API to extend its capabilities. This application was designed to store the data in a flat file. As initially planned, he created an API to retrieve data and display it in a simple form.

Unfortunately, this simple plan did not run as expected. Although he has spent a lot of time researching, joining developer forums, reading documentation and articles, he is still unable to connect to a flat file. Most resources give a reference to connect to a common relational database.

Connecting to a Flat File

Basically, MuleSoft provides a database connector to connect to any database, which allows JDBC connectivity. By using this connector, we can execute any SQL operations: insert, select, update, delete, or even store procedures.

By default, MuleSoft only provides out-of-the-box connectivity with Oracle, MySQL, and Derby databases. To connect to other databases, we should use a Generic Database Configuration. Therefore, in this article we will try to connect to a flat file, Microsoft Access Database (*.mdb, *.accdb).

First, we have to prepare the database. For example, a contact database, with simple fields and values.

Then, create an empty Mule project to access the data:

1. Drag and drop the HTTP connector.
2. Complete the Connector Configuration setting.
   • Click +.
   • Leave all default values.
   • Click the OK button.
3. Complete the Basic Settings
   • Set the Field with /access.
   • Set the Allowed Method with GET.
4. Drag and drop the Database connector.
5. Complete the Connector Configuration setting
   • Click +.
   • Select Generic Database Configuration and click OK (double-click).
   • Set the Name with MSAccess_Database_Configuration.
   • Select the Database URL option and set the URL with jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Access\Contacts.accdb;.
   • Set the Driver Class Name with sun.jdbc.odbc.JdbcOdbcDriver.
6. Click Test Connection… to ensure the configuration is correct

Notes:

An error occurs if you are using JDK 8: Unable to find type 'sun.jdbc.odbc.JdbcOdbcDriver' on build path of project access. That's because the JDBC-ODBC bridge is no longer supported by JDK 8. Oracle recommends that you use JDBC drivers provided by the vendor of your database instead of the JDBC-ODBC Bridge.

Therefore, there are two options to solve this issue:

1. Use the library from an older JDK, e.g.: C:\Program Files (x86)\Java\jre1.6.0_26\lib\rt.jar, and add it to the build path.
   • Right-click on Project and choose Build Path >> Configure Build Path...
   • Open the Libraries tab.
   • Click Add External JARs…
2. Use UcanaccessDriver library
   • Similar with the steps above, add all required libraries: commons-lang-2.6, commons-logging-1.1.1, hsqldb, jackcess-2.0.8, ucanaccess-2.0.9.4.
   • Change the URL to: jdbc:ucanaccess://C:\\Access\\Contacts.accdb;.
   • Change the Driver Class Name to: net.ucanaccess.jdbc.UcanaccessDriver.

There is one other possible issue. Hence we have to install the Microsoft Access Database Engine (search for the appropriate engine. In this example, I use Microsoft Access 2013 and JDK 32-bit. A Windows restart might be needed).

Once configuration is completed and no problems have occurred, then continue to the next step:

1. Click OK to finish the configuration.
2. Go back to the Database connector properties and set the Operation with Select.
3. Fill the Parameterized query with an SQL query: SELECT * FROM Contacts.
4. Drag and drop Object to String transformer at the end of this flow.
5. Finally, run the Mule application and test.

Now we can connect to a flat file and retrieve its value. We can evaluate it through Mule Debugger, and eventually, the following result will be displayed in the browser: [{ADDRESS=SF, NAME=Max, ID=1}, {ADDRESS=ID, NAME=Sulthon, ID=2}].

However, the current implementation is too simple. It retrieves all values without any constraint. Next, we will try to retrieve it dynamically based on user input. To make it easier, we will send the request through http-query-string. The expected result is a specific data based on the request. For example, retrieve a contact based on ID.

1. Modify the Parameterized query value by adding a constraint: SELECT * FROM Contacts WHERE ID = #[message.inboundProperties.'http.query.params'.id];
2. Save and re-run the application.
3. Then execute the URL with a query string: http://localhost:8081/access?id=1.
4. Finally, we can get the specific result based on user input, e.g.: [{ADDRESS=SF, NAME=Max, ID=1}].

Although a specific MS Access connector is not provided, we are still able to execute the SQL operation by using a Generic Database Configuration. So we can conclude that Mule is eligible for Legacy Modernization purpose. So, let us extend our capabilities by enhancing legacy application!