SQL Server
SQL Server
Azure File Service: Upload Large File
See more Azure Cloud Storage Examples
Sample code to upload a large file to a directory in a share in the Azure File Service. A file is uploaded by first creating the file in the Azure share and then writing ranges of bytes to the file. Azure imposes a 4MB limit for each PUT to write a range. Files larger than 4MB need to be uploaded by making multiple "Put Range" calls. This example uploads a large file requiring multiple "Put Range" calls.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
-- Azure File Service Example: Upload a large file.
-- See: https://docs.microsoft.com/en-us/rest/api/storageservices/create-share
-- also see: https://docs.microsoft.com/en-us/rest/api/storageservices/put-range
-- 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 Azure Storage Blob Service
DECLARE @bTls int
SELECT @bTls = 1
DECLARE @port int
SELECT @port = 443
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
-- In this example, the storage account name is "chilkat".
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'chilkat.file.core.windows.net', @port, @bTls, @bAutoReconnect
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
RETURN
END
-- This example will upload a larger file.
DECLARE @localFilePath nvarchar(4000)
SELECT @localFilePath = 'qa_data/zips/somethingBig.zip'
DECLARE @fac int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
-- Note: The FileSize method returns a signed 32-bit integer. If the file is potentially larger than 2GB, call FileSizeStr instead to return
-- the size of the file as a string, then convert to an integer value.
DECLARE @szLocalFile int
EXEC sp_OAMethod @fac, 'FileSize', @szLocalFile OUT, @localFilePath
IF @szLocalFile < 0
BEGIN
EXEC sp_OAGetProperty @fac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @fac
RETURN
END
-- Provide Azure Cloud credentials for the REST calls.
DECLARE @azAuth int
EXEC @hr = sp_OACreate 'Chilkat.AuthAzureStorage', @azAuth OUT
EXEC sp_OASetProperty @azAuth, 'AccessKey', 'AZURE_ACCESS_KEY'
-- The account name used here should match the 1st part of the domain passed in the call to Connect (above).
EXEC sp_OASetProperty @azAuth, 'Account', 'chilkat'
EXEC sp_OASetProperty @azAuth, 'Scheme', 'SharedKey'
EXEC sp_OASetProperty @azAuth, 'Service', 'File'
-- This causes the "x-ms-version: 2021-08-06" header to be automatically added.
EXEC sp_OASetProperty @azAuth, 'XMsVersion', '2021-08-06'
EXEC sp_OAMethod @rest, 'SetAuthAzureStorage', @success OUT, @azAuth
-- Note: The application does not need to explicitly set the following
-- headers: x-ms-date, Authorization. These headers
-- are automatically set by Chilkat.
-- However, a few additional headers are required for the "Create File" operation:
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'x-ms-type', 'file'
-- This required header specifies the final size of the file (or the maximum size it can be).
DECLARE @sbFileSize int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbFileSize OUT
EXEC sp_OAMethod @sbFileSize, 'AppendInt', @success OUT, @szLocalFile
EXEC sp_OAMethod @sbFileSize, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'x-ms-content-length', @sTmp0
-- Send a PUT request to create the file (or replace the file if it already exists).
-- This will initialize the file in the Azure file storage. To upload content, we'll need
-- to do the "Put Range" operation one or more times.
-- The following will create the file "somethingBig.zip" in the share "pip"
DECLARE @responseStr nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseStr OUT, 'PUT', '/pip/somethingBig.zip'
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 @fac
EXEC @hr = sp_OADestroy @azAuth
EXEC @hr = sp_OADestroy @sbFileSize
RETURN
END
-- When successful, the Azure File Service will respond with a 201 (Created) response status code,
-- with no XML response body. If an error response is returned, there will be an XML response body.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 201
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 (if any): ' + @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 @rest
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @azAuth
EXEC @hr = sp_OADestroy @sbFileSize
RETURN
END
PRINT 'Successfully created somethingBig.zip'
-- --------------------------------------------------------------------------------
-- Upload the file data...
-- Make sure the headers from the "Create File" operation are removed.
EXEC sp_OAMethod @rest, 'ClearAllHeaders', @success OUT
-- The x-ms-write header is required for each "Put Range" request.
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'x-ms-write', 'update'
DECLARE @sbRange int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRange OUT
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
DECLARE @bdFileData int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdFileData OUT
-- Open the file. We'll be reading and uploading in chunks..
EXEC sp_OAMethod @fac, 'OpenForRead', @success OUT, @localFilePath
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @fac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @azAuth
EXEC @hr = sp_OADestroy @sbFileSize
EXEC @hr = sp_OADestroy @sbRange
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @bdFileData
RETURN
END
DECLARE @numBytesLeft int
SELECT @numBytesLeft = @szLocalFile
DECLARE @curIndex int
SELECT @curIndex = 0
WHILE @numBytesLeft > 0
BEGIN
DECLARE @chunkSize int
SELECT @chunkSize = @numBytesLeft
-- Azure allows for 4MB max chunks (4 x 1024 x 1024 = 4194304)
IF @chunkSize > 4194304
BEGIN
SELECT @chunkSize = 4194304
END
-- The only tricky part here is to correctly add the x-ms-range header.
-- It will be formatted like this:
-- x-ms-range: bytes=0-759623
EXEC sp_OAMethod @sbRange, 'Clear', NULL
EXEC sp_OAMethod @sbRange, 'Append', @success OUT, 'bytes='
EXEC sp_OAMethod @sbRange, 'AppendInt', @success OUT, @curIndex
EXEC sp_OAMethod @sbRange, 'Append', @success OUT, '-'
EXEC sp_OAMethod @sbRange, 'AppendInt', @success OUT, @curIndex + @chunkSize - 1
-- This replaces the header if it already exists..
EXEC sp_OAMethod @sbRange, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'x-ms-range', @sTmp0
-- Read the next chunk from the local file.
EXEC sp_OAMethod @bdFileData, 'Clear', @success OUT
EXEC sp_OAMethod @fac, 'FileReadBd', @success OUT, @chunkSize, @bdFileData
EXEC sp_OAMethod @rest, 'FullRequestBd', @success OUT, 'PUT', '/pip/somethingBig.zip?comp=range', @bdFileData, @sbResponseBody
IF @success <> 1
BEGIN
-- This would indicate a failure where no response was received.
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @azAuth
EXEC @hr = sp_OADestroy @sbFileSize
EXEC @hr = sp_OADestroy @sbRange
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @bdFileData
RETURN
END
-- A 201 response indicates the chunk was uploaded.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 201
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 (if any): ' + @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 @rest
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @azAuth
EXEC @hr = sp_OADestroy @sbFileSize
EXEC @hr = sp_OADestroy @sbRange
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @bdFileData
RETURN
END
PRINT 'Uploaded chunk from ' + @curIndex + ' to ' + @curIndex + @chunkSize - 1
SELECT @curIndex = @curIndex + @chunkSize
SELECT @numBytesLeft = @numBytesLeft - @chunkSize
END
EXEC sp_OAMethod @fac, 'FileClose', NULL
PRINT 'success.'
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @azAuth
EXEC @hr = sp_OADestroy @sbFileSize
EXEC @hr = sp_OADestroy @sbRange
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @bdFileData
END
GO