SQL Server

Viewing a Simple Three-Step Execution Plan

See more CURL Examples

This example demonstrates a straightforward, valid dependency chain that results in a three-step execution plan. The goal is not to execute the requests, but to inspect how the plan is constructed using ExaminePlan.

The target curl command requires {{order_id}}. A helper function is defined to produce order_id, but it depends on customer_id. Another helper function produces customer_id, which depends on account_name.

Because account_name is provided as a known input, the dependency chain can be fully resolved:

• customer_id is obtained from account_name
• order_id is obtained from customer_id
• The target curl command uses order_id

When ExaminePlan is called, it builds and returns a three-step execution plan showing the order in which each function would be executed.

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 target curl command requires {{order_id}}.
    DECLARE @targetCurl nvarchar(4000)
    SELECT @targetCurl = 'curl -X GET https://api.example.com/orders/{{order_id}}'

    -- Define a helper function that produces order_id from customer_id.
    DECLARE @fnName nvarchar(4000)
    SELECT @fnName = 'getOrderId'
    EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'curl -X GET https://api.example.com/order-id?customer={{customer_id}}'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, 'order.id', 'order_id'

    -- Define a helper function that produces customer_id from account_name.
    SELECT @fnName = 'getCustomerId'
    EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'curl -X GET https://api.example.com/customer-id?account={{account_name}}'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, 'customer.id', 'customer_id'

    -- Provide the starting known input.
    EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'account_name', 'acme'

    -- Examine the execution plan without running any requests.
    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

    -- Success is expected to be 1.

    PRINT 'success = ' + @success

    EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Expected result:
    -- 
    -- {
    --   "plan": [{
    --     "function": "getCustomerId",
    --     "inputs": ["account_name"],
    --     "outputs": ["customer_id"]
    --   },{
    --     "function": "getOrderId",
    --     "inputs": ["customer_id"],
    --     "outputs": ["order_id"]
    --   },{
    --     "function": "targetCurl",
    --     "inputs": ["order_id"],
    --     "outputs": []
    --   }]
    -- }

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


END
GO