SQL Server

Test Salesforce OAuth2 Access Token

See more Salesforce Examples

Demonstrates how to make a simple Salesforce REST API call to test a previously obtained access token. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example does the following:

    -- curl -X GET https://yourInstance.salesforce.com/services/oauth2/userinfo \
    --      -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use the following online tool to generate HTTP code from a CURL command
    -- Convert a cURL Command to HTTP Source Code

    -- This example assumes the OAuth2 access token was previously fetched 
    -- and saved to a file.  See Get SalesForce OAuth2 Access Token via Authorization Flow

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/tokens/_salesforce.json'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load OAuth2 access token.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Here's an example of the JSON:

    -- {
    --   "access_token": "00D41000....uLZBpT6",
    --   "refresh_token": "5Aep....25xdGgkrV",
    --   "signature": "cjTbSc5DvcKpaMoRTzuQTJLb1tcMw8LEO01flq4aMD4=",
    --   "scope": "refresh_token id",
    --   "instance_url": "https://d41000000f8a0eak-dev-ed.my.salesforce.com",
    --   "id": "https://login.salesforce.com/id/00D41000000F8A0EAK/005410000....xAAE",
    --   "token_type": "Bearer",
    --   "issued_at": "1738348388166"
    -- }

    -- Adds the "Authorization: Bearer YOUR_ACCESS_TOKEN" header.
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0

    -- We want to build the following URL:  https://<instance_id>.salesforce.com/services/oauth2/userinfo
    DECLARE @sbUrl int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrl OUT

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'instance_url'
    EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, '/services/oauth2/userinfo'

    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @sbUrl, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, @sTmp0, @sbResponseBody
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbUrl
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END


    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    PRINT 'Response status code = ' + @iTmp0

    DECLARE @jsonResponse int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResponse OUT

    EXEC sp_OAMethod @jsonResponse, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @jsonResponse, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonResponse, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The expected JSON response is something like this:

    -- {
    --   "sub": "005xxxxxxxxxxxx",
    --   "name": "John Doe",
    --   "preferred_username": "johndoe@example.com",
    --   "email": "johndoe@example.com",
    --   "profile": "https://na85.salesforce.com/005xxxxxxxxxxxx"
    -- ...
    -- ...
    -- }

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sbUrl
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jsonResponse


END
GO