Access JDBC-ODBC Bridge Data in Mule Applications Using the CData JDBC Driver

The CData JDBC-ODBC Bridge Driver connects JDBC-ODBC Bridge data to Mule applications enabling read , write, update, and delete functionality with familiar SQL queries. The JDBC Driver allows users to easily create Mule applications to backup, transform, report, and analyze JDBC-ODBC Bridge data.

This article demonstrates how to use the CData JDBC-ODBC Bridge Driver inside of a Mule project to create a Web interface for JDBC-ODBC Bridge data. The application created allows you to request JDBC-ODBC Bridge data using an HTTP request and have the results returned as JSON. The exact same procedure outlined below can be used with any CData JDBC Driver to create a Web interface for the hundreds of available data sources.

1. Create a new Mule Project in Anypoint Studio.
2. Add an HTTP Connector to the Message Flow.
3. Configure the address for the HTTP Connector. 👁 Add and Configure the HTTP Connector
   
4. Add a Database Select Connector to the same flow, after the HTTP Connector.
5. Create a new Connection (or edit an existing one) and configure the properties.
   • Set Connection to "Generic Connection"
   • Select the CData JDBC Driver JAR file in the Required Libraries section (e.g. cdata.jdbc.jdbcodbc.jar). 👁 Adding the JAR file (Salesforce is shown).
     
   • Set the URL to the connection string for JDBC-ODBC Bridge To connect to an ODBC data source, specify either the DSN (data source name) or specify an ODBC connection string: Set Driver and the connection properties for your ODBC driver.

     Built-in Connection String Designer

     For assistance in constructing the JDBC URL, use the connection string designer built into the JDBC-ODBC Bridge JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

     		java -jar cdata.jdbc.jdbcodbc.jar
     		

     Fill in the connection properties and copy the connection string to the clipboard.

   • Set the Driver class name to cdata.jdbc.jdbcodbc.JDBCODBCDriver. 👁 A configured Database Connection (Salesforce is shown).
     
   • Click Test Connection.
6. Set the SQL Query Text to a SQL query to request JDBC-ODBC Bridge data. For example:

   SELECT Id, Name FROM Account WHERE Id = '1'

   👁 Configure the Select object (Salesforce is Shown)
   
7. Add a Transform Message Component to the flow.
8. Set the Output script to the following to convert the payload to JSON:

   %dw 2.0
   output application/json
   ---
   payload
    

   👁 Add the Transform Message Component to the Flow
   
9. To view your JDBC-ODBC Bridge data, navigate to the address you configured for the HTTP Connector (localhost:8081 by default): http://localhost:8081. The JDBC-ODBC Bridge data is available as JSON in your Web browser and any other tools capable of consuming JSON endpoints.

At this point, you have a simple Web interface for working with JDBC-ODBC Bridge data (as JSON data) in custom apps and a wide variety of BI, reporting, and ETL tools. Download a free, 30 day trial of the JDBC Driver for JDBC-ODBC Bridge and see the CData difference in your Mule Applications today.