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