SQL Server
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 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.
-- 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.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 = 0
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.Xml', @xml OUT
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 @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.CkDateTime', @dt OUT
DECLARE @dtObj int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dtObj 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
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
-- 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
EXEC @hr = sp_OADestroy @dtObj
END
GO