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
    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