(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 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
    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)
    -- This example assumes the Chilkat HTTP API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    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

    -- 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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbContinuationToken OUT

    DECLARE @bGetMore int
    SELECT @bGetMore = 1
    DECLARE @sbUri int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUri OUT

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

    DECLARE @success int

    DECLARE @itemKey nvarchar(4000)

    DECLARE @itemSizeDecimalStr nvarchar(4000)

    DECLARE @lastModTimestamp nvarchar(4000)

    DECLARE @dt int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt 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 <> 1
          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
            RETURN
          END


        EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
        PRINT 'Response status code = ' + @iTmp0

        EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @strXml
        IF @success <> 1
          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
            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
            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
            DECLARE @dtObj int
            EXEC sp_OAMethod @dt, 'GetDtObj', @dtObj OUT, @bLocal




            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
            EXEC @hr = sp_OADestroy @dtObj

            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


END
GO