How to Synchronize Android Database with an online SQL Server?

Question

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.

Waistcoat

Write a Webservice and get updated data from the service and update your sqllite database ..vice versa – Gravelly

I've used PHP to write a webservice in the past. Depending on your implementation you may just want to bring down new events and put them into your sqlite db or add/delete/modify. The best/only way though is to write a webservice as middleware to communicate between your application and backend! – Clementinaclementine

are you syncing between sqllite and a mysql server? – Minnie

@user1042031 if in between internet connection is not available then how to maintain, and also if i have 10000 record then? – Af

Answer 1

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.

Interfertile

A link to a sample. – Dioecious

are there not any decent libraries that would just do this for us already? – Johann

@Johann Exactly my question! Aren't there SQL driver libraries where the data sync feature is a configuration switch away? Like we have in the noSQL world, for example in FireStore! :D – Marotta