SQL Server

List Google Calendars

See more Google Calendar Examples

Demonstrates how to list the Google Calendars for an account. SQL Server

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

    -- This example uses a previously obtained access token having permission for the 
    -- Google Calendar scope.

    -- In this example, Get Google Calendar OAuth2 Access Token, the access
    -- token was saved to a JSON file.  This example fetches the access token from the file..
    DECLARE @jsonToken int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/googleCalendar.json'
    EXEC sp_OAMethod @jsonToken, 'HasMember', @iTmp0 OUT, 'access_token'
    IF @iTmp0 = 0
      BEGIN

        PRINT 'No access token found.'
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END
    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0

    DECLARE @jsonResponse nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @jsonResponse OUT, 'https://www.googleapis.com/calendar/v3/users/me/calendarList'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @http
        RETURN
      END


    PRINT @jsonResponse

    PRINT '-----------------------------'

    -- This is a sample JSON response:
    -- (Sample code to iterate over the JSON response is shown below.)

    -- {
    --  "kind": "calendar#calendarList",
    --  "etag": "\"p32cfpufit76da0g\"",
    --  "nextSyncToken": "CJj8-fLpzNUCEhhzdXBwb3J0QGNoaWxrYXRjbG91ZC5jb20=",
    --  "items": [
    --   {
    --    "kind": "calendar#calendarListEntry",
    --    "etag": "\"1465249947472000\"",
    --    "id": "support@chilkatcloud.com",
    --    "summary": "support@chilkatcloud.com",
    --    "timeZone": "America/Chicago",
    --    "colorId": "14",
    --    "backgroundColor": "#9fe1e7",
    --    "foregroundColor": "#000000",
    --    "selected": true,
    --    "accessRole": "owner",
    --    "defaultReminders": [
    --     {
    --      "method": "popup",
    --      "minutes": 10
    --     }
    --    ],
    --    "notificationSettings": {
    --     "notifications": [
    --      {
    --       "type": "eventCreation",
    --       "method": "email"
    --      },
    --      {
    --       "type": "eventChange",
    --       "method": "email"
    --      },
    --      {
    --       "type": "eventCancellation",
    --       "method": "email"
    --      },
    --      {
    --       "type": "eventResponse",
    --       "method": "email"
    --      }
    --     ]
    --    },
    --    "primary": true
    --   },
    --   {
    --    "kind": "calendar#calendarListEntry",
    --    "etag": "\"1502373382732000\"",
    --    "id": "#contacts@group.v.calendar.google.com",
    --    "summary": "Contacts",
    --    "timeZone": "America/Chicago",
    --    "colorId": "13",
    --    "backgroundColor": "#92e1c0",
    --    "foregroundColor": "#000000",
    --    "selected": true,
    --    "accessRole": "reader",
    --    "defaultReminders": []
    --   },
    --   {
    --    "kind": "calendar#calendarListEntry",
    --    "etag": "\"1502373376447000\"",
    --    "id": "en.usa#holiday@group.v.calendar.google.com",
    --    "summary": "Holidays in United States",
    --    "timeZone": "America/Chicago",
    --    "colorId": "8",
    --    "backgroundColor": "#16a765",
    --    "foregroundColor": "#000000",
    --    "selected": true,
    --    "accessRole": "reader",
    --    "defaultReminders": []
    --   }
    --  ]
    -- }
    -- 

    -- Iterate over the JSON response..
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonResponse

    DECLARE @numCalendars int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numCalendars OUT, 'items'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numCalendars
      BEGIN


        PRINT '--- ' + @i + ' ---'
        EXEC sp_OASetProperty @json, 'I', @i

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'items[i].kind'
        PRINT 'kind: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'items[i].id'
        PRINT 'id: ' + @sTmp0

        -- Examine the notification settings, if any..
        DECLARE @numSettings int
        EXEC sp_OAMethod @json, 'SizeOfArray', @numSettings OUT, 'items[i].notificationSettings.notifications'
        IF @numSettings > 0
          BEGIN
            DECLARE @j int
            SELECT @j = 0
            WHILE @j < @numSettings
              BEGIN
                EXEC sp_OASetProperty @json, 'J', @j

                EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'items[i].notificationSettings.notifications[j].type'
                PRINT 'Notification Type: ' + @sTmp0

                EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'items[i].notificationSettings.notifications[j].method'
                PRINT 'Notification Method: ' + @sTmp0
                SELECT @j = @j + 1
              END
          END
        SELECT @i = @i + 1
      END

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


END
GO