Introduction

The conceptualization of a new Database Adapter on Agent V3 is the same as JDBC Adapter on the old Agent. It used as a connector to transfer data from Salesforce to Database and from Database to Salesforce. Any Connect or Agent V3 currently supports many types of Databases such as Oracle, SQL Server, MySQL, and PostgreSQL.

The user will learn how to send data in multiple objects from Salesforce to the multi-table in the database synchronously and asynchronously through Agent V3 or Any Connect.

The user will know about Transactional and Non-Transaction mode during processing. Creating an adapter, called Database Adapter within Internal Key Management for insert a record to the database. MYSQL Database is used for example below to Insert a record to the database and it located on EC2 instance.

Pre-required

• • Create Integration and configuration on the new Agent Control Board
  • Create database adapter (for outbound and inbound) on new Agent Control Board
  • Create Metadata Provider, Repository and generate Message Type from the database
  • Create outbound Interface (Request) and inbound Interface (Response)
  • Checking Cache monitoring status
  • Callout V3 to new Agent
  • Transactional and None-Transactional mode

Case1

–  How to do insert operation using a Synchronous and Asynchronous outbound interface?

Since the user now completely understand; How to use the Insert Operation, by hitting the upper link.

          – What is an Integration and How to create it?

     Since the user now completely understand; How to create Integration, by hitting the upper link.

Step2: Click on Agent Control Board Tab

  –How to use the Agent Control Board?

Since the user now completely understand; How to use the Agent Control Board, by hitting the upper link, now we understand the Architect of the Agent Control Board now follows below on Salesforce org.

Step-3: Create an outbound Database Adapter on the new Agent Control Board

• • • Fill all required field
      • Name: any name
      • Type: Agent Database
      • Direction: Outbound
      • Status: Active
      • Database Type: MySQL
      • Driver: mysql.jdbc.Driver
      • Username: (your user name on database)
      • Password: (your password on the database)
      • Hostname: 124.40.1949 (public ipv4 on EC2 AWS)
      • Port: 3306(default)
      • Database Name: your database name
      • Insert Operation Mode: Internal Key Management
      • Connection URI: jdbc:mysql://3.124.40.194:3306/testdb (connection URL format is protocol//[hosts][/database])
      • Connection Destination: your destination name

      *** on EC2, make sure you have allowed MYSQL port 3306 on Group Policy for connectivity from other systems.

      

Step-4 Generate Message Type from the database

–  How to create a message type?

Since the user now completely understand; How to create a message type, by hitting the upper link.

• • • Fill all required field then click Retrieve Database to get the Table’s record
      • Agent Instance: your instance name
      • Adapter Name: your adapter name
      • Object Type: Table, View, Procedure
      • Database Operation: Select (database’s template will be generated based on operation)
    • Choose Table’s name for creating Message Type
    • After that, we got the Message Type generated from the database
    • Here the example for the Table called: “tbl_account_db_atn”

Step-5 Setup Interface

• • • For creating the Interface need to follow this guide.

– What is an interface and how to create it?

Since the user now completely understand; How to create an Interface, by hitting the upper link.

• • • Define the request Interface

Create outbound Interface for Synchronous mode, operation(insert), choose Adapter, Metadata Provider, Repository, and Message Type (Request)

• • • Define the response Interface

We need to define a response Interface because of INSERT operation within the Internal Key Management mode, the value of the Primary Key did not send from Salesforce, it is auto-generated by the database. In this case, Agent will return the Id (Primary Key) to Salesforce. The user can take this response to do Inbound V3. For example, the user can update the existing Account’s object to make sure the record on Salesforce and database, are the same.

• • • Create inbound Interface with Synchronous mode, operation(update), choose Metadata Provider, Repository, and Message Type (Response)
    • In the example below, we use the response from Agent to update the existing sObject
    • Mapping

Adding Response Interface (Inbound) to Request Interface (Outbound)

