SQL Server

curl with use of Local Manager Secrets for Credentials

See more CURL Examples

This example demonstrates how to retrieve secrets from the local credential manager instead of embedding them directly in your source code.

On Windows, this refers to the Windows Credential Manager, and on macOS, it refers to the Apple Keychain.

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
    -- 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 will run the following curl command

    -- curl -X GET "https://graph.microsoft.com/v1.0/sites/{{sharepoint_hostname}}:/sites/{{site_name}}" \
    --   -H "Authorization: Bearer ACCESS_TOKEN" \
    --   -H "Accept: application/json"

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

    EXEC sp_OAMethod @sb, 'AppendLn', @success OUT, 'curl -X GET "https://graph.microsoft.com/v1.0/sites/{{sharepoint_hostname}}:/sites/{{site_name}}" \'
    EXEC sp_OAMethod @sb, 'AppendLn', @success OUT, '  -H "Authorization: Bearer ACCESS_TOKEN" \'
    EXEC sp_OAMethod @sb, 'AppendLn', @success OUT, '  -H "Accept: application/json"'

    -- Build the JSON that provides information for getting the OAuth2 access token using the OAuth2 client credentials flow.
    DECLARE @jsonOAuth2 int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOAuth2 OUT

    -- The notation "!!sharepoint|oauth2|client_id" is a secrets specification string 
    -- that can have up to 4 components: !!appName|service|domain|username
    -- where appName and domain are optional.
    -- See Chilkat v11.5.0 — Secrets Integration
    -- Also see: Chilkat Secrets API

    -- The following example shows how to store (or update) the secrets used in this example
    -- Storing Secrets in the Local Manager

    -- We must enable secrets integration for Chilkat to auto-resolve when the string begins with "!!".
    EXEC sp_OASetProperty @jsonOAuth2, 'EnableSecrets', 1
    EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.client_id', '!!sharepoint|oauth2|client_id'
    EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.client_secret', '!!sharepoint|oauth2|client_secret'
    EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.scope', 'https://graph.microsoft.com/.default'
    EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.token_endpoint', '!!sharepoint|oauth2|token_endpoint'

    DECLARE @httpCurl int
    EXEC @hr = sp_OACreate 'Chilkat.HttpCurl', @httpCurl OUT

    -- Provide the information for getting the OAuth2 access token from the token endpoint
    -- Note: The Authorization header specified in the curl command will be ignored and replaced using the OAuth2 access token obtained at runtime from the token endpoint.
    EXEC sp_OAMethod @httpCurl, 'SetAuth', @success OUT, @jsonOAuth2

    -- The placeholders {{sharepoint_hostname}} and {{site_name}} represent variables that must be defined before execution.
    -- When DoYourThing runs the curl command, it automatically substitutes these placeholders with their corresponding values.
    -- Below are the values assigned to these variables:
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'sharepoint_hostname', 'example.sharepoint.com'
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'test'

    -- Run the curl command.
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @httpCurl, 'DoYourThing', @success OUT, @sTmp0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @httpCurl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @jsonOAuth2
        EXEC @hr = sp_OADestroy @httpCurl
        RETURN
      END

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

    EXEC sp_OASetProperty @responseJson, 'EmitCompact', 0

    EXEC sp_OAMethod @httpCurl, 'GetResponseJson', @success OUT, @responseJson

    DECLARE @statusCode int
    EXEC sp_OAGetProperty @httpCurl, 'StatusCode', @statusCode OUT

    PRINT 'response status code: ' + @statusCode

    EXEC sp_OAMethod @responseJson, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @jsonOAuth2
    EXEC @hr = sp_OADestroy @httpCurl
    EXEC @hr = sp_OADestroy @responseJson


END
GO