Introduction #
This tutorial will guide you on how to send data in multiple objects from Salesforce to the multi-table in the database synchronously and asynchronously through Agent V3 to Select record from the database using an outbound Interface. MYSQL Database is used for example to retrieve data from the existing records in the database and it located on EC2 instance.
Pre-required #
- Create a Repository and generate Message Type from the database
- Modify message type for adding a field in WHERE-Clause
- Using Mapping tool in WHERE condition
- Checking Cache monitoring status
- Callout v3 to new Agent
How to Select operation using a Synchronous outbound interface? #
In this example, we use the same Database Adapter in the previous section as it used for INSERT operation. Now we use that Adapter with SELECT operation.
Step 1: create another Repository for Select operation and then generate Message Type from the database
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 base on operation)
- Choose the Table’s name for creating Message Type
After that, we got the Message Type generated from the database
Here the example for Table called: “tbl_account_db_atn”
[su_box title=”Note” box_color=”#2a8af0″ title_color=”#000000″]
For Select operation, we have a template for generating Message Type for a single Table
– Create a root Message Type with prefix “Database_” + “Table’s name” within type as “Database Table Template”
– Create two Message Type as a child of the root with the fixed name. The first one is “Request”, type as “Database Request”. And the other one is “Response”, type as “Database Response”
– Under “Request”, create a Message Type as the child. The Table’s name is used for this Message Type within type as “Database Table”. i.e: tbl_account_db_atn. In this Message Type, was generated the field name like on Table
– Under “Response”, create a Message Type as the child with the fixed name “records” within type as “Plain structure”
– Under “records”, create a Message Type as the child. The Table’s name is used for this Message Type within type as “Plain structure”. In this Message Type, was generated the field name like on Table
[/su_box]
Currently, we need to manual re-order Message Type as the parent and child following to Request and Response. As the example below, we have three Message Type refer to three Tables on the database. Under “Request” we take “tbl_account_db_atn” as the parent of “tbl_contact_db_atn” and “tbl_case_db_atn” as the child of “tbl_contact_db_atn”, these Message Type are Database Table type. On “Response”, and under “records” we take “tbl_account_db_atn” as the parent of “tbl_contact_db_atn” and “tbl_case_db_atn” as the child of “tbl_contact_db_atn”, these Message Type are Plain structure type.
- In WHERE-Clause Message Type, we did not generate any field from the Table because we never use all the fields on the Table in the condition. The user needs to add a field on WHERE-Clause for using in the condition by going to WHERE-Clause which relevant to its parent Message Type then navigate to Related tab => Select Field From Repository
In this example below is showing WHERE-Clause on tbl_account_db_atn. For another Message Type like tbl_contact_db_atn and tbl_case_db_atn is the same step scenario for adding the field on WHERE-Clause.
At this point, we need to choose the Metadata provider and Repository name and then select the field that we need to put in the condition.
After click Save, we have got the field below
Step 3: Set up Interface
For creating the Interface need to follow this guide https://docs.skyvva-dev.com/docs/tutorial-v2-41-lightning/what-is-an-interface-and-how-to-create-it/
Define the Request Interface
- Create an outbound Interface for Synchronous mode, operation(query), choose Adapter, Metadata Provider, Repository and Message Type (Request)
- On the Interface details, click the button Open Mapping
- In the mapping tool, we map it followed to the standard SELECT statement with WHERE-Clause.SELECT column1, column2, …
FROM table_name
WHERE condition; - Example:
- We map “Account” sObject to node “tbl_account_db_atn”: to define which Table use for the Select operation. If we did not map any field of node “tbl_account_db_atn” then the Select statement will be query all the fields, otherwise, it will query only the field that we have a map. Select statements will be generated into the field called “Database Query” on Interface or the user can put Select statement manually.
- “skyvvasolutions__Account_ExID” to “DB_AccountId” of node WHERE-Clause: to define the condition field’s name.
- “Functions” to “Expression” of node WHER-Expression: to define the condition field’s value. After map, this field, the Formula and Expression’s form will be pop up. We can also map another field to Expression, then check the Formula flag
[su_box title=”Note” box_color=”#2a8af0″ title_color=”#000000″]
In WHERE-Expression, we need to put the question mark (?) for getting dynamic value from the field that presents in the WHERE-Clause. We can put the static value in WHERE-Expression unless that field did not present in WHERE-Clause. If we put the static value for the field that comes from WHERE-Clause then we will be getting the incorrect expected result. The static value must be inside the quote symbol.
If we do hierarchical mapping for SELECT operation, at least the Primary key field must be map to WHERE-Clause on parent level.
The Agent will fetch the record base on the value put on Database Query and use condition from the mapping tool.
[/su_box]
- Define the response Interface
We do SELECT operation using outbound Interface means that we want to get data from the database to Salesforce. So in this case, we need the response Interface to do inbound V3.
- Create inbound Interface, choose Metadata provider, Repository, and Message type (Response)
In the example below is used the response from Agent to do an upsert operation on
Mapping
Adding Response Interface (Inbound) to Request Interface (Outbound)
In this example, the Request interface was named “MYSQL_Database_InternalKEY_Insert_SYNC_multiple table” and the Response interface is “Response Insert SYNC”
Step- 4: Checking Cache record to ensure it synchronized to new Agent.
Go to Agent control board then click Cache Monitor
[su_box title=”Note” box_color=”#2a8af0″ title_color=”#000000″]
We are using cache on a new Agent for storing the SKYVVA object and its setting, so the new Agent will be looking at Cache record on its local database to get the SKYVVA object setting before transfer data. It is different from the old Agent that is always read the SKYVVA object setting from Salesforce again and again that makes too much time before transfer data. The cache is built automatically when you save the interface with an adapter destination.
[/su_box]
Step-5: 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.
[aux_code language=”javascript” theme=”tomorrow” extra_classes=””]
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_SELECT_SYNC_multiple table’,ids,’SYNC’,c);
[/aux_code]
- After callout, the record has been retrieved from the database to do inbound V3. As shown the result on Message monitoring below:
How to do Select 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.
On Request Interface
On Response Interface
we need also need to add an inbound Database Adapter. In this example below, we used the same Adapter as the previous section for the Insert operation
[su_box title=”Note” box_color=”#2a8af0″ title_color=”#000000″]
For Asynchronous mode with Outbound Interface is a dependency on the field “Transfer Package Size” on the Interface. By default, this field is blank mean that the value “1”. The calculation of API call is depending on this field.
For example, in case we are sending 3 records Account’s id:
- If “Transfer Package Size=2”, it will execute 2 API call.
- If “Transfer Package Size=1”, it will execute 3 API call.
[/su_box]
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[]{‘0014E00001C8fAwQAJ’};//Account Id
skyvvasolutions.Iservices.invokeCalloutV3(‘Test MYSQL-Database Integration’, MYSQL_Database_InternalKEY_SELECT_SYNC_multiple table’,ids,’Auto’,c);
[/aux_code]
[su_box title=”Note” box_color=”#2a8af0″ title_color=”#000000″]
if your data contain much more, so you cannot process it by Synchronous call due to the request time out error, we need an Asynchronous. In the Asynchronous call, multi-task does at the same time in the background job, the user no need to wait to see the response back. In this case, Agent will be responding to the update message Complete or Failed for a while.
[/su_box]
After callout, the record has been retrieved from the database to do inbound V3. As shown the result on Message monitoring below:
Summary #
Finally, we have learned about how to do select operation 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.
