SQL Server
SQL Server
Retrieve the metadata for a DriveItem
See more OneDrive Examples
Fetches the JSON metadata for a DriveItem.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
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- 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
-- Sends the following GET request:
-- GET https://graph.microsoft.com/v1.0/users/{user-id}/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
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'item_path', 'Misc//penguins.jpg'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'user_id', '4fe732c3-322e-4a6b-b729-2fd1eb5c6104'
DECLARE @metaData nvarchar(4000)
EXEC sp_OAMethod @http, 'QuickGetStr', @metaData OUT, 'https://graph.microsoft.com/v1.0/users/{$user_id}/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 @json
EXEC @hr = sp_OADestroy @http
RETURN
END
EXEC sp_OASetProperty @json, 'EmitCompact', 0
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 @json
EXEC @hr = sp_OADestroy @http
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 @json
EXEC @hr = sp_OADestroy @http
END
GO