(SQL Server) OneDrive -- List Large Directory

See more OneDrive Examples

Demonstrates how to get the entire collection of DriveItem children when the collection is large and multiple pages need to be requested. To test this example, I created a DriveItem with 450 children.

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

    DECLARE @success int
    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

    -- This example will get the DriveItems in /lots-of-files, which has 450 children..
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'item_path', '/lots-of-files'
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'user_id', '4fe732c3-322e-4a6b-b729-2fd1eb5c6104'

    DECLARE @resp nvarchar(4000)

    DECLARE @i int

    DECLARE @numItems int

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    -- This loop will download DriveItems in sets of 25 until no more exist.
    DECLARE @bGetMore int
    SELECT @bGetMore = 1
    -- (Add the $top query parameter to define the page size, i.e. the max number of results to return).
    DECLARE @nextLink nvarchar(4000)
    SELECT @nextLink = 'https://graph.microsoft.com/v1.0/users/{$user_id}/drive/root:{$item_path}:/children?$top=25'

    WHILE (@bGetMore = 1)
      BEGIN

        -- Get the next chunk of DriveItems
        EXEC sp_OAMethod @http, 'QuickGetStr', @resp OUT, @nextLink
        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

        -- The response should be JSON.
        EXEC sp_OAMethod @json, 'Load', @success OUT, @resp

        -- A successful response should return a status code of 200.
        EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
        IF @iTmp0 <> 200
          BEGIN
            EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
            PRINT @sTmp0

            EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
            PRINT 'Response status = ' + @iTmp0
            EXEC @hr = sp_OADestroy @json
            EXEC @hr = sp_OADestroy @http
            RETURN
          END

        -- Iterate over the DriveItems in the JSON response:
        SELECT @i = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @numItems OUT, 'value'
        WHILE @i < @numItems
          BEGIN
            EXEC sp_OASetProperty @json, 'I', @i

            EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value[i].name'
            PRINT 'name: ' + @sTmp0
            SELECT @i = @i + 1
          END

        -- If a nextLink was included in the JSON, use it to get the next set of 50 DriveItems.
        -- Otherwise, exit the loop.
        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, '"@odata.nextLink"'
        IF @iTmp0 = 1
          BEGIN
            EXEC sp_OAMethod @json, 'StringOf', @nextLink OUT, '"@odata.nextLink"'

            PRINT 'nextLink: ' + @nextLink
          END
        ELSE
          BEGIN
            SELECT @bGetMore = 0
          END
      END

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @http


END
GO