SQL Server

curl with Variable Substitution in the Request Body

See more CURL Examples

This example demonstrates using variables located in the request body with the {{variable_name}} syntax. 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

    -- Variables can also be used within the HTTP request body.
    -- Variable names are enclosed between {{ and }}

    -- Here is a curl command with two variables {{name}} and {{age}} located in the data.

    -- curl -X POST https://api.example.com/data \
    --   -H "Content-Type: application/json" \
    --   -d '{"name":"{{name}}","age":{{age}}}'

    DECLARE @sbCurl int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbCurl OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @sbCurl, 'AppendLn', @success OUT, 'curl -X POST https://api.example.com/data \'
    EXEC sp_OAMethod @sbCurl, 'AppendLn', @success OUT, '  -H "Content-Type: application/json" \'
    EXEC sp_OAMethod @sbCurl, 'AppendLn', @success OUT, '  -d ''{"name":"{{name}}","age":{{age}}}'''

    DECLARE @curl int
    EXEC @hr = sp_OACreate 'Chilkat.HttpCurl', @curl OUT

    -- Provide values for variables.
    EXEC sp_OAMethod @curl, 'SetVar', NULL, 'name', 'Alice'
    EXEC sp_OAMethod @curl, 'SetVar', NULL, 'age', '30'

    -- To demonstrate how the variables are replaced, this example does not execute the curl command. 
    -- Instead, it generates the raw HTTP request that would be sent if the curl command were run.
    DECLARE @sbRawRequest int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRawRequest OUT

    EXEC sp_OAMethod @sbCurl, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @curl, 'ToRawRequest', @success OUT, @sTmp0, @sbRawRequest
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbCurl
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @sbRawRequest
        RETURN
      END

    EXEC sp_OAMethod @sbRawRequest, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Output:

    -- POST /data HTTP/1.1
    -- Host: api.example.com
    -- Content-Type: application/json
    -- Content-Length: 25
    -- 
    -- {"name":"Alice","age":30}

    -- --------------------------------------------------------------------------------------------------
    -- Note: Variable substitution in the request body can be turned off by 
    -- setting the EnableBodyVars property equal to 0

    EXEC sp_OASetProperty @curl, 'EnableBodyVars', 0

    EXEC sp_OAMethod @sbCurl, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @curl, 'ToRawRequest', @success OUT, @sTmp0, @sbRawRequest
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbCurl
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @sbRawRequest
        RETURN
      END

    EXEC sp_OAMethod @sbRawRequest, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- The raw request with body variable substitution disable:

    -- Host: api.example.com
    -- Content-Type: application/json
    -- Content-Length: 33
    -- 
    -- {"name":"{{name}}","age":{{age}}}

    EXEC @hr = sp_OADestroy @sbCurl
    EXEC @hr = sp_OADestroy @curl
    EXEC @hr = sp_OADestroy @sbRawRequest


END
GO