Few issues regarding accessing remote SQL Server from Android

Question

I have an application in hand where we need to use a tab for data entry. The tab loads initial data from the remote server. Subsequently remote server needs to be updated, inserted (for new data) as the user inserts/updates data on the tab. Out database server is SQL Server 2008.
As suggested by the many experts at stackoverflow we are going to use Webservices at the server to facilitate data interchange. However I am still not sure of the following points -
1. whats the best mechanism for authentication in such case.
2. should i take a chunk of data from sqlite table at android, convert to JSON and pass it on to the Webservices for insert/update operation or take single row and update. Though I think sending single row would not be efficient.
3.How I manage failure to upgrade remote server. This is easier in case I use single row. My plan is to set status flag for sqlite records to 1 (default is 0) for records being updated/inserted to remote server. If update/insert fails I change the status flag back to 0 so that i can use them again next time. In case of success change the flag to 2.

thanks in advance

UPDATE
Doen some study and tried to use SampleSyncAdapter. Still some confusion about the whole operation. My Sqlite database is created by a program and content provider class exists in that application. Package name for the application is com.xylo.pds. I am trying to write a sync application which attempts to sync the data used in the first application. If I follow the SampleSyncAdapter sample - I need to develop server side application for authentication and then uploading android data to the server(in my case one way is sufficient). I can do that with the help of server side code given with the sample. So I just copied codes of the sample code for my Authentication and Sync. My authenticator.xml has the existing entries- android:contentAuthority="com.android.contacts" android:accountType="com.example.android.samplesync

So now my application can add account and sync the contact. And no wonder it works with dummy server id given with the sample.
Now I need to put my own code in the application so that I can load my local database to the server. In order to that I need to add codes at onPerformSync of SyncAdapter. In order to use existing ContentProvider I have the following entries in the manifest file <uses-permission android:name="com.xylo.pds.RCDataProvider" />. The application which defines the ContentProvider has the following entries -
<provider android:name=".RCDataProvider" android:authorities="com.xylo.pds.provider" android:exported="true" android:readPermission="android.permission.permRead" />
Now if I have added a call to the contentresolver inside SyncAdapter keeping every thing else same just to check things are ok. So that, it is ok, I can change onPerformSync to add codes for uploading data. However now the application stops sysnc the contacts. What I am missing Please enlighten me. Thanks

Answer 1

1. a) You could ask the users to sign in / sign up from the mobile apps, exactly as you would do it for a web site. b) Take a look at this http://android-developers.blogspot.ro/2013/01/verifying-back-end-calls-from-android.html

2. Send more records in the same request, the idea is to make as few requests as possible.

3. I don't think you should keep the flag for error the same as the default value, it should be a different value so that you will be able to handle it more easily.

Answer 2

1) whats the best mechanism for authentication in such case.

You could/should use OAuth2. either implement your own token on web service website or use common OAuth2 web services in conjunction with the Android Account Manager.

The reason for suggesting this approach is really down to the suggested/recommended way of handling user authentication as per the Google docs. See "Remembering your user" here http://developer.android.com/training/id-auth/identify.html

Which leads nicely on to your next questions

2) should i take a chunk of data from sqlite table at android, convert to JSON and pass it on to the Webservices for insert/update operation or take single row and update. Though I think sending single row would not be efficient.

You should use the android sync adapter which will make use of the account manager functionality described in the link I gave you in answer to question 1

You can code your android service in whatever way you wish but you should be using JSON rather than XML in both directions.

The really neat thing about using the account manager with a sync adapter is that your SQLite content provider methods can use the notifyChange method to tell the sync adapter to update the web service.

You can tell the sync adapter to get the latest data from your web service at the same time or you can schedule syncs.

3) How I manage failure to upgrade remote server. This is easier in case I use single row. My plan is to set status flag for sqlite records to 1 (default is 0) for records being updated/inserted to remote server. If update/insert fails I change the status flag back to 0 so that i can use them again next time. In case of success change the flag to 2.

This is explained by Virgil in the Google I/O video embeded into into this sync adapter tutorial https://sites.google.com/site/andsamples/concept-of-syncadapter-androidcontentabstractthreadedsyncadapter

An alternative solution to using a sync adapter to get the data FROM your web service would be to use GCM (Google Cloud Messaging service. http://developer.android.com/google/gcm/gs.html

Basically Account Manager/sync adapter/gcm is the functionality that Android recommend you use and these are the services that Google uses itself for things like GMail and give your users the ability to keep their data intact even after clearing their data or uninstalling the app and re-installing the app and it also allows for a user to be able to install the app on a new phone and keeping their data.

Hope that helps

UPDATE in response to comments

You should always try hard to minimise traffic and size of data being sent in either direction. I would send the whole lot in one single JSON request gzipped. Your web server should be able to automatically handle gzipped requests and if gzip is not installed on your server it's simple enough to add

A reference to creating a gzipped output stream from your app can be found here http://developer.android.com/reference/java/util/zip/GZIPOutputStream.html

For the web server the solution you need will depend on the server you use and you should check with your host about gzip but here are a couple of links for the most popular web servers

Apache - http://howtounix.info/howto/Apache-gzip-compression-with-mod_deflate NGinx Tutorial - http://www.howtoforge.com/how-to-save-traffic-with-nginxs-httpgzipmodule-debian-squeeze

GZip is the most popular solution for web servers and is very simple to implement.

I don't really have enough info to provide much more advice other than to say that I normally use Ruby on Rails for stuff like this and gzip is handled very simply with the ative support gzip library using something similar to this contacts = ActiveSupport::JSON.decode(gzipped_contacts.gsub("+", ""))

UPDATE 2 notifyChange()

Just to pick up on your point about notifyChange not being appropriate in the content provider due to there being no internet access.

It is fine to use notifyChange() in your content provider as it will tell the sync adapter to update as soon as it is appropriate to do so which means when the device is not too busy and as soon as an internet connection becomes available. That's the whole point of the sync adapter. Should you need to make use of notifyChange in your content provider for other services such as array adapters but you do not want the sync adapter to be told to update then there is a little documented boolean parameter that you can add to the end of the notifyChange params list. set it to false and the content provider will ignore the notifyChange

e.g. instead of the usual getContext().getContentResolver().notifyChange(uri, null);You can use getContext().getContentResolver().notifyChange(uri, null, false);