(SQL Server) MS Graph Calendar List Events

Retrieve a list of events in a calendar. The list contains single instance meetings and series masters.

For more details, see https://docs.microsoft.com/en-us/graph/api/calendar-list-events?view=graph-rest-1.0

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.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use your previously obtained access token as shown here:
    --    Get Microsoft Graph OAuth2 Access Token with Calendars.ReadWrite scope.

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

    DECLARE @success int
    EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/msGraphCalendar.json'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @jsonToken, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END
    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0

    -- Specify the calendar id
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'id', 'AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAgEGAAAA5_vF7TKKdE6bGCRqXyl2PQAClEpRTgAAAA=='
    -- Send a GET request to https://graph.microsoft.com/v1.0/me/calendars/{$id}/events
    DECLARE @strResponse nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @strResponse OUT, 'https://graph.microsoft.com/v1.0/me/calendars/{$id}/events'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END

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

    EXEC sp_OAMethod @json, 'Load', @success OUT, @strResponse
    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    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 'Failed, response status code = ' + @iTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample output:
    -- (See parsing code below..)

    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('admin%40chilkat.io')/calendars('AQMkADAwATM0MDAAMS1i ... AClEpRTgAAAA%3D%3D')/events",
    --   "value": [
    --     {
    --       "@odata.etag": "W/\"5+vF7TKKdE6bGCRqXyl2PQAClIgmmw==\"",
    --       "id": "AQMkADAwATM0MDAAM ... AApRZ7JkAAAA=",
    --       "createdDateTime": "2019-04-26T14:31:39.8791929Z",
    --       "lastModifiedDateTime": "2019-04-26T14:31:41.2753537Z",
    --       "changeKey": "5+vF7TKKdE6bGCRqXyl2PQAClIgmmw==",
    --       "categories": [
    --       ],
    --       "originalStartTimeZone": "Pacific Standard Time",
    --       "originalEndTimeZone": "Pacific Standard Time",
    --       "iCalUId": "040000008200E ... A230FEBFE5F7486A",
    --       "reminderMinutesBeforeStart": 15,
    --       "isReminderOn": true,
    --       "hasAttachments": false,
    --       "subject": "Let's go for lunch",
    --       "bodyPreview": "Does mid month work for you?",
    --       "importance": "normal",
    --       "sensitivity": "normal",
    --       "isAllDay": false,
    --       "isCancelled": false,
    --       "isOrganizer": true,
    --       "responseRequested": true,
    --       "seriesMasterId": null,
    --       "showAs": "busy",
    --       "type": "singleInstance",
    --       "webLink": "https://outlook.live.com/owa/?itemid=AQMkADAwATM0MDAAMS1iNTcwLWI2NT ... gkal8pdj0AApRZ7JkAAAA%3D&exvsurl=1&path=/calendar/item",
    --       "onlineMeetingUrl": null,
    --       "recurrence": null,
    --       "responseStatus": {
    --         "response": "organizer",
    --         "time": "0001-01-01T00:00:00Z"
    --       },
    --       "body": {
    --         "contentType": "html",
    --         "content": "<html>\r\n<head>\r\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">\r\n<meta content=\"text/html; charset=us-ascii\">\r\n</head>\r\n<body>\r\nDoes mid month work for you?\r\n</body>\r\n</html>\r\n"
    --       },
    --       "start": {
    --         "dateTime": "2019-11-15T20:00:00.0000000",
    --         "timeZone": "UTC"
    --       },
    --       "end": {
    --         "dateTime": "2019-11-15T22:00:00.0000000",
    --         "timeZone": "UTC"
    --       },
    --       "location": {
    --         "displayName": "Harry's Bar",
    --         "locationType": "default",
    --         "uniqueId": "Harry's Bar",
    --         "uniqueIdType": "private"
    --       },
    --       "locations": [
    --         {
    --           "displayName": "Harry's Bar",
    --           "locationType": "default",
    --           "uniqueId": "Harry's Bar",
    --           "uniqueIdType": "private"
    --         }
    --       ],
    --       "attendees": [
    --         {
    --           "type": "required",
    --           "status": {
    --             "response": "none",
    --             "time": "0001-01-01T00:00:00Z"
    --           },
    --           "emailAddress": {
    --             "name": "Adele Vance",
    --             "address": "adelev@contoso.onmicrosoft.com"
    --           }
    --         }
    --       ],
    --       "organizer": {
    --         "emailAddress": {
    --           "name": "Matt",
    --           "address": "outlook_3A33FCEB9B74CC15@outlook.com"
    --         }
    --       }
    --     }
    --   ]
    -- }

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    DECLARE @odataContext nvarchar(4000)

    DECLARE @i int

    DECLARE @count_i int

    DECLARE @odataEtag nvarchar(4000)

    DECLARE @id nvarchar(4000)

    DECLARE @createdDateTime nvarchar(4000)

    DECLARE @lastModifiedDateTime nvarchar(4000)

    DECLARE @changeKey nvarchar(4000)

    DECLARE @originalStartTimeZone nvarchar(4000)

    DECLARE @originalEndTimeZone nvarchar(4000)

    DECLARE @iCalUId nvarchar(4000)

    DECLARE @reminderMinutesBeforeStart int

    DECLARE @isReminderOn int

    DECLARE @hasAttachments int

    DECLARE @subject nvarchar(4000)

    DECLARE @bodyPreview nvarchar(4000)

    DECLARE @importance nvarchar(4000)

    DECLARE @sensitivity nvarchar(4000)

    DECLARE @isAllDay int

    DECLARE @isCancelled int

    DECLARE @isOrganizer int

    DECLARE @responseRequested int

    DECLARE @seriesMasterId nvarchar(4000)

    DECLARE @showAs nvarchar(4000)

    DECLARE @v_type nvarchar(4000)

    DECLARE @webLink nvarchar(4000)

    DECLARE @onlineMeetingUrl nvarchar(4000)

    DECLARE @recurrence nvarchar(4000)

    DECLARE @responseStatusResponse nvarchar(4000)

    DECLARE @responseStatusTime nvarchar(4000)

    DECLARE @bodyContentType nvarchar(4000)

    DECLARE @bodyContent nvarchar(4000)

    DECLARE @startDateTime nvarchar(4000)

    DECLARE @startTimeZone nvarchar(4000)

    DECLARE @endDateTime nvarchar(4000)

    DECLARE @endTimeZone nvarchar(4000)

    DECLARE @locationDisplayName nvarchar(4000)

    DECLARE @locationLocationType nvarchar(4000)

    DECLARE @locationUniqueId nvarchar(4000)

    DECLARE @locationUniqueIdType nvarchar(4000)

    DECLARE @organizerEmailAddressName nvarchar(4000)

    DECLARE @organizerEmailAddressAddress nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @displayName nvarchar(4000)

    DECLARE @locationType nvarchar(4000)

    DECLARE @uniqueId nvarchar(4000)

    DECLARE @uniqueIdType nvarchar(4000)

    DECLARE @statusResponse nvarchar(4000)

    DECLARE @statusTime nvarchar(4000)

    DECLARE @emailAddressName nvarchar(4000)

    DECLARE @emailAddressAddress nvarchar(4000)

    EXEC sp_OAMethod @json, 'StringOf', @odataContext OUT, '"@odata.context"'
    SELECT @i = 0
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'value'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @odataEtag OUT, 'value[i]."@odata.etag"'
        EXEC sp_OAMethod @json, 'StringOf', @id OUT, 'value[i].id'
        EXEC sp_OAMethod @json, 'StringOf', @createdDateTime OUT, 'value[i].createdDateTime'
        EXEC sp_OAMethod @json, 'StringOf', @lastModifiedDateTime OUT, 'value[i].lastModifiedDateTime'
        EXEC sp_OAMethod @json, 'StringOf', @changeKey OUT, 'value[i].changeKey'
        EXEC sp_OAMethod @json, 'StringOf', @originalStartTimeZone OUT, 'value[i].originalStartTimeZone'
        EXEC sp_OAMethod @json, 'StringOf', @originalEndTimeZone OUT, 'value[i].originalEndTimeZone'
        EXEC sp_OAMethod @json, 'StringOf', @iCalUId OUT, 'value[i].iCalUId'
        EXEC sp_OAMethod @json, 'IntOf', @reminderMinutesBeforeStart OUT, 'value[i].reminderMinutesBeforeStart'
        EXEC sp_OAMethod @json, 'BoolOf', @isReminderOn OUT, 'value[i].isReminderOn'
        EXEC sp_OAMethod @json, 'BoolOf', @hasAttachments OUT, 'value[i].hasAttachments'
        EXEC sp_OAMethod @json, 'StringOf', @subject OUT, 'value[i].subject'
        EXEC sp_OAMethod @json, 'StringOf', @bodyPreview OUT, 'value[i].bodyPreview'
        EXEC sp_OAMethod @json, 'StringOf', @importance OUT, 'value[i].importance'
        EXEC sp_OAMethod @json, 'StringOf', @sensitivity OUT, 'value[i].sensitivity'
        EXEC sp_OAMethod @json, 'BoolOf', @isAllDay OUT, 'value[i].isAllDay'
        EXEC sp_OAMethod @json, 'BoolOf', @isCancelled OUT, 'value[i].isCancelled'
        EXEC sp_OAMethod @json, 'BoolOf', @isOrganizer OUT, 'value[i].isOrganizer'
        EXEC sp_OAMethod @json, 'BoolOf', @responseRequested OUT, 'value[i].responseRequested'
        EXEC sp_OAMethod @json, 'StringOf', @seriesMasterId OUT, 'value[i].seriesMasterId'
        EXEC sp_OAMethod @json, 'StringOf', @showAs OUT, 'value[i].showAs'
        EXEC sp_OAMethod @json, 'StringOf', @v_type OUT, 'value[i].type'
        EXEC sp_OAMethod @json, 'StringOf', @webLink OUT, 'value[i].webLink'
        EXEC sp_OAMethod @json, 'StringOf', @onlineMeetingUrl OUT, 'value[i].onlineMeetingUrl'
        EXEC sp_OAMethod @json, 'StringOf', @recurrence OUT, 'value[i].recurrence'
        EXEC sp_OAMethod @json, 'StringOf', @responseStatusResponse OUT, 'value[i].responseStatus.response'
        EXEC sp_OAMethod @json, 'StringOf', @responseStatusTime OUT, 'value[i].responseStatus.time'
        EXEC sp_OAMethod @json, 'StringOf', @bodyContentType OUT, 'value[i].body.contentType'
        EXEC sp_OAMethod @json, 'StringOf', @bodyContent OUT, 'value[i].body.content'
        EXEC sp_OAMethod @json, 'StringOf', @startDateTime OUT, 'value[i].start.dateTime'
        EXEC sp_OAMethod @json, 'StringOf', @startTimeZone OUT, 'value[i].start.timeZone'
        EXEC sp_OAMethod @json, 'StringOf', @endDateTime OUT, 'value[i].end.dateTime'
        EXEC sp_OAMethod @json, 'StringOf', @endTimeZone OUT, 'value[i].end.timeZone'
        EXEC sp_OAMethod @json, 'StringOf', @locationDisplayName OUT, 'value[i].location.displayName'
        EXEC sp_OAMethod @json, 'StringOf', @locationLocationType OUT, 'value[i].location.locationType'
        EXEC sp_OAMethod @json, 'StringOf', @locationUniqueId OUT, 'value[i].location.uniqueId'
        EXEC sp_OAMethod @json, 'StringOf', @locationUniqueIdType OUT, 'value[i].location.uniqueIdType'
        EXEC sp_OAMethod @json, 'StringOf', @organizerEmailAddressName OUT, 'value[i].organizer.emailAddress.name'
        EXEC sp_OAMethod @json, 'StringOf', @organizerEmailAddressAddress OUT, 'value[i].organizer.emailAddress.address'
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'value[i].categories'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            -- ...
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'value[i].locations'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @displayName OUT, 'value[i].locations[j].displayName'
            EXEC sp_OAMethod @json, 'StringOf', @locationType OUT, 'value[i].locations[j].locationType'
            EXEC sp_OAMethod @json, 'StringOf', @uniqueId OUT, 'value[i].locations[j].uniqueId'
            EXEC sp_OAMethod @json, 'StringOf', @uniqueIdType OUT, 'value[i].locations[j].uniqueIdType'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'value[i].attendees'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @v_type OUT, 'value[i].attendees[j].type'
            EXEC sp_OAMethod @json, 'StringOf', @statusResponse OUT, 'value[i].attendees[j].status.response'
            EXEC sp_OAMethod @json, 'StringOf', @statusTime OUT, 'value[i].attendees[j].status.time'
            EXEC sp_OAMethod @json, 'StringOf', @emailAddressName OUT, 'value[i].attendees[j].emailAddress.name'
            EXEC sp_OAMethod @json, 'StringOf', @emailAddressAddress OUT, 'value[i].attendees[j].emailAddress.address'
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END


    PRINT 'Success.'

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


END
GO