SQL Server
SQL Server
S3 Upload the Parts for a Multipart Upload
See more Amazon S3 (new) Examples
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 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 @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlInit OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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
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
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
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
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
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
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:
-- <Part>
-- <PartNumber>PartNumber</PartNumber>
-- <ETag>ETag</ETag>
-- </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