Database and Third-Party Integrations
  • 22 Apr 2022
  • 10 Minutes to read
  • Contributors
  • Dark
    Light

Database and Third-Party Integrations

  • Dark
    Light

The following databases can be accessed through the Integration Builder:

  1. EdgeReady Database
  2. External Database,
  3. Salesforce Database
  4. SAP Database
  5. SnowFlakes Database  

The Data can be fetched from their respective platforms through the different lanes. While the EdgeReady Database is our own it can be accessed directly from the On-cloud lane. The others are 3rd party databases and are accessed from the plugin lane. All the data fetched are stored in the EdgeReady Drive

EdgeReady Database

For the EdgeReady Database, 5 actions can be performed in the Integration builder. These actions are done using variable mapping by inserting necessary input and output variables in the "where" and "to" spaces.



DB OperationDB Operation on Canvas

Select Data: 

It is a simple SQL statement block to select data from a database table with or without a where condition. The data can be sorted and stored in a variable(table)

The user provides the following details:

  • The data can be fetched by selecting the table and the required columns.
  • The where condition can be specified by selecting the required columns from the drop down list. 
  • The corresponding Inequality Operation is selected.
  • The condition can be hard-coded in the oval box or an input variable can be added.
  • The sort by field and order is specified (either ascending or descending).
  • The resultant data is stored in the output variable that is dragged in the "to" oval box.
  • Variable mapping is done by clicking the variable mapping icon (highlighted in the image).
     
Note

For more information on input and output variables, refer Input, Output and Local Variables section.

     


ERdb-selectblock

Create Data: 

It is a SQL statement block to create a new table with the required columns.

The user provides the following details:

  • In the Create drop down list the user selects the table from where the data is taken.
  • The number of columns can be appended based on the data.
  • More columns can be added by hovering the cursor over the oval block that reveals a + button. 
  • The column value can be hard-coded or an input variable can be added in the oval box.
  • Additionally, the user can add an Inequality operation or a function in the oval box if required.
  • The user adds an output variable in the "to" oval box, where the new table is stored.
ERdb-CreateAdding a Mathematical Operation or a Function:
ER-db-create-operator-function

Update Data:

It is a SQL statement block to update existing data within a table.

The user provides the following details:

  • Table name is selected.
  • The column to be updated is selected in SET. The column value can be hard coded or an input variable is added.
  • Additionally, the user can add an Inequality operation(=,>=,<=,>,<) or a function in the oval box to make modifications to the new data if required.
  • More columns can be added by hovering the cursor over the block that reveals a + button.
  • The Where condition is specified. It is hardcoded or an input variable can be added. An inequality operation or a function can be added as well.
ERdb-update

Delete Data:

It is a SQL statement block to delete existing data within a table.

The user provides the following details:

  • The user selects the existing table name from the Delete dropdown list
  • The Where condition is specified. It is hardcoded or an input variable can be added.
  • Additionally, the user can add an Inequality operation or a function in the oval box if required.
  • More columns can be added by hovering the cursor over the block that reveals a + button. 
ERdb-delete

Prepared Statements

It is a feature used to execute same or similar SQL statements repeatedly with high efficiency.

Uses:

  • The queries can be written for all types of statements as a part of executing the BOS and gives the table data as output. 
  • Writing queries offers more flexibility while joining multiple tables.
The user provides the following details:
A SQL query in the Prepared Statement oval box
The required Parameters are added.
An output variable is added in the "to" oval box.
Click the Variable mapping icon to map the columns if required.

wip

 

Third Party Servers - (External Database, SAP, and Salesforce): 


The Edgeready platform supports the extraction of data from the following third party servers:

  • External Database
  • Salesforce Database
  • SAP database
  • Snowflakes Database

The configuration of plugins and adapters for the third party servers with the Edgeready platform is done in the Administration->Integrations tab. Once configured, the data can be accessed through the relevant plugin and adapter in the Integration builder for any logical operations.

