SQL Server

curl POST with JSON Input and JSON Output

See more CURL Examples

Demonstrates running a simple curl command with JSON input and JSON output. 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

    -- Run the following curl command

    --  curl -X POST https://httpbin.org/post \
    --       -H "Content-Type: application/json" \
    --       -d '{
    --             "title": "foo",
    --             "body": "bar",
    --             "userId": 1
    --           }'

    -- The backslashes at the end of lines are not required.  Chilkat ignores them if present.
    DECLARE @sbTargetCurl int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbTargetCurl OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @sbTargetCurl, 'AppendLn', @success OUT, ' curl -X POST https://httpbin.org/post \'
    EXEC sp_OAMethod @sbTargetCurl, 'AppendLn', @success OUT, '      -H "Content-Type: application/json" \'
    EXEC sp_OAMethod @sbTargetCurl, 'AppendLn', @success OUT, '      -d ''{'
    EXEC sp_OAMethod @sbTargetCurl, 'AppendLn', @success OUT, '            "title": "foo",'
    EXEC sp_OAMethod @sbTargetCurl, 'AppendLn', @success OUT, '            "body": "bar",'
    EXEC sp_OAMethod @sbTargetCurl, 'AppendLn', @success OUT, '            "userId": 1'
    EXEC sp_OAMethod @sbTargetCurl, 'AppendLn', @success OUT, '          }'''

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

    -- Run the curl command.
    EXEC sp_OAMethod @sbTargetCurl, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @httpCurl, 'DoYourThing', @success OUT, @sTmp0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @httpCurl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbTargetCurl
        EXEC @hr = sp_OADestroy @httpCurl
        RETURN
      END

    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

    DECLARE @statusCode int
    EXEC sp_OAGetProperty @httpCurl, 'StatusCode', @statusCode OUT

    PRINT 'response status code: ' + @statusCode

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

    -- Output:

    -- response status code: 200
    -- {
    --   "args": {},
    --   "data": "{\r\n            \"title\": \"foo\",\r\n            \"body\": \"bar\",\r\n            \"userId\": 1\r\n          }",
    --   "files": {},
    --   "form": {},
    --   "headers": {
    --     "Content-Length": "96",
    --     "Content-Type": "application/json",
    --     "Host": "httpbin.org",
    --     "X-Amzn-Trace-Id": "Root=1-69e8db8b-459b3bdf7b7a3bc749184968"
    --   },
    --   "json": {
    --     "body": "bar",
    --     "title": "foo",
    --     "userId": 1
    --   },
    --   "origin": "123.222.222.222",
    --   "url": "https://httpbin.org/post"
    -- }

    -- ----------------------------------------------------------------------------------
    -- Another example:

    -- curl -X POST https://postman-echo.com/post \
    --      -H "Content-Type: application/json" \
    --      -d '{"foo":"bar"}'

    DECLARE @targetCurl nvarchar(4000)
    SELECT @targetCurl = 'curl -X POST https://postman-echo.com/post -H "Content-Type: application/json" -d ''{"foo":"bar"}'''

    -- Run the curl command.
    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 @sbTargetCurl
        EXEC @hr = sp_OADestroy @httpCurl
        EXEC @hr = sp_OADestroy @responseJson
        RETURN
      END

    EXEC sp_OAMethod @httpCurl, 'GetResponseJson', @success OUT, @responseJson

    EXEC sp_OAGetProperty @httpCurl, 'StatusCode', @statusCode OUT

    PRINT 'response status code: ' + @statusCode

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

    -- Output:

    -- response status code: 200
    -- {
    --   "args": {},
    --   "data": {
    --     "foo": "bar"
    --   },
    --   "files": {},
    --   "form": {},
    --   "headers": {
    --     "host": "postman-echo.com",
    --     "content-length": "13",
    --     "content-type": "application/json",
    --     "x-forwarded-proto": "https",
    --     "accept-encoding": "gzip, br"
    --   },
    --   "json": {
    --     "foo": "bar"
    --   },
    --   "url": "https://postman-echo.com/post"
    -- }

    EXEC @hr = sp_OADestroy @sbTargetCurl
    EXEC @hr = sp_OADestroy @httpCurl
    EXEC @hr = sp_OADestroy @responseJson


END
GO