SQL Server
SQL Server
eBay -- Download Data using FileTransferService
See more eBay Examples
Demonstrates how to download a data file using the eBay File Transfer API.Chilkat SQL Server Downloads
-- 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 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
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @req int
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
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OASetProperty @xml, 'Tag', 'downloadFileRequest'
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 @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpSReq', @success OUT, 'storage.sandbox.ebay.com', 443, 1, @req, @resp
IF @success = 0
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
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @statusCode int
EXEC sp_OAGetProperty @resp, 'StatusCode', @statusCode OUT
PRINT 'Response status code = ' + @statusCode
DECLARE @responseBody int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @responseBody OUT
EXEC sp_OAMethod @resp, 'GetBodyBd', @success OUT, @responseBody
-- 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 @resp
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
EXEC @hr = sp_OACreate 'Chilkat.Mime', @mime OUT
EXEC sp_OAMethod @mime, 'LoadMimeBd', @success OUT, @responseBody
IF @success = 0
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 @resp
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 @resp
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 @hr = sp_OACreate 'Chilkat.Mime', @part0 OUT
EXEC sp_OAMethod @mime, 'PartAt', @success OUT, 0, @part0
IF @success = 0
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 @resp
EXEC @hr = sp_OADestroy @responseBody
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @part0
RETURN
END
DECLARE @downloadResponseXml nvarchar(4000)
EXEC sp_OAMethod @part0, 'GetBodyDecoded', @downloadResponseXml OUT
DECLARE @xmlResp int
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
PRINT '----'
-- Now get the zip from the second part (index=1), unzip, and examine..
DECLARE @part1 int
EXEC @hr = sp_OACreate 'Chilkat.Mime', @part1 OUT
EXEC sp_OAMethod @mime, 'PartAt', @success OUT, 1, @part1
IF @success = 0
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 @resp
EXEC @hr = sp_OADestroy @responseBody
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @part0
EXEC @hr = sp_OADestroy @xmlResp
EXEC @hr = sp_OADestroy @part1
RETURN
END
DECLARE @zipData int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @zipData OUT
EXEC sp_OAMethod @part1, 'GetBodyBd', @success OUT, @zipData
-- Check to see if we have a zip or gzip.
DECLARE @sbContentType int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbContentType OUT
EXEC sp_OAGetProperty @part1, 'ContentType', @sTmp0 OUT
EXEC sp_OAMethod @sbContentType, 'Append', @success OUT, @sTmp0
DECLARE @xmlFromZip int
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
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 = 0
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 @resp
EXEC @hr = sp_OADestroy @responseBody
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @part0
EXEC @hr = sp_OADestroy @xmlResp
EXEC @hr = sp_OADestroy @part1
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
EXEC @hr = sp_OACreate 'Chilkat.Zip', @zip OUT
EXEC sp_OAMethod @zip, 'OpenBd', @success OUT, @zipData
IF @success = 0
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 @resp
EXEC @hr = sp_OADestroy @responseBody
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @part0
EXEC @hr = sp_OADestroy @xmlResp
EXEC @hr = sp_OADestroy @part1
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 @resp
EXEC @hr = sp_OADestroy @responseBody
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @part0
EXEC @hr = sp_OADestroy @xmlResp
EXEC @hr = sp_OADestroy @part1
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 @hr = sp_OACreate 'Chilkat.ZipEntry', @entry OUT
EXEC sp_OAMethod @zip, 'EntryAt', @success OUT, 0, @entry
IF @success = 0
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 @resp
EXEC @hr = sp_OADestroy @responseBody
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @part0
EXEC @hr = sp_OADestroy @xmlResp
EXEC @hr = sp_OADestroy @part1
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
EXEC @hr = sp_OADestroy @entry
RETURN
END
EXEC sp_OAMethod @entry, 'UnzipToString', @sTmp0 OUT, 0, 'utf-8'
EXEC sp_OAMethod @xmlFromZip, 'LoadXml', @success OUT, @sTmp0
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 @resp
EXEC @hr = sp_OADestroy @responseBody
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @part0
EXEC @hr = sp_OADestroy @xmlResp
EXEC @hr = sp_OADestroy @part1
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
EXEC @hr = sp_OADestroy @entry
END
GO