(SQL Server) Retrieve the metadata for a DriveItem

Fetches the JSON metadata for a DriveItem.

For more information, see https://docs.microsoft.com/en-us/onedrive/developer/rest-api/api/driveitem_get?view=odsp-graph-online

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

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use your previously obtained access token here:
    -- See the following examples for getting an access token:
    --    Get Microsoft Graph OAuth2 Access Token (Azure AD v2.0 Endpoint).
    --    Get Microsoft Graph OAuth2 Access Token (Azure AD Endpoint).
    --    Refresh Access Token (Azure AD v2.0 Endpoint).
    --    Refresh Access Token (Azure AD Endpoint).

    -- (Make sure your token was obtained with the FilesRead or Files.ReadWrite scope.)
    EXEC sp_OASetProperty @http, 'AuthToken', 'MICROSOFT_GRAPH_ACCESS_TOKEN'

    -- Sends the following GET request:
    -- GET https://graph.microsoft.com/v1.0/me/drive/root:/{item-path}

    -- Make sure to automatically follow redirects
    EXEC sp_OASetProperty @http, 'FollowRedirects', 1

    -- This example will get the metadata for /Misc/wildlife/penguins.jpg
    DECLARE @success int
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'item_path', 'Misc/wildlife/penguins.jpg'

    DECLARE @metaData nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @metaData OUT, 'https://graph.microsoft.com/v1.0/me/drive/root:/{$item_path}'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    DECLARE @success int
    EXEC sp_OAMethod @json, 'Load', @success OUT, @metaData
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample JSON metadata result:

    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('admin%40chilkat.io')/drive/root/$entity",
    --   "@microsoft.graph.downloadUrl": "https://public.dm.files.1drv.com/y4mh0z_Og97O7Q...o2q1HhNBU",
    --   "createdDateTime": "2017-06-04T20:40:22.48Z",
    --   "cTag": "aYzozQTMzRkNFQjlCNzRDQzE1ITQ4NzIuMjU3",
    --   "eTag": "aM0EzM0ZDRUI5Qjc0Q0MxNSE0ODcyLjY",
    --   "id": "3A33FCEB9B74CC15!4872",
    --   "lastModifiedDateTime": "2018-10-20T18:22:29.977Z",
    --   "name": "penguins.jpg",
    --   "size": 777835,
    --   "webUrl": "https://1drv.ms/i/s!ABXMdJvr_DM6pgg",
    --   "rating": {
    --     "rating": 75,
    --     "simpleRating": 4
    --   },
    --   "createdBy": {
    --     "user": {
    --       "displayName": "Joe Programmer",
    --       "id": "3a33fceb9b74cc15"
    --     }
    --   },
    --   "lastModifiedBy": {
    --     "user": {
    --       "displayName": "Joe Programmer",
    --       "id": "3a33fceb9b74cc15"
    --     }
    --   },
    --   "parentReference": {
    --     "driveId": "3a33fceb9b74cc15",
    --     "driveType": "personal",
    --     "id": "3A33FCEB9B74CC15!4871",
    --     "name": "wildlife",
    --     "path": "/drive/root:/Misc/wildlife"
    --   },
    --   "file": {
    --     "mimeType": "image/jpeg",
    --     "hashes": {
    --       "sha1Hash": "DF7BE9DC4F467187783ACA68C7CE98E4DF2172D0"
    --     }
    --   },
    --   "fileSystemInfo": {
    --     "createdDateTime": "2017-06-04T20:40:22.48Z",
    --     "lastModifiedDateTime": "2009-07-14T05:32:31.674Z"
    --   },
    --   "image": {
    --     "height": 768,
    --     "width": 1024
    --   },
    --   "photo": {
    --     "takenDateTime": "2008-02-18T05:07:31Z"
    --   },
    --   "shared": {
    --     "scope": "users",
    --     "owner": {
    --       "user": {
    --         "displayName": "Joe Programmer",
    --         "id": "3a33fceb9b74cc15"
    --       }
    --     }
    --   }
    -- }
    -- 

    -- If the response status code was not 200, then it failed.
    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
        PRINT 'Response Status Code = ' + @iTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Demonstrate how to parse the JSON...

    DECLARE @odata_context nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @odata_context OUT, '"@odata.context"'
    DECLARE @microsoft_graph_downloadUrl nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @microsoft_graph_downloadUrl OUT, '"@microsoft.graph.downloadUrl"'
    DECLARE @createdDateTime nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @createdDateTime OUT, 'createdDateTime'
    DECLARE @cTag nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @cTag OUT, 'cTag'
    DECLARE @eTag nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @eTag OUT, 'eTag'
    DECLARE @id nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @id OUT, 'id'
    DECLARE @lastModifiedDateTime nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @lastModifiedDateTime OUT, 'lastModifiedDateTime'
    DECLARE @name nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @name OUT, 'name'
    DECLARE @size int
    EXEC sp_OAMethod @json, 'IntOf', @size OUT, 'size'
    DECLARE @webUrl nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @webUrl OUT, 'webUrl'
    DECLARE @ratingRating int
    EXEC sp_OAMethod @json, 'IntOf', @ratingRating OUT, 'rating.rating'
    DECLARE @ratingSimpleRating int
    EXEC sp_OAMethod @json, 'IntOf', @ratingSimpleRating OUT, 'rating.simpleRating'
    DECLARE @createdByUserDisplayName nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @createdByUserDisplayName OUT, 'createdBy.user.displayName'
    DECLARE @createdByUserId nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @createdByUserId OUT, 'createdBy.user.id'
    DECLARE @lastModifiedByUserDisplayName nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @lastModifiedByUserDisplayName OUT, 'lastModifiedBy.user.displayName'
    DECLARE @lastModifiedByUserId nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @lastModifiedByUserId OUT, 'lastModifiedBy.user.id'
    DECLARE @parentReferenceDriveId nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @parentReferenceDriveId OUT, 'parentReference.driveId'
    DECLARE @parentReferenceDriveType nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @parentReferenceDriveType OUT, 'parentReference.driveType'
    DECLARE @parentReferenceId nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @parentReferenceId OUT, 'parentReference.id'
    DECLARE @parentReferenceName nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @parentReferenceName OUT, 'parentReference.name'
    DECLARE @parentReferencePath nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @parentReferencePath OUT, 'parentReference.path'
    DECLARE @fileMimeType nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @fileMimeType OUT, 'file.mimeType'
    DECLARE @fileHashesSha1Hash nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @fileHashesSha1Hash OUT, 'file.hashes.sha1Hash'
    DECLARE @fileSystemInfoCreatedDateTime nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @fileSystemInfoCreatedDateTime OUT, 'fileSystemInfo.createdDateTime'
    DECLARE @fileSystemInfoLastModifiedDateTime nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @fileSystemInfoLastModifiedDateTime OUT, 'fileSystemInfo.lastModifiedDateTime'
    DECLARE @imageHeight int
    EXEC sp_OAMethod @json, 'IntOf', @imageHeight OUT, 'image.height'
    DECLARE @imageWidth int
    EXEC sp_OAMethod @json, 'IntOf', @imageWidth OUT, 'image.width'
    DECLARE @photoTakenDateTime nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @photoTakenDateTime OUT, 'photo.takenDateTime'
    DECLARE @sharedScope nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @sharedScope OUT, 'shared.scope'
    DECLARE @sharedOwnerUserDisplayName nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @sharedOwnerUserDisplayName OUT, 'shared.owner.user.displayName'
    DECLARE @sharedOwnerUserId nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @sharedOwnerUserId OUT, 'shared.owner.user.id'

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


END
GO