SQL Server

MWS RequestReport (Amazon Marketplace Web Service)

See more Amazon MWS Examples

Creates a report request and submits the request to Amazon MWS.

See Amazon MWS RequestReport for more information.

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.

    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 Amazon MWS REST server.
    -- 
    -- Make sure to connect to the correct Amazon MWS Endpoint, otherwise
    -- you'll get an HTTP 401 response code.
    -- 
    -- The possible servers are:
    -- 
    -- North America (NA) 	https://mws.amazonservices.com
    -- Europe (EU) 	https://mws-eu.amazonservices.com
    -- India (IN) 	https://mws.amazonservices.in
    -- China (CN) 	https://mws.amazonservices.com.cn
    -- Japan (JP) 	https://mws.amazonservices.jp 
    -- 
    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, 'mws.amazonservices.com', @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

    EXEC sp_OASetProperty @rest, 'Host', 'mws.amazonservices.com'

    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'AWSAccessKeyId', '0PB842EXAMPLE7N4ZTR2'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Action', 'RequestReport'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'EndDate', '2008-06-26T18:12:21'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'MWSAuthToken', 'amzn.mws.4ea38b7b-f563-7709-4bae-87aeaEXAMPLE'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Marketplace', 'ATVPDKIKX0DER'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'ReportType', '_GET_MERCHANT_LISTINGS_DATA_'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'SellerId', 'A1XEXAMPLE5E6'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'SignatureMethod', 'HmacSHA256'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'SignatureVersion', '2'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'StartDate', '2009-01-03T18:12:21'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Version', '2009-01-01'

    -- Add the MWS Signature param.  (Also adds the Timestamp parameter using the curent system date/time.)
    -- The AddMwsSignature method adds the Timestamp and Signature query params.
    EXEC sp_OAMethod @rest, 'AddMwsSignature', @success OUT, 'POST', '/Reports/2009-01-01', 'mws.amazonservices.com', 'MWS_SECRET_KEY'

    DECLARE @responseXml nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestFormUrlEncoded', @responseXml OUT, 'POST', '/Reports/2009-01-01'
    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
        RETURN
      END

    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        -- Examine the request/response to see what happened.

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'response status code = ' + @iTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'response status text = ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT 'response header: ' + @sTmp0

        PRINT 'response body: ' + @responseXml

        PRINT '---'

        EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
        PRINT 'LastRequestStartLine: ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
        PRINT 'LastRequestHeader: ' + @sTmp0
      END

    -- Examine the XML returned in the response body.

    PRINT @responseXml

    PRINT '----'

    PRINT 'Success.'

    -- Sample Response

    -- Use this online tool to generate parsing code from sample XML: 
    -- Generate Parsing Code from XML

    -- <?xml version="1.0"?>
    -- <RequestReportResponse
    --     xmlns="http://mws.amazonaws.com/doc/2009-01-01/">
    --     <RequestReportResult>
    --         <ReportRequestInfo>
    --             <ReportRequestId>2291326454</ReportRequestId>
    --             <ReportType>_GET_MERCHANT_LISTINGS_DATA_</ReportType>
    --             <StartDate>2009-01-21T02:10:39+00:00</StartDate>
    --             <EndDate>2009-02-13T02:10:39+00:00</EndDate>
    --             <Scheduled>false</Scheduled>
    --             <SubmittedDate>2009-02-20T02:10:39+00:00</SubmittedDate>
    --             <ReportProcessingStatus>_SUBMITTED_</ReportProcessingStatus>
    --         </ReportRequestInfo>
    --     </RequestReportResult>
    --     <ResponseMetadata>
    --         <RequestId>88faca76-b600-46d2-b53c-0c8c4533e43a</RequestId>
    --     </ResponseMetadata>
    -- </RequestReportResponse>

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @responseXml

    DECLARE @RequestReportResponse_xmlns nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetAttrValue', @RequestReportResponse_xmlns OUT, 'xmlns'
    DECLARE @ReportRequestId nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @ReportRequestId OUT, 'RequestReportResult|ReportRequestInfo|ReportRequestId'
    DECLARE @ReportType nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @ReportType OUT, 'RequestReportResult|ReportRequestInfo|ReportType'
    DECLARE @StartDate nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @StartDate OUT, 'RequestReportResult|ReportRequestInfo|StartDate'
    DECLARE @EndDate nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @EndDate OUT, 'RequestReportResult|ReportRequestInfo|EndDate'
    DECLARE @Scheduled nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @Scheduled OUT, 'RequestReportResult|ReportRequestInfo|Scheduled'
    DECLARE @SubmittedDate nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @SubmittedDate OUT, 'RequestReportResult|ReportRequestInfo|SubmittedDate'
    DECLARE @ReportProcessingStatus nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @ReportProcessingStatus OUT, 'RequestReportResult|ReportRequestInfo|ReportProcessingStatus'
    DECLARE @RequestId nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @RequestId OUT, 'ResponseMetadata|RequestId'

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @xml


END
GO