Connecting to a relational database using JDBC in Azure Logic Apps (Standard)
Published Nov 25 2022 06:12 AM 1,577 Views
Microsoft

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
    }
    Screenshot 2022-11-22 211219.jpg
  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:
      Screenshot 2022-11-22 211409.jpg
    2. Navigate to  site/wwwroot :
      Screenshot 2022-11-22 211917.jpg
    3. Create Directory lib/builtinOperationSdks/JAR/:
      Screenshot 2022-11-22 212405.jpg
    4. Upload all your driver JARs to site/wwwroot/lib/builtinOperationSdks/JAR/*:
      Screenshot 2022-11-22 212648.jpg

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
    Screenshot 2022-11-22 213545.jpg
  2. Add JDBC Connections:
    Screenshot 2022-11-22 214054.jpg
  3. Run Trigger to See Execution Output: JDBC Currently supports 3 actions:
    1. Get Tables: Gets list of tables from the connected database.
      Screenshot 2022-11-22 214712.jpg
    2. Get Table Schema: Get schema for the table.
      Screenshot 2022-11-22 215007.jpg
    3. Execute Query: Execute raw query on the connected database.
      Screenshot 2022-11-22 215326.jpg

4. More Details and References:

Co-Authors
Version history
Last update:
‎Nov 22 2022 08:45 AM
Updated by: