(SQL Server) S3 Upload String using AWS Signature Version 2

Example to upload the contents of a string to the Amazon S3 service, using the older AWS Signature Version 2.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- Demonstrates how to use older AWS S3 Signature Version 2 for uploading the contents
    -- of a string variable to an object in a bucket.

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    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.
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    -- The file is uploaded to the bucket named "chilkat100", which becomes part of the domain:
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'chilkat100.s3.amazonaws.com', @port, @bTls, @bAutoReconnect

    -- ----------------------------------------------------------------------------
    -- Important: For buckets created in regions outside us-east-1,
    -- there are three important changes that need to be made.
    -- See Working with S3 Buckets in Non-us-east-1 Regions for the details.
    -- ----------------------------------------------------------------------------

    -- Provide AWS credentials for the REST call.
    DECLARE @authAws int
    -- Use "Chilkat_9_5_0.AuthAws" for versions of Chilkat < 10.0.0
    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', 's3'

    -- For AWS Signature Version 2, the following two properties need to be set:
    EXEC sp_OASetProperty @authAws, 'SignatureVersion', 2
    -- See http://docs.aws.amazon.com/AmazonS3/latest/dev/RESTAuthentication.html#ConstructingTheCanonicalizedResourceElement
    -- The bucket is "chilkat100", and the uriPath is "/hamlet_play.xml", which must match
    -- the URI path passed to the FullRequestString method below.
    EXEC sp_OASetProperty @authAws, 'CanonicalizedResourceV2', '/chilkat100/hamlet_play.xml'

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

    -- Load a text file into memory.
    DECLARE @fac int
    -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

    DECLARE @fileContents nvarchar(4000)
    EXEC sp_OAMethod @fac, 'ReadEntireTextFile', @fileContents OUT, 'qa_data/xml/hamlet.xml', 'utf-8'
    EXEC sp_OAGetProperty @fac, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @fac, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authAws
        EXEC @hr = sp_OADestroy @fac
        RETURN
      END

    -- To send the file in gzip or deflate compressed format, set the Content-Encoding request
    -- header to "gzip" or "deflate".  (this is optional)
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Encoding', 'gzip'

    -- Indicate the Content-Type of our upload.  (This is optional)
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'text/xml'

    -- We can add an "Expect: 100-continue" header so that if the request is rejected
    -- by the server immediately after receiving the request header, it can respond
    -- and the client (Chilkat) can avoid sending the file data.
    -- (this is optional)
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Expect', '100-continue'

    -- Upload the file to Amazon S3.
    DECLARE @responseBodyStr nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestString', @responseBodyStr OUT, 'PUT', '/hamlet_play.xml', @fileContents
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 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 @fac
        RETURN
      END

    -- Did we get a 200 response indicating success?
    DECLARE @statusCode int
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @statusCode OUT
    IF @statusCode <> 200
      BEGIN

        PRINT 'Error response: ' + @responseBodyStr


        EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'Status code: ' + @statusCode + ', Status text: ' + @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authAws
        EXEC @hr = sp_OADestroy @fac
        RETURN
      END


    PRINT 'File successfully uploaded.'

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @authAws
    EXEC @hr = sp_OADestroy @fac


END
GO