(SQL Server) eBay -- Download Data using FileTransferService

Demonstrates how to download a data file using the eBay File Transfer API.

Note: This example requires Chilkat v9.5.0.67 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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Use a previously obtained access token.  The token should look something like this:
    -- "AgAAAA**AQA ..."
    DECLARE @accessToken nvarchar(4000)
    SELECT @accessToken = 'EBAY_ACCESS_TOKEN'

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @req int
    -- Use "Chilkat_9_5_0.HttpRequest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT

    EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
    EXEC sp_OASetProperty @req, 'Path', '/FileTransferService'
    EXEC sp_OASetProperty @req, 'ContentType', 'application/xml'

    -- Build the XML body for the request.
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'downloadFileRequest'
    DECLARE @success int
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns', 'http://www.ebay.com/marketplace/services'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'taskReferenceId', '50013004806'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'fileReferenceId', '50015579016'

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @req, 'LoadBodyFromString', @success OUT, @sTmp0, 'utf-8'

    -- The XML body looks like this:

    -- 	<?xml version="1.0" encoding="UTF-8"?>
    -- 	<downloadFileRequest xmlns="http://www.ebay.com/marketplace/services">
    -- 	<taskReferenceId>50013004806</taskReferenceId>
    -- 	<fileReferenceId>50015579016</fileReferenceId>
    -- 	</downloadFileRequest>

    EXEC sp_OAMethod @req, 'AddHeader', NULL, 'X-EBAY-SOA-OPERATION-NAME', 'downloadFile'
    EXEC sp_OAMethod @req, 'AddHeader', NULL, 'X-EBAY-SOA-SECURITY-TOKEN', @accessToken

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'SynchronousRequest', @resp OUT, 'storage.sandbox.ebay.com', 443, 1, @req
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    DECLARE @statusCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @statusCode OUT

    PRINT 'Response status code = ' + @statusCode

    DECLARE @responseBody int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @responseBody OUT

    EXEC sp_OAMethod @resp, 'GetBodyBd', @success OUT, @responseBody
    EXEC @hr = sp_OADestroy @resp

    -- We can save the response body to a file for examination if we get an unanticipated response.  
    -- (It's binary, so it won't open well in a text editor.)
    EXEC sp_OAMethod @responseBody, 'WriteFile', @success OUT, 'qa_output/response.mime'

    IF @statusCode <> 200
      BEGIN

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @responseBody
        RETURN
      END

    -- The response body looks like this:

    -- 	--MIMEBoundaryurn_uuid_2B668636C1E17A4D4114925305818684241
    -- 	Content-Type: application/xop+xml; charset=utf-8; type="text/xml"
    -- 	Content-Transfer-Encoding: binary
    -- 	Content-ID: <0.urn:uuid:2B668636C1E17A4D4114925305818684242>
    -- 
    -- 	<?xml version='1.0' encoding='UTF-8'?>
    -- 	<downloadFileResponse xmlns="http://www.ebay.com/marketplace/services">
    -- 	<ack>Success</ack>
    -- 	<version>1.1.0</version>
    -- 	<timestamp>2017-04-18T15:49:41.868Z</timestamp>
    -- 	<fileAttachment>
    -- 	    <Size>587</Size>
    -- 	    <Data>
    --                 <xop:Include xmlns:xop="http://www.w3.org/2004/08/xop/include" href="cid:urn:uuid:A37C3C73E994C267F11492530585522"/>
    -- 	    </Data>
    -- 	</fileAttachment>
    -- 	</downloadFileResponse>
    -- 	--MIMEBoundaryurn_uuid_2B668636C1E17A4D4114925305818684241
    -- 	Content-Type: application/zip
    -- 	Content-Transfer-Encoding: binary
    -- 	Content-ID: <urn:uuid:A37C3C73E994C267F11492530585522>
    -- 
    -- 	<the binary bytes of the zip start here...>
    -- 

    -- Load the binary response into a MIME object.
    DECLARE @mime int
    -- Use "Chilkat_9_5_0.Mime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Mime', @mime OUT

    DECLARE @success int
    EXEC sp_OAMethod @mime, 'LoadMimeBd', @success OUT, @responseBody
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @responseBody
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

    -- Make sure we have 2 sub-parts.  The 1st sub-part is the XML response, the 2nd sub-part
    -- is the zip containing the data.

    -- Note: The 2nd sub-part can be a "zip" or "gzip".  These are two different file formats.
    -- A zip is indicated with a Content-Type header equal to "application/zip",
    -- whereas a gzip is indicated with a Content-Type header equal to "application/x-gzip"
    EXEC sp_OAGetProperty @mime, 'NumParts', @iTmp0 OUT
    IF @iTmp0 <> 2
      BEGIN

        PRINT 'Expected the MIME to have 2 parts.'

        EXEC sp_OAGetProperty @mime, 'NumParts', @iTmp0 OUT
        PRINT 'NumParts = ' + @iTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @responseBody
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

    -- Get the XML from the 1st MIME sub-part.
    DECLARE @part0 int
    EXEC sp_OAMethod @mime, 'GetPart', @part0 OUT, 0
    DECLARE @downloadResponseXml nvarchar(4000)
    EXEC sp_OAMethod @part0, 'GetBodyDecoded', @downloadResponseXml OUT
    DECLARE @xmlResp int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlResp OUT

    EXEC sp_OAMethod @xmlResp, 'LoadXml', @success OUT, @downloadResponseXml

    PRINT 'Download Response XML:'
    EXEC sp_OAMethod @xmlResp, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0
    EXEC @hr = sp_OADestroy @part0


    PRINT '----'

    -- Now get the zip from the second part (index=1), unzip, and examine..
    DECLARE @part1 int
    EXEC sp_OAMethod @mime, 'GetPart', @part1 OUT, 1
    DECLARE @zipData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @zipData OUT

    -- This example requires Chilkat v9.5.0.67 or later.
    -- The GetBodyBd method was added in v9.5.0.67.
    EXEC sp_OAMethod @part1, 'GetBodyBd', @success OUT, @zipData

    -- Check to see if we have a zip or gzip.
    DECLARE @sbContentType int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbContentType OUT

    EXEC sp_OAGetProperty @part1, 'ContentType', @sTmp0 OUT
    EXEC sp_OAMethod @sbContentType, 'Append', @success OUT, @sTmp0
    EXEC @hr = sp_OADestroy @part1

    DECLARE @xmlFromZip int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlFromZip OUT

    EXEC sp_OAMethod @sbContentType, 'Contains', @iTmp0 OUT, 'gzip', 0
    IF @iTmp0 = 1
      BEGIN
        -- This is a gzip compressed file.
        DECLARE @gzip int
        -- Use "Chilkat_9_5_0.Gzip" for versions of Chilkat < 10.0.0
        EXEC @hr = sp_OACreate 'Chilkat.Gzip', @gzip OUT

        -- in-place uncompress the data.
        -- Note: The UncompressBd method was added in Chilkat v9.5.0.67
        EXEC sp_OAMethod @gzip, 'UncompressBd', @success OUT, @zipData
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @gzip, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @http
            EXEC @hr = sp_OADestroy @req
            EXEC @hr = sp_OADestroy @xml
            EXEC @hr = sp_OADestroy @responseBody
            EXEC @hr = sp_OADestroy @mime
            EXEC @hr = sp_OADestroy @xmlResp
            EXEC @hr = sp_OADestroy @zipData
            EXEC @hr = sp_OADestroy @sbContentType
            EXEC @hr = sp_OADestroy @xmlFromZip
            EXEC @hr = sp_OADestroy @gzip
            RETURN
          END
        EXEC sp_OAMethod @zipData, 'GetString', @sTmp0 OUT, 'utf-8'
        EXEC sp_OAMethod @xmlFromZip, 'LoadXml', @success OUT, @sTmp0

      END
    ELSE
      BEGIN
        -- This is a zip archive.

        -- Load the body into a Zip object.
        DECLARE @zip int
        -- Use "Chilkat_9_5_0.Zip" for versions of Chilkat < 10.0.0
        EXEC @hr = sp_OACreate 'Chilkat.Zip', @zip OUT

        EXEC sp_OAMethod @zip, 'OpenBd', @success OUT, @zipData
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @http
            EXEC @hr = sp_OADestroy @req
            EXEC @hr = sp_OADestroy @xml
            EXEC @hr = sp_OADestroy @responseBody
            EXEC @hr = sp_OADestroy @mime
            EXEC @hr = sp_OADestroy @xmlResp
            EXEC @hr = sp_OADestroy @zipData
            EXEC @hr = sp_OADestroy @sbContentType
            EXEC @hr = sp_OADestroy @xmlFromZip
            EXEC @hr = sp_OADestroy @gzip
            EXEC @hr = sp_OADestroy @zip
            RETURN
          END

        -- Save the .zip to a file (so we can examine it for debugging if something is not as expected)
        EXEC sp_OAMethod @zipData, 'WriteFile', @success OUT, 'qa_output/ebay_data.zip'

        -- The zip should contain a single XML file.
        EXEC sp_OAGetProperty @zip, 'NumEntries', @iTmp0 OUT
        IF @iTmp0 <> 1
          BEGIN

            PRINT 'Expected the .zip to have 1 entry.'

            EXEC sp_OAGetProperty @zip, 'NumEntries', @iTmp0 OUT
            PRINT 'NumEntries = ' + @iTmp0

            PRINT 'Failed.'
            EXEC @hr = sp_OADestroy @http
            EXEC @hr = sp_OADestroy @req
            EXEC @hr = sp_OADestroy @xml
            EXEC @hr = sp_OADestroy @responseBody
            EXEC @hr = sp_OADestroy @mime
            EXEC @hr = sp_OADestroy @xmlResp
            EXEC @hr = sp_OADestroy @zipData
            EXEC @hr = sp_OADestroy @sbContentType
            EXEC @hr = sp_OADestroy @xmlFromZip
            EXEC @hr = sp_OADestroy @gzip
            EXEC @hr = sp_OADestroy @zip
            RETURN
          END

        DECLARE @entry int
        EXEC sp_OAMethod @zip, 'GetEntryByIndex', @entry OUT, 0

        EXEC sp_OAMethod @entry, 'UnzipToString', @sTmp0 OUT, 0, 'utf-8'
        EXEC sp_OAMethod @xmlFromZip, 'LoadXml', @success OUT, @sTmp0
        EXEC @hr = sp_OADestroy @entry

      END


    PRINT 'XML contained in the zip:'
    EXEC sp_OAMethod @xmlFromZip, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '----'


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @responseBody
    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @xmlResp
    EXEC @hr = sp_OADestroy @zipData
    EXEC @hr = sp_OADestroy @sbContentType
    EXEC @hr = sp_OADestroy @xmlFromZip
    EXEC @hr = sp_OADestroy @gzip
    EXEC @hr = sp_OADestroy @zip


END
GO