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.
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!).
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.
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:
and put that into the address bar of my browser, I would see:
For now, I’ll make the calendar public (we’ll deal with authentication later)
Which will now enable us to view the XML from the calendar feed URL.
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 –
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
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
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
So, if we now use this namespace in the call to extractValue, the query will now be able to parse the XML correctly
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
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
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.
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’).
This updated query can be used in the source of the report region in the application.
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.
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.