(SQL Server) MS Graph Calendar Create Event

Creates a new event in a specified calendar.

For more details, see https://docs.microsoft.com/en-us/graph/api/calendar-post-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
    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 as shown here:
    --    Get Microsoft Graph OAuth2 Access Token with Calendars.ReadWrite scope.

    DECLARE @jsonToken int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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

    -- Create a JSON body for the HTTP POST
    -- Use this online tool to generate the code from sample JSON: 
    -- Generate Code to Create JSON

    -- {
    --   "subject": "Let's go for lunch",
    --   "body": {
    --     "contentType": "HTML",
    --     "content": "Does mid month work for you?"
    --   },
    --   "start": {
    --       "dateTime": "2019-11-15T12:00:00",
    --       "timeZone": "Pacific Standard Time"
    --   },
    --   "end": {
    --       "dateTime": "2019-11-15T14:00:00",
    --       "timeZone": "Pacific Standard Time"
    --   },
    --   "location":{
    --       "displayName":"Harry's Bar"
    --   },
    --   "attendees": [
    --     {
    --       "emailAddress": {
    --         "address":"adelev@contoso.onmicrosoft.com",
    --         "name": "Adele Vance"
    --       },
    --       "type": "required"
    --     }
    --   ]
    -- }

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

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'subject', 'Let''s go for lunch'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'body.contentType', 'HTML'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'body.content', 'Does mid month work for you?'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'start.dateTime', '2019-11-15T12:00:00'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'start.timeZone', 'Pacific Standard Time'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'end.dateTime', '2019-11-15T14:00:00'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'end.timeZone', 'Pacific Standard Time'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'location.displayName', 'Harry''s Bar'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'attendees[0].emailAddress.address', 'adelev@contoso.onmicrosoft.com'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'attendees[0].emailAddress.name', 'Adele Vance'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'attendees[0].type', 'required'

    -- POST the JSON to https://graph.microsoft.com/v1.0/me/calendars/{id}/events
    -- This is posting to a calendar in the default calendarGroup.

    -- Specify the calendar id
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'id', 'AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAgEGAAAA5_vF7TKKdE6bGCRqXyl2PQAClEpRTgAAAA=='
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://graph.microsoft.com/v1.0/me/calendars/{$id}/events', 'application/json', @json
    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
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 <> 201
      BEGIN
        EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
        PRINT 'Failed, response status code = ' + @iTmp0
        EXEC @hr = sp_OADestroy @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @json
        RETURN
      END
    EXEC @hr = sp_OADestroy @resp

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

    -- A sample response:
    -- (See code for parsing this response below..)

    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('admin%40chilkat.io')/calendars('AQMkADAwATM0MDAAMS ... AClEpRTgAAAA%3D%3D')/events/$entity",
    --   "@odata.etag": "W/\"5+vF7TKKdE6bGCRqXyl2PQAClIgmmw==\"",
    --   "id": "AQMkADAwAT ... ApRZ7JkAAAA=",
    --   "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": "040000008200E00074C5B7101A82E00800000000F05DF1C23CFCD40100000000000000001000000009911D155F71EF42A230FEBFE5F7486A",
    --   "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=AQMkADAwATM0MDAAMS1iNTcwL...pRZ7JkAAAA%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-15T12:00:00.0000000",
    --     "timeZone": "Pacific Standard Time"
    --   },
    --   "end": {
    --     "dateTime": "2019-11-15T14:00:00.0000000",
    --     "timeZone": "Pacific Standard Time"
    --   },
    --   "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 @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)

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


    PRINT 'Success.'

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


END
GO