(SQL Server) curl Dependency Engine Multi-Step Execution Plan

See more CURL Examples

This example expands on the basic concept by demonstrating a multi-step execution plan, where several curl commands are chained together through their inputs and outputs.

Instead of resolving a single missing input with one helper command, the target curl command in this example depends on values that are produced by other commands, which may themselves depend on additional inputs. This creates a dependency chain that requires multiple steps to fully resolve.

When the DoYourThing method is called with the target curl command, the following occurs:

• Scans the target command for required inputs using the {{variable}} syntax
• Determines which inputs already have assigned values
• For any missing inputs:
  • Identifies a curl command that can produce the required value
  • Recursively evaluates that command to determine its own required inputs
  • Continues this process until all dependencies are resolved down to known values

Once all dependencies are identified, a multi-step execution plan is constructed. Each curl command is ordered so that it runs only after the values it depends on have been produced by earlier steps.

The execution plan is then carried out step by step, with intermediate results flowing between commands, and the final step being the target curl command originally passed to DoYourThing.

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

For more information, see https://www.chilkatsoft.com/curl_dependency_engine.asp

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

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

    -- The final curl command we want to execute.
    -- It depends on {{drive_id}}, which is not yet known.
    DECLARE @targetCurl nvarchar(4000)
    SELECT @targetCurl = 'curl -X GET https://graph.microsoft.com/v1.0/drives/{{drive_id}}/root/children'

    -- Define a helper function that can produce drive_id.
    -- This function lists drives for a given site, which requires {{site_id}}.
    DECLARE @fnName nvarchar(4000)
    SELECT @fnName = 'getDrives'
    EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'curl -X GET https://graph.microsoft.com/v1.0/sites/{{site_id}}/drives'

    -- Define the output of getDrives.
    -- The JSON response contains an array named "value".
    -- We take the first element (value[0]) and extract its "id" as drive_id.
    DECLARE @jsonPath nvarchar(4000)
    SELECT @jsonPath = 'value[0].id'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, @jsonPath, 'drive_id'

    -- drive_id depends on site_id, which is also not yet known.
    -- Define another helper function to obtain site_id.
    SELECT @fnName = 'getSite'
    EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'GET https://graph.microsoft.com/v1.0/sites/root:/sites/{{site_name}}'

    -- Map the "id" field from the getSite response to the variable site_id.
    SELECT @jsonPath = 'id'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, @jsonPath, 'site_id'

    -- Provide the initial known input.
    -- site_name is the starting value that allows the dependency chain to be resolved.
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'test'

    -- ----------------------------------------------------------------------------------------------------------------------
    -- 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 enabled.
    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
    -- ----------------------------------------------------------------------------------------------------------------------

    -- Execute the target curl command.
    -- Internally, the system will:
    -- 
    -- 1) Detect that {{drive_id}} is required but not defined.
    -- 2) Find that "getDrives" can produce drive_id.
    -- 3) Detect that getDrives requires {{site_id}}, which is also not defined.
    -- 4) Find that "getSite" can produce site_id.
    -- 5) Verify that getSite requires {{site_name}}, which is already known.
    -- 6) Build a multi-step execution plan:
    --      - First:  run getSite (produces site_id)
    --      - Second: run getDrives (produces drive_id)
    --      - Third:  run the target curl (uses drive_id)
    EXEC sp_OAMethod @httpCurl, 'DoYourThing', @success OUT, @targetCurl
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @httpCurl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @httpCurl
        EXEC @hr = sp_OADestroy @jsonOAuth2
        RETURN
      END

    -- Retrieve and display the HTTP response status code.
    DECLARE @statusCode int
    EXEC sp_OAGetProperty @httpCurl, 'StatusCode', @statusCode OUT

    PRINT 'response status code: ' + @statusCode

    -- Load and display the JSON response from the final curl command.
    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
    EXEC sp_OAMethod @responseJson, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

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


END
GO