(SQL Server) Debug REST HTTP Request

Demonstrates how to generate the HTTP Request (with all headers intact) without actually sending the request.

Note: This example requires Chilkat v9.5.0.77 or later.

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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example will connect to the web server, but does not actually send a request.
    -- When in DebugMode, the request is composed in memory and can be retrieved by calling
    -- GetLastDebugRequest.

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

    -- Connect Code...
    -- URL: https://test-api.service.hmrc.gov.uk/organisations/vat/MY_HMRC_VRN/returns
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'test-api.service.hmrc.gov.uk', @port, @bTls, @bAutoReconnect
    IF @success <> 1
      BEGIN

        EXEC sp_OAGetProperty @rest, 'ConnectFailReason', @iTmp0 OUT
        PRINT 'ConnectFailReason: ' + @iTmp0
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Build the request body...
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'periodKey', 'A001'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'vatDueSales', '105.50'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'vatDueAcquisitions', '-100.45'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'totalVatDue', '5.05'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'vatReclaimedCurrPeriod', '105.15'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'netVatDue', '100.10'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'totalValueSalesExVAT', 300
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'totalValuePurchasesExVAT', 300
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'totalValueGoodsSuppliedExVAT', 3000
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'totalAcquisitionsExVAT', 3000
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'finalised', 1

    -- Add Headers...
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Accept', 'application/vnd.hmrc.1.0+json'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Authorization', 'Bearer HMRC_ACCESS_TOKEN'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/json'

    DECLARE @sbRequestBody int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbRequestBody OUT

    EXEC sp_OAMethod @json, 'EmitSb', @success OUT, @sbRequestBody

    -- Set DebugMode so that no request is actually sent.
    EXEC sp_OASetProperty @rest, 'DebugMode', 1

    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @rest, 'FullRequestSb', @success OUT, 'POST', '/organisations/vat/MY_HMRC_VRN/returns', @sbRequestBody, @sbResponseBody
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbRequestBody
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    -- Get the exact contents of what would've been sent.
    -- This includes the HTTP start line, the HTTP request headers, and the request body.
    -- Given that it's possible for the request body to contain binary data,
    -- the GetLastDebugRequest fetches into a BinData object.
    -- In this case, however, our request body contained JSON, so we can
    -- examine it as a string..
    DECLARE @bdRequest int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdRequest OUT

    EXEC sp_OAMethod @rest, 'GetLastDebugRequest', @success OUT, @bdRequest


    PRINT '----'
    EXEC sp_OAMethod @bdRequest, 'GetString', @sTmp0 OUT, 'utf-8'
    PRINT @sTmp0

    PRINT '----'

    -- The output for the above case:

    -- POST /organisations/vat/MY_HMRC_VRN/returns HTTP/1.1
    -- Accept: application/vnd.hmrc.1.0+json
    -- Host: test-api.service.hmrc.gov.uk
    -- Authorization: Bearer HMRC_ACCESS_TOKEN
    -- Content-Type: application/json
    -- Content-Length: 281
    -- 
    -- {"periodKey":"A001","vatDueSales":105.50, ... ,"finalised":true}
    -- 
    -- 

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sbRequestBody
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @bdRequest


END
GO