SQL Server
SQL Server
S3 Get Bucket Objects XML
See more Amazon S3 Examples
Gets information about the objects (files) in a bucket.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 @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: 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