Introduction #
We can call SKYVVA API via Stored Procedure in SQL Server database to push data into Salesforce. Therefore we need to create an inbound Interface at SKYVVA. There are many entities for making HTTP request call such as request Method, request URI, Query Parameter, Authentication, HTTP header, and Body. We need to implement those things in the Stored Procedure to make the HTTP request. Before we can make a REST call from Stored Procedure, we need to enable the OLE automation procedures. By default it was disabled.
Pre-required #
- Create a connected app on Salesforce to get Client-Id and Client-secret key for generating token used OAuth 2.0 authentication
- Generate Message Type from the payload
- Create inbound Interface
- Get access token by calling Salesforce API
- Call SKYVVA API from Stored Procedure
Step -1: Create Salesforce Connected app
Navigate to the search box and just type “app…” for finding the App Manager menu then click New Connected App to create a new connected app on Salesforce
Fill required information
After that click button Save
Click continue
Here are the Client-Id (Consumer Key) and Client-Secret (Consumer Secret). But we cannot use those keys immediately as Salesforce has a display of a warning in the red line
Step 2- Create a Message Type to get the structure for do mapping
We need to create Metadata Provider, Repository, and Message Type by generating from payload or creating manually
Create Metadata Provider and Repository, if they are not there.
- Create Metadata Provider
Navigate to the Metadata Provider tab then click New
Fill require field and Save
- Create Repository
Go to Metadata Provider details and click New to create a new Repository
Fill required field then Save
- Generate message from the payload
We need the structure for doing mapping in SKYVVA, the structure must be the same as the incoming payload if it does not match then the run-time will not work properly. So we need to use the incoming payload to generate the Message Type.
In this example, we have used the function “Open Editor” to generate Message Type from JSON payload or we can use the function Import Metadata by uploading the file.
Step-3 Create inbound Interface
Go to Integration then create a new inbound Interface
Fill all required fields and also choose Metadata Provider, Repository, and Message Type
After that, we can do mapping
Step-4 Generate Token
In this example, we use the Postman to call Salesforce API to get an access token.
Use the Salesforce endpoint URL to the relevant Salesforce Org
Sandbox Org = https://test.salesforce.com/services/oauth2/token
Production Org = https://login.salesforce.com/services/oauth2/token
username : your salesforce login name
password : your salesforce password and security token
grant_type: password
client_secret : the “Consumer Secret” found on Connect App
client_id : the “Consumer Key” found on Connect App
After we send the request success, then the Salesforce will return the Access Token and Instance URL that is needed to do the authentication request in the Stored Procedure.
Step-5 Execute Stored Procedure
If we have built the Stored Procedure to call SKYVVA API correctly and then when executing the Stored Procedure then the API will return the response as below:
Here is the sample Stored Procedure to call SKYVVA API:
Create procedure SKYYVA_POST as DECLARE @SkyvvaAPI NVARCHAR(500)='services/apexrest/skyvvasolutions/V4/integrate' DECLARE @InstanceURL NVARCHAR(500) ='https://sobject-dyno-1746-dev-ed.cs100.my.salesforce.com' /*** Salesforce base URL***/ DECLARE @IntegrationName NVARCHAR(255)='Test Integration' DECLARE @InterfaceName NVARCHAR(255)='Account Repo' DECLARE @Mode NVARCHAR(32)='Synchronous' DECLARE @RequestFormat NVARCHAR(32)='JSON' DECLARE @ResponeFormat NVARCHAR(32)='JSON' DECLARE @Endpoint NVARCHAR(max) DECLARE @Object INT DECLARE @ResponseText VARCHAR (8000) DECLARE @Status NVARCHAR(32) DECLARE @StatusText NVARCHAR(32) DECLARE @AuthHeader NVARCHAR(500) DECLARE @ContentType NVARCHAR(64) DECLARE @Body NVARCHAR(max) SET @Endpoint =CONCAT(@InstanceURL,'/',@SkyvvaAPI,'?integration=',@IntegrationName,'&interface=',@InterfaceName,'&mode=', @Mode,'&response-format=',@ResponeFormat,'&request-format=',@RequestFormat) SET @AuthHeader = 'Bearer 00D1x0000002ed4!ARAAQEk6MwZGDTzQ_7lB2PzhtSucIVlJm8A58fF5pXcVgCEDyk9nlmAZI3oeXaWbvG3RHi2OV6IdVHfBaaSAvgzAZEFOqmbj'; /*** Bearer +token ***/ SET @ContentType = 'application/json'; SET @Body = /*** Data sent to Salesforce. Use this structure for create Message Type for doing mapping in SKYVVA ***/ '{ "AccountTestV3": { "AccountNumber": "20002", "Name": "TestAccount2-SB", "BillingCountry": "Cambodia2", "BillingCity": "PhnomPenh2", "Account_ID": "101", "Description": "Test description ACCOUNT object" } }' EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Object out; EXEC sp_OAMethod @Object, 'open', NULL, 'POST', @Endpoint, 'false'; EXEC sp_OAMethod @Object, 'SetRequestHeader', NULL, 'Authorization', @AuthHeader; EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-type', 'application/json'; /*** Print response ***/ EXEC sp_OAMethod @Object, 'send', NULL, @Body; EXEC sp_OAGetProperty @Object, 'status', @status OUT; EXEC sp_OAGetProperty @Object, 'statusText', @statusText OUT; exec sp_OAMethod @Object,'responseText',@ResponseText output PRINT 'Status: ' + @status + ' (' + @statusText + ')'; PRINT 'Response text: ' + @responseText; EXEC sp_OADestroy @Object;
Summary #
Finally, we have learned how to call SKYVVA API from Stored Procedure in SQL Server Database, we understand how to create Salesforce connected app, generate Message Type from payload for doing mapping, create the interface, generate an access token, and we know the sample Stored Procedure for calling SKYVVA API.