Integrating Apex with Google Calendar (Part 1)

There was an interesting post on the Apex forums where Zvika Gutkin asks

is it possible to integrate apex with google calendar ?

This kind of question comes up quite regularly in the forums, I don’t mean specifically integrating with Google Calendar, but integrating Apex with some other (web-based) third-party solution. Whilst Apex is a fantastic tool for developing ‘standalone’ web applications, it’s true power (to me) comes when you start integrating that application with other existing websites or services. Many online services, such as Flickr, Ebay, Paypal and many many others now offer and document application programming interfaces (API’s) which will allow you to interact directly with their services and integrate them directly into your application.

I have to admit at this point that over the years I’ve had lots and lots of experience of working on what I like to call ‘interface systems’, where system X needs to ‘talk’ to system Y, sometimes the communication has been in one direction, other times it’s been in both directions, sometimes I’ve been ‘lucky’ and system X and Y have well known API’s that have been documented, other times I’ve not been so lucky and the documentation could have been written on the back of a Lilliputian sized stamp.

Fortunately the Google Calendar API is documented pretty well and includes some examples on how to integrate it with desktop client or web applications. Google supply some client libraries (currently available for Java and C#) to make integration even easier. So we could –

  • Load the java library into the database
  • Write some PL/SQL wrapper routines around the java library routines
  • Call the PL/SQL wrapper routines from the Apex application

However, whilst this would work, I’m a big fan of the Tom Kyte philosophy of “if you can do it in the database, then do it in the database” (I’m paraphrasing since I couldn’t find the exact quote), so by using features already in the database we can avoid the need to load the java libraries at all, thus making the application far more portable.

I’d like to stress at this point that I haven’t had any experience of Google Calendar until about 15 minutes ago, also the techniques here are not really specific to the Google Calendar, since you can adapt them to be able to interact with all sorts of different websites and services.

Ok…on with the ‘demo’. The first thing was to sign up for a Google Calendar account.

Google Calendar

Obviously since I haven’t used the Calendar before, mine is completely empty, so I created an appointment (totally fictional, although that reminds me I must lookup when my dental appointment actually is!).

Calendar with Appointment

You can choose whether to make your calendar public or not, if your calendar is public then anyone will be able to view it (if they know the URL) and your appointments will also show up in a calendar search.

Google Calendar Sharing

By default, my Calendar wasn’t being shared publically, i.e. if I copied the link for the XML feed for the Calendar Address, which is in the form:

http://www.google.com/calendar/feeds/jes@shellprompt.net/public/basic

and put that into the address bar of my browser, I would see:

Calendar Access Denied

For now, I’ll make the calendar public (we’ll deal with authentication later)

Public Calendar

Which will now enable us to view the XML from the calendar feed URL.

Calendar XML Feed

So, now we have the XML feed, we get to the ‘interesting’ part, how can we use this from our Apex application. At this point I’ve seen lots of people reach for Java routines, or use UTL_HTTP and trying to parse the output themselves, however Oracle have gone to a lot of effort to make working with XML within the database as easy as possible (although it can take a little while to get used to it if you’re not familiar with XML). By using the built in functionality of the database we can work with this XML data as easily as though it were data stored in a regular relational table.

Please note, I’m not going to go into any great depth regarding the various XQuery, XPath and XML functionality provided by the database, since entire books can (and have) been written about them. I would urge anyone interested to look at the XML related documentation available on OTN for more information.

Without furthur ado, here’s our first query –

XML Query

I apologise for having to use an image, rather than quoting the SQL in text, however due to the length of the line no matter how I tried to line it up the ‘breakup’ tended to make it unreadable, so an images will have to do!

There is quite a lot ‘going on’ in that query, with references to ‘xmlsequence’, ‘httpuritype’ and so on (remember what I said about reading the documentation?). However in a nutshell, the database will use the URL that we have passed in and give the result of calling that URL back to us in an XML format, as shown here

Query Results

As you can see, the result comes back as a single item containing all the XML returned by the URL, this where it gets interesting because we can use the various XQuery and XPath functionality to request particular parts of the XML document. So, for example if we want to get the name of the person the Calendar belongs to, we can use the following query

Extracting the Calendar owner

Here we used the extractValue function to retrieve the value of a particular fragment of the XML document (the /feed/author/name element) and used the text() method to obtain the actual text value of the element. However it didn’t quite work, since the value returned was Null rather than the value we expected. The reason for this is due to the namespace of the returned XML document, looking at the XML which is returned by the Calender Feed URL, we can see the namespace that is defined

xmlns=”http://www.w3.org/2005/Atom”

So, if we now use this namespace in the call to extractValue, the query will now be able to parse the XML correctly

Extracting the Calendar Owner using a namespace

If you ever find yourself having problems extracting data from your XML document, then always doublecheck that the namespace isn’t the cause of your problems, forgetting to use the correct namespace can easily chew up hours of head-scratching and banging your head on the table.

Of course, we’re not limited to just extracting a single value at a time, we can extract the value from the /feed/author/name and the /feed/author/email elements at the same time

Extracting multiple element values

In order to get the actual appointment entries, we can modify the query to look at each of the /feed/entry fragments and then extract the details from each individual entry, unless you’re familiar with XML this part can seem like a bit of a ‘mental leap’, however you can think of it as analagous to iterating round a collection, the query looks like this

Extracting a Calendar Entry
Obviously I only have one calendar entry, so you can’t really see the ‘iteration’ at work, however if I add another entry to my calendar, then the results become

Multiple Calendar Events

As you can see, we now have a row for each entry in the calendar, which makes it extremely easy to work with the data. Note also that the summary column contains some HTML markup which was generated by the Google calender itself.

You can now create a simple Application Express application which lists your Google Calendar appoints, I created a new application with a single page. On that page I created an SQL report region and used the same SQL as the region source.

Apex Report Region

Running the page shows the Calendar appointments in a report.
Apex Report

We can even take this a bit furthur, and make one of the columns in the report a link back to the original appointment in Google Calendar (thereby allowing you to edit the details easily). If you examine the XML returned by the Feed URL you will notice that each calendar entry appears to have two ‘link’ elements, one element provides a link to subscribe to the event (via an RSS reader for example) the other element provides a link back to the calendar entry. By using some carefully crafted syntax we can extract the value from the correct link element by matching against the attributes (one link element has the value of ‘alternate’ in the ‘rel’ attribute, the other element has the value of ‘self’).
Extracting the link

This updated query can be used in the source of the report region in the application.

Linked Report

Here I’ve set the link column to not display in the report and I’ve made the title column a link (to a popup window) back to the original Google calendar entry (by using #LINK# as the URL). If I click on the link in the title column then I will be taken to my Google calendar entry (and made to login if necessary), as shown here.
Calendar link

So, there we have it…you can now list your Google Calendar appointments in your Apex application and be able to link back to the original calendar entries to make amendments.

I’ll cover how to create calendar entries directly from your application in Part 2.

5 thoughts on “Integrating Apex with Google Calendar (Part 1)

  1. Dimitri Gielis

    Great post John!

    Part 2 will be interesting for the DG Tournament application. I’m thinking of adding a button in DG Tournament to add directly the schedule of the games in your own google calendar 😉

    Like

    Reply
  2. Pingback: Integrating Apex with Google Calendar (Part 2) » Johns Blog

  3. Tobin

    I keep getting a “missing right parenthesis” error, but i’ve triple checked and everything is exactly as you stated

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s