(SQL Server) curl Dependency Engine Simple Example

See more CURL Examples

This example demonstrates a simple way to define curl commands with declared inputs and outputs, and then execute a target command by automatically resolving its dependencies.

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

• Scans the curl command for required inputs using the {{variable}} syntax
• Determines which of those inputs already have values
• For any missing inputs:
  • Locates another curl command that can produce the needed value
  • Recursively resolves that command’s own inputs in the same way
• Constructs an execution plan so that each curl command runs only after all of its required inputs are available

The execution plan is then carried out, with 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 target curl command we ultimately want to execute.
    -- It requires a {{site_id}} value, which is not yet known.
    DECLARE @targetCurl nvarchar(4000)
    SELECT @targetCurl = 'GET https://graph.microsoft.com/v1.0/sites/{{site_id}}/drives'

    -- Because site_id is unknown, we define a helper function (curl command)
    -- that can retrieve it.
    DECLARE @fnName nvarchar(4000)
    SELECT @fnName = 'getSite'
    EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'GET https://graph.microsoft.com/v1.0/sites/root:/sites/{{site_name}}'

    -- Define the outputs produced by the helper function.
    -- Outputs are extracted from the JSON response using JSON paths.
    -- Here, the "id" field from the response is mapped to the variable "site_id".
    DECLARE @jsonPath nvarchar(4000)
    SELECT @jsonPath = 'id'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, @jsonPath, 'site_id'

    -- Provide a value for site_name, which is required by the getSite function.
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'test'

    -- ----------------------------------------------------------------------------------------------------------------------
    -- Build the OAuth2 configuration used for authentication.
    -- This uses the client credentials flow, with secrets retrieved
    -- from the local secrets manager (because EnableSecrets = 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

    -- Execute the target curl command.
    -- Internally, the system will:
    -- 1) Detect that {{site_id}} is required but not yet defined.
    -- 2) Find that the "getSite" function can produce site_id.
    -- 3) Verify that getSite's input (site_name) is already available.
    -- 4) Build an execution plan: first run getSite, then run the target curl.
    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