For example: The user needs to fetch data from the SAP database, process it and store it in the local Edgeready database. In this scenario, the user inserts the plugin lane block (that holds the Plugin and adaptor information)within the On-cloud lane block, and within the plugin lane fits the required action block for data manipulation that is executed on the SAP data. The output can be stored in the Edgeready database by inserting the required blocks in the On-cloud lane.


External Database:

External database refers to any SQL database that the user can integrate with the Edgeready platform. Currently, the user can access the MySQL, Oracle and MSSQL databases.

  • For external databases, the action blocks are added to the Plugin lane. It requests user credentials (selection of plugin name and adapter name) in the Plugin lane.
  • When the plugin and adapter are selected by the user, the username is displayed in the first oval box and the user is prompted to enter the password in the second oval box.
  • The external database consists of the SelectCreateUpdateDeleteand Prepared actions blocksThe action and functionalities are the same as in the EdgeReady DB.
  • Similar to EdgeReady DB, these actions are done using variable mapping by inserting necessary input and output variables in the "where" and "to" spaces provided.
 

External Database Thumbnail 

External Database On Canvas Operations 


Extdb-thumbnail






ExtDB-alloperations


Salesforce Database

The Salesforce database does not require a plugin but the salesforce system type is configured in the Administration->Integrations tab by adding a New Adapter. The adapter information is then selected by the user in the Integration builder to access the salesforce data and manipulate it with the action blocksthat are available for salesforce database

  • The user selects the adapter  from the dropdown list and the username configured for the selection is generated in the first oval box. The user then enters the corresponding password in the second oval box.
  • There are 4 actions that can be performed in the Integration Builder. The options are List, CreateUpdate, & Delete
  • The action and functionalities for Create, Update & Delete are the same as in the EdgeReady DB.
  • The List helps to retrieve a list of data items rather than a single file or folder. It is similar to a SELECT query.
  • Like in the EdgeReady DB and external DB, these actions are done using variable mapping by inserting necessary input and output variables in the "where" and "to" spaces that are provided.

Salesforce Thumbnail and on Canvas operations


Salesforce All Operations in the on-cloud lane


Salesforce-thumbnail


Enter the user access password in the second oval box

Salesforce-credentialboxes



Salesforce-alloperations

SAP

The SAP database requires a plugin and adapter to be configured in the Administration->Integrations tab. For servers like Edgeready, Salesforce, the data retrieval is query-related. Whereas, in the SAP server, data is fetched using Functions and not queries. 

The data is not retrieved in the form of tables. They are fetched in Key-value pairs that are in the form of transactions. 

The SAP action blocks are: 

  • BAPI/RFC
  • Commit
  • Rollback
  • Start Transaction
  •  Stop Transaction
SAPthumbnail

There is a select plugin option for the SAP block. The plugin is mandatory for SAP blocks. All the SAP operations are carried out in the plugin lane. They are explained below.

SAP action blocksSAP Operation on canvas

BAPI / RFC

It is a standard application interface that enables the Edgeready server to access the SAP database.

The user provides the following details:

  • When the plugin and adapter are selected by the user, the username is displayed in the first oval box and the user is prompted to enter the password in the second oval box.
  • Select Yes/No for Include Non-Remote FM
  • Select the required function from the BAPI/RFC dropdown list. 
  • Select the Input and Output parameters from the respective dropdown lists.
  • Drag and drop the input and output variables in the oval boxes.

 


SAP-BAPI

Commit

Commit helps to fetch data and perform certain operations to it. It helps to conclude a database and start a new one.


SAP-commit1

Roll Back


Rollback helps to restore values to previous values by negating recent changes. It undoes all changes and restores the database and updates.  


SAP-Rollback1

Start/Stop Transaction: 


A function call can be started or stopped using the Start/Stop Transaction Blocks.


     
Note
Both Start and Stop transactions can be done after either commit or rollback operations. All SAP actions follow a logical destination path.
 



SAP-start-stop transaction-new


