SQL Server

Impossible Execution Plan

See more CURL Examples

This example demonstrates what happens when it is not possible to construct an execution plan due to missing dependencies.

The target curl command requires a {{drive_id}} value. However, no variable named drive_id has been defined, and no helper function is registered that can produce it. Although a helper function (getSite) is defined, it only produces site_id, which does not satisfy the requirement.

When ExaminePlan is called, it reports that the execution plan cannot be created because a required input is missing and no function exists to provide it.

When DoYourThing is called, it fails for the same reason. The failure is not due to a network or HTTP error, but because the dependency graph cannot be resolved. The FailReason property indicates this condition with a specific error code.

This example highlights that for an execution plan to be valid, every required input must either:

• Already have a defined value, or
• Be producible by a defined curl function with resolvable inputs

If neither condition is met, the system correctly detects the unresolved dependency and prevents execution.

SQL Server

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