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

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

    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

    -- This target curl command 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 can produce site_id.
    -- However, it does NOT produce drive_id.
    DECLARE @fnName nvarchar(4000)
    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'

    -- Provide site_name, so getSite can run.
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'test'

    -- Try to examine the execution plan.
    -- This fails because {{drive_id}} is required by the target curl command,
    -- but drive_id is not already known and no defined function produces it.
    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
    IF @success = 0
      BEGIN
        -- Should equal 5.

        EXEC sp_OAGetProperty @httpCurl, 'FailReason', @iTmp0 OUT
        PRINT 'ExaminePlan fail reason = ' + @iTmp0

        -- Examine the error(s) returned in planJson
        EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        -- Expected output:
        -- {
        --   "errors": [{
        --     "variable": "drive_id",
        --     "msg": "No candidate functions"
        --   }]
        -- }
      END

    -- DoYourThing will fail for the same reason.
    EXEC sp_OAMethod @httpCurl, 'DoYourThing', @success OUT, @targetCurl

    -- We expect DoYourThing to fail because it is not possible to construct
    -- a valid execution plan (drive_id cannot be resolved).
    IF @success = 0
      BEGIN
        -- FailReason provides a numeric code indicating why the operation failed.
        -- A value of 5 specifically means that an execution plan could not be created
        -- due to unresolved dependencies (i.e., a required variable has no source).

        EXEC sp_OAGetProperty @httpCurl, 'FailReason', @iTmp0 OUT
        PRINT 'DoYourThing fail reason = ' + @iTmp0
      END
    ELSE
      BEGIN
        -- If execution succeeds, it means the plan was somehow resolved,
        -- which would be unexpected in this scenario.

        PRINT 'Unexpected success.'
      END

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


END
GO