(SQL Server) Inspect the Complete Set of Defined Variables

See more CURL Examples

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

    -- This example shows how to inspect the complete set of defined variables
    -- after executing the curl dependency plan.
    -- The code that enumerates and displays all variables is located at the end of the example.

    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

    -- ----------------------------------
    -- Examine all defined variables.
    -- ----------------------------------
    DECLARE @definedVarsJson int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @definedVarsJson OUT

    EXEC sp_OASetProperty @definedVarsJson, 'EmitCompact', 0

    EXEC sp_OAMethod @httpCurl, 'GetAllVars', NULL, @definedVarsJson
    EXEC sp_OAMethod @definedVarsJson, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample output:

    -- {
    --   "vars": [
    --     {
    --       "name": "site_name",
    --       "value": "test"
    --     },
    --     {
    --       "name": "site_id",
    --       "value": "example.sharepoint.com,9b923c5e-5117-44ad-8b03-adbb8e19be85,b2451e19-290f-5f29-9f5d-674c2950a9f7"
    --     },
    --     {
    --       "name": "drive_id",
    --       "value": "b!XjySmxdRrrtLA827jhm-nRkeRbIPKSlPn11nTClQqfeVOj5VUvQMQae34rwOHdiP"
    --     }
    --   ]
    -- }

    -- Sample code to parse the above JSON.
    DECLARE @i int
    SELECT @i = 0
    DECLARE @numVars int
    EXEC sp_OAMethod @definedVarsJson, 'SizeOfArray', @numVars OUT, 'vars'
    WHILE @i < @numVars
      BEGIN
        EXEC sp_OASetProperty @definedVarsJson, 'I', @i

        EXEC sp_OAMethod @definedVarsJson, 'StringOf', @sTmp0 OUT, 'vars[i].name'
        PRINT 'name: ' + @sTmp0

        EXEC sp_OAMethod @definedVarsJson, 'StringOf', @sTmp0 OUT, 'vars[i].value'
        PRINT 'value: ' + @sTmp0
        SELECT @i = @i + 1
      END

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


END
GO