Integrating Apex with Google Calendar (Part 2)

In the last blog post I showed how to integrate the Google Calendar with an Application Express application so that you could view the appointments in your calendar and to be able to link back to the original calendar entry.

This time around, I’m (hopefully) going to show how you can create calendar entries directly from your application and also how to use secure authentication (rather than the public access that I used last time to read the calendar).

I’ll stress again however that this isn’t a complete walkthrough by any means, you should also take the time to read through the Google Calendar API documentation to make sure you understand all the different methods of integratation and the impact of using different types of authentication.

One of the first things you will notice when you look at the authentication methods for the calendar, is that they need to be done via HTTP, however with a bit of configuration we can do this using the UTL_HTTP package.

The first thing to do, if you haven’t already done so, is to configure the Oracle Wallet Manager, this will enable us to access an HTTPS website if the SSL certficate they use is signed by a Certificate Authority already stored in your wallet. If the certficate is not recognised because it is signed by an Authority that is not already installed in your wallet, then you can add the certificate of the authority who signed the original certificate to your wallet, which essentially says that you ‘trust’ them to be who they say they are (assuming that you do).

Certificates are all about ‘trust’, if you trust entity ‘A’ implicity and entity ‘A’ signs a certificate that verifies the credentials of entity ‘B’, then you know you will also be able to trust entity ‘B’. In this way you can build up entire chains of trusted certificates. When the Oracle Wallet Manager is installed, it will install some certificates, also know as root level certificates, belonging to certain companies who are recognised as Certificate Authorities. So, after you have installed the Wallet Manager you will be able to access any SSL enabled websites that have had their certificates signed by one of the authorities in the root level certificates that have been preinstalled for you.

The Wallet Manager program is a java program called ‘owm’ which usually lives in the $ORACLE_HOME/bin directory, although it may be elsewhere depending on your particular installation. The ‘owm’ program is a graphical java program, so if you are accessing the machine remotely (via telnet or ssh for example) make sure you have your DISPLAY environment variable setup (under Unix).

When you start the OWM, you should see a screen similar to this.

Oracle Wallet Manager

If you don’t already have an existing wallet, then you can choose to create one. I’m creating a new one here, so that I can demonstrate what happens (you can actually have multiple wallets if you chose to, since you can store them in different places).
Oracle Wallet Manager

After you choose to create the new wallet, you will be asked to create a password so that access to the wallet is secured. You will then be asked whether you wish to create a certificate request, you can choose no at this stage since we do not require one.
Oracle Wallet Manager

The wallet will now be created with some root level certificates already preinstalled.
Oracle Wallet Manager

You should now save the wallet in a suitable location, I’m choosing to save mine in ‘/etc/oracle/wallets’ (note that I’m using a Unix machine), the wallet should be stored somewhere where the user that the Oracle software is running as will have permissions to access it.
Oracle Wallet Manager

Now we need to perform a test to make sure that the OWM is working correctly, we can do this by trying to retrieve the contents of a webpage via HTTPS. A good example to test is the URL ‘https://www.google.com/accounts/Login’, using the UTL_HTTP package we can write a small anonymous PL/SQL block to try and retrieve this page –

HTTPS Connection with UTL_HTTP

This code should be fairly straightforward if you’re familiar with the UTL_HTTP package, if you’re not familiar with it then I’d recommend reading the documentation. Essentially this PL/SQL block will attempt to perform a GET against the ‘https://www.google.com/accounts/Login’ URL and will generate an exception if it gets an error status code (i.e. an HTTP 4xx or 5xx code) back from the webserver since we have set the set_response_error_check to true.

However if we run this piece of code, we get the following result.

HTTPS Failed connection
The reason is quite obvious, we haven’t told it to use our wallet yet.

HTTPS Connection using the OWM
Notice the additional call to UTL_HTTP.set_wallet on line 7.

The first parameter to the set_wallet routine is the path to the wallet, which should match the location you saved the wallet earlier, it’s very important to get the syntax correct here, especially if you are using Windows to use the correct path delimiter (remember I’m using Unix here). The second parameter to the routine is the password associated with the wallet, if you don’t pass in the correct password then the wallet will not be able to be opened.

HTTPS Connection Success

Running the new PL/SQL block results in a successful page retrieval this time, as you can see by the HTTP-200 response code, you could modify the code to display the actual page that is returned if you want to be convinced that it has worked.

So now we know the wallet has been installed correctly and that we can use it to connect to an HTTPS webpage, we can look at using it with the Google Calendar authentication methods.

As I stressed earlier, you should really look at the Google documentation to understand how the authentication process needs to be performed, otherwise the rest of this article might not make too much sense.

