(SQL Server) Invalidating Dependent Variables When Inputs Change

See more CURL Examples

This example demonstrates how HttpCurl automatically updates the execution plan when a known variable is cleared or changed.

After a multi-step execution plan runs successfully, variables such as site_id and drive_id may become known. Later calls to DoYourThing can reuse those values, resulting in a shorter execution plan.

However, if an input variable is cleared using ClearVar, or changed using SetVar, any variables that depend on that value are automatically invalidated. This ensures that stale values are not reused.

For example, if site_name changes, then the previously resolved site_id is no longer valid. Because drive_id depends on site_id, it is also invalidated. The next execution plan is rebuilt to resolve the dependency chain again using the new input value.

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

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

    -- The target curl command we ultimately want to execute.
    -- It requires drive_id.
    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 produces drive_id.
    -- This 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'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, 'value[0].id', 'drive_id'

    -- Define another helper function that produces site_id.
    -- This requires site_name.
    SELECT @fnName = 'getSite'
    EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'curl -X GET https://graph.microsoft.com/v1.0/sites/root:/sites/{{site_name}}'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, 'id', 'site_id'

    -- site_name is the starting known value.
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'test'

    -- Configure OAuth2 authentication.
    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

    -- -----------------------------------------------------------------------------
    -- First execution plan:
    -- site_id and drive_id are not known yet, so the full dependency chain is needed.
    -- -----------------------------------------------------------------------------
    DECLARE @planJson int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @planJson OUT

    EXEC sp_OASetProperty @planJson, 'EmitCompact', 0


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

    -- Expected:
    -- 
    -- {
    --   "plan": [{
    --     "function": "getSite",
    --     "inputs": ["site_name"],
    --     "outputs": ["site_id"]
    --   },{
    --     "function": "getDrives",
    --     "inputs": ["site_id"],
    --     "outputs": ["drive_id"]
    --   },{
    --     "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 'After first call:'

    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 execution plan:
    -- site_id and drive_id are now known, so only the target curl command is needed.
    -- -----------------------------------------------------------------------------

    PRINT 'Execution plan after first call:'
    EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson
    EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

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

    -- -----------------------------------------------------------------------------
    -- Change the original input.
    -- Because site_id was produced from site_name, changing site_name invalidates site_id.
    -- Because drive_id depends on site_id, drive_id is also invalidated.
    -- -----------------------------------------------------------------------------

    -- Note: Make sure this site exists on in your SharePoint...
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'anotherSite'


    PRINT 'After changing site_name:'

    EXEC sp_OAMethod @httpCurl, 'VarDefined', @iTmp0 OUT, 'site_id'
    PRINT 'site_id defined?  ' + @iTmp0

    EXEC sp_OAMethod @httpCurl, 'VarDefined', @iTmp0 OUT, 'drive_id'
    PRINT 'drive_id defined? ' + @iTmp0

    -- -----------------------------------------------------------------------------
    -- The execution plan is automatically rebuilt.
    -- Since site_id and drive_id were invalidated, the full dependency chain is needed again.
    -- -----------------------------------------------------------------------------

    PRINT 'Execution plan after changing site_name:'
    EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson
    EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Expected:
    -- 
    -- {
    --   "plan": [{
    --     "function": "getSite",
    --     "inputs": ["site_name"],
    --     "outputs": ["site_id"]
    --   },{
    --     "function": "getDrives",
    --     "inputs": ["site_id"],
    --     "outputs": ["drive_id"]
    --   },{
    --     "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 'After running again with the new site_name:'

    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

    -- -----------------------------------------------------------------------------
    -- Clearing a variable also invalidates values that depend on it.
    -- Here, clearing site_id also invalidates drive_id.
    -- -----------------------------------------------------------------------------
    EXEC sp_OAMethod @httpCurl, 'ClearVar', NULL, 'site_id'


    PRINT 'After clearing site_id:'

    EXEC sp_OAMethod @httpCurl, 'VarDefined', @iTmp0 OUT, 'site_id'
    PRINT 'site_id defined?  ' + @iTmp0

    EXEC sp_OAMethod @httpCurl, 'VarDefined', @iTmp0 OUT, 'drive_id'
    PRINT 'drive_id defined? ' + @iTmp0


    PRINT 'Execution plan after clearing site_id:'
    EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson
    EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

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

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


END
GO