(SQL Server) S3 List Objects in Bucket Folder

See more Amazon S3 Examples

Demonstrates how to retrieve the XML listing of the objects (i.e. files) stored in an Amazon S3 bucket, but just within a particular folder.

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

    -- In Amazon S3 (Simple Storage Service), there is a distinction between buckets and folders,
    -- which are both used for organizing and storing data.

    -- Bucket: A bucket is the top-level container in Amazon S3. It is similar to a directory or folder
    -- at the root level and acts as a unique namespace for your objects (files). Each bucket must have a globally
    -- unique name across all of Amazon S3. Buckets are created at the AWS account level and are used to store objects.

    -- Folder (Prefix): In Amazon S3, folders are not actual physical entities but are virtual constructs called prefixes.
    -- They are part of the object keys used to organize and group objects within a bucket. Object keys are essentially the
    -- full path or name of an object, including the folder structure. For example, if you have an object named
    -- "my-image.jpg" inside a folder named "photos," the object key would be "photos/my-image.jpg."
    -- Folders are helpful for organizing objects within a bucket and creating a hierarchical structure.

    -- In reality, S3 does not have a true folder structure. It stores objects in a flat structure with unique keys.
    -- However, the keys can include slashes ("/") to create a visual separation that resembles a folder structure.
    -- Various tools and S3 clients interpret the slashes as folder separators and display them accordingly.

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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'

    -- ----------------------------------------------------------------------------------
    -- List the objects having prefix "images/"
    -- ----------------------------------------------------------------------------------
    DECLARE @strXml nvarchar(4000)
    EXEC sp_OAMethod @http, 'S3_ListBucketObjects', @strXml OUT, 'chilkat100?prefix=images/'
    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
        RETURN
      END


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

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml OUT

    DECLARE @success int
    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 @xml
        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 @xml
        RETURN
      END

    -- A sample response is shown below.
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '----'

    -- Use this online tool to generate parsing code from sample XML: 
    -- Generate Parsing Code from XML

    -- Iterate over the bucket items and get information for each..
    DECLARE @numItems int
    EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @numItems OUT, 'Contents'

    PRINT 'Number of bucket items = ' + @numItems

    DECLARE @itemKey nvarchar(4000)

    DECLARE @itemSizeDecimalStr nvarchar(4000)

    DECLARE @lastModTimestamp nvarchar(4000)

    DECLARE @dt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.CkDateTime', @dt OUT

    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

    -- Sample output from the above loop:

    -- 0: images/africa/, 0, 11-6-2020:20:18
    -- 1: images/africa/giraffe.jpg, 262769, 11-6-2020:20:20
    -- 2: images/africa/lion.jpg, 1026769, 11-6-2020:20:20
    -- 3: images/sea_creatures/starfish123.jpg, 6229, 19-1-2017:10:45
    -- 4: images/sea_creatures/starfish.jpg, 6229, 19-1-2017:12:7

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @dt


END
GO