SQL Server

Amazon Glacier Describe Vault

See more Amazon Glacier Examples

Returns information about a vault, including the vault Amazon Resource Name (ARN), the date the vault was created, the number of archives contained within the vault, and the total size of all the archives in the vault... SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr 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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @rest int
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Connect to the Amazon AWS REST server in the desired region.
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'glacier.us-west-2.amazonaws.com', @port, @bTls, @bAutoReconnect

    -- Provide AWS credentials.
    DECLARE @authAws int
    EXEC @hr = sp_OACreate 'Chilkat.AuthAws', @authAws OUT

    EXEC sp_OASetProperty @authAws, 'AccessKey', 'AWS_ACCESS_KEY'
    EXEC sp_OASetProperty @authAws, 'SecretKey', 'AWS_SECRET_KEY'
    EXEC sp_OASetProperty @authAws, 'ServiceName', 'glacier'
    EXEC sp_OASetProperty @authAws, 'Region', 'us-west-2'

    EXEC sp_OAMethod @rest, 'SetAuthAws', @success OUT, @authAws

    -- --------------------------------------------------------------------------
    -- Note: The above REST connection and setup of the AWS credentials
    -- can be done once.  After connecting, any number of REST calls can be made.
    -- The "auto reconnect" property passed to rest.Connect indicates that if
    -- the connection is lost, a REST method call will automatically reconnect
    -- if needed.
    -- --------------------------------------------------------------------------

    -- 
    -- For more information, see Glacier Describe Vault Reference Documentation
    -- 
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'x-amz-glacier-version', '2012-06-01'

    -- Describe the "chilkat" vault
    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @rest, 'FullRequestNoBodySb', @success OUT, 'GET', '/AWS_ACCOUNT_ID/vaults/chilkat', @sbResponseBody
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authAws
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @respStatusCode OUT
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Status Code = ' + @respStatusCode

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Response Body:'
        EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authAws
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    -- Success is indicated by a 200 response status with an empty response body.

    PRINT 'response status code = ' + @respStatusCode

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

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

    DECLARE @CreationDate nvarchar(4000)

    DECLARE @LastInventoryDate nvarchar(4000)

    DECLARE @NumberOfArchives int

    DECLARE @SizeInBytes int

    DECLARE @VaultARN nvarchar(4000)

    DECLARE @VaultName nvarchar(4000)

    EXEC sp_OAMethod @json, 'StringOf', @CreationDate OUT, 'CreationDate'
    EXEC sp_OAMethod @json, 'StringOf', @LastInventoryDate OUT, 'LastInventoryDate'
    EXEC sp_OAMethod @json, 'IntOf', @NumberOfArchives OUT, 'NumberOfArchives'
    EXEC sp_OAMethod @json, 'IntOf', @SizeInBytes OUT, 'SizeInBytes'
    EXEC sp_OAMethod @json, 'StringOf', @VaultARN OUT, 'VaultARN'
    EXEC sp_OAMethod @json, 'StringOf', @VaultName OUT, 'VaultName'

    -- Sample response
    -- 
    -- {
    --   "CreationDate": "2019-04-05T19:10:28.428Z",
    --   "LastInventoryDate": null,
    --   "NumberOfArchives": 0,
    --   "SizeInBytes": 0,
    --   "VaultARN": "arn:aws:glacier:us-west-2:954491834127:vaults/chilkat",
    --   "VaultName": "chilkat"
    -- }
    -- 

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @authAws
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @json


END
GO