(SQL Server) curl How Known Variables Reduce Execution Steps

See more CURL Examples

This example demonstrates how previously resolved values can simplify future executions.

When DoYourThing is called for a curl command, it builds an execution plan by resolving any missing inputs. This may require running one or more helper curl commands to produce the needed values.

As each command runs, any defined output variables are stored in the HttpCurl object and remain available for subsequent calls.

If another curl command is executed afterward—and it requires variables that are already known from earlier executions—those inputs do not need to be resolved again. As a result, the dependency chain is shorter, and the execution plan contains fewer steps.

In other words, each call to DoYourThing benefits from the current set of known variables. The more values that have already been resolved, the simpler and more direct the execution plan becomes.

This demonstrates that dependency resolution is dynamic and incremental: only missing inputs trigger additional steps, while previously computed values are reused automatically.

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

    SELECT @success = 0

    -- -----------------------------------------------------------------------------
    -- NOTE:
    -- This example builds on the earlier multi-step dependency example, where the
    -- execution plan required multiple curl commands (getSite → getDrives → target).
    -- 
    -- If you have not seen that example, refer to it here: curl Multi-Step Dependency
    -- 
    -- 
    -- In this example, we demonstrate how previously resolved variables (site_id,
    -- drive_id, etc.) are reused across multiple calls to DoYourThing, resulting
    -- in simpler execution plans for subsequent requests.
    -- -----------------------------------------------------------------------------

    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 may or may not already be known.
    DECLARE @targetCurl nvarchar(4000)
    SELECT @targetCurl = 'curl -X GET https://graph.microsoft.com/v1.0/drives/{{drive_id}}/root/children'

    -- Define helper function to get drives (produces drive_id, 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'
    DECLARE @jsonPath nvarchar(4000)
    SELECT @jsonPath = 'value[0].id'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, @jsonPath, 'drive_id'

    -- Define helper function to get site (produces site_id, requires site_name)
    SELECT @fnName = 'getSite'
    EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'GET https://graph.microsoft.com/v1.0/sites/root:/sites/{{site_name}}'
    SELECT @jsonPath = 'id'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, @jsonPath, 'site_id'

    -- Provide the initial known input.
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'test'

    -- Configure OAuth2 authentication (client credentials with secrets)
    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

    -- -----------------------------------------------------------------------------
    -- EXAMINE THE EXECUTION PLAN (BEFORE RUNNING):
    -- At this point, drive_id and site_id are NOT known.
    -- We can inspect the plan that will be executed without sending any requests.
    -- -----------------------------------------------------------------------------
    DECLARE @planJson int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @planJson OUT

    EXEC sp_OASetProperty @planJson, 'EmitCompact', 0

    EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson

    PRINT 'Execution plan before first call:'
    EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Expected output:
    -- 
    -- {
    --   "plan": [{
    --     "function": "getSite",
    --     "inputs": ["site_name"],
    --     "outputs": ["site_id"]
    --   },{
    --     "function": "getDrives",
    --     "inputs": ["site_id"],
    --     "outputs": ["drive_id"]
    --   },{
    --     "function": "targetCurl",
    --     "inputs": ["drive_id"],
    --     "outputs": []
    --   }]
    -- }

    -- -----------------------------------------------------------------------------
    -- FIRST CALL:
    -- At this point, drive_id and site_id are NOT known.
    -- The execution plan will include all required steps:
    -- 
    --   1) getSite    → produces site_id
    --   2) getDrives  → produces drive_id
    --   3) targetCurl → 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
        EXEC @hr = sp_OADestroy @planJson
        RETURN
      END


    PRINT 'First call completed.'

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

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

    -- -----------------------------------------------------------------------------
    -- SECOND CALL (DIFFERENT TARGET):
    -- Now that site_id is already known, we can run another curl command that
    -- depends only on site_id. No need to call getSite again.
    -- 
    -- Execution plan:
    -- 
    --   1) targetCurl2 → uses existing site_id
    -- -----------------------------------------------------------------------------

    DECLARE @targetCurl2 nvarchar(4000)
    SELECT @targetCurl2 = 'curl -X GET https://graph.microsoft.com/v1.0/sites/{{site_id}}'

    EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl2, @planJson

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

    -- Expected output:
    -- 
    -- {
    --   "plan": [{
    --     "function": "targetCurl",
    --     "inputs": ["site_id"],
    --     "outputs": []
    --   }]
    -- }

    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
        EXEC @hr = sp_OADestroy @planJson
        RETURN
      END


    PRINT 'Second call completed (used existing site_id).'

    -- -----------------------------------------------------------------------------
    -- THIRD CALL (ORIGINAL TARGET AGAIN):
    -- drive_id is already known from the first call.
    -- Therefore, no helper functions are needed this time.
    -- 
    -- Execution plan:
    -- 
    --   1) targetCurl → uses existing drive_id
    -- -----------------------------------------------------------------------------
    EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson

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

    -- Expected output:
    -- 
    -- {
    --   "plan": [{
    --     "function": "targetCurl",
    --     "inputs": ["drive_id"],
    --     "outputs": []
    --   }]
    -- }

    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
        EXEC @hr = sp_OADestroy @planJson
        RETURN
      END


    PRINT 'Third call completed.'

    PRINT 'No dependency resolution was needed because all inputs were already known.'

    EXEC @hr = sp_OADestroy @httpCurl
    EXEC @hr = sp_OADestroy @jsonOAuth2
    EXEC @hr = sp_OADestroy @planJson


END
GO