(SQL Server) HTTP POST JSON

Demonstrates how to send a JSON POST and get the JSON response.

Download Chilkat HTTP ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @req int
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    DECLARE @success int

    --  Any string unlocks the component for the 1st 30 days.
    EXEC sp_OAMethod @http, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  This example duplicates the HTTP POST shown at
    --  http://json.org/JSONRequest.html

    --  Specifically, the request to be sent looks like this:
    --  
POST /request HTTP/1.1
Accept: application/jsonrequest
Content-Encoding: identity
Content-Length: 72
Content-Type: application/jsonrequest
Host: json.penzance.org

{"user":"doctoravatar@penzance.com","forecast":7,"t":"vlIj","zip":94089}


    --  First, remove default header fields that would be automatically
    --  sent.  (These headers are harmless, and shouldn't need to
    --  be suppressed, but just in case...)
    EXEC sp_OASetProperty @http, 'AcceptCharset', ''
    EXEC sp_OASetProperty @http, 'UserAgent', ''
    EXEC sp_OASetProperty @http, 'AcceptLanguage', ''
    --  Suppress the Accept-Encoding header by disallowing
    --  a gzip response:
    EXEC sp_OASetProperty @http, 'AllowGzip', 0

    --  If a Cookie needs to be added, it may be added by calling
    --  AddQuickHeader:
    EXEC sp_OAMethod @http, 'AddQuickHeader', NULL, 'Cookie', 'JSESSIONID=1234'

    DECLARE @jsonText nvarchar(4000)

    SELECT @jsonText = '{"user":"doctoravatar@penzance.com","forecast":7,"t":"vlIj","zip":94089}'

    --  To use SSL/TLS, simply use "https://" in the URL.

    --  IMPORTANT: Make sure to change the URL, JSON text,
    --  and other data items to your own values.  The URL used
    --  in this example will not actually work.

    DECLARE @resp int

    EXEC sp_OAMethod @http, 'PostJson', @resp OUT, 'http://json.penzance.org/request', @jsonText
    IF @resp Is NULL 
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
      END
    ELSE
      BEGIN
        --  Display the JSON response.
        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT

        PRINT @sTmp0
      END


END
GO