How to Synchronize Android Database with an online SQL Server?

android sql sqlite synchronization
Wassim Taher picture Wassim Taher · Jul 2, 2012 · Viewed 38.2k times · Source

I am developing an Android App that stores different types of data in the built-in SQLite provided by the Android Platform.

Inside the App I have placed a "Sync" button which is supposed to Sync the data between the local SQLite Database, with an Online SQL Server database on my server.

What is the workaround to handle this? This feature can be found in Google Calendar, where you can view the calendar's events on your mobile, and when you add a new event and Sync the data, you can view the updated data by going to your online account too.

Note: I don't want to centralize my database online, because I also want to give the mobile users the ability to use the App without internet connection.

Answer

tiguchi picture tiguchi · Jul 2, 2012

You should take a look at the SampleSyncAdapter project in the Android SDK demos (Transferring Data Using Sync Adapters). It shows you how to do synchronization "Android style", which requires no user interaction by manually tapping a sync button all the time.

On top of that you need to write server software that is able to provide your SyncAdapter with a "delta" of all changes since the last commit. The most basic approach is keeping a "last synchronized" time stamp in your app which has to come from the server, otherwise you could get into trouble because of time differences between client and server. Normalize all time stamps as GMT or any timezone of your choice, but stick with that decision. If your app needs to display a time stamp in local time, then you need to use the Java Calendar and TimeZone class for converting the normalized time stamp into local time.

Your app also needs to flag changed local records as "dirty", so your SyncAdapter knows that it needs to upload these changed or new records to the server.

Following minimum features are needed for your server software:

  • Update existing record(s) function
  • Add new record(s) function
  • Get updated records since last synchronization (app provides time stamp)
  • Get new records since last synchronization (app provides time stamp)

You also may want to read through some Google API (like Google Calendar) for getting an idea of how all of this works and how to design the server API interface for the communication.

Related questions

SQLite in Android How to update a specific row

I've been trying to update a specific row for a while now, and it seems that there are two ways to do this. From what I've read and tried, you can just use the: execSQL(String sql) method or the: …

Read More
Best way to work with dates in Android SQLite

I'm having some trouble working with dates on my Android application that uses SQLite. I have a couple questions: What type should I use to store dates in SQLite (text, integer, ...)? Given the best way to store dates how do …

Read More
Android SQLite: Update Statement

I need to implement SQLite in my application. I followed this tutorial.. Creating and using databases in Android one Everything is working fine. I inserted 1 row with 5 columns. Now I want to update value of 1 column only and others will …

Read More