SQL Server
SQL Server
Wasabi List Bucket Objects
See more Wasabi Examples
Demonstrates how to download and parse XML for the list of objects 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
-- 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', 'access-key'
-- Insert your secret key here:
EXEC sp_OASetProperty @http, 'AwsSecretKey', 'secret-key'
-- Use the endpoint matching the bucket's region.
EXEC sp_OASetProperty @http, 'AwsEndpoint', 's3.us-west-1.wasabisys.com'
DECLARE @bucketName nvarchar(4000)
SELECT @bucketName = 'chilkattest'
DECLARE @strXml nvarchar(4000)
EXEC sp_OAMethod @http, 'S3_ListBucketObjects', @strXml OUT, @bucketName
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.Xml', @xml OUT
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
-- <?xml version="1.0" encoding="UTF-8"?>
-- <ListBucketResult xmlns="http://s3.amazonaws.com/doc/2006-03-01/">
-- <Name>chilkattest</Name>
-- <Prefix/>
-- <Marker/>
-- <MaxKeys>1000</MaxKeys>
-- <IsTruncated>false</IsTruncated>
-- <Contents>
-- <Key>orchard.json</Key>
-- <LastModified>2021-10-27T22:58:49.000Z</LastModified>
-- <ETag>"303fb46cf341094fef6274b7789cd6aa"</ETag>
-- <Size>22</Size>
-- <Owner>
-- <ID>1039F31570DBC320E89D391632FCA06FE6D10CBB2ADBD0BF6439BB1DA0C3FAD6</ID>
-- <DisplayName>admin</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>seahorse.jpg</Key>
-- <LastModified>2021-10-27T22:35:29.000Z</LastModified>
-- <ETag>"a8551f0a5437f43a796fca7623ee9232"</ETag>
-- <Size>24388</Size>
-- <Owner>
-- <ID>1039F31570DBC320E89D391632FCA06FE6D10CBB2ADBD0BF6439BB1DA0C3FAD6</ID>
-- <DisplayName>admin</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- <Contents>
-- <Key>seahorse2.jpg</Key>
-- <LastModified>2021-10-27T22:03:51.000Z</LastModified>
-- <ETag>"a8551f0a5437f43a796fca7623ee9232"</ETag>
-- <Size>24388</Size>
-- <Owner>
-- <ID>1039F31570DBC320E89D391632FCA06FE6D10CBB2ADBD0BF6439BB1DA0C3FAD6</ID>
-- <DisplayName>admin</DisplayName>
-- </Owner>
-- <StorageClass>STANDARD</StorageClass>
-- </Contents>
-- </ListBucketResult>
DECLARE @Key nvarchar(4000)
DECLARE @LastModified nvarchar(4000)
DECLARE @ETag nvarchar(4000)
DECLARE @SizeDecimalStr nvarchar(4000)
DECLARE @ID nvarchar(4000)
DECLARE @DisplayName nvarchar(4000)
DECLARE @StorageClass nvarchar(4000)
DECLARE @ListBucketResult_xmlns nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetAttrValue', @ListBucketResult_xmlns OUT, 'xmlns'
DECLARE @Name nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetChildContent', @Name OUT, 'Name'
DECLARE @MaxKeys int
EXEC sp_OAMethod @xml, 'GetChildIntValue', @MaxKeys OUT, 'MaxKeys'
DECLARE @IsTruncated nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetChildContent', @IsTruncated OUT, 'IsTruncated'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_i OUT, 'Contents'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @xml, 'I', @i
EXEC sp_OAMethod @xml, 'GetChildContent', @Key OUT, 'Contents[i]|Key'
EXEC sp_OAMethod @xml, 'GetChildContent', @LastModified OUT, 'Contents[i]|LastModified'
EXEC sp_OAMethod @xml, 'GetChildContent', @ETag OUT, 'Contents[i]|ETag'
EXEC sp_OAMethod @xml, 'GetChildContent', @SizeDecimalStr OUT, 'Contents[i]|Size'
EXEC sp_OAMethod @xml, 'GetChildContent', @ID OUT, 'Contents[i]|Owner|ID'
EXEC sp_OAMethod @xml, 'GetChildContent', @DisplayName OUT, 'Contents[i]|Owner|DisplayName'
EXEC sp_OAMethod @xml, 'GetChildContent', @StorageClass OUT, 'Contents[i]|StorageClass'
PRINT @i + ': ' + @Key
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @xml
END
GO