CPI with PostgreSQL

[Nagaraj]

Hello All,😎

What is in this blog?

In this blog, I am going to show how to insert multiple records into the PostgreSQL database from CPI using the JDBC driver. Most of the blogs I have seen in the SAP community use Microsoft SQL Server. That's why I chose another database.

The flow will be

1. Postman will call the CPI rest-based web service with a JSON payload.
2. The Groovy script modifies the JSON payload into the appropriate query format required for PostgreSQL.
3. CPI will establish the connection with PostgreSQL and insert the records.

PostgreSQL Setup

I am using PostgreSQL 15.

I will not be explaining in depth; if you have any queries, let me know. I am glad to clear the query.

Table

Function Code

CREATE OR REPLACE FUNCTION public.create_data(
	inp input[])
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
in_1 input;
begin
foreach in_1 in array inp
loop
  INSERT into public."EMPLOYEE"(empid,empname)
  VALUES("in_1".i_empid,"in_1".i_empname);
end loop;

RETURN TRUE;

end;
$BODY$;

(If there is a duplicate key, none of the records are inserted )

Record Insertion

Record Insertion Failure Case

Cloud Connector Setup:

The Database is running on my local machine port 5432. We have to use TCP protocol to connect with database.

 CPI Setup:

JDBC Setup

1.Upload JDBC Driver (Use the same version 8.2.2 driver; I got stuck in an error for two days and found that the reason is that I am using the 8.4 version driver 😐)

2. Create a JDBC data source (you have to use a virtual host and port in the JDBC URL; CPI_TEST is the database name).

 I-flow Development

1. Groovy Script to Convert JSON Format into an Appropriate Query Format

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
import groovy.json.*;

def Message processData(Message message) {
// Delcaration
 def jsonSlurper = new JsonSlurper();
 def first_time = 'X'
def query = "SELECT public.create_data(ARRAY["
 //Get Body
 def body = message.getBody(java.lang.String) as String;
 // Parse Json
 json_obj = jsonSlurper.parseText(body);
 json_obj = json_obj.employee_details;
 //Loop Json Array and construct the string
 for(item in json_obj){
    if(first_time == 'X')
    {
            query = query + "("+item.employee_id+","+ "'"+item.employee_name+"')"
    }
    else
    {
      query = query + ",("+item.employee_id+","+ "'"+item.employee_name+"')"

    }
    first_time = ''
   
}
query = query + "]::input[])"

 message.setBody(query);
   
    return message;
}

Here is the attached image showing how this code transforms JSON into a query.

2. Connect to the database and insert records.

  I-flow Output

 

Conclusion:

In this blog, I show how to connect to the PostgreSQL database and insert the records. In the same way, you can read, modify, and delete.  This blog is purely created for learning purposes. If you are doing it for a real-time PostgreSQL database, ensure you are following adhered-to standards and practices.

Thanks. If you have any queries, comments, or feedback, kindly comment.