Connecting to a relational database using JDBC in Azure Logic Apps (Standard)

[siddharth]

1. What is JDBC?

Java Database Connectivity is an API for the programming language Java, which defines how a client may access a database. JDBC APIs can be used to connect to most of the Relational Database (RDB). Some of the supported RDBs include MS SQL Server, AWS Aurora, My SQL, Snowflake etc.

2. Why JDBC?

1. JDBC Connector in Logic App allows you to connect to a RDB for multiple use cases:

   1. Like replicating data from your source of truth database to any warehouse database for analytical purposes.

   2. IoT use cases to dump event data to your database.

   3. etc...

2. Since JDBC works on plug and play model you can bring in JDBC drivers of any JDBC supported RDB and start using it, without the need of native DB support in Logic Apps.

3. JDBC is a built-in connector that allows you to connect to your databases without any Data Gateways.

3. How to use JDBC in Logic Apps?

3.1. Pre requisites:

1. Create a Logic App: Create Standard workflows in single-tenant Azure Logic Apps with the Azure portal
2. Enable Multi Language Support: Multi-Language support is currently under feature flag, and will be supported by Q1 2023. For now, it can be enable by setting following configuration:

   {
     "name": "AzureWebJobsFeatureFlags",
     "value": "EnableMultiLanguageWorker",
     "slotSetting": false
   }

   

3. JDBC Jar Libraries: All JDBC JAR Libraries needs to be uploaded to site/wwwroot/lib/builtinOperationSdks/JAR/* using kudu tool.

   1. Open Kudu tool:
      
   2. Navigate to  site/wwwroot :
      
   3. Create Directory lib/builtinOperationSdks/JAR/:
      
   4. Upload all your driver JARs to site/wwwroot/lib/builtinOperationSdks/JAR/*:
      

3.2. Using JDBC Connector:

1. Create a workflow with JDBC Connector: Create Standard workflows in single-tenant Azure Logic Apps with the Azure portal
   
2. Add JDBC Connections:
   
3. Run Trigger to See Execution Output: JDBC Currently supports 3 actions:
   1. Get Tables: Gets list of tables from the connected database.
      
   2. Get Table Schema: Get schema for the table.
      
   3. Execute Query: Execute raw query on the connected database.
      
      

4. More Details and References:

• Contributions were made in Azure Functions to support multiple languages in single runtime: Azure Functions 3.x and Azure Functions 4.x