The important thing to understand from the Google documentation is that the authentication process relies on obtaining an authentication token which you can then use in your requests to create calendar events. It is not going to be easy to present an example of how you do this in an anonymous PL/SQL block since the authentication process also relies on directing the user to the Google login page (so that you can enter your credentials to authenticate) and then performs a redirect back to your (web) application.

The web application needs to send a request to the URL ‘https://www.google.com/accounts/AuthSubRequest’ (details are available in the documentation here) with parameters which determine whether the token is a secure token or not, whether the user requires a one-time token or a session token and also which page the user will be redirected back to after logging into their Google account.

In the same Apex application I created in part 1, I have added a button which I am going to use to fire the sequence of tasks that need to happen to add an event to my calendar. When I created the button I made it branch back to the same page when it is pressed.

Create Calendar Entry Button

Now comes the ‘tricky part’, what we actually want to happen when the button is pressed is this –

  • The user is redirected to the Google Website in order to authenticate their account.
  • On successfull authentication, the user is redirected back to our application with a valid authentication token.
  • The token is then used in the call to create a calendar entry.

There is a potential ‘issue’ here, the issue is how the authentication token is passed back to our application. Using the example given in the Google documentation, we would use a URL like this to direct the user to the Google website to authenticate


https://www.google.com/accounts/AuthSubRequest?next=http%3A%2F%2Fwww.yourwebapp.com%2Fshowcalendar.html&
scope=http%3A%2F%2Fwww.google.com%2Fcalendar%2Ffeeds%2F&session=0&secure=0

Again, apologies for the length of the line.

The issue is that the example uses a URL of ‘www.yourwebapp.com/showcalendar.html’ as the URL to be used to redirect back to upon successfull authentication. The token will be tagged onto the end of this URL, like this –


http://www.yourwebapp.com/showcalendar.html?token=CKF50YzIHxCT85KMAg

Clearly this won’t work with the type of URL’s we are used to using in Apex, so the question becomes how can we possibly pass across our current (Apex format) URL in a format that can accept the token parameter being added to it by the Google authentication engine. Well, the answer is, we can use a ‘trick’. Well, it’s not really a ‘trick’ as such, we will use a different URL to redirect back to which will then generate the real URL we want to redirect to. That last part probably isn’t very clear, so lets demonstrate.

We’ll create a procedure which can be called directly via a URL (using the mod_plsql module), this procedure will accept a parameter named ‘token’ which will enable us to capture the value of the token parameter passed from the Google authentication process.

Redirect Procedure

Notice the grant that was performed to enable the procedure to be called by user specified in the database access descriptor (DAD), in my DAD I am using the user APEX_PUBLIC_USER.

You can test the procedure works by typing the URL to the procedure into your browser, for example in my case the URL would be ‘http://apex.shellprompt.net/pls/apex/jes.gcal_redirect?token=XYZ’ since I created the procedure in the ‘JES’ schema. Putting this URL into my browser gives the following result.

Testing the procedure in the browser

Excellent, we can now use the URL to this procedure as the destination for the redirection after the Google Authentication, if we modify the example URL (from the documentation) and change the redirection URL to be our new procedure (after encoding the URL), we get


https://www.google.com/accounts/AuthSubRequest?
next=http%3A%2F%2Fapex.shellprompt.net%2Fpls%2Fapex%2Fjes.gcal_redirect
&scope=http%3A%2F%2Fwww.google.com%2Fcalendar%2Ffeeds%2F
&session=0&secure=0

Note that I’ve broken the URL up to make it easier to read.

If you plug this URL into a browser you will be taken to the Google website to authenticate.
Google Login

Once you have successfully logged in, you will be asked to grant access to your Google Calendar account. Note that at this point we’re just obtaining a onetime token.
Access Request

After allowing access, you will be redirected back to the URL pointing to the gcal_redirect procedure and you will be able to see the value of the token you have been issued with.

Token Access

We can now adapt the gcal_redirect procedure so that it can redirect us back to the original Apex page that we started on. I’m going to tidy things up a little and put the gcal_redirect procedure inside a package (named PKG_GCAL) rather than as a standalone procedure, also we need a way of storing the token value in the Apex application, to do this I’ve created a hidden page item called (P1_TOKEN), you could create an application item or use a context if you prefer, however for this simple demonstration a page level item will suffice.

PKG_GCAL

The package body is fairly simple at this point.
PKG_GCAL Body

