SQL Server

POST JSON to REST API with non-us-ascii Chars Escaped

See more REST Examples

Demonstrates how to POST to a REST API with non-usascii chars within JSON Unicode escaped. 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

    SELECT @success = 0

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

    -- Connect using TLS.
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'chilkatsoft.com', 443, 1, @bAutoReconnect

    -- Load JSON containing the following Korean text.

    --  {
    --    "BillAddr": {
    --       "Id": "239615",
    --       "Line1": "류리하",
    --       "Line2": "류리하류리하",
    --       "City": "류리하류리하",
    --       "Country": "US",
    --       "CountrySubDivisionCode": "AK",
    --       "PostalCode": "류리하"
    --     }
    -- }

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/korean.json'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @json, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/json; charset=UTF-8'

    DECLARE @sb int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT

    EXEC sp_OAMethod @json, 'EmitSb', @success OUT, @sb
    EXEC sp_OAMethod @sb, 'Encode', @success OUT, 'unicodeescape', 'utf-8'

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

    -- The StringBuilder contains this:

    -- {
    --   "BillAddr": {
    --     "Id": "239615",
    --     "Line1": "\ub958\ub9ac\ud558",
    --     "Line2": "\ub958\ub9ac\ud558\ub958\ub9ac\ud558",
    --     "City": "\ub958\ub9ac\ud558\ub958\ub9ac\ud558",
    --     "Country": "US",
    --     "CountrySubDivisionCode": "AK",
    --     "PostalCode": "\ub958\ub9ac\ud558"
    --   }
    -- }

    DECLARE @sbResp int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResp OUT

    EXEC sp_OAMethod @rest, 'FullRequestSb', @success OUT, 'POST', '/echo_request_body.asp', @sb, @sbResp
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @sbResp
        RETURN
      END

    -- Show the response. 

    EXEC sp_OAMethod @sbResp, 'GetAsString', @sTmp0 OUT
    PRINT 'Json Response: ' + @sTmp0

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @sbResp


END
GO