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

Note: This example requires Chilkat v9.5.0.97 or greater.

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)
    DECLARE @sTmp1 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    -- 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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    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 sp_OAMethod @http, 'PostJson2', @resp OUT, @url, 'application/json', '{}'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- If successful, a 200 status code is returned, with the session details (in JSON format).
    DECLARE @jsonSession int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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 @resp

        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonSession
        RETURN
      END

    EXEC @hr = sp_OADestroy @resp

    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
    -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
    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 @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
    -- Use "Chilkat_9_5_0.Url" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.HttpRequest" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbOffset OUT

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

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

        EXEC sp_OAGetProperty @uploadUrl, 'Host', @sTmp0 OUT
        EXEC sp_OAMethod @http, 'SynchronousRequest', @resp OUT, @sTmp0, 443, 1, @req
        EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 <> 1
          BEGIN
            EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @json
            EXEC @hr = sp_OADestroy @http
            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 @resp

            EXEC @hr = sp_OADestroy @json
            EXEC @hr = sp_OADestroy @http
            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 @hr = sp_OADestroy @resp

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