Connector Builder Guide – Creating a Database Connector

Connector Builder Guide – Creating a Database Connector

Open Connectors consists of over 170 connectors to 3rd Party Applications natively embedded in the SAP ecosystem. If the Connector you need does not exist in the catalog, you can build it quickly with Connector Builder. This instruction guide will walk you through using this tool, with concrete examples throughout. The beginning, Part 1, can be found here.

When creating a Database Connector, we want to adhere to the Restful normalization of the API. The goal will be to enter in a JSON body and have the Connector convert that to a SQL query string. The first step will be to choose Database as the service type on the Information Screen. We’ll use AWS Redshift as an example:

Moving to the Setup Screen, let’s take the JDBC URL and enter it in the Properties. You’ll also notice the Authentication Type is automatically set to Custom.

The db.host and db.name are variables since they are dynamic. We will want to ask for those from the user upon creating a Connector Instance. We can do that by creating configuration variables for db.host and db.name.

Of the other default configuration variables that you see above, you will need to choose the Driver Classname. In this case it is org.postgresql.Driver.

Let’s now define the parameters so that our Base URL (which is now the JDBC URL) can be properly populated.

This process of populating a Base URL using configuration variables is explained in further detail here.

Let’s leave the Hook empty for now. If you’d like to add Events, polling would be the choice as Redshift does not support webhooks.

Now let’s proceed to the Resources Screen and add a POST resource.

Let’s say we have a table called “account” – we’ll add that as the resource name when creating the resource. The body parameter is automatically added when you have a POST resource.

In the prehook, we’ll transform a JSON body to a query string that the database can understand.

let body = request_body_map;
let colA = [];
let valA = [];
let col = "";
let val = "";

for (var i in body) {
  colA.push(i);
  valA.push("'"+body[i]+"'");
}

col = colA.join(',');
val = valA.join(',');

let query = "INSERT INTO account (" + col + ") VALUES (" + val + ");";
console.log(query);

done({"request_vendor_path":query,
  "continue":true
     });

The PreRequest Hook guide goes into more detail about hooks.

Now, you can post a JSON body that represents the column headers (properties) and the values (values).

{
  "column1": "value1",
  "column2": "value2"
}

This is all you need to get you up and running with a new Database Connector. All that is left is to create an Instance:

To fully realize the features on both screens, please refer to the complete guides for the Setup screen and the Resources screen.

Happy Building!