Saturday, February 28, 2009

Sync Oracle Calendar to Google Calendar + iCal + iPhone

I've been searching for a reliable method to automate the synchronization of events from Oracle Calendar (formerly CorporateTime) to my Google Calendar, iCal on my Mac, and internal iPhone calendar on my iPhone.

Slide1.png

Recently I learned of a promising iPhone app available at iTunes called Todo+Cal+Sync that could do most of what I was looking for with synchronizing calendars. However, I didn't want to fork over $14.99 for an application that, instead of importing Oracle Calendar events into the native iPhone calendar, added an additional calendar application on my iPhone. Synthesis AG, the developer of the Todo+Cal+Sync application, is required to do this because of limitations imposed by Apple's iPhone software development kit (SDK). In other words, Apple does not allow 3rd part applications, such as Todo+Cal+Sync, to access the internal iPhone calendar, nor sync with iCal. This is a risk/benefit that Apple needs to manage; is the benefit of restricting access to the internal iPhone calendar worth the impact it has on the development of 3rd party applications and subsequent ripple effect? Until Apple's iPhone SDK allow such access, I did not want two calendar applications and continued looking for something that would better match my needs.

After digging around and tinkering with different solutions, I worked out a method that did exactly what I wanted. To make this solution even better, it cost $0 - in other words, FREE!

Below are the steps that I came up with to make the calendar sync work for me. Steps 1-3 are also useful for those who do not necessarily have an iPhone or iTouch but want to sync their Oracle Calendar with other devices and/or calendar apps that support Google Calendar's CalDAV sync.

  1. Begin by changing your password for your Oracle Calendar user account. Make it a unique password that you are not using anywhere else. In other words, your new Oracle Calendar password should not be the same password as you're using for other email accounts, online banking, eBay, PayPal, etc. This new password should also comply to any password policies that may exist for users of the Oracle Calendar system.
  2. Create a "magic" URL using SyncML2iCal.com. This URL will be used in step #3. You will want your magic URL to look something like the following:

Example - Oracle Calendar supporting https on port 443

http://sync.syncml2ical.com/?serverurl=https://YOUR.ORACLE.CALENDAR.COM:443/ocas-bin/ocas.fcgi?sub=syncml&user=USERNAME&pass=PASSWORD&eventsdb=./Calendar/Events?/dr(-7,30)

SECURITY WARNING - There is an increased security risk with this method. It's up to you to determine if this is a risk you are willing to accept and that it doesn't violate any policies or restrictions imposed by the organization running the Oracle Calendar service that you are using. The risks include:

  • Unauthorized interception of your password from the URL as it's being transmitted to SyncML2iCal.com or from SyncML2iCal.com.
  • SyncML2iCal.com itself becoming compromised and allowing an attacker to intercept your password.

In my opinion, the likelihood of the above risks happening are medium to low. You can keep this risk on the lower end by never connecting to untrusted networks or using insecure wireless, which includes wireless networks that use WEP encryption.

Additionally, you will need to determine if the impact of an unauthorized user obtaining your Oracle Calendar password would have a significant impact or not. In most instances, I would imagine the impact would be low.

This is why doing step #1 above is critical in helping minimize the impact if your password was compromised.

Anyone using an application that syncs using the SyncML functionality of Oracle Calendar should take the same precautions irregardless if he or she are using SyncML2iCal.com as a proxy to convert SynchML to iCal format.

  1. Go to Google Calendar and add a new calendar by selecting Add by URL . You will use the URL you created from step #2. You may also want to change the display name and color of this new calendar on Google Calendar.

    AddCal.png
    Do note that Google has stated that external feeds added via the "Add by URL" method should be refreshed every 24 hours.

  1. Download and run Calaboration from Google Code. This will allow you to add your Oracle calendar to your Mac's iCal application. Before you can add the new calendar, click on preferences within Calaboration and enable allowing read only calendars to be added. Make sure your new calendar is selected and let Calaboration do the setup work for you. Your Oracle calendar will then sync with iCal.
Calaboration.png

  1. Use iTunes to sync Oracle calendar from iCal to your iPhone.
iTunes-Calendar.png

One minor annoying issue I came across was with how day events and day notes from Oracle Calendar were handled by the time they showed up in iCal. Day events and notes from Oracle Calendar showed up in iCal as being a blocked all-day event from 0000-2359. As a quick temporary solution I simply denied day events and notes within Oracle Calendar and re-synced. This temporary approach was acceptable for me since I use Google Calendar to manage my daily notes and I can look at a user's Oracle calendar if I need to know if he or she is on vacation, on-call, etc.