The reason for the ‘replace’ command is so that we can easily subsitute the value of the token issued by Google into the value of the page item. We therefore need to make sure that the URL we’re going to pass across to Google also contains the P1_TOKEN item and TOKEN_VALUE value, otherwise the replace won’t work. Now depending on your exact requirements there are a few different ways to do this, as a simple example I have created a before header page process that simply performs a redirect back to the same page if the P1_TOKEN item is null and sets the value of the P1_TOKEN page item to TOKEN_VALUE.

Set empty token

Set empty token condition

Basically, this means that if the P1_TOKEN has not been set to it’s default value then we are redirected back to the same page with the P1_TOKEN value being set in the URL. Note that if you need to set any other values in the URL you should modify this process.

Now the ‘tricky’ part, we need to pass across the current URL to our gcal_redirect procedure, you could hardcode the URL (or as much of it as you need to), however by using some of the CGI Environment variables available to you, you can make the procedure fairly generic.
I’m extending the PKG_GCAL package to include a new procedure which we can call to get our intial redirect to the Google Calendar authentication URL note that I’m using screenshots from SQLDeveloper here rather than SQLPlus just so the code is a bit more readable due to the linebreaks.
pkg_gcal2.jpg
pkg_gcal_body2.jpg

The gcal_authenticate routine might look a little complicated, but really all it is doing is constructing the URL that must be passed into the gcal_redirect routine to direct back to the Apex calling page, you could choose to hardcode the URL here, but using the CGI Environment variables makes it much more portable.

Now I’ll add another before header page process to my Apex page and call the gcal_authenticate routine from it (if the token hasn’t already been set), essentially this means that whenever I access the page, if I don’t already have a valid session token from Google I will be forced to authenticate.
Redirection Page Process.jpg
Redirection Page Process Condition.jpg

Now when I run my page, a number of things will happen –

  • The P1_TOKEN item will be set to its default value of TOKEN_VALUE
  • The gcal_authenticate routine will be called, passing across the gcal_redirect routine as the URL to be redirected back to once the authentication has been successful, the current URL for the Apex page (and session id etc) will be passed across to the gcal_redirect routine so that the token value can be substituted for the default value of TOKEN_VALUE.
  • When the page redirects back to the Apex page, the P1_TOKEN page item will be set to the token value generated by the Google authentication process.

The screenshot of my page shows that the token value has been sent across in the URL and you can also see that the P1_TOKEN page item has actually stored the value of the token in session state.
Token Established.jpg

Now at this point, I’d like to highlight a problem, the token you have just got back from Google is not infact a session token (even though we specified that we wanted a session token), it is infact a token that we can *use* to get a session token. This is a very very subtle difference and can easily catch you it (it certainly caught me), if you try and use this token in any subsequent calls to the Google API, then you will probably find you get ‘Token Revoked’ type error messages back from the Google server. The reason is, as I mentioned, that we have still just got a one-time token, but we can exchange that one-time token for a session token.

So, I’m going to do two things –

  • Add a page item called P1_SESSION_TOKEN which will store the real session token
  • Add some code to the pkg_gcal package in order to convert the one-use token into a session token, this code is the gcal_get_session_token function.

Now I can modify my page and add another before header page process which will set the P1_SESSION_TOKEN page item by calling the gcal_get_session_token function, passing in the P1_TOKEN value. Obviously this page process needs to be called after the page process which obtains the initial token and it also has to have some conditional logic so that the session token is only obtained if the session token is not already set and the value of P1_SESSION_TOKEN is not its default value of P1_TOKEN.

get_session_token.jpg

get_session_token_condition.jpg

Then I can create another page process which will execute when the button is pressed and which calls the gcal_postevent procedure.
Note that in my gcal_postevent I am using the demo event that is provided in the Google documentation, I’ve used this demo event just so it’s easier to understand, rather than building up even more logic to create the XML for the event. However there is nothing to prevent you from creating your own event definition which will work so long as it conforms to the right syntax.
insert_entry.jpg
gcal_postevent.jpg

Finally, after glueing all the pieces together, I can run the page and see that the session token is obtain correctly.

session_token.jpg

Now that I have a valid session token, I can press the button, which will call the gcal_postevent procedure, passing in the session token and creating a demo event.
entry_added.jpg

Excellent, the event has been created, just to double check (although we know it must be true) we can check the actual Google calendar to confirm the entry is visible there too.

double_checking.jpg
So, in answer to the question “is it possible to integrate Google Calendar with Apex” the answer is a resounding “Yes!”, however it is far from straightforward and there quite a few hoops to jump through. However I would hasten to add that this is not the fault of Apex in any way, it is more a result of the way that Google have implemented their API’s (such as not being able to obtain a session token in one go).

Now for some disclaimers!!!!

