How to call stored procedure with SQL Server on Agent?

Introduction

A store procedure is a set of SQL statements with an assigned name, stored in an RDBMS as a group so that multiple programs can reuse and share it. 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.

In this tutorial, we use the SQL Server database as an example. Here, when we call the Stored Procedure to the Database to do any database operation, we expect it will respond back the result to 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 SQL Server database is located on an EC2 instance and the Stored Procedure was written to Insert new data if no record is present on the Database and update data if the record exists.

Pre-required

• Create Integration and test connection on the Agent Control Board
• Create outbound and inbound Database Adapter
• Create Metadata Provider, Repository and generate Message Type from the Database
• Create an outbound interface (Request) and inbound interface (Response) and attach Adapter to each Interface
• Linking response interface to Request interface
• Checking Caches status
• Manual Process

How to call Stored Procedure with SQL Server on Agent?

Step1: Create Integration and test connection destination

Create new Integration by following the guide, the link is given below:

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

• After Integration created and then navigate to AGENT Control Board as shown below.

• Ping connection to Agent on control board to make sure Agent is online

Step2: Create Outbound and Inbound Database Adapter for connection to the SQL Server database presented on the EC2 instance

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

• Fill in all required field

Name: any name

• Type: Agent Database
• Direction: Outbound/Inbound
• Status: Active
• Database Type: SQL Server
• Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
• Username: (your username on database)
• Password: (your password on database)
• Hostname: 3.124.40.194 (public ipv4 on EC2 AWS)
• Port: 1433 (custom port number)
• Server instance: SQLEXPRESS
• Insert Operation Mode: Internal Key Management
• Connection URL: jdbc:sqlserver://3.124.40.194:1433;databaseName=chetvi_db1;encrypt=false (connection URL has followed the systax: jdbc:sqlserver::;databaseName=;encrypt=false )
• Connection Destination: your destination name

Step3: generate Message Type from the database

• Create Metadata Provider
• Create a Repository for using the Insert operation
• Go to the Repository detail page, click the Import Database Metadata button

Fill in all required information then click the button Retrieve Database

• Agent Instance: Instance’s name
• Adapter Name: adapter’s name
• Object type: Stored procedure
• Choose the stored Procedure’s name and then click Create Message Type

• After clicking Create Message Type, the stored procedure template will be generated as the below example:

[su_box title="Note" box_color="#2a8af0" title_color="#000000"]

For Database Stored Procedure, we have a template for generating Message Type for a single Table

• Create a root Message type within the name same as the Stored Procedure’s name and type as Database Stored Procedure Template
• Create Message Type as a child of the root with name as the Stored Procedure’s name including the postfix “_Request” within the type as Stored Procedure Request. On this Message Type, we have generated the field for the INPUT parameter on Stored Procedure
• Create Message Type as a child of the root with name as the Stored Procedure’s name including the postfix” _Response” within the type as Stored Procedure Response. Under this node, we have generated the fixed name of the Message Type called “output parameter” within the type “Plain structure”. On this Message Type, we have generated the field for the OUTPUT parameter same as on the Stored Procedure.

[/su_box]

Step4: Set up the Interface

• For creating the Interface need to follow this guide: https://apsara-consulting.com/docs/tutorial-v2-41-lightning/what-is-an-interface-and-how-to-create-it/
• Create an outbound interface for synchronous mode, operation (stored procedure), choose Adapter, Metadata Provider, Repository, and Message Type (Request)

• Mapping

• Create an inbound Interface (Response)

Create an inbound interface for synchronous mode, operation(upsert), choose Adapter, Metadata Provider, Repository, and Message Type (Response)

• Mapping

Step5: Linking Response Interface to Request Interface

In this example Request Interface name is “Request Interface” and Response Interface name is “Request Interface”

• Go to the Request Interface detail page then add the Response Interface

Step6: Checking Cache status

Before sending data from salesforce to Agent or from Agent to salesforce, we need to check on Cache Monitor and make sure the Cache record is present on H2.

Step7: Manual Process

• Navigate to the Interface tab and select the interface

• Click on the Manual Process button and choose the second radio
• Click send

Check the result on Message Monitoring

The Stored Procedure has been executed and responded to Salesforce correctly

Here is the result on SQL Server