local and remote database

[digitalfriction]

Hi,

I am new to android development, so apologies if this is a basic question. I have 2 or 3 C# programs that I have written, which connect to SQL Server databases. I would like to create an android app, which has a local database, and when initially launched, I would like to populate the local database with data from the main SQL database on the server.

My reason for this is so that the android does not have to be online to receive data from the database, it can use the local version, that will have selected information in it, then when the device is online the changes can be sent back up to the server database.

I can see tutorials on reading from a SQL server, and tutorials on creating a local database. However I would like to know if anyone can point me in the right direction to achieve the above 'sync' between 2 databases. Is it possible to use replication?

Any help or guidance is appreciated, sorry for such a :sign0104: question !

 

[Erel]

You will need to implement a replication solution. You can add a 'time' field for each record and store the last updated time.

This will allow you to download (or upload) the more recent fields.

 

[digitalfriction]

Thanks for your reply, do you know if there is an easy way to check if the android device is online? If so, what I could do is every x minutes check if online and then sync the data?

I am also wondering if I could extract the data from the SQL server using asp or asp.net webpage and then create a local database on the fly to store the information? This would then prevent me having to set up each device individually as a replication subscriber?

 

[Erel]

The connection should be done from the Android device. You can periodically check for an internet connection and then sync the data.

See this link:
Connect Android to MS SQL Server Tutorial

 

[keirS]

You will need to implement a replication solution. You can add a 'time' field for each record and store the last updated time.

This will allow you to download (or upload) the more recent fields.

I have seen this answer posted several times to this question and it makes me shudder each time. It works very well if you don't mind duplicate records in your tables but the best way of ensuring that you don't is to use a hash function on the fields in the row on both table to be synced. This generates a unique(ish) value that represents the field values in the row/record. You can the compare the two set's of values to find the new and altered records in each table.


MD5 and other cryptographic hash functions will 99.999% of the time guarantee a unique hash per set of field values but are computationally expensive. For most synchronization tasks using something like the CRC32 function will suffice and give a very low probability of duplicate hash values.
Even Java's built in java.lang.String.hashCode() function may suffice.

 

[citywest]

I have seen this answer posted several times to this question and it makes me shudder each time. It works very well if you don't mind duplicate records in your tables but the best way of ensuring that you don't is to use a hash function on the fields in the row on both table to be synced.

Why?

From what I can see of this answer the DateTimeStamp is to identify the changed records at each end i.e. compare the two and if they exist at both ends the newest one needs to be replicated/sent to the other end. If it doesn't exist at the other end then it needs to be inserted. If they are the same then no action needs to be taken.

This presupposes that you have unique key(s) for each table in the DB which you should any way. It's just good DB schema design. The Db schema design in general should not allow duplicates unless you specifically decide/want to which is the exception rather than the norm IMHO.

 

[keirS]

Why?

From what I can see of this answer the DateTimeStamp is to identify the changed records at each end i.e. compare the two and if they exist at both ends the newest one needs to be replicated/sent to the other end. If it doesn't exist at the other end then it needs to be inserted. If they are the same then no action needs to be taken.

This presupposes that you have unique key(s) for each table in the DB which you should any way. It's just good DB schema design. The Db schema design in general should not allow duplicates unless you specifically decide/want to which is the exception rather than the norm IMHO.

Several problems with it. Firstly you have to synchronize between time zones. The device could be in Asia the the web service in Europe and the main database in the US. Then you have the issue of the device crossing time zones. If the date and time settings are set to automatic on the device then the date and time will sync to the local service provider. So you don't just have to store a date time stamp; you have to store a time zone as well; or have code that syncs the datetime stamp to a fixed timezone before you store it. You also have to account for the changes from and to daylight saving.

 

[citywest]

It's pretty simple you just store and handle dates, times and TZ offsets correctly with whatever you are using around your DB(s) i.e.

2010-12-21T10:36:31.234-0500

Which to me is a 'standard' ISO style date and time stamp. If people are using date and time stamps in a DB which don't include this detail then it is a 'flawed' design IMO. This is certainly what I mean by a date and time stamp.

I don't know of any dev environment, B4A included that can't do this. Sure sometimes you may have to store it as a string and cast from/to date and time stamps but it's a trivial and once off exercise if you build a library.

Amongst my production systems I have a very large, several hundred Gb centralised DB accessed by many thousands of devices from all corners of the world. Portions of this are shared and synced back to devices with each and every device having the ability to edit data.

We don't have any problems with this approach over many years nor with other systems using the same methodology.

Cheers,

Mark S.

 

[aklisiewicz]

Mark - may I ask how you handle unique PrimaryKeys ?
If the device dB and remote dB are editable, normally you will endup often with a duplicate error message.
I've mentioned something about hashing but could you be more specific, on your methodology ?

Arthur

 

[citywest]

Hi Arthur,

Well it depends on the data. Let's say you had a set of tables containing customer orders. Now the order number field is always going to be unique, I mean to say we assume and demand that no organization will reuse internal order numbers.

So setup a unique index on the order number field. For arguments sake let's assume the order can be edited at either end - so add a date and time stamp field in ISO style format.

You can then compare the two and use the latest. To make this more robust:

1) Check for a later version prior to any edits.
2) Add another int field called say status which is reset when a record is edited. This way you can lookup only those records which have the status field set in a particular way. It's more efficient than looking at all records and date time stamps. Just pick the ones with the status field set in a particular way and then do the date time stamp comparison.

But you say what happens when two people edit the same record at the same time? Add another field called say lock expiry and update all copies of the record with an expiry date and time stamp. So you are locking this record when somebody is editing it. Just check for a unexpired lock prior to allowing the edit. Add more code to check for locks prior to saving.

The actual implementation depends very much on the data and real world operations around it.

Cheers,

Mark S.

 

[LucaMs]

There is a "little" problem adding/using a date field like LastUpdate:
deleted rows have no fields!

I fear that, if you want to generalize, ie to use the same strategy regardless of the structure of the database, the only method is to use only a centralized remote db.