• • • Go to the Request Interface details page then add Response Interface on-field Response Interface. In this example, the Request interface was named “MYSQL_Database_InternalKEY_Insert_SYNC_multiple table” and the Response interface is “Response Insert SYNC”

Step- 6: Checking Cache record to ensure it synchronized to new Agent.

Go to Agent control board then click Cache Monitor

– How to use Cache Monitoring on Agent Control Board?

Since the user now completely understand; How to use Cache Monitoring , by hitting the upper link.

Step-7:  Callout v3

• • • Navigate to Setup gear icon => Developer Console => Debug menu => Open Execute Anonymous Window then put sample code below and click the Execute button.
      

      
      skyvvasolutions.CallOutControl c = new skyvvasolutions.CallOutControl();
      
      c.returnJSONComplete=true;
      
      c.actionDoIntegrate=true;
      
      c.isCreateMessage=true;
      
      String[] ids= new String[]{‘0014E00001C8fAwQAJ’};//Account Id
      
      skyvvasolutions.Iservices.invokeCalloutV3(‘Test MYSQL-Database Integration’,’MYSQL_Database_InternalKEY_Insert_SYNC_multiple table’,ids,’SYNC’,c);
      
      

    • After callout, the record is inserted into the database then its response back to update the relevant object. As shown the result on Message Monitoring below:
      
    • Checking the result on the database, the record has been inserted correctly on the MYSQL database

In this example field “DB_AccountId” is the primary key of Table “tbl_account_db_atn”

• • • field “DB_ContactId” is primary key of Table “tbl_contact_db_atn”

• • • field “DB_CaseId” is primary key of Table “tbl_case_db_atn”

How to do insert operation using an Asynchronous outbound interface?

In this example, we use the same Interface as we have practiced for the Synchronous scenario, just change the processing mode to Asynchronous on Interface then Callout v3.

• • • Request Interface

• • • Response Interface

We create another inbound Database Adapter that connects to the database in as inbound direction then add it to the Response Interface

• • • Callout v3

Navigate to Setup gear icon => Developer Console => Debug menu => Open Execute Anonymous Window then put code for update Account object then click the Execute button.

skyvvasolutions.CallOutControl c = new skyvvasolutions.CallOutControl();

c.returnJSONComplete=true;

c.actionDoIntegrate=true;

c.isCreateMessage=true;

String[] ids= new String[]{‘0011X00000hPU1iQAG’};//Account Id

skyvvasolutions.Iservices.invokeCalloutV3(‘Test MYSQL-Database Integration’,’MYSQL_Database_InternalKEY_Insert_SYNC_multiple table’,ids,’Auto’,c);

After callout, the record is inserted into the database then its response back to update the relevant object. As shown the result on Message Monitoring below:

Insert operation and Transactional mode

Normally, when we creating a new Interface the field “isTransactional” is unchecked it means that Non- Transactional Mode. If that field is checked then it is Transactional mode.

The example below has shown the Non-transactional Mode which is one message is failed to insert.

The example below is used the same message scenario for non-transactional Mode, it just switching to Transactional Mode. In Transactional Mode, when one message is unable to do any database operation then it makes another message which is complete to rollback and marked the whole tree as Failed status.

• • • For Synchronous mode, the Agent will return only the root message status.

• • • For Asynchronous mode, the Agent will return the whole tree message status.

We explain here how to do INSERT operation using Synchronous and Asynchronous outbound interface. We have understood how to create a Database Adapter, generate Message Type from the database table, Primary Key and Foreign Key on Mapping too, Internal Key and External Key Management, response Interface, and callout V3 to the Agent.

Case2

-How to do update operation using an Asynchronous / Synchronous outbound interface?

Since the user now completely understand; How to use the Update operation, by hitting the upper link.

Users can use the same Database Adapter in the previous section as it used for INSERT operation. Now we use that Adapter for UPDATE operation.

We explain here how to do UPDATE operation using a Synchronous and Asynchronous outbound interface. We have understood about Message Type in WHERE-Clause, knowing about putting value in WHERE-Clause on mapping tool, and callout V3 to Agent. And we also have known about Transactional Mode.

