SQL Server
SQL Server
OneDrive -- Upload Large Files with an Upload Session
See more OneDrive Examples
Demonstrates how to upload large files with an upload session. See OneDrive Upload Session for more general information.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 @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- This example uses the OAuth client credentials flow.
-- See How to Create an Azure App Registration for OAuth 2.0 Client Credentials
-- Use your client ID, client secret, and tenant ID in the following lines
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_id', '2871da2c-8176-4b7f-869b-2311aa82e743'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_secret', '2hu9Q~r5QuryUcEkNbg1btLtnfU1VUXzhSCG6brH'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'scope', 'https://graph.microsoft.com/.default'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'token_endpoint', 'https://login.microsoftonline.com/114d7ed6-71bf-4dbe-a866-748364121bf2/oauth2/v2.0/token'
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
-- ----------------------------------------------------------------------------
-- Step 1: Create an upload session
-- To begin a large file upload, your app must first request a new upload session. This creates a
-- temporary storage location where the bytes of the file will be saved until the complete file is uploaded.
-- Once the last byte of the file has been uploaded the upload session is completed and the final file is shown
-- in the destination folder.
-- Send the following POST to create an upload session:
-- If not using "me", then the path should be /v1.0/users/{id | userPrincipalName}/...
-- POST /v1.0/users/{user_id}/drive/root:/{path_to_item}:/createUploadSession
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'path_to_item', '/somefolder/big.zip'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'user_id', '4fe732c3-322e-4a6b-b729-2fd1eb5c6104'
DECLARE @url nvarchar(4000)
SELECT @url = 'https://graph.microsoft.com/v1.0/users/{$user_id}/drive/root:/{$path_to_item}:/createUploadSession'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', @url, '{}', 'utf-8', 'application/json', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- If successful, a 200 status code is returned, with the session details (in JSON format).
DECLARE @jsonSession int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonSession OUT
EXEC sp_OASetProperty @jsonSession, 'EmitCompact', 0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @jsonSession, 'Load', @success OUT, @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAMethod @jsonSession, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response status = ' + @iTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonSession
RETURN
END
EXEC sp_OAMethod @jsonSession, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- A sample response:
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#microsoft.graph.uploadSession",
-- "uploadUrl": "https://api.onedrive.com/rup/3a33fceb9b74cc15/eyJSZXNvdXJjZUlEI ... 65yDYUiS3JTDnnhqCHxw",
-- "expirationDateTime": "2017-06-11T12:40:23.239Z",
-- "nextExpectedRanges": [
-- "0-"
-- ]
-- }
--
-- ----------------------------------------------------------------------------
-- Step 2: Upload Data in Segments (a.k.a. Fragments or Chunks)
-- Microsoft states this requirement: Use a fragment size that is a multiple of 320 KiB (320 * 1024 bytes).
-- Failing to use a fragment size that is a multiple of 320 KiB can result in large file transfers failing after the
-- last fragment is uploaded. (Note: This is a detail imposed by Microsoft's OneDrive server-side implementation.)
DECLARE @fragSize int
SELECT @fragSize = 320 * 1024
DECLARE @localFilePath nvarchar(4000)
SELECT @localFilePath = 'qa_data/zips/big.zip'
-- Upload the file big.zip in 320KiB segments.
-- 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 @fac int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
DECLARE @fileSize int
EXEC sp_OAMethod @fac, 'FileSize', @fileSize OUT, @localFilePath
-- Open the file to get the number of fragments.
EXEC sp_OAMethod @fac, 'OpenForRead', @success OUT, @localFilePath
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @fac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonSession
EXEC @hr = sp_OADestroy @fac
RETURN
END
DECLARE @numFragments int
EXEC sp_OAMethod @fac, 'GetNumBlocks', @numFragments OUT, @fragSize
EXEC sp_OAMethod @fac, 'FileClose', NULL
DECLARE @i int
SELECT @i = 0
PRINT 'fileSize = ' + @fileSize
PRINT 'numFragments = ' + @numFragments
DECLARE @uploadUrl int
EXEC @hr = sp_OACreate 'Chilkat.Url', @uploadUrl OUT
EXEC sp_OAMethod @jsonSession, 'StringOf', @sTmp0 OUT, 'uploadUrl'
EXEC sp_OAMethod @uploadUrl, 'ParseUrl', @success OUT, @sTmp0
EXEC sp_OASetProperty @json, 'EmitCompact', 0
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
EXEC sp_OASetProperty @req, 'HttpVerb', 'PUT'
EXEC sp_OAGetProperty @uploadUrl, 'PathWithQueryParams', @sTmp0 OUT
EXEC sp_OASetProperty @req, 'Path', @sTmp0
EXEC sp_OASetProperty @req, 'ContentType', 'application/octet-stream'
DECLARE @sbOffset int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbOffset OUT
DECLARE @sbNumBytes int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbNumBytes OUT
DECLARE @sbRange int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRange OUT
-- IMPORTANT: The uploadUrl is a temporary URL to be used to upload the fragment.
-- It requires no authentication (because the URL itself could only have been obtained from an authenticated
-- request to start the upload session). Therefore, do not allow the upload URL to be publicly seen,
-- otherwise anybody could upload to your OneDrive.
EXEC sp_OASetProperty @http, 'AuthToken', ''
DECLARE @bytesRemaining int
SELECT @bytesRemaining = @fileSize
WHILE @i < @numFragments
BEGIN
-- The success response code for intermediate chunks is 202,
-- whereas the final chunk will have a 201 success response where
-- the response body is the JSON DriveItem.
DECLARE @chunkSize int
SELECT @chunkSize = @fragSize
DECLARE @expectedStatusCode int
SELECT @expectedStatusCode = 202
IF @bytesRemaining < @chunkSize
BEGIN
SELECT @chunkSize = @bytesRemaining
SELECT @expectedStatusCode = 201
END
PRINT ' this chunkSize = ' + @chunkSize
-- Indicate the fragment in the local file to be streamed in the upload.
EXEC sp_OAMethod @sbOffset, 'Clear', NULL
EXEC sp_OAMethod @sbOffset, 'AppendInt', @success OUT, @i * @fragSize
EXEC sp_OAMethod @sbNumBytes, 'Clear', NULL
EXEC sp_OAMethod @sbNumBytes, 'AppendInt', @success OUT, @chunkSize
EXEC sp_OAMethod @sbOffset, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @sbNumBytes, 'GetAsString', @sTmp1 OUT
EXEC sp_OAMethod @req, 'StreamChunkFromFile', @success OUT, @localFilePath, @sTmp0, @sTmp1
-- The Content-Range header field must be set for this fragment. For example:
-- Content-Range: bytes 0-25/128
EXEC sp_OAMethod @sbRange, 'SetString', @success OUT, 'bytes start-end/fileSize'
DECLARE @numReplaced int
EXEC sp_OAMethod @sbRange, 'ReplaceI', @numReplaced OUT, 'start', @i * @fragSize
EXEC sp_OAMethod @sbRange, 'ReplaceI', @numReplaced OUT, 'end', @i * @fragSize + @chunkSize - 1
EXEC sp_OAMethod @sbRange, 'ReplaceI', @numReplaced OUT, 'fileSize', @fileSize
EXEC sp_OAMethod @sbRange, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Content-Range', @sTmp0
EXEC sp_OAMethod @sbRange, 'GetAsString', @sTmp0 OUT
PRINT ' this content-range: ' + @sTmp0
DECLARE @domain nvarchar(4000)
EXEC sp_OAGetProperty @uploadUrl, 'Host', @domain OUT
EXEC sp_OAMethod @http, 'HttpSReq', @success OUT, @domain, 443, 1, @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonSession
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @uploadUrl
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbOffset
EXEC @hr = sp_OADestroy @sbNumBytes
EXEC @hr = sp_OADestroy @sbRange
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
-- A 202 response status code indicates success.
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> @expectedStatusCode
BEGIN
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response status = ' + @iTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonSession
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @uploadUrl
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbOffset
EXEC @hr = sp_OADestroy @sbNumBytes
EXEC @hr = sp_OADestroy @sbRange
RETURN
END
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT '---- Chunk ' + @i + ' uploaded ----'
SELECT @bytesRemaining = @bytesRemaining - @chunkSize
SELECT @i = @i + 1
END
PRINT 'data uploaded.'
-- ----------------------------------------------------------------------------
-- Sample output for the above session:
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#microsoft.graph.uploadSession",
-- "uploadUrl": "https://api.onedrive.com/rup/3a33fceb9b74cc15/eyJSZXNvd ... QoKK2iuh1A",
-- "expirationDateTime": "2017-06-11T14:04:45.438Z",
-- "nextExpectedRanges": [
-- "0-"
-- ]
-- }
--
-- fileSize = 1366807
-- numFragments = 5
-- this chunkSize = 327680
-- this content-range: bytes 0-327679/1366807
-- {
-- "expirationDateTime": "2017-06-11T14:04:45.438Z",
-- "nextExpectedRanges": [
-- "327680-1366806"
-- ]
-- }
--
-- ---- Chunk 0 uploaded ----
-- this chunkSize = 327680
-- this content-range: bytes 327680-655359/1366807
-- {
-- "expirationDateTime": "2017-06-11T14:04:45.438Z",
-- "nextExpectedRanges": [
-- "655360-1366806"
-- ]
-- }
--
-- ---- Chunk 1 uploaded ----
-- this chunkSize = 327680
-- this content-range: bytes 655360-983039/1366807
-- {
-- "expirationDateTime": "2017-06-11T14:04:45.438Z",
-- "nextExpectedRanges": [
-- "983040-1366806"
-- ]
-- }
--
-- ---- Chunk 2 uploaded ----
-- this chunkSize = 327680
-- this content-range: bytes 983040-1310719/1366807
-- {
-- "expirationDateTime": "2017-06-11T14:04:45.438Z",
-- "nextExpectedRanges": [
-- "1310720-1366806"
-- ]
-- }
--
-- ---- Chunk 3 uploaded ----
-- this chunkSize = 56087
-- this content-range: bytes 1310720-1366806/1366807
-- {
-- "createdBy": {
-- "application": {
-- "displayName": "Chilkat",
-- "id": "441c9990"
-- },
-- "user": {
-- "id": "3a33fceb9b74cc15"
-- }
-- },
-- "createdDateTime": "2017-06-04T14:04:47.247Z",
-- "cTag": "aYzozQTMzRkNFQjlCNzRDQzE1ITQ4NjguMjU3",
-- "eTag": "aM0EzM0ZDRUI5Qjc0Q0MxNSE0ODY4LjA",
-- "id": "3A33FCEB9B74CC15!4868",
-- "lastModifiedBy": {
-- "application": {
-- "displayName": "Chilkat",
-- "id": "441c9990"
-- },
-- "user": {
-- "id": "3a33fceb9b74cc15"
-- }
-- },
-- "lastModifiedDateTime": "2017-06-04T14:04:47.247Z",
-- "name": "big.zip",
-- "parentReference": {
-- "driveId": "3a33fceb9b74cc15",
-- "id": "3A33FCEB9B74CC15!4862",
-- "name": "someFolder",
-- "path": "/drive/root:/someFolder"
-- },
-- "size": 1366807,
-- "webUrl": "https://1drv.ms/u/s!ABXMdJvr_DM6pgQ",
-- "file": {
-- "hashes": {
-- "sha1Hash": "252059AA13004220DB912B97D4D3FF9599CCD8D9"
-- },
-- "mimeType": "application/zip"
-- },
-- "fileSystemInfo": {
-- "createdDateTime": "2017-06-04T14:04:47.246Z",
-- "lastModifiedDateTime": "2017-06-04T14:04:47.246Z"
-- },
-- "tags": [
-- ],
-- "lenses": [
-- ]
-- }
--
-- Response status = 201
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonSession
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @uploadUrl
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbOffset
EXEC @hr = sp_OADestroy @sbNumBytes
EXEC @hr = sp_OADestroy @sbRange
END
GO