Configure PostgreSQL Connection in Logic Apps (Standard) with JDBC, call Function with Execute Query

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Omar_Abu_Arisheh_21-1680301476039.png*

 

Many are using relational databases, the JDBC Connector for Logic Apps (Standard) allows to establish a connection with most relational databases and perform actions. We are exploring here, step-by-step, how to create an Azure PostgreSQL, configure the connection with all the requirements, then call a function and use the response in the body of an email to be sent.

 

Here is a step-by-step for creating the PostgreSQL in Azure, configuring the connection and other requirements, then creating the Logic App (Standard) workflow, add the actions, and test a function, finally parse the response and send it by email.

 

  • Create a PostgreSQL on Azure, search for PostgreSQL in the search box in Azure Portal, then select “Azure Database for PostgreSQL”, the cost will depend on the VM resources you select.

Omar_Abu_Arisheh_0-1680301019450.png

 

           

 

  • Once you create the server, browse to the Connection strings and copy the one for JDBC, this will be used later.

Omar_Abu_Arisheh_1-1680301019453.png

 

 

 

  • I downloaded the pgAdmin to be able to create the database, tables, enter some data, and create the function that we will be calling: PostgreSQL: File Browser
  • I changed the below settings, as I was able to connect from pgAdmin using the Certificate, but not from Logic Apps.

Omar_Abu_Arisheh_2-1680301019459.png

 

Omar_Abu_Arisheh_3-1680301019464.png

 

  • Under Connection, enter the server URL, full username@servername:

Omar_Abu_Arisheh_4-1680301019472.png

 

  • Under parameters, use sslmode “verify-full”, if you disabled the SSL/TLS in PostgreSQL server you can select “disable”.
  • Use the Certificate mentioned earlier if using SSL/TLS.

Omar_Abu_Arisheh_5-1680301019480.png

 

  • After connecting successfully, you can create Tables, insert data, and create functions:

Omar_Abu_Arisheh_6-1680301019484.png

           

Omar_Abu_Arisheh_7-1680301019490.png

 

Omar_Abu_Arisheh_8-1680301019492.png

 

  • You can use the portal:

Omar_Abu_Arisheh_9-1680301019496.png

 

  • You can use Microsoft Azure Storage Explorer as well:

Omar_Abu_Arisheh_10-1680301019503.png

 

 

Omar_Abu_Arisheh_11-1680301019506.png

 

  • If you don’t know which storage to upload to, browse to the Logic App Configurations, and find the storage name:

Omar_Abu_Arisheh_12-1680301019508.png

 

  • Under the Logic App Configuration, create a new Application setting, this will allow JDBC Connector use the JAR library we uploaded:
  • Name: AzureWebJobsFeatureFlags
  • Value: EnableMultiLanguageWorker

Omar_Abu_Arisheh_13-1680301019515.png

 

  • We then add a new action, from the Connectors search, under buit-in Connectors, select JDBC, Execute Query action.
  • To create a new connection, use the URL we got earlier from Postgresql Server, enter the username, and password:

Omar_Abu_Arisheh_14-1680301019516.png

 

    • You can edit these values later from the Logic App Configuration (App Settings).
    • The URL should look like this: jdbc:postgresql://mypostgresqlserver.postgres.database.azure.com:5432/postgres?user=myuser@mypostgresqlserver&password=mypassword@&sslmode=disable

Omar_Abu_Arisheh_15-1680301019523.png

 

  • For the action, you can either start with a simple select statement on one of your tables, or instead of executing a query you can use Get tables action.
  • For our example, we are calling a Function and passing a parameter value of 1, “SELECT demo_function(1)”

Omar_Abu_Arisheh_16-1680301019526.png

 

  • In pgAdmin, create this Function (assuming we have a table called mydata with two columns, data1 (bigint) and data2(text)):

 

CREATE or replace FUNCTION demo_function(in mydata1 bigint)

RETURNS text AS $mydata2$

DECLARE

mydata2 text;

begin

    select data2 from mydata where data1 = mydata1 limit 1 into mydata2;

RETURN mydata2;

end

$mydata2$ language plpgsql

 

  • Fill the query in the action:

Omar_Abu_Arisheh_17-1680301019527.png

 

  • Save your workflow, and on the workflow overview; execute the trigger, you should have the response as below:

Omar_Abu_Arisheh_18-1680301019529.png

 

  • We can now use the result from the execute query and send an email for example.
  • Add a Send an email action (Connector: Outlook 365).
  • And to parse the output refer to this reference if needed: Reference guide for expression functions - Azure Logic Apps | Microsoft Learn
  • As the response is inside an array, I can either put the Send an email action in a for-each or just get the first item (this will throw an error if there is no items in the response of the previous action).

body('Execute_query_2')[0]?['demo_function']

 

Omar_Abu_Arisheh_19-1680301019532.png

 

  • Now will test the workflow again, and receive an email with the data.

Omar_Abu_Arisheh_20-1680301019536.png

 

Thank you :)

 

*PostgreSQL and JDBC logos are sole property of their respective owners.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.