(SQL Server) Azure File Service: Upload File

Sample code to upload a 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 writes a range 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 file small enough for a single "Put Range" call.

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 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 relatively small file (760K), so it may as well 
    -- just be load it into memory..
    DECLARE @jpgData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @jpgData OUT

    EXEC sp_OAMethod @jpgData, 'LoadFile', @success OUT, 'qa_data/jpg/penguins.jpg'

    -- 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_OAGetProperty @jpgData, 'NumBytes', @iTmp0 OUT
    EXEC sp_OAMethod @sbFileSize, 'AppendInt', @success OUT, @iTmp0
    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 "penguins.jpg" in the share "pip", in the directory "/wildlife/antarctic"
    DECLARE @responseStr nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseStr OUT, 'PUT', '/pip/wildlife/antarctic/penguins.jpg'
    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 @jpgData
        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 @jpgData
        EXEC @hr = sp_OADestroy @azAuth
        EXEC @hr = sp_OADestroy @sbFileSize
        RETURN
      END


    PRINT 'Successfully created penguins.jpg'

    -- --------------------------------------------------------------------------------
    -- Upload the file data...

    -- Make sure the headers from the "Create File" operation are removed.
    EXEC sp_OAMethod @rest, 'ClearAllHeaders', @success OUT

    -- 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

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

    EXEC sp_OAMethod @sbRange, 'Append', @success OUT, 'bytes=0-'
    EXEC sp_OAGetProperty @jpgData, 'NumBytes', @iTmp0 OUT
    EXEC sp_OAMethod @sbRange, 'AppendInt', @success OUT, @iTmp0 - 1

    EXEC sp_OAMethod @sbRange, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'x-ms-range', @sTmp0

    -- The x-ms-write header is also required:
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'x-ms-write', 'update'

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

    -- IMPORTANT:  Don't forget the "?comp=range" at the end of the path passed in the 2nd argument.
    EXEC sp_OAMethod @rest, 'FullRequestBd', @success OUT, 'PUT', '/pip/wildlife/antarctic/penguins.jpg?comp=range', @jpgData, @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 @jpgData
        EXEC @hr = sp_OADestroy @azAuth
        EXEC @hr = sp_OADestroy @sbFileSize
        EXEC @hr = sp_OADestroy @sbRange
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    -- A 201 response indicates the data 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 @jpgData
        EXEC @hr = sp_OADestroy @azAuth
        EXEC @hr = sp_OADestroy @sbFileSize
        EXEC @hr = sp_OADestroy @sbRange
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END


    PRINT 'Successfully uploaded data into penguins.jpg'

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @jpgData
    EXEC @hr = sp_OADestroy @azAuth
    EXEC @hr = sp_OADestroy @sbFileSize
    EXEC @hr = sp_OADestroy @sbRange
    EXEC @hr = sp_OADestroy @sbResponseBody


END
GO