(SQL Server) List Events on a Specified Google Calendar

See more Google Calendar Examples

Demonstrates how to fetch the list of events on a particular Google Calendar. The calendar can be referenced by the calendar ID, or by the keyword "primary".

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.

    -- 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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    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
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

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

    -- Let's get the primary calendar.  A calendar ID could have be used instead of "primary".
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'calendarId', 'primary'
    DECLARE @sbResponse int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.googleapis.com/calendar/v3/calendars/{$calendarId}/events', @sbResponse
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponse
        RETURN
      END

    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        -- Note: If a 401 unauthorized response is received, it likely means that the OAuth2 access token needs
        -- to be refreshed or re-fetched.

        EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
        PRINT 'Error response status: ' + @iTmp0
        EXEC sp_OAMethod @sbResponse, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponse
        RETURN
      END

    -- A sample JSON response:
    -- (Code for parsing the Google Calendar events is shown below.)

    -- {
    --  "kind": "calendar#events",
    --  "etag": "\"p32cfpufit76da0g\"",
    --  "summary": "support@chilkatcloud.com",
    --  "updated": "2017-08-10T14:00:27.199Z",
    --  "timeZone": "America/Chicago",
    --  "accessRole": "owner",
    --  "defaultReminders": [
    --   {
    --    "method": "popup",
    --    "minutes": 10
    --   }
    --  ],
    --  "nextSyncToken": "CJj8-fLpzNUCEJj8-fLpzNUCGAU=",
    --  "items": [
    --   {
    --    "kind": "calendar#event",
    --    "etag": "\"3004746854620000\"",
    --    "id": "5jt9fopfg3rr9eftegiuq7gc5k",
    --    "status": "confirmed",
    --    "htmlLink": "https://www.google.com/calendar/event?eid=NWp0OWZvcGZnM3JyOWVmdGVnaXVxN2djNWsgc3VwcG9ydEBjaGlsa2F0Y2xvdWQuY29t",
    --    "created": "2017-08-10T13:57:07.000Z",
    --    "updated": "2017-08-10T13:57:07.310Z",
    --    "summary": "Run 10 miles",
    --    "creator": {
    --     "email": "support@chilkatcloud.com",
    --     "self": true
    --    },
    --    "organizer": {
    --     "email": "support@chilkatcloud.com",
    --     "self": true
    --    },
    --    "start": {
    --     "dateTime": "2017-08-10T12:00:00-05:00"
    --    },
    --    "end": {
    --     "dateTime": "2017-08-10T13:00:00-05:00"
    --    },
    --    "iCalUID": "5jt9fopfg3rr9eftegiuq7gc5k@google.com",
    --    "sequence": 0,
    --    "hangoutLink": "https://plus.google.com/hangouts/_/chilkatcloud.com/support?hceid=c3VwcG9ydEBjaGlsa2F0Y2xvdWQuY29t.5jt9fopfg3rr9eftegiuq7gc5k",
    --    "reminders": {
    --     "useDefault": true
    --    }
    --   },
    --   {
    --    "kind": "calendar#event",
    --    "etag": "\"3004746898118000\"",
    --    "id": "7dvh49vd219r3sq7jdueieu8rc",
    --    "status": "confirmed",
    --    "htmlLink": "https://www.google.com/calendar/event?eid=N2R2aDQ5dmQyMTlyM3NxN2pkdWVpZXU4cmMgc3VwcG9ydEBjaGlsa2F0Y2xvdWQuY29t",
    --    "created": "2017-08-10T13:57:29.000Z",
    --    "updated": "2017-08-10T13:57:29.059Z",
    --    "summary": "Eat Giordano's Pizza",
    --    "creator": {
    --     "email": "support@chilkatcloud.com",
    --     "self": true
    --    },
    --    "organizer": {
    --     "email": "support@chilkatcloud.com",
    --     "self": true
    --    },
    --    "start": {
    --     "dateTime": "2017-08-10T15:00:00-05:00"
    --    },
    --    "end": {
    --     "dateTime": "2017-08-10T16:00:00-05:00"
    --    },
    --    "iCalUID": "7dvh49vd219r3sq7jdueieu8rc@google.com",
    --    "sequence": 0,
    --    "hangoutLink": "https://plus.google.com/hangouts/_/chilkatcloud.com/support?hceid=c3VwcG9ydEBjaGlsa2F0Y2xvdWQuY29t.7dvh49vd219r3sq7jdueieu8rc",
    --    "reminders": {
    --     "useDefault": true
    --    }
    --   },
    --   {
    --    "kind": "calendar#event",
    --    "etag": "\"3004746915224000\"",
    --    "id": "5btd89ljn07bg3tlgpimcaqrro",
    --    "status": "confirmed",
    --    "htmlLink": "https://www.google.com/calendar/event?eid=NWJ0ZDg5bGpuMDdiZzN0bGdwaW1jYXFycm8gc3VwcG9ydEBjaGlsa2F0Y2xvdWQuY29t",
    --    "created": "2017-08-10T13:57:37.000Z",
    --    "updated": "2017-08-10T13:57:37.612Z",
    --    "summary": "Nap",
    --    "creator": {
    --     "email": "support@chilkatcloud.com",
    --     "self": true
    --    },
    --    "organizer": {
    --     "email": "support@chilkatcloud.com",
    --     "self": true
    --    },
    --    "start": {
    --     "dateTime": "2017-08-10T16:30:00-05:00"
    --    },
    --    "end": {
    --     "dateTime": "2017-08-10T17:30:00-05:00"
    --    },
    --    "iCalUID": "5btd89ljn07bg3tlgpimcaqrro@google.com",
    --    "sequence": 0,
    --    "hangoutLink": "https://plus.google.com/hangouts/_/chilkatcloud.com/support?hceid=c3VwcG9ydEBjaGlsa2F0Y2xvdWQuY29t.5btd89ljn07bg3tlgpimcaqrro",
    --    "reminders": {
    --     "useDefault": true
    --    }
    --   }
    --  ]
    -- }
    -- 

    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, 'LoadSb', @success OUT, @sbResponse

    DECLARE @numEvents int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numEvents OUT, 'items'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numEvents
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'items[i].summary'
        PRINT @sTmp0
        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'items[i].start.dateTime'
        PRINT @sTmp0
        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'items[i].end.dateTime'
        PRINT @sTmp0

        PRINT '--'
        SELECT @i = @i + 1
      END

    -- Sample output:

    -- Run 10 miles
    -- 2017-08-10T12:00:00-05:00
    -- 2017-08-10T13:00:00-05:00
    -- --
    -- Eat Giordano's Pizza
    -- 2017-08-10T15:00:00-05:00
    -- 2017-08-10T16:00:00-05:00
    -- --
    -- Nap
    -- 2017-08-10T16:30:00-05:00
    -- 2017-08-10T17:30:00-05:00
    -- --
    -- 

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


END
GO