As for effectively managing tasks using your iPhone, see my previous article titled, Tools To Get Things Done.

Steve

###

46 comments:

sitta said...

Hi-I'm not quite sure what I'm supposed to enter on the SyncML website to get a magic URL--when I enter my details into the "Oracle-style" fields on the SyncML page that you linked to, I get a .ics file that I can import into iCal, but no URL. What am I missing?

sitta said...

Ah-never mind...I watched as syncML was processing the request and saw the new URL flash up in the address bar (I'm using Safari 4). I copied the link before it flashed back to the "TryIt" link as it was downloading the .ics file to the desktop. With this link it appears to be working fine, and it was in the style of your sample URL before step 2. Thanks for this great workaround!

sitta said...

OK-one last question (assuming I don't stumble into an answer before you can respond): it appears that it syncs the two weeks prior to the current date, and one week into the future. I tried adding a suffix to the URL like on your sample URL (-7, 30) but it had no effect (I've included the two URLs with my username/password removed so you can see what I entered):
http://sync.syncml2ical.com/?serverurl=https%3A%2F%2FMYSERVER%3A443%2Focas-bin%2Focas.fcgi%3Fsub%3Dsyncml&user=MYUSERNAME&pass=MYPASSWORD&eventsdb=.%2FCalendar%2FEvents?/

Steve Zenone said...

Sitta - The Oracle Calender server that you're using may be configured to limit the limit the range of events that you are able to download.

To help troubleshoot you can try several manual tests by entering variations of the URL within your browser and saving the ics files. Change the time values from "dr(-7,30)" to "dr(0,30)" and look at the contents of the ics file (notepad, vi, TextEdit, ...). Were you able to download data beyond one week in the future? If not, shorten the second variable to 14 days, "dr(0,14)". Any luck with this?

Oracle has more information on this at: http://spedr.com/ipfu

sitta said...

Hi Steve-
Thanks for the help. AFter consulting the Oracle website I realized that I was missing a ? in the address and now it seems to work fine. However, it doesn't seem to be an option for me to sync it to my iPhone--all of my google subscribed calendars don't show up in the list of potential ical calendars to sync...any other helpful hints? BTW, even without a full iphone sync this is *way* better than booting up ORacle just to check a meeting time--iCal is so much more nimble than that behemoth...

Steve Zenone said...

Sitta - After you setup Google Calendar to sync your Oracle calender through SyncML2ical.com, have you done step #3 using Calaboration to get iCal on your workstation/laptop to sync the new Google calendar?

sitta said...

Hey Steve-
After monkeying around a bit I figured out what was happening-I used Calaboration and it worked like money! The problem was that I had my three computers & iPhone set up to sync via MobileMe, and MM doesn't seem to be interested in syncing calendars that you're subscribed to in iCal (which doesn't seem right). As soon as I disabled MM on the iPhone, I could see the dialogue box in iTunes that allowed me to choose the calendars I was subscribed to in iCal to be synced to the iPhone. Working like a charm now! Thanks for the great post. Been waiting for this confluence for about 2 years : )

Kavan said...

Hi Steve -- Great post! I have one question -- I use MobileMe to sync iCal with my Iphone, and it doesn't seem to want to recognize the Google calendars. Any advice? Thanks
Kbilly

Steve Zenone said...

Kavan - I’m glad the post was useful. I don’t use MobileMe, so I might be of much help here. However, as I understand it, there is an inherent limitation with syncing iCal subscriptions with MobileMe. Only local calendars listed under the Calendars header within iCal will sync with MobileMe. Any additional subscribed calendars within iCal will not sync with MobileMe.

Though I don’t use MobileMe, nor do I use the following work around, the tool BusySync (http://www.busymac.com/) will make additional calendar subscriptions appear as local calendars within iCal, not as subscriptions. Therefore, MobileMe will allow the syncing of additional calendars that you’ve added. Here’s a shortened URL for a posting on the BusyMac Blog that has some additional information-- http://spedr.com/10gta.

Kavan said...

Awesome -- that worked perfectly. Thanks again Steve, I can't emphasize enough how frustrating it's been digging through the multitude of patchwork sync programs. Finally I think I'm good to go!

Unknown said...

It worked perfect, EXCEPT for one major issue. For some reason when I sync oracle with google, all my times are off by 4 hours. I looked at the setting in the google calendar and for my oracle calendar it has Greenwich Mean time, but I can't figure out how to change it.

Steve Zenone said...

Michael - open up your Oracle Calendar client, go to PREFERENCES | GENERAL and click on the Time Zones tab. Change the time zone there to match the timezone of your GCal.

I haven't experimented with this to see if it changes the times of events imported into GCal. Let me know if this solves the problem.

Unknown said...

Steve,

I change my settings on iCal in my iPhone to Greenwich Mean time and now my phone is still on Eastern Time and my Calendar has the right times.

But now, my google calendar won't refresh the updates from my Oracle. How do you force a sync from Oracle to Google. The refresh button doesn't work.

Steve Zenone said...

Google has stated that external feeds added via the "Add by URL" method should be refreshed every 24 hours. http://spedr.com/5b2zr

That said - while you are setting this up and troubleshooting, the quickest method (and not the cleanest) to force a refresh is to unsubscribe from the calendar within GCal, and then re-add the calendar by URL.

Max Spiegel said...

Oh so close. I also have the 4 hour difference problem, but do not have control of my Oracle timezone settings beyond a single session. Nor did it work within that session. Are there any other variables associated with syncml2ical? I could imagine a timezone variable to make this correction from GMT. Also, using all the same settings in Outlook syncs it perfectly on the right times.

gene said...

me zone problem also. Think is, when I download it from syncml2ical it is correct, its only when I add to gcal that it is a problem. G cal is set to gmt and wont allow a change.

Rachel said...

Hi Steve,
I'm an avid Google Apps user/iPhone user, but use at PC at home and at work...and my office uses Oracle.
When I try to make the sync URL, I can't open the file on my PC or see a URL. Do I need to be using Safari? Will this patch not work for me?
Thanks for your help and for putting time and effort into giving so many of us a helping hand!

Steve Zenone said...

Rachel - The file that you'll download from the syncml2ical site will be in .ics format. You can open it up in notepad in Windows to make sure there's content in there.

You don't need Safari. After you create the "magic" syncml2ical URL, copy the URL, go to Google Calendar, add a calendar by URL and paste the magic URL. I'm assuming you're using Google Calendar. If not, you'll just need to copy the syncml2ical created URL to the specific app you're using on your PC.

Steve Zenone said...

Max / Eugene - any luck with the timezone issue? If so, what was needed to resolve it?

Max Spiegel said...

Our IT dept gave up on SyncML completely and turned off the oracle server link. It created too many different scenarios for our 14,000 employees trying to sync to different phones and calendars. Not to mention it transmits your password without encryption in the URL itself.

Steve Zenone said...

Max - I agree with the increased risk by one's password being sent in the URL. Thus step #1 above and the warning:

-----BEGIN-----
SECURITY WARNING - There is an increased security risk with this method. It's up to you to determine if this is a risk you are willing to accept and that it doesn't violate any policies or restrictions imposed by the organization running the Oracle Calendar service that you are using. The risks include:

* Unauthorized interception of your password from the URL as it's being transmitted to SyncML2iCal.com or from SyncML2iCal.com.
* SyncML2iCal.com itself becoming compromised and allowing an attacker to intercept your password.
-----END-----

Only if Oracle published private calendars in iCal...

Chris Rimple said...

Steve, thanks for posting this. Now that Google offers a calendar sync solution for iPhone, you could simplify by stopping after your Step #3 and then installing Google Sync (see http://www.google.com/support/mobile/bin/answer.py?hl=en&answer=138740). No need for iTunes anymore.

Steve Zenone said...

Chris - I tried Google Sync for iPhone a couple of months back. I quickly stopped using it because of the apparent limitation to support the synchronization of multiple calendars. I have multiple calendars for GCal that I depend on; personal, work, travel, etc.

Doing a quick Google search this afternoon it looks as if there *is* a way to sync multiple calendars: http://www.knowliz.com/2009/02/how-to-sync-multiple-calendars-with.html

I tested it. It worked.

THANK YOU for the nudge to look into this again!

Shannon said...

Like Sitta I only get the .ics file but I cant seem to 'catch' the URL before it flashes back to "TryIt" link. I've tried opening .ics file in TextEdit but there isn't a URL in the file... the .ics file automatically adds itself into my ical calendar but I would rather it in my google calendar. Any ideas?

Eugene said...

Still not fix on the time zone issue for me. IF anyone knows one, let me know.

John said...

Hey folks,
surfing the internet I found another quick, solid and above all cheap solution. It's an app, called remote calendar. What made me finally choose for this app was that it can sync a variety of calendars using calDAV with my iphone. Me and my girlfirend are using ical, while in office we are using outlook. With remote calendar I have it all in sync. You should look out for it in the app store. I tried the above-mentioned Busy Sync and Google-Sync. It was confusing having to combine them. The app I found is much better, 'cause it i an all-in-one-solution. Check it out. www.remote-calendar.com

Steve Zenone said...

John - thanks for your comment. Remote Calendar looks like an interesting app, but has the same issues that I wanted to avoid with the App Todo+Cal+Sync.

The objectives of what I wanted to achieve were:

+ Import Oracle Calendar entries into Google Calendar [Tutorial: this blog post]
+ Sync multiple Google Calendars, including the imported Oracle Calendar, with *native* iPhone calendar [Tutorial: http://spedr.com/2hb2t]
+ Achieve the above at a cost of $FREE$

Fortunately, I was able to do all three of the above.

Unknown said...

I was excited to see this a solution to get my company's Oracle Calendar on my iPhone. However, I too am plagued with the time zone issue. Has there been any resolution on that? This solution is perfect except for that one annoying bug!

bmurray said...

Anyone resolve the time zone issue or find a variable to pass in the url?

Anonymous said...

No Time Zone fix??

Anonymous said...

This script - http://ical.dudeami.com/ - lets you work around the timezone issue.

It offsets the ical feed by X number of hours.

Bruce Kendall said...

I'm having difficulty with step 2. When I run the magic url and look at the resulting ics file, it contains the message "Error: Server is not speaking SyncML. Check the server url
" I've tried various permutations of the server url. Should I be using the url that appears at the login screen, or the one that appears when I am viewing my calendar? Or does this mean that the calendar administrator has somehow disabled this functionality?

Mike said...

So, I'm having mixed success. I add the calendar by URL and *some* but not all of my appoitments show up in the Google Calendar. I can't figure out why some are showing up and others are not. There is nothing different about those showing up versus those not showing up. Anyone have a similar problem?

Kevin Costa said...

Still still no URL variable we could pass that would adjust by X hours??

Steve said...

Am i wrong but does this do a one way sync from oracle to google and a two way from google to ical? google to ical is very easy if you use ical 4. you can just add a calDav account for google in ical and boom instant two way sync.. i cannot get a two way sync with orical

RasmusScholer said...

Thanks to whoever made ical.dudeami.com - the comment was right and it even eliminates the issue about always sending username and pass in clear text - it seems like the website stores the credentials and you just need to pass an url like the following to google calendar: webcal://ical.dudeami.com/cal/get/uc7jxllkxjdsf52d9/-1

(minus one being the offset)

It works like a charm.

Don said...

The dudeami site is great, but be warned - since it just changes the times, not the timezone, when the time changes it'll be off. You'll need to generate a new url after DST kicks in.

Dr Susan said...

Hello I'm getting lost in the world of syncing and working my way through your workarounds.
I have successfully managed to sync multiple calendars between gCal and iCal on my iphone, but I am also trying to put oracle in the mix. I gave up on trying to sync directly between oracle and iCal on my iPhone - but I need to have Oracle at work. Is a two way sync possible between oracle and gCal? I have tried the add URL calendar thing and it didn't show up any of my iCal calendars on oracle or vice versa. Am I missing a step? Many thanks indeed, Susan

Dr Susan said...

sorry I shoudl also have said that the way I am (successfully) syncing between iCal on my iPhone and google calendars is using google sync/microsoft exchange which works a treat - its just having to also two way sync with oracle that is causing the grief!

Waz said...

Mind you that if you have "funny chars" in your password you need to transform them.

Like # becomes %23

(I feel like such a nerd now :), but I got it working !)

Frances said...

This is a great article, and a great topic to explore. Thanks for sharing.

angrypanda said...

Is it possible that our Oracle calendar does not support SyncML? Or is it built in? When I follow the instructions to put in details on the SyncML2iCal website I get an ics file produced with the following in it:

Error: Server is not speaking SyncML. Check the server url

Not sure what I'm doing wrong.....

Cheers, Jon

angrypanda said...
This comment has been removed by the author.
angrypanda said...

Okay - so I managed to get this to work - our IT weren't using the standard paths in the URL line, which once I'd got the information and put in the right password (!) got me up and running.

One thing though, I think it's a bit misleading to call this a method for Syncing - as it's only one-way.

mspy review said...

The exporting and importing suggestion is really the only easy way to do it right now. If Oracle Calendar begins supporting caldav in a future version, you should be able to just subscribe to your corporate calendar caldav URL directly from Google Calendar.

Anonymous said...

Syncevolution, a Linux package appears to be able to act as a bridge between syncml and Caldav somitnshould be able to utomate the syncing, although I've not had a chance to look at it yet.