SQL Server
SQL Server
Amazon S3 List More than 1000 Objects in Bucket
See more Amazon S3 Examples
S3 limits the size of the "List Objects" response to 1000 objects. To retrieve a listing of all of the objects in a bucket containing more than 1000 objects, we'll need to send several requests using continuation tokens.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
DECLARE @iTmp1 int
DECLARE @iTmp2 int
DECLARE @iTmp3 int
DECLARE @iTmp4 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 HTTP API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Insert your access key here:
EXEC sp_OASetProperty @http, 'AwsAccessKey', 'AWS_ACCESS_KEY'
-- Insert your secret key here:
EXEC sp_OASetProperty @http, 'AwsSecretKey', 'AWS_SECRET_KEY'
-- For the example, we'll get a listing containing approx. 25 objects
-- using continuation tokens with an artificially low max-keys set to 5.
-- (You may omit the max-keys query parameter to get the default 1000 maximum
-- number of keys per request.)
DECLARE @sbContinuationToken int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbContinuationToken OUT
DECLARE @bGetMore int
SELECT @bGetMore = 1
DECLARE @sbUri int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUri OUT
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
DECLARE @itemKey nvarchar(4000)
DECLARE @itemSizeDecimalStr nvarchar(4000)
DECLARE @lastModTimestamp nvarchar(4000)
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT
DECLARE @dtObj int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dtObj OUT
WHILE @bGetMore = 1
BEGIN
-- Make sure to set the "list-type" query param equal to "2".
-- This chooses the GET Bucket (List Objects) Version 2 of the method.
-- IMPORTANT: You must include the max-keys param to get a response with a continuation token.
-- S3 limits the number of objects in a single response to 1000. Therefore, your max-keys
-- can be up to 1000. If, for example, you have 2000 objects and do not specify
-- max-keys, then the response will contain 1000 objects with no continuation token.
EXEC sp_OAMethod @sbUri, 'SetString', @success OUT, 'chilkat100?list-type=2&max-keys=5'
EXEC sp_OAGetProperty @sbContinuationToken, 'Length', @iTmp0 OUT
IF @iTmp0 > 0
BEGIN
EXEC sp_OAMethod @sbUri, 'Append', @success OUT, '&continuation-token='
EXEC sp_OAMethod @sbContinuationToken, 'GetEncoded', @sTmp0 OUT, 'url', 'utf-8'
EXEC sp_OAMethod @sbUri, 'Append', @success OUT, @sTmp0
END
DECLARE @strXml nvarchar(4000)
EXEC sp_OAMethod @sbUri, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'S3_ListBucketObjects', @strXml OUT, @sTmp0
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbContinuationToken
EXEC @hr = sp_OADestroy @sbUri
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @dtObj
RETURN
END
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
PRINT 'Response status code = ' + @iTmp0
EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @strXml
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbContinuationToken
EXEC @hr = sp_OADestroy @sbUri
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @dtObj
RETURN
END
-- If the response status code was not 200, then the XML response is not a
-- listing of objects, but instead contains error information.
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbContinuationToken
EXEC @hr = sp_OADestroy @sbUri
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @dtObj
RETURN
END
-- If this is not the final response, then we'll get an XML response that begins
-- like this. (The IsTruncated child will be "true", and the NextContinuationToken
-- will have a value to be used in the next request.)
-- <?xml version="1.0" encoding="UTF-8" ?>
-- <ListBucketResult xmlns="http://s3.amazonaws.com/doc/2006-03-01/">
-- <Name>chilkat100</Name>
-- <Prefix />
-- <NextContinuationToken>1Mlcx+W9OKsr8cxp3DP6r71wgsTUMj0vqlntWoaJKNbYdrauLdf40LsUdBeSYGFhzbGIHdcf5DSLcEBWbqG+1fW1UcQkUW1V4qgQONAKOwb8y8vOLJAQ8iQ==</NextContinuationToken>
-- <KeyCount>5</KeyCount>
-- <MaxKeys>5</MaxKeys>
-- <IsTruncated>true</IsTruncated>
--
-- Iterate over the bucket items in this chunk get information for each..
DECLARE @numItems int
EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @numItems OUT, 'Contents'
PRINT 'Number of bucket items = ' + @numItems
DECLARE @i int
SELECT @i = 0
WHILE @i < @numItems
BEGIN
EXEC sp_OASetProperty @xml, 'I', @i
EXEC sp_OAMethod @xml, 'GetChildContent', @itemKey OUT, 'Contents[i]|Key'
EXEC sp_OAMethod @xml, 'GetChildContent', @itemSizeDecimalStr OUT, 'Contents[i]|Size'
EXEC sp_OAMethod @xml, 'GetChildContent', @lastModTimestamp OUT, 'Contents[i]|LastModified'
EXEC sp_OAMethod @dt, 'SetFromRfc822', @success OUT, @lastModTimestamp
-- Get a local date/time.
DECLARE @bLocal int
SELECT @bLocal = 1
EXEC sp_OAMethod @dt, 'ToDtObj', NULL, @bLocal, @dtObj
EXEC sp_OAGetProperty @dtObj, 'Day', @iTmp0 OUT
EXEC sp_OAGetProperty @dtObj, 'Month', @iTmp1 OUT
EXEC sp_OAGetProperty @dtObj, 'Year', @iTmp2 OUT
EXEC sp_OAGetProperty @dtObj, 'Hour', @iTmp3 OUT
EXEC sp_OAGetProperty @dtObj, 'Minute', @iTmp4 OUT
PRINT @i + ': ' + @itemKey + ', ' + @itemSizeDecimalStr + ', ' + @iTmp0 + '-' + @iTmp1 + '-' + @iTmp2 + ':' + @iTmp3 + ':' + @iTmp4
SELECT @i = @i + 1
END
-- Check IsTruncated.
SELECT @bGetMore = 0
EXEC sp_OAMethod @xml, 'ChildContentMatches', @iTmp0 OUT, 'IsTruncated', 'true', 1
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'NextContinuationToken'
EXEC sp_OAMethod @sbContinuationToken, 'SetString', @success OUT, @sTmp0
EXEC sp_OAGetProperty @sbContinuationToken, 'Length', @iTmp0 OUT
IF @iTmp0 > 0
BEGIN
SELECT @bGetMore = 1
END
END
END
-- The output of this example (when tested) was:
-- Response status code = 200
-- Number of bucket items = 5
-- 0: Abc.ics, 1833, 25-5-2011:9:53
-- 1: Corpse Bride film poster.jpg, 53481, 6-9-2016:13:32
-- 2: chiliPepper.gif, 7718, 12-3-2017:12:18
-- 3: chilkatdude.jpg, 35137, 20-5-2011:16:14
-- 4: cloud.search/dfe/indexer/pscc/2016/3/28/id,x-2-15-0-25-87-0.json, 1238, 2-4-2016:12:0
-- Response status code = 200
-- Number of bucket items = 5
-- 0: cloud.search/dfe/indexer/pscc/2016/3/28/idx-2-15-0-25-87-0.json, 1238, 2-4-2016:11:33
-- 1: dude.gif, 6373, 25-5-2011:17:29
-- 2: french.txt, 47, 12-3-2017:12:18
-- 3: hamlet.xml, 279658, 2-5-2016:12:21
-- 4: hamlet_play.xml, 279658, 20-3-2017:8:22
-- Response status code = 200
-- Number of bucket items = 5
-- 0: images/sea_creatures/starfish123.jpg, 6229, 19-1-2017:10:45
-- 1: images/sea_creatures/starfishåäö.jpg, 6229, 19-1-2017:12:7
-- 2: new folder/, 0, 26-11-2014:12:36
-- 3: new_starfish.jpg, 6229, 20-3-2017:8:22
-- 4: pigs.xml, 2804, 20-3-2017:8:22
-- Response status code = 200
-- Number of bucket items = 5
-- 0: somethingBig.zip, 13089458, 26-9-2016:9:29
-- 1: starfish.jpg, 6229, 12-3-2017:12:18
-- 2: starfish/, 0, 10-11-2014:10:7
-- 3: starfish/starfish.jpg, 6229, 10-11-2014:10:8
-- 4: starfish/starfish2.jpg, 6229, 19-11-2014:10:36
-- Response status code = 200
-- Number of bucket items = 5
-- 0: starfish/starfish3.jpg, 6229, 24-11-2014:14:33
-- 1: starfish2.jpg, 5987, 20-4-2012:12:6
-- 2: starfish3.jpg, 5987, 11-4-2012:7:10
-- 3: starfishA.jpg, 6229, 10-5-2016:8:44
-- 4: starfishCust.jpg, 6229, 12-11-2014:18:25
-- Response status code = 200
-- Number of bucket items = 1
-- 0: xyz.ics, 1833, 25-5-2011:8:52
--
--
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbContinuationToken
EXEC @hr = sp_OADestroy @sbUri
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @dtObj
END
GO