SQL Server
SQL Server
S3 List Objects in Bucket
See more Amazon S3 Examples
Demonstrates how to retrieve the XML listing of the objects (i.e. files) stored in an Amazon S3 bucket. (This is like a directory listing.)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'
DECLARE @strXml nvarchar(4000)
EXEC sp_OAMethod @http, 'S3_ListBucketObjects', @strXml OUT, 'chilkat100'
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 '----'
-- 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 @itemSize int
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, 'GetChildIntValue', @itemSize 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 + ', ' + @itemSize + ', ' + @iTmp0 + '-' + @iTmp1 + '-' + @iTmp2 + ':' + @iTmp3 + ':' + @iTmp4
SELECT @i = @i + 1
END
-- Sample output from the above loop:
-- 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
-- 5: cloud.search/dfe/indexer/pscc/2016/3/28/idx-2-15-0-25-87-0.json, 1238, 2-4-2016:11:33
-- 6: dude.gif, 6373, 25-5-2011:17:29
-- 7: french.txt, 47, 12-3-2017:12:18
-- 8: hamlet.xml, 279658, 2-5-2016:12:21
-- 9: hamlet_play.xml, 279658, 20-3-2017:8:22
-- 10: images/sea_creatures/starfish123.jpg, 6229, 19-1-2017:10:45
-- 11: images/sea_creatures/starfishåäö.jpg, 6229, 19-1-2017:12:7
-- 12: new folder/, 0, 26-11-2014:12:36
-- 13: new_starfish.jpg, 6229, 20-3-2017:8:22
-- 14: pigs.xml, 2804, 20-3-2017:8:22
-- 15: somethingBig.zip, 13089458, 26-9-2016:9:29
-- 16: starfish.jpg, 6229, 12-3-2017:12:18
-- 17: starfish/, 0, 10-11-2014:10:7
-- 18: starfish/starfish.jpg, 6229, 10-11-2014:10:8
-- 19: starfish/starfish2.jpg, 6229, 19-11-2014:10:36
-- 20: starfish/starfish3.jpg, 6229, 24-11-2014:14:33
-- 21: starfish2.jpg, 5987, 20-4-2012:12:6
-- 22: starfish3.jpg, 5987, 11-4-2012:7:10
-- 23: starfishA.jpg, 6229, 10-5-2016:8:44
-- 24: starfishCust.jpg, 6229, 12-11-2014:18:25
-- 25: xyz.ics, 1833, 25-5-2011:8:52
-- <?xml version="1.0" encoding="UTF-8" ?>
-- <ListBucketResult xmlns="http://s3.amazonaws.com/doc/2006-03-01/">
-- <Name>chilkat100</Name>
-- <Prefix />
-- <Marker />
-- <MaxKeys>1000</MaxKeys>
-- <IsTruncated>false</IsTruncated>
-- <Contents>
-- <Key>Abc.ics</Key>
-- <LastModified>2011-05-25T14:53:27.000Z</LastModified>
-- <ETag>"12ed2e28ff4abd4faead06b491836e64"</ETag>
-- <Size>1833</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>Corpse Bride film poster.jpg</Key>
-- <LastModified>2016-09-06T18:32:05.000Z</LastModified>
-- <ETag>"5a63ba56f0641244e4988dd171b8c5ae"</ETag>
-- <Size>53481</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>chiliPepper.gif</Key>
-- <LastModified>2017-03-12T17:18:16.000Z</LastModified>
-- <ETag>"8ee9b257fa70f69400d596977809da94"</ETag>
-- <Size>7718</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>chilkatdude.jpg</Key>
-- <LastModified>2011-05-20T21:14:44.000Z</LastModified>
-- <ETag>"6f7555035d080421e09e932daf19c79d"</ETag>
-- <Size>35137</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>cloud.search/dfe/indexer/pscc/2016/3/28/id,x-2-15-0-25-87-0.json</Key>
-- <LastModified>2016-04-02T17:00:10.000Z</LastModified>
-- <ETag>"518d70f41c9df4f85762ecde8a34cdea"</ETag>
-- <Size>1238</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>cloud.search/dfe/indexer/pscc/2016/3/28/idx-2-15-0-25-87-0.json</Key>
-- <LastModified>2016-04-02T16:33:36.000Z</LastModified>
-- <ETag>"518d70f41c9df4f85762ecde8a34cdea"</ETag>
-- <Size>1238</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>dude.gif</Key>
-- <LastModified>2011-05-25T22:29:31.000Z</LastModified>
-- <ETag>"208b908388905d442e81ded00a55541b"</ETag>
-- <Size>6373</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>french.txt</Key>
-- <LastModified>2017-03-12T17:18:16.000Z</LastModified>
-- <ETag>"54468302e7cda1c1dfe45748d430035a"</ETag>
-- <Size>47</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>hamlet.xml</Key>
-- <LastModified>2016-05-02T17:21:27.000Z</LastModified>
-- <ETag>"e04ea9ac8f616b0f35c5a7522de123dd"</ETag>
-- <Size>279658</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>hamlet_play.xml</Key>
-- <LastModified>2017-03-20T13:22:40.000Z</LastModified>
-- <ETag>"e04ea9ac8f616b0f35c5a7522de123dd"</ETag>
-- <Size>279658</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>images/sea_creatures/starfish123.jpg</Key>
-- <LastModified>2017-01-19T16:45:52.000Z</LastModified>
-- <ETag>"2e9c59dbf2662367dc97dfdda85da048"</ETag>
-- <Size>6229</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>images/sea_creatures/starfishåäö.jpg</Key>
-- <LastModified>2017-01-19T18:07:11.000Z</LastModified>
-- <ETag>"2e9c59dbf2662367dc97dfdda85da048"</ETag>
-- <Size>6229</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>new folder/</Key>
-- <LastModified>2014-11-26T18:36:22.000Z</LastModified>
-- <ETag>"d41d8cd98f00b204e9800998ecf8427e"</ETag>
-- <Size>0</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>new_starfish.jpg</Key>
-- <LastModified>2017-03-20T13:22:57.000Z</LastModified>
-- <ETag>"2e9c59dbf2662367dc97dfdda85da048"</ETag>
-- <Size>6229</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>pigs.xml</Key>
-- <LastModified>2017-03-20T13:22:58.000Z</LastModified>
-- <ETag>"8252a4499a0df9afbc5d8f3d9995d816"</ETag>
-- <Size>2804</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>somethingBig.zip</Key>
-- <LastModified>2016-09-26T14:29:36.000Z</LastModified>
-- <ETag>"109aa5c86378979c7b93752163c7a51f-3"</ETag>
-- <Size>13089458</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>starfish.jpg</Key>
-- <LastModified>2017-03-12T17:18:16.000Z</LastModified>
-- <ETag>"2e9c59dbf2662367dc97dfdda85da048"</ETag>
-- <Size>6229</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>starfish/</Key>
-- <LastModified>2014-11-10T16:07:45.000Z</LastModified>
-- <ETag>"d41d8cd98f00b204e9800998ecf8427e"</ETag>
-- <Size>0</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>starfish/starfish.jpg</Key>
-- <LastModified>2014-11-10T16:08:05.000Z</LastModified>
-- <ETag>"2e9c59dbf2662367dc97dfdda85da048"</ETag>
-- <Size>6229</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>starfish/starfish2.jpg</Key>
-- <LastModified>2014-11-19T16:36:38.000Z</LastModified>
-- <ETag>"2e9c59dbf2662367dc97dfdda85da048"</ETag>
-- <Size>6229</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>starfish/starfish3.jpg</Key>
-- <LastModified>2014-11-24T20:33:11.000Z</LastModified>
-- <ETag>"2e9c59dbf2662367dc97dfdda85da048"</ETag>
-- <Size>6229</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>starfish2.jpg</Key>
-- <LastModified>2012-04-20T17:06:45.000Z</LastModified>
-- <ETag>"525887bb7ff22c9b7f5446cb9748d59e"</ETag>
-- <Size>5987</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>starfish3.jpg</Key>
-- <LastModified>2012-04-11T12:10:40.000Z</LastModified>
-- <ETag>"525887bb7ff22c9b7f5446cb9748d59e"</ETag>
-- <Size>5987</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>starfishA.jpg</Key>
-- <LastModified>2016-05-10T13:44:15.000Z</LastModified>
-- <ETag>"2e9c59dbf2662367dc97dfdda85da048"</ETag>
-- <Size>6229</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>starfishCust.jpg</Key>
-- <LastModified>2014-11-13T00:25:17.000Z</LastModified>
-- <ETag>"2e9c59dbf2662367dc97dfdda85da048"</ETag>
-- <Size>6229</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>xyz.ics</Key>
-- <LastModified>2011-05-25T13:52:51.000Z</LastModified>
-- <ETag>"12ed2e28ff4abd4faead06b491836e64"</ETag>
-- <Size>1833</Size>
-- <Owner>
-- <ID>cf311e0b13e11b646949b7107f11b2bfbc5358f90df39395054ec40000695168</ID>
-- <DisplayName>johnson</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- </ListBucketResult>
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @dtObj
END
GO