ADDITIONAL INFORMATION
  • Business Application Programming Interface (BAPI) is used in mySAP to achieve business-related functionalities.
  •  It is a remote-enabled functional module provided by SAP, that enables stable and standardized methods to achieve seamless integration between the R/3 system and external applications (legacy systems and add ons).
  • BAPIs are implemented as RFC-enabled function modules and are created in the Function Builder of the ABAP Workbench.
  •  RFC is the protocol used by SAP for remote communications (between SAP & non SAP) as well as communication between two independent systems(non SAP) or modules of the same system. 

EXAMPLE: How does a typical SAP block function in real-time?

  • For a given function, the plugin and BOS are selected and credentials are entered. 
  • BAPI/RFC is included for non-remote FM and the type of BAPI/RFC is selected. 
  • The respective input and output variables with their functional properties and values (table column values) are dragged and dropped. 
  • The values are parsed into the respective input and output columns of the table. 

BAPI/RFC:

SAP-BAPI

SnowFlakes Database

The Snowflakes is a Datawarehousing database that holds structured and semi-structured data. While accessing it from the Edgeready DB it does not require a plugin but the system type is configured in the Administration->Integrations tab by adding a New Adapter. While configuring the adapter, the databases that are available to the user in snowlflakes are listed and the user can choose any one. This database is available for selection in the Integration builder while working with the Snowflakes action blocks

  • The data in Snowflakes database are organized into schemas which in turn are organized into tables.
  • There are 4 actions that can be performed for snowflakes in the Integration Builder. The options are SelectCreate, Update, & Delete
  • The action and functionalities for Create, Update & Delete are the same as in the EdgeReady DB

FTP Block:


FTP helps transferring files from a particular source to a specified destination. This is done by specifying the URL path. 

Description

FTP Block  Thumbnail and On Canvas

The operations that can be done to the files in the destination are Upload, Download, List, Copy, Move, Delete. 


Transfer Logic:

sftp <<logical destination>><<operation>>


Source:  <<location>>

Destination:  <<location>>


Source: Location could be file /folder at Source (FM / Tenant Source System)

Destination: Location could be file /folder at Destination (FM / Tenant Source System)




Service Block & Sharepoint:


These are services provided to the 3rd party servers. They work both in on-cloud and Plugin lanes. 

Description

Services Block on Canvas

Web Service:

SOAP (SAP Web Services): They provide web services to the SAP servers. They select relevant input/output and provide methods to retrieve data from the Drive. 


Rest Services: They select relevant input/output and URL access to retrieve data from the Drive. 


They are available both on-cloud and Plugin lanes. 


Input:

<<input parameter-type>> <<parameter-name>> <<parameter-value>> +

Output:

<<parameter-name>> <<parameter-value>>


Sharepoint: 


Similar to the rest services in SAP, the rest services in share point also request input/output parameters with respect to the URL for fetching data. They are available both on-cloud and Plugin lanes. 


Logical Path:

Rest Service <<Logical destination>> <<userId>> <password>> (optional)


Input:

<<input parameter-type>> <<parameter-name>> <<parameter-value>> +


Output:

<<parameter-name>> <<parameter-value>>

The input and output parameters can be selected by clicking the select options near the input and output block. 

Input: postParams, queryParams, headers, filePath, needResponseType


Output: status, responseString, responseXML



Sharepoint only provides Rest Services



The Input params are:

 


The Output params are: 

NOTE
These params are also available for SAP web services. 

Business Users:


Refers to the users of the App created in the platform. Refer to Users & Roles section for more information. 

Business User Description

Business User Blocks on Canvas

This block is used to regulate the number of users who are logged into the App. They are nothing but Roles assigned to particular business functions for a project. 


  1. Create User: Helps to create new users. Requests for login ID, first name, user name, email, title, and path. 
  2. Update User: Helps to make changes to the existing user profile. Requests for User ID, first name, last name, email, title. 
  3. Add Role: Gives a particular role for a user.  Requests for username and role selection.
NOTE
  • The block by default has required parameters and additional parameters can be added as needed
  • All the parameters on the right-hand side are required
  • For the update user option, one should add the parameters which need to be modified/updated.




Was this article helpful?