SQL Server

REST URL Encode Path Parts and Query Params

See more REST Examples

When passing a path to a Chilkat REST function, the path parts and query params should be URL encoded. This example explains.. 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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example demonstrates how to URL encode the path passed to a REST function.
    -- It is demonstrated with an Amazon SP API GET request to get details about a listings item for a selling partner.
    -- See https://developer-docs.amazon.com/sp-api/docs/listings-items-api-v2021-08-01-reference#getlistingsitem

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

    -- Connect to the REST server.
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'sellingpartnerapi-eu.amazon.com', @port, @bTls, @bAutoReconnect

    EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'marketplaceids', 'XYZABC123'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'includedData', 'offers'

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'x-amz-access-token', 'YOUR_ACCESS_TOKEN'

    DECLARE @authAws int
    EXEC @hr = sp_OACreate 'Chilkat.AuthAws', @authAws OUT

    EXEC sp_OASetProperty @authAws, 'AccessKey', 'YOUR_AWS_APP_ID'
    EXEC sp_OASetProperty @authAws, 'SecretKey', 'YOUR_AWS_APP_SECRET_KEY'
    EXEC sp_OASetProperty @authAws, 'Region', 'eu-west-1'
    EXEC sp_OASetProperty @authAws, 'ServiceName', 'execute-api'
    EXEC sp_OAMethod @rest, 'SetAuthAws', @success OUT, @authAws

    -- The path that is passed to FullRequestNobBody

    -- Here's a sample path that is not yet URL encoded.
    DECLARE @path nvarchar(4000)
    SELECT @path = '/listings/2022-07-01/items/ABCDEFGHIJ/100x100_28g_LANCETS(BOXED)'

    -- The path passed to FullRequestNoBody needs to have the parts URL-encoded.
    -- The "/" chars are not URL encoded, but the individual path parts should be URL encoded.
    -- For example:  /listings/2022-07-01/items/ABCDEFGHIJ/100x100_28g_LANCETS%28BOXED%29

    -- In this case, we'll prepare the path like this:
    DECLARE @sbPath int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT

    EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '100x100_28g_LANCETS(BOXED)'
    -- URL encode the contents of the sbPath.
    EXEC sp_OAMethod @sbPath, 'Encode', @success OUT, 'url', 'utf-8'
    -- Prepend the remaining which does not need to be URL encoded.
    EXEC sp_OAMethod @sbPath, 'Prepend', @success OUT, '/listings/2022-07-01/items/ABCDEFGHIJ/'


    EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
    PRINT 'URL encoded path: ' + @sTmp0

    DECLARE @responseJson nvarchar(4000)
    EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseJson OUT, 'GET', @sTmp0
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authAws
        EXEC @hr = sp_OADestroy @sbPath
        RETURN
      END


    PRINT @responseJson

    PRINT '----'

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @authAws
    EXEC @hr = sp_OADestroy @sbPath


END
GO