(SQL Server) S3 Upload the Parts for a Multipart Upload

This example uploads a large file in parts. The multipart upload needs to have been first initiated prior to uploading the parts.

See http://docs.aws.amazon.com/AmazonS3/latest/API/mpUploadUploadPart.html for more information about uploading parts.

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)
    -- In the 1st step for uploading a large file, the multipart upload was initiated
    -- as shown here: Initiate Multipart Upload

    -- Other S3 Multipart Upload Examples:
    -- Complete Multipart Upload
    -- Abort Multipart Upload
    -- List Parts

    -- When we initiated the multipart upload, we saved the XML response to a file.  This
    -- XML response contains the UploadId.  We'll begin by loading that XML and getting
    -- the Upload ID.

    DECLARE @xmlInit int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlInit OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @xmlInit, 'LoadXmlFile', @success OUT, 's3_multipart_uploads/initiate.xml'
    IF @success <> 1
      BEGIN

        PRINT 'Did not find the initiate.xml XML file.'
        EXEC @hr = sp_OADestroy @xmlInit
        RETURN
      END

    DECLARE @uploadId nvarchar(4000)
    EXEC sp_OAMethod @xmlInit, 'GetChildContent', @uploadId OUT, 'UploadId'

    PRINT 'UploadId = ' + @uploadId

    -- When uploading parts, we need to keep an XML record of each part number
    -- and its corresponding ETag, which is received in the response for each part.
    -- There can be up to 10000 parts, numbered 1 to 10000.  
    -- After all parts have been uploaded, the final step will be to complete
    -- the multipart upload (see Complete Multipart Upload)

    -- In this example, the large file we want to upload is somethingBig.zip
    DECLARE @fileToUploadPath nvarchar(4000)
    SELECT @fileToUploadPath = 's3_multipart_uploads/somethingBig.zip'

    -- The minimum allowed part size is 5MB (5242880 bytes).  The last part can be smaller because
    -- it will contain the remainder of the file.  (This minimum is enforced by the AWS service.)
    -- We'll use the minimum allowed part size for this example.
    DECLARE @partSize int
    SELECT @partSize = 5242880

    -- Let's use Chilkat's FileAccess API to examine the file to be uploaded.  We'll get the size
    -- of the file and find out how many parts will be needed, including the final "partial" part.
    DECLARE @fac int
    -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

    EXEC sp_OAMethod @fac, 'OpenForRead', @success OUT, @fileToUploadPath

    -- How many parts will there be if each part is 5242880 bytes?
    DECLARE @numParts int
    EXEC sp_OAMethod @fac, 'GetNumBlocks', @numParts OUT, @partSize

    PRINT 'numParts = ' + @numParts
    EXEC sp_OAMethod @fac, 'FileClose', NULL

    -- Imagine that we may be running this for the 1st time, or maybe we already
    -- attempted to upload parts, and something failed. Maybe there was a network problem
    -- the resulted in not all parts getting uploaded.  We'll write this code so that if run again,
    -- it will upload whatever parts haven't yet been uploaded.

    -- We'll keep a partsList.xml file to record the parts that have already been successfully
    -- uploaded.  If this file does not yet exist, we'll create it..
    DECLARE @partsListFile nvarchar(4000)
    SELECT @partsListFile = 's3_multipart_uploads/partsList.xml'
    DECLARE @partsListXml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @partsListXml OUT

    EXEC sp_OAMethod @fac, 'FileExists', @iTmp0 OUT, @partsListFile
    IF @iTmp0 = 1
      BEGIN
        EXEC sp_OAMethod @partsListXml, 'LoadXmlFile', @success OUT, @partsListFile
      END
    -- Make sure the top-level tag is "CompleteMultipartUpload"
    EXEC sp_OASetProperty @partsListXml, 'Tag', 'CompleteMultipartUpload'

    -- --------------------------------------
    -- Before entering the loop to upload parts,
    -- setup the REST object with AWS authentication,
    -- and make the initial connection.
    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT

    -- 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
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, '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'
    EXEC sp_OAMethod @rest, 'SetAuthAws', @success OUT, @authAws

    -- Set the bucket name via the HOST header.
    -- In this case, the bucket name is "chilkat100".
    EXEC sp_OASetProperty @rest, 'Host', 'chilkat100.s3.amazonaws.com'
    -- --------------------------------------

    DECLARE @partNumber int
    SELECT @partNumber = 1
    DECLARE @sbPartNumber int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPartNumber OUT

    WHILE (@partNumber <= @numParts)
      BEGIN


        PRINT '---- ' + @partNumber + ' ----'

        -- This cumbersome way of converting an integer to a string is because
        -- Chilkat examples are written in a script that is converted to many programming languages.
        -- At this time, the translator does not have integer-to-string code generation capability..
        EXEC sp_OAMethod @sbPartNumber, 'Clear', NULL
        EXEC sp_OAMethod @sbPartNumber, 'AppendInt', @success OUT, @partNumber

        DECLARE @bPartAlreadyUploaded int
        SELECT @bPartAlreadyUploaded = 0

        -- If there are no children, then the XML is empty and no parts have yet been uploaded.
        DECLARE @numUploadedParts int
        EXEC sp_OAGetProperty @partsListXml, 'NumChildren', @numUploadedParts OUT
        IF @numUploadedParts > 0
          BEGIN
            -- If some parts have been uploaded, check to see if this particular part was already upload.
            -- If so, then it can be skipped.

            -- Position ourselves at the 1st record.
            DECLARE @xRec0 int
            EXEC sp_OAMethod @partsListXml, 'GetChild', @xRec0 OUT, 0
            DECLARE @foundRec int
            EXEC sp_OAMethod @sbPartNumber, 'GetAsString', @sTmp0 OUT
            EXEC sp_OAMethod @xRec0, 'FindNextRecord', @foundRec OUT, 'PartNumber', @sTmp0
            EXEC sp_OAGetProperty @xRec0, 'LastMethodSuccess', @iTmp0 OUT
            IF @iTmp0 = 1
              BEGIN
                SELECT @bPartAlreadyUploaded = 1


                PRINT 'Part ' + @partNumber + ' was previously uploaded.'
                EXEC sp_OAMethod @foundRec, 'GetXml', @sTmp0 OUT
                PRINT @sTmp0
                EXEC @hr = sp_OADestroy @foundRec

              END

            EXEC @hr = sp_OADestroy @xRec0

          END

        -- If this part was not already uploaded, we need to upload.
        -- Also update the partsListXml and save as each part is successfully uploaded.
        IF @bPartAlreadyUploaded = 0
          BEGIN


            PRINT 'Uploading part ' + @partNumber + ' ...'

            -- Setup the stream source for the large file to be uploaded..
            DECLARE @fileStream int
            -- Use "Chilkat_9_5_0.Stream" for versions of Chilkat < 10.0.0
            EXEC @hr = sp_OACreate 'Chilkat.Stream', @fileStream OUT

            EXEC sp_OASetProperty @fileStream, 'SourceFile', @fileToUploadPath
            -- The Chilkat Stream API has features to make uploading a parts
            -- of a file easy.  Indicate the part size by setting the SourceFilePartSize
            -- property.
            EXEC sp_OASetProperty @fileStream, 'SourceFilePartSize', @partSize

            -- Our HTTP start line to upload a part will look like this:
            -- PUT /ObjectName?partNumber=PartNumber&uploadId=UploadId HTTP/1.1

            -- Set the query params.  We'll need partNumber and uploadId.
            -- Make sure the query params from previous iterations are clear.
            EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
            EXEC sp_OAMethod @sbPartNumber, 'GetAsString', @sTmp0 OUT
            EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'partNumber', @sTmp0
            EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'uploadId', @uploadId

            -- Upload this particular file part.
            -- Tell the fileStream which part is being uploaded.
            -- Our partNumber is 1-based (the 1st part is at index 1), but the fileStream's SourceFilePart
            -- property is 0-based.  Therefore we use partNumber-1.
            EXEC sp_OASetProperty @fileStream, 'SourceFilePart', @partNumber - 1

            -- Because the SourceFilePart and SourceFilePartSize properties are set, the stream will 
            -- will provide just that part of the file.  
            DECLARE @responseStr nvarchar(4000)
            EXEC sp_OAMethod @rest, 'FullRequestStream', @responseStr OUT, 'PUT', '/somethingBig.zip', @fileStream
            EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
            IF @iTmp0 <> 1
              BEGIN
                EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                EXEC @hr = sp_OADestroy @xmlInit
                EXEC @hr = sp_OADestroy @fac
                EXEC @hr = sp_OADestroy @partsListXml
                EXEC @hr = sp_OADestroy @rest
                EXEC @hr = sp_OADestroy @authAws
                EXEC @hr = sp_OADestroy @sbPartNumber
                EXEC @hr = sp_OADestroy @fileStream
                RETURN
              END

            EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
            IF @iTmp0 <> 200
              BEGIN
                -- Examine the request/response to see what happened.

                EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
                PRINT 'response status code = ' + @iTmp0

                EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
                PRINT 'response status text = ' + @sTmp0

                EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
                PRINT 'response header: ' + @sTmp0

                PRINT 'response body: ' + @responseStr

                PRINT '---'

                EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
                PRINT 'LastRequestStartLine: ' + @sTmp0

                EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
                PRINT 'LastRequestHeader: ' + @sTmp0
                EXEC @hr = sp_OADestroy @xmlInit
                EXEC @hr = sp_OADestroy @fac
                EXEC @hr = sp_OADestroy @partsListXml
                EXEC @hr = sp_OADestroy @rest
                EXEC @hr = sp_OADestroy @authAws
                EXEC @hr = sp_OADestroy @sbPartNumber
                EXEC @hr = sp_OADestroy @fileStream
                RETURN
              END

            -- OK, this part was uploaded..
            -- The response will have a 0-length body.  The only information we need is the 
            -- ETag response header field.
            DECLARE @etag nvarchar(4000)
            EXEC sp_OAMethod @rest, 'ResponseHdrByName', @etag OUT, 'ETag'
            -- It should be present, but just in case there was no ETag header...
            EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
            IF @iTmp0 <> 1
              BEGIN

                PRINT 'No ETag response header found!'

                EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
                PRINT 'response header: ' + @sTmp0
                EXEC @hr = sp_OADestroy @xmlInit
                EXEC @hr = sp_OADestroy @fac
                EXEC @hr = sp_OADestroy @partsListXml
                EXEC @hr = sp_OADestroy @rest
                EXEC @hr = sp_OADestroy @authAws
                EXEC @hr = sp_OADestroy @sbPartNumber
                EXEC @hr = sp_OADestroy @fileStream
                RETURN
              END

            -- We need to add record to the partsListXml.
            -- The record will look like this:
            -- &lt;Part>
            --   &lt;PartNumber>PartNumber&lt;/PartNumber>
            --   &lt;ETag>ETag&lt;/ETag>
            -- &lt;/Part>
            DECLARE @xPart int
            EXEC sp_OAMethod @partsListXml, 'NewChild', @xPart OUT, 'Part', ''
            EXEC sp_OAMethod @xPart, 'NewChildInt2', NULL, 'PartNumber', @partNumber
            EXEC sp_OAMethod @xPart, 'NewChild2', NULL, 'ETag', @etag
            EXEC @hr = sp_OADestroy @xPart

            EXEC sp_OAMethod @partsListXml, 'SaveXml', @success OUT, @partsListFile
            IF @success <> 1
              BEGIN
                EXEC sp_OAGetProperty @partsListXml, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                EXEC @hr = sp_OADestroy @xmlInit
                EXEC @hr = sp_OADestroy @fac
                EXEC @hr = sp_OADestroy @partsListXml
                EXEC @hr = sp_OADestroy @rest
                EXEC @hr = sp_OADestroy @authAws
                EXEC @hr = sp_OADestroy @sbPartNumber
                EXEC @hr = sp_OADestroy @fileStream
                RETURN
              END



            PRINT '-- Part ' + @partNumber + ' uploaded. ---------------------'
          END

        SELECT @partNumber = @partNumber + 1
      END


    PRINT 'Finished.  All parts uploaded.'

    EXEC @hr = sp_OADestroy @xmlInit
    EXEC @hr = sp_OADestroy @fac
    EXEC @hr = sp_OADestroy @partsListXml
    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @authAws
    EXEC @hr = sp_OADestroy @sbPartNumber
    EXEC @hr = sp_OADestroy @fileStream


END
GO