We have an APEX application that provides calendar functionality but have recently begun looking into migrating to Google Calendar. As a result I was tasked with investigating what functionality is exposed via the Google Calendar API’s and how easy it would be to access through PL/SQL.
In the first installment of this blog I want to cover Google Calendar authentication and creating an individual calendar event.
Checking the Google documentation I could see that my first goal would be to get the authentication working and with this done I could then move on to look at maintaining calendar events.
I already had a calendar account and the easiest thing to do seemed to be to use the ClientLogin service for authentication. This involves sending a POST request to the following URL with the password and email credentials in the body of the request:
https://www.google.com/accounts/ClientLogin
The basic framework for the authentication code is shown below. Probably the only point worth mentioning is the use of the Oracle wallet to allow utl_http to communicate over HTTPS.
create or replace procedure authenticate_service(
p_email in varchar2,
p_password in varchar2)
is
l_request utl_http.req;
l_response utl_http.resp;
l_params varchar2(255);
begin
-- access the oracle wallet to allow us to make an https request
utl_http.set_wallet(
path => 'file:/home/oracle/wallet',
password => 'xxxxxxxx');
-- set up the request body with our credentials
l_params := 'Email=' || p_email || '&Passwd=' || p_password ||
'&service=cl' || '&source=e-DBA-test-1.0';
l_request := utl_http.begin_request(
'https://www.google.com/accounts/ClientLogin',
'POST',
'HTTP/1.1');
-- set the request headers
utl_http.set_header(
l_request,
'Content-Type',
'application/x-www-form-urlencoded');
utl_http.set_header(
l_request,
'Content-Length',
length(l_params));
-- write out the request body
utl_http.write_text( l_request, l_params );
-- get the response
l_response := utl_http.get_response( r => l_request );
dbms_output.put_line('Status Code: '||l_response.status_code);
utl_http.end_response ( l_response );
end authenticate_service;
I ran the code using my credentials and received an HTTP 200 Response code to indicate the authentication was successful. The next step is to parse out the authentication token from the response since I am going to need this before I can do anything useful with my calendar.
As a starter I used the following piece of code to output the response text so I could see what was being returned:
begin
loop
utl_http.read_line( r => l_response, data => l_resp_data);
dbms_output.put_line(l_resp_data);
end loop;
exception
when utl_http.end_of_body then
null;
end;
After adding this to my procedure and running it I got the following response back from Google:
Status Code: 200
SID=DQAAAHAAAACCTMRxTAddMWSqVlp2DMZJoURi
7AbW9aGHnKvH_A2j__j8gBFjPQGqlqQKtVmxc-aC
IP_EqWJkWwznqBlTUMxJ8AyRTl9W7Re9Ozi2yJpY
LNMPPoK1M6fAuLjtUhbftb6q89XC1mGG4KpLxc00
_LSKLSID=DQAAAHMAAAAwj9uCd9RGKaCag7E41CB6aK1
N-ihqohUDw2s06J7HHZztZAia5WHTzeiSRnrOguT
wqsgJlAw7cPQ9BfFg3TsnrEwkXLsEpOsyAhx9KWM
nIqv_zmyD_FeaHG8Yq_tuDthjVWwFyOvHKhuRddu
m9xx9H-xDRwnnjLiuBQKXyx9JoQAuth=DQAAAHIAAAAwj9uCd9RGKaCag7E41CB6aK1
N-ihqohUDw2s06J7HHZztZAia5WHTzeiSRnrOguQ
G_l5IqYxk-8RMUVpYuTKskqSerxAUJ6S9xZ2kgRo
Da55ftgAsgl8IE3EMCN5rsQG2gSpxNrLuAdTSkYU
LVJF_DXwzI3aV2xfd-qz4W5Km4w
Hooray! I have managed to authenticate myself.
So far, so good, now on to extracting the token and creating calendar events.
To get the token it looks like the simplest thing to do is to loop through the response lines, looking for the line that begins “Auth=” and return the string of characters that follow:
begin
loop
utl_http.read_line( r => l_response, data => l_resp_data);
dbms_output.put_line(l_resp_data);
if substr(l_resp_data, 1, 5) = 'Auth=' then
l_auth_token := substr(l_resp_data, 6);
end if;
end loop;
exception
when utl_http.end_of_body then
null;
end;
Now I have retrieved the token I can attempt to add a new entry to my calendar. So back to the Google docs to find out what this involves. It looks like I need to send a POST request to the default URL http://www.google.com/calendar/feeds/default/private/full which then gets re-directed to the read/write private calendar feed for my authenticated user.
To keep things simple I decided to use the example XML entry from the Developer’s Guide as my template and use substitution variables to update template with my calendar values. My first attempt at the Create Event code is shown below.
create or replace procedure create_event(
p_auth_token in varchar2,
p_title in varchar2,
p_content in varchar2,
p_location in varchar2,
p_start_time in date,
p_end_time in date)
is
-- template for a single occurrence calendar entry
l_entry_template varchar2(4000) :=
q'[<entry xmlns='http://www.w3.org/2005/Atom']' ||
q'[ xmlns:gd='http://schemas.google.com/g/2005'>]' ||
q'[ <category scheme='http://schemas.google.com/g/2005#kind']' ||
q'[ term='http://schemas.google.com/g/2005#event'></category>]' ||
q'[ <title type='text'>#TITLE#</title>]' ||
q'[ <content type='text'>#CONTENT#</content>]' ||
q'[ <gd:transparency]' ||
q'[ value='http://schemas.google.com/g/2005#event.opaque'>]' ||
q'[ </gd:transparency>]' ||
q'[ <gd:eventStatus]' ||
q'[ value='http://schemas.google.com/g/2005#event.confirmed'>]' ||
q'[ </gd:eventStatus>]' ||
q'[ <gd:where valueString='#LOCATION#'></gd:where>]' ||
q'[ <gd:when startTime='#STARTTIME#' endTime='#ENDTIME#'></gd:when>]' ||
q'[</entry>]';
l_cal_entry varchar2(4000);
l_request utl_http.req;
l_response utl_http.resp;
begin
-- replace the substitution variables in the template with the parameter values
l_cal_entry := l_entry_template;
l_cal_entry := replace(l_cal_entry, '#TITLE#', p_title );
l_cal_entry := replace(l_cal_entry, '#CONTENT#', p_content );
l_cal_entry := replace(l_cal_entry, '#LOCATION#', p_location );
l_cal_entry := replace(l_cal_entry, '#STARTTIME#', to_char(p_start_time, 'YYYY-MM-DD')||'T'||TO_CHAR(p_start_time,'HH24:MI:SS') );
l_cal_entry := replace(l_cal_entry, '#ENDTIME#', to_char(p_end_time, 'YYYY-MM-DD')||'T'||TO_CHAR(p_end_time,'HH24:MI:SS') );
l_request := utl_http.begin_request(
'http://www.google.com/calendar/feeds/default/private/full',
'POST',
'HTTP/1.1');
utl_http.set_header(
l_request,
'Content-Type',
'application/atom+xml');
utl_http.set_header(
l_request,
'Content-Length',
length(l_cal_entry));
utl_http.set_header(
l_request,
'Authorization',
'GoogleLogin auth='||p_auth_token);
utl_http.write_text(
l_request,
l_cal_entry);
l_response := utl_http.get_response( r => l_request );
dbms_output.put_line('Status Code: ' || l_response.status_code);
utl_http.end_response ( l_response );
end create_event;
Running this code using the following PL/SQL block ends up giving me a 302 Response code which, as mentioned in the documentation, indicates Google is redirecting me to a different URL. I am not sure whether this always occurs but every time I have run the code I have received this response.
You night also notice that I have changed my authenticate procedure into a function which returns the authentication token from Google.
declare
l_auth_token varchar2(255);
begin
l_auth_token := authenticate_service('vincent.migue@e-dba.com','xxxxxxxx');
create_event(
p_auth_token => l_auth_token,
p_title => 'Google Calendar Blog Entry',
p_content => 'How to access Google Calendar thru PL/SQL',
p_location => 'Brighton Office',
p_start_time => SYSDATE,
p_end_time => SYSDATE + (1/24));
end;
In order to handle this I need to parse out the redirect URL from the response headers and then resend the same request to the new URL. I used the following code to pull the redirect URL out of the response headers.
-- check for a redirect response
if l_response.status_code = utl_http.http_found then
for i in 1..utl_http.get_header_count ( r => l_response )
loop
utl_http.get_header ( l_response, i, l_name, l_value );
-- pull out the redirect url from the response headers
if l_name = 'Location' then
l_redirect_url := l_value;
end if;
end loop;
end if;
I added this code to my create_event procedure, together with the additional code to resend the request to the new URL, fired up SQL*Plus and ran the code fully expecting a new entry to magically appear on my Google calendar. What did I actually get? The following error:
Status Code: 400
Content is not allowed in prolog.
Well it didn’t mean anything to mean and in situations like this I tend to believe Google is my friend. A quick search turned up a number of hits all pointing to a XML parsing problem. No problem, I thought, I’ve obviously got the XML code for the event wrong. Should be a simple fix!
I certainly wasn’t thinking it was a simple fix a few hours later. Try as I might I just couldn’t see a problem with the code.
It was at this point that I stumbled across a post on the Google Apps APIs group which described a similar problem to mine. Although there wasn’t a solution, I sent off an email to the lady who had posted the problem in the hope she had got to the bottom of the problem.
Later the same day I got back a response from Stephen Leary at Leeds Metropolitan University who had fixed the problem and had a solution. It turns out that when parsing the token from the authentication response, I’ve got an unwanted Carriage Return & Linefeed characters on the end. All I need to do is strip this off and hey presto, we’re back in business!
Since there is an additional parameter to the utl_http.readline function to accomplish this, the fix is simply to change the call in my authenticate_service function to the following:
utl_http.read_line(
r => l_response,
data => l_resp_data,
remove_crlf => TRUE);
I made the change, ran the code again and with a small sigh of relief got back a 201 status code to indicate the event was created. Just to confirm, I logged into Google using Firefox and there was my Calendar Event waiting for me.
So many thanks to Stephen, who was a life-saver. I’m not sure how long it would have taken me to get to the solution, but he probably saved me many hours of hair pulling.
In the next installment I will cover updating and deleting calendar entries.
