SQL Server
SQL Server
Upload File in Blocks and Commit the Block List
See more Azure Cloud Storage Examples
Demonstrates how to upload a file in blocks and then commit the block list. For files larger than 64MB, this is the way to upload to Azure Storage. Azure limits the size of each block to a maximum of 4MB.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 Blob Service Example: Upload a file in blocks, and then commit the block list.
-- See also: https://msdn.microsoft.com/en-us/library/azure/dd135726.aspx
-- 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.blob.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
-- Provide Azure Cloud credentials for the REST call.
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', 'Blob'
-- 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: Content-Length, x-ms-date, Authorization. These headers
-- are automatically set by Chilkat.
-- As the blocks are uploaded, we'll keep an XML block list for the subsequent commit..
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OASetProperty @xml, 'Tag', 'BlockList'
-- Any type of file can be uploaded in this way. It can a text file, binary file, anything...
-- This example will upload an XML file that is approximately 275K in size. It can be downloaded
-- at http://www.chilkatsoft.com/hamlet.xml
DECLARE @fac int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
EXEC sp_OAMethod @fac, 'OpenForRead', @success OUT, 'qa_data/xml/hamlet.xml'
-- Assuming success for the example..
-- We'll upload in 16K blocks (normally a program would upload in larger block sizes than this,
-- but this is just an example...)
DECLARE @blockSize int
SELECT @blockSize = 16384
-- How many 16K blocks? (Including 1 for the last partial block)
DECLARE @numBlocks int
EXEC sp_OAMethod @fac, 'GetNumBlocks', @numBlocks OUT, @blockSize
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
DECLARE @uriPath int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @uriPath OUT
DECLARE @blockId nvarchar(4000)
DECLARE @dataBlock int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @dataBlock OUT
DECLARE @i int
SELECT @i = 0
WHILE @i < @numBlocks
BEGIN
EXEC sp_OAMethod @dataBlock, 'Clear', @success OUT
EXEC sp_OAMethod @fac, 'ReadBlockBd', @success OUT, @i, @blockSize, @dataBlock
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @fac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @azAuth
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @uriPath
EXEC @hr = sp_OADestroy @dataBlock
RETURN
END
-- Generate a base64 block ID.
-- (Chilkat provides a helper method named GenBlockId to make this easy)
-- A pre-base64 encoded block ID length of 4 is sufficient in this case because
-- this file certainly won't have more than 99,999 blocks..
EXEC sp_OAMethod @fac, 'GenBlockId', @blockId OUT, @i, 4, 'base64'
-- Add this blockId to the list of blocks to be committed.
EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'Latest', @blockId
-- Build the URI path
EXEC sp_OAMethod @uriPath, 'Clear', NULL
EXEC sp_OAMethod @uriPath, 'Append', @success OUT, '/mycontainer/hamlet.xml?comp=block&blockId='
EXEC sp_OAMethod @uriPath, 'Append', @success OUT, @blockId
-- Upload this block..
EXEC sp_OAMethod @sbResponseBody, 'Clear', NULL
EXEC sp_OAMethod @uriPath, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'FullRequestBd', @success OUT, 'PUT', @sTmp0, @dataBlock, @sbResponseBody
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @azAuth
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @uriPath
EXEC @hr = sp_OADestroy @dataBlock
RETURN
END
-- Verify that we received a 201 status code.
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
EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
PRINT 'response body (if any): ' + @sTmp0
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 @azAuth
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @uriPath
EXEC @hr = sp_OADestroy @dataBlock
RETURN
END
SELECT @i = @i + 1
END
EXEC sp_OAMethod @fac, 'FileClose', NULL
-- Now commit the blocks.
-- Let's have a look at the XML that will commit the blocks:
DECLARE @xmlStr nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetXml', @xmlStr OUT
PRINT @xmlStr
-- The XML will look like this:
-- <?xml version="1.0" encoding="utf-8" ?>
-- <BlockList>
-- <Latest>MDAwMA==</Latest>
-- <Latest>MDAwMQ==</Latest>
-- <Latest>MDAwMg==</Latest>
-- <Latest>MDAwMw==</Latest>
-- <Latest>MDAwNA==</Latest>
-- <Latest>MDAwNQ==</Latest>
-- <Latest>MDAwNg==</Latest>
-- <Latest>MDAwNw==</Latest>
-- <Latest>MDAwOA==</Latest>
-- <Latest>MDAwOQ==</Latest>
-- <Latest>MDAxMA==</Latest>
-- <Latest>MDAxMQ==</Latest>
-- <Latest>MDAxMg==</Latest>
-- <Latest>MDAxMw==</Latest>
-- <Latest>MDAxNA==</Latest>
-- <Latest>MDAxNQ==</Latest>
-- <Latest>MDAxNg==</Latest>
-- <Latest>MDAxNw==</Latest>
-- </BlockList>
-- Send the PUT Block List...
DECLARE @responseStr nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestString', @responseStr OUT, 'PUT', '/mycontainer/hamlet.xml?comp=blocklist', @xmlStr
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 @azAuth
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @uriPath
EXEC @hr = sp_OADestroy @dataBlock
RETURN
END
-- When successful, the Azure Storage service will respond with a 201 response status code,
-- with no 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 @azAuth
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @uriPath
EXEC @hr = sp_OADestroy @dataBlock
RETURN
END
PRINT 'Success.'
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @azAuth
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @uriPath
EXEC @hr = sp_OADestroy @dataBlock
END
GO