Case3

-How to do delete operation using an Asynchronous / Synchronous outbound interface?

Since the user now completely understand; How to use Delete operation, by hitting the upper link.

Users can use the same Database Adapter in the previous section as it used for INSERT operation. Now we use that Adapter for DELETE operation.

We explain here how to do UPDATE operation using a Synchronous and Asynchronous outbound interface. We have understood about message type in WHERE-Clause, knowing about putting value in WHERE-Clause on mapping tool, and callout V3 to Agent. And we also have known about Transactional Mode.

Case4

-How to do Select operation using an Asynchronous / Synchronous outbound interface?

Since the user now completely understand; How to use Select opertaion, by hitting the upper link.

Users can use the same Database Adapter in the previous section as it used for INSERT operation. Now we use that Adapter for QUERY operation.

We explain here how to do select operation QUERY using Synchronous and Asynchronous outbound interface. We have understood about Message Type in WHERE-Clause, knowing about putting value in WHERE condition in mapping tool, Database Query’s field, and callout V3 to Agent.

Case5

–   How to call Stored Procedure on a new Agent?

Since the user now completely understand; How to call Stored Procedure, by hitting the upper link.

Users can use the same Database Adapter in the previous section as it used for call INSERT Procedure. Now we use that Adapter for  Stored operation.

A stored procedure is a set of SQL statements with an assigned name, which are stored in an RDBMS as a group, so it can be reused and shared by multiple programs. Stored procedures can access or modify data in a database, but it is not tied to a specific database or object, which offers a number of advantages.

Users can use the ORACLE database as an example. In here, when we call the Stored Procedure to the Database to do any database operation, we have expected it will respond back the result to the Salesforce. In this case, we need to have the INPUT and OUTPUT parameters in the Stored Procedure and create a Request and Response Interface corresponding to those parameter types. The ORACLE database located on an EC2 instance and the Stored Procedure was written to Insert new data if no record present on the Database and update data if the record exists.

Check the result on Message Monitoring

The Stored Procedure has been executed and response to Salesforce correctly

We call how to call Stored Procedure from Salesforce to the database, and respond to the Salesforce, we have known how the Message type structure generated from the Database.

Agent Database Inbound Adapter?

How to synchronize the data from the database and push it to salesforce. For the database, we can select the data from the table or we can use the Stored Procedure to push the data into Salesforce. On the Salesforce side, We do any operation such as upsert, insert, and update or use the custom class when the business logic is complex that is something available already in the SKYVVA solution. MYSQL database is used for example to retrieve data from the existing records in the database and it located on EC2 instance.

–  How to do synchronize database data using an inbound Interface?

In this case, we need a scheduler to synchronize data from the database into Salesforce. The scheduler will fetch the database record based on the value put on Database Query in Interface.

Step-1: Create Integration and test connection destination.

Create new Integration by following the guide, Link is given below.

https://apsara-consulting.com/docs/tutorial-v2-41-lightning/what-is-an-integration-and-how-to-create-it/

Step-2: Create an Inbound Database Adapter on the new Agent Control Board

• Navigate to the Adapter tab after that click the button New

• Fill all required field
• Name: any name
• Type: Agent Database
• Direction: Inbound
• Status: Active
• Database Type: MySQL
• Driver: mysql.jdbc.Driver
• Username: (your user name on database)
• Password: (your password on database)
• Hostname: 124.40.1949 (public ipv4 on EC2 AWS)
• Port: 3306(default)
• Database Name: your database name
• Insert Operation Mode: Internal Key Management
• Connection URI: jdbc:mysql://3.124.40.194:3306/testdb (connection url format is protocol//[hosts][/database])
• Connection Destination: your destination name

*** on EC2, make sure you have allowed MYSQL port 3306 on Group Policy for connectivity from other system.

• As a result of message monitoring, the record has been sent to Salesforce correctly via Scheduler.