(SQL Server) Azure File Service: Upload Large File

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 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)
    -- 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
    -- 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 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".
    DECLARE @success int
    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
    -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.AuthAzureStorage" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRange OUT

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

    DECLARE @bdFileData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    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