I really want to stress that I couldn’t spend anywhere near as much time as I wanted to on this, since I have other commitments and I’m due to ship off to Oracle OpenWorld soon, so there are probably ‘bugs-a-plenty’ in some of the code I have written, infact I’m sure of it. There is nowhere near the level of error checking that you would want in order to be able to use this in a production application, however I didn’t approach this from the ‘write a polished framework for integrating with Google Calendar’, I approached this from the ‘How can it be done’ perspective.

So, don’t be surprised if there’s bugs in the code, don’t be surprised if it needs some work to ‘polish’ it up, but I hope I have shown how the concept can (and does) work and how you can use the various techniques at your disposal to not only integrate with Google Calendar, but also to integrate with any similar web based tool like it.

I’ve placed a copy of the pkg_gcal here perhaps when I can devote more time to it I will make it into a much more useable framework, however for now it may prove useful to someone. So once again, consider this *rough* code…it’s your responsibility to make it work in your own environment (or you could always hire me 😉

So…Part 3 (coming soon…possibly…maybe not).

8 thoughts on “Integrating Apex with Google Calendar (Part 2)

  1. Dimitri Gielis

    Well you made it… Part 2 is here 😉

    Maybe you’ll discover the “bugs” at OOW… somebody of the audience will have tried your solution and asks you some difficult questions during the panel ;-))

    Like

    Reply
  2. Steve Roach

    Thanks for this post – it helped a lot – specifically to do with setting up and using the wallet. Which is more that I can say about the Oracle docs 🙂

    Got me out of a hole, anyway. Cheers

    Like

    Reply
  3. Shamim Ashrafi

    Hi,

    I am trying to do this but getting bellow error. Can you please help me?

    Bad Request
    Your browser sent a request that this server could not understand.

    mod_plsql: /pls/apex/f HTTP-400 Missing ‘=’ in query string or post form

    Oracle-Application-Server-10g/10.1.2.0.0 Oracle-HTTP-Server Server at racdb02 Port 80

    Like

    Reply
  4. Prateek

    Hi John,
    I must agree, the way you have explained is simply awesome.
    It would be great if you could help me with the last part.

    After authenticating from google successfully, I am successfully being redirect back to my apex application as :
    http://:/apex/f?p=103:1:4194275508082616::::P1_TOKEN:1/ZWgmbmjvPx3PHDEMla0o4kZP9ATcHpUzLWB_vXMcz-g

    This page should actually contain my calendar application however, I am getting this error :

    ORA-29273: HTTP request failed ORA-06512: at “SYS.UTL_HTTP”, line 1369 ORA-29268: HTTP client error 403 – Attempt to upgrade a non-upgradeable token

    Can you please help.

    Like

    Reply
  5. Prateek

    Hi John

    was able to fix this after a little bit of observation. The problem was after the session was set in gcal_authenticate procedure, I was still passing session=0 as parameter whereas this should ideally be session=1.

    Cheers!!

    Like

    Reply
  6. Prateek

    Hi,

    Everything is working fine now for me except getting the calendar feeds. Displaying public feeds is working perfectly fine as per your part-1 tutorial, but I am not able to display private feeds even after being authenticated successfully from google.

    For your reference I am including my pls/sql procedure as below:

    Token is the value of session token as per your tutorial above:P1_SESSION_TOKEN

    procedure gcal_get_events(token in varchar2) is
    l_request utl_http.req;
    l_response utl_http.resp;
    value varchar2(1024);
    v_auth_url varchar2(100) := ‘http://www.google.com/calendar/feeds/default/private/full’;
    v_auth_text varchar2(200) := ‘AuthSub token=”‘ || token || ‘”‘;
    begin
    utl_http.set_response_error_check(enable=>true);
    l_request := utl_http.begin_request(url=>v_auth_url, method=>’GET’);
    utl_http.set_header(r=>l_request, name=>’Content-Type’, value=>’application/x-www-form-urlencoded’);
    utl_http.set_header(r=>l_request, name=> ‘Authorization’, value=>v_auth_text);
    l_response := utl_http.get_response(r=>l_request);

    dbms_output.put_line(l_response.status_code);
    dbms_output.put_line(‘Reason: ‘ || l_response.reason_phrase);
    LOOP
    utl_http.read_line(r=>l_response, data=>value, remove_crlf => TRUE);
    dbms_output.put_line(value);
    END LOOP;
    utl_http.end_response(l_response);

    EXCEPTION
    WHEN utl_http.end_of_body THEN
    utl_http.end_response(l_response);
    end gcal_get_events;

    The error I am getting is :

    ORA-29273: HTTP request failed ORA-06512: at “SYS.UTL_HTTP”, line 1493 ORA-06502: PL/SQL: numeric or value error.

    Please help!!

    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