(SQL Server) curl with Target Outputs

See more CURL Examples

For curl requests that return JSON, you can define output variables that extract specific values directly from the response. Instead of manually parsing the JSON, you provide a JSON path for each value you want. Chilkat uses this path to locate the value and assign it to a variable, which your application can then retrieve using GetVar.

Note: This example requires Chilkat v11.5.0 or greater.

Chilkat ActiveX Downloads

ActiveX for 32-bit and 64-bit Windows

-- 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 executes a curl command to retrieve information about a SharePoint site
    -- from Microsoft Graph. The request uses variable placeholders that will be replaced
    -- at runtime with actual values.
    -- 
    -- Equivalent 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"
    -- 
    -- A typical JSON response looks like this:
    -- 
    -- {
    --   "@odata.context": "...",
    --   "createdDateTime": "...",
    --   "description": "Test site",
    --   "id": "example.sharepoint.com,...",
    --   "lastModifiedDateTime": "...",
    --   "name": "test",
    --   "webUrl": "...",
    --   "displayName": "test",
    --   "root": {},
    --   "siteCollection": {
    --     "hostname": "example.sharepoint.com"
    --   }
    -- }
    -- 
    -- Rather than processing the entire JSON response, this example extracts only the
    -- specific values we care about: id, description, and siteCollection.hostname.
    -- These values are located at known JSON paths, so we can define them as "target outputs".

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

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

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

    -- Configure OAuth2 authentication using the client credentials flow.
    -- Secrets (client_id, client_secret, token_endpoint) are retrieved from the
    -- local secrets manager because EnableSecrets is set to true.
    DECLARE @jsonOAuth2 int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOAuth2 OUT

    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'
    EXEC sp_OAMethod @httpCurl, 'SetAuth', @success OUT, @jsonOAuth2

    -- Define values for the variables used in the curl command.
    -- These replace the {{sharepoint_hostname}} and {{site_name}} placeholders at runtime.
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'sharepoint_hostname', 'example.sharepoint.com'
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'test'

    -- Define target outputs for the curl command.
    -- Each call maps a JSON path in the response to a variable name.
    -- After execution, these variables can be retrieved using GetVar.
    EXEC sp_OAMethod @httpCurl, 'AddTargetOutput', NULL, 'id', 'site_id'
    EXEC sp_OAMethod @httpCurl, 'AddTargetOutput', NULL, 'description', 'site_description'
    EXEC sp_OAMethod @httpCurl, 'AddTargetOutput', NULL, 'siteCollection.hostname', 'site_hostname'

    -- Execute the curl command. Variable substitution and authentication
    -- are handled automatically.
    EXEC sp_OAMethod @sbTargetCurl, '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 @sbTargetCurl
        EXEC @hr = sp_OADestroy @httpCurl
        EXEC @hr = sp_OADestroy @jsonOAuth2
        RETURN
      END

    -- Load the JSON response from the server.
    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

    -- Check the HTTP status code returned by the request.
    DECLARE @statusCode int
    EXEC sp_OAGetProperty @httpCurl, 'StatusCode', @statusCode OUT

    PRINT 'response status code: ' + @statusCode

    IF @statusCode <> 200
      BEGIN
        -- If the request failed, the JSON response will contain error details
        -- instead of the expected data.
        EXEC sp_OAMethod @responseJson, 'Emit', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbTargetCurl
        EXEC @hr = sp_OADestroy @httpCurl
        EXEC @hr = sp_OADestroy @jsonOAuth2
        EXEC @hr = sp_OADestroy @responseJson
        RETURN
      END

    -- Verify that all target output variables were successfully extracted.
    -- Passing "!" to VarDefined returns 1 only if all target outputs are defined.
    DECLARE @allTargetsDefined int
    EXEC sp_OAMethod @httpCurl, 'VarDefined', @allTargetsDefined OUT, '!'
    IF @allTargetsDefined = 0
      BEGIN
        EXEC sp_OAGetProperty @httpCurl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Not all target outputs were located and defined.'
        EXEC @hr = sp_OADestroy @sbTargetCurl
        EXEC @hr = sp_OADestroy @httpCurl
        EXEC @hr = sp_OADestroy @jsonOAuth2
        EXEC @hr = sp_OADestroy @responseJson
        RETURN
      END

    -- Retrieve and display the extracted values from the response.

    EXEC sp_OAMethod @httpCurl, 'GetVar', @sTmp0 OUT, 'site_id'
    PRINT 'site_id = ' + @sTmp0

    EXEC sp_OAMethod @httpCurl, 'GetVar', @sTmp0 OUT, 'site_description'
    PRINT 'site_description = ' + @sTmp0

    EXEC sp_OAMethod @httpCurl, 'GetVar', @sTmp0 OUT, 'site_hostname'
    PRINT 'site_hostname = ' + @sTmp0

    -- Example output:

    -- site_id = example.sharepoint.com,9b923c5e-5117-44ad-8b03-cdbb8e19ae85,b2451e19-290f-4f29-9f5d-674c2951a9f7
    -- site_description = Test site
    -- site_hostname = example.sharepoint.com

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


END
GO