How to sync a Remote DB with SQLite?

[Cyberox]

Hi,

We use ORACLE as main database and SAP.

I need to do a little app to collect data offline, and sync (with WIFI or 3G) to an Oracle db with a webservice.

My android application is connected first with a local Sqlite db. So I want to sync this data with oracle db over http. The best way is a webservice, right?

But:

Same data must be in both databases.

If my remote DB (Oracle) is updated... how can i update my sqlite just with the new data?

Maybe i need to have a remote sqlite db (mydata.db) and copy it over http to my android device?



Reason for data sync using a web service directly is the user will enter/update the data using the android app from some place where he wont get the network connectivity.

 

[Erel]

Are you using Basic4android?

 

[warwound]

I had to do something similar recently - keeping a device SQLite database synchronised with an online MySQL database.

Adding a 'last updated' column to tables in both databases worked well.
The device connects to a PHP script (the web service) and tells it which rows the device SQLite database contains and also the 'last updated' value of each row.

The PHP script queries the MySQL database and establishes:

• Which rows the MySQL database contains that the SQLite database does not contain.
• Which rows contained in the SQLite database have been updated - a newer version of that row exists in the MySQL database.
• Which rows in the SQLite database no longer exist in the MySQL database.

From there the script returns a JSON object to the device with three properties, each property is an array of row data.

One property is new rows, one property is updated rows and one property is deleted rows.

The device can now process that JSON and update the SQLite database - adding the new rows, updating existing rows and deleting rows that no longer exist in the MySQL database.

The MySQL database is the 'master' database, all changes are made to this master database - the SQLite database simply mirrors the MySQL database.

Martin.

 

[Cyberox]

Thanks!! great idea!

and yes, i'm using b4a trial

 

[Penko]

Excuse me but how do you use Basic4Android Trial and the SQL library?

 

[aklisiewicz]

I had to do something similar recently - keeping a device SQLite database synchronised with an online MySQL database.

Adding a 'last updated' column to tables in both databases worked well.
The device connects to a PHP script (the web service) and tells it which rows the device SQLite database contains and also the 'last updated' value of each row.

The PHP script queries the MySQL database and establishes:

• Which rows the MySQL database contains that the SQLite database does not contain.
• Which rows contained in the SQLite database have been updated - a newer version of that row exists in the MySQL database.
• Which rows in the SQLite database no longer exist in the MySQL database.

From there the script returns a JSON object to the device with three properties, each property is an array of row data.

One property is new rows, one property is updated rows and one property is deleted rows.

The device can now process that JSON and update the SQLite database - adding the new rows, updating existing rows and deleting rows that no longer exist in the MySQL database.

The MySQL database is the 'master' database, all changes are made to this master database - the SQLite database simply mirrors the MySQL database.

Martin.

I need to implement something like that. For me the problem is the PHP script. I'm not really familiar with PHP and have no idea how to do it on the server site. Any sugestions ?

Arthur

 

[Harris]

I had to do something similar recently - keeping a device SQLite database synchronised with an online MySQL database.

Adding a 'last updated' column to tables in both databases worked well.
The device connects to a PHP script (the web service) and tells it which rows the device SQLite database contains and also the 'last updated' value of each row.

The PHP script queries the MySQL database and establishes:

• Which rows the MySQL database contains that the SQLite database does not contain.
• Which rows contained in the SQLite database have been updated - a newer version of that row exists in the MySQL database.
• Which rows in the SQLite database no longer exist in the MySQL database.

From there the script returns a JSON object to the device with three properties, each property is an array of row data.

One property is new rows, one property is updated rows and one property is deleted rows.

The device can now process that JSON and update the SQLite database - adding the new rows, updating existing rows and deleting rows that no longer exist in the MySQL database.

The MySQL database is the 'master' database, all changes are made to this master database - the SQLite database simply mirrors the MySQL database.

Martin.

How many shandy's for this code (php and b4a).

I expect that the device can also insert data into the server as well.

Thanks Martin,

Harris

 

[Harris]

MySQL :: Building a Database-Driven Web Site Using PHP and MySQL


If you know nothing about php ( the server side scripting language ) - like I do (did), read up on this link above. Very basic - very informative...

I now understand most of this: (Erel's example php)
I also understand that this is not the best way to "safely" accomplish this - and how to do so.


<?

$databasehost = "xxx.com";
$databasename = "axxx";
$databaseusername ="axxx";
$databasepassword = "bxxxx";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$query = file_get_contents("php://input");
$sth = mysql_query($query);

if (mysql_errno()) {
header("HTTP/1.1 500 Internal Server Error");
echo $query.'\n';
echo mysql_error();
}
else
{
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$rows[] = $r;
}
print json_encode($rows);
}
?>


I can also understand why members can be reluctant to provide specific examples of their code. Not in every case is it proprietary, moreover, they shall enter into a never ending thread (lesson) explaining something you know nothing about!

Erel exhibits this well. One line of explanation is all you need - you should know or learn the rest. Point well taken...

Thanks

 

[jalle007]

QCDBSync is a free, open source, thread safe, SQLite database synchronization library written in Android Java available under the BSD license. You may use it for open or closed source applications. You may give your application away free or charge for it. The BSD license has no restrictions regarding these topics. This library is included in the QuickConnect Android application template. The SyncronizedDB class contains the methods used to interact with an SQLite database file included in your Android applications' assets directory and a remote HTTP service setup by you.


Anybody have idea if this could be wrapped and used in B4A ?

 

[warwound]

How much work is involved...
I've wrapped the two main classes required and this is the generated reference:

QCDBSync
Comment: The SynchronizedDB class provides a safe way of keeping a SQLite file on an Android device in sync with a remote server. The API for this class is the only API needed.

Transactions are started and stopped using the SynchronizedDB StartTransaction and EndTransaction methods.
If any error occurs while executing the SQL found within these two transaction method calls then a roll back of the changes made will be executed.
If your database is not going to be used any longer you can use the SynchronizedDB cleanUp method to free the resources.

http://www.quickconnectfamily.org/
Version: 0.001

• DataAccessResult
  Methods:
  • GetColumnNames As String[]
    Accessor for the field names of the resultant table of a query.
  • GetErrorDescription As String
    Accessor for any error that may have occurred as a result of a call to the DataAccessObject GetData or SetData methods.
  • GetResults As ArrayList
  • IsInitialized As Boolean
  • SetColumnNames (columnNames() As String)
  • SetErrorDescription (errorDescription As String)
  • SetResults (results As ArrayList)
• SynchronizedDB
  Methods:
  • CleanUp
    This method sends a logout command to the remote HTTP service and closes down the HTTP client on the Android device.
  • ClearSync
    This is a method that should rarely, if ever, be used.
  • EndTransaction
    This method is called after the StartTransaction method and any number of SetData calls.
  • GetData (sqlKey As String, parameters() As Object) As DataAccessResult
    This method is used to retrieve data from the SQLite database on the device.
    sqlKey:
    parameters:
    Return type: @return:
  • Initialize (aDbName As String, aRemoteURL As URL, port As Int, aRemoteUname As String, aRemotePword As String, syncTimeout As Long)
    Creates a SynchronizedDB object used to interact with a local database and a remote HTTP service.
    pBA:
    aDbName:
    aRemoteURL:
    port:
    aRemoteUname:
    aRemotePword:
    syncTimeout:
  • IsInitialized As Boolean
  • RegisterSyncStatements (keySQLMap As HashMap)
    This method is used to associate a series of key - SQL pairs as if they had been registered individually using the registerSynchedStatement method.
    keySQLMap:
  • RegisterSynchedStatement (sqlKey As String, SQL As String)
    This method is used to associate a representative key String with a String containing SQL.
    sqlKey:
    SQL:
  • SetData (sqlKey As String, parameters() As Object) As DataAccessResult
    This method is used to insert data into the SQLite database on the device or do any other type of database modification.
    sqlKey:
    parameters:
    Return type: @return:
  • StartTransaction
    This method is called prior to making multiple SetData calls.
  • Sync
    This method pushes any stored SetData parameters to the HTTP service, waits for any data from the service, and then inserts any data received from the service into the appropriate tables in the local SQLite database.

The SynchronizedDB Initialize method requires a java.net.URL object as a parameter, the java.net.URL is (as far as i know) not available within the b4a IDE so that class would also require wrapping so it could be created in the b4a IDE. Or a String could be passed to the Initialize method and the library could construct the java.net.URL object.

The SynchronizedDB RegisterSyncStatements method requires a java.util.HashMap<java.lang.String,java.lang.String> parameter - i could update that to accept a b4a Map object so not much work required there.

Finally the DataAccessResult methods GetResults and SetResults use java.util.ArrayList<java.lang.String>> parameters - they'd need updating to instead use a b4a List - again not a lot of work required.

BUT if i did that would the library be useable?

The SyncronizedDB class contains the methods used to interact with an SQLite database file included in your Android applications' assets directory and a remote HTTP service setup by you.

• I didn't think a database file in the assets folder could be opened, let alone written to. Presumably you actually pass the path to a database file on readable/writeable memory?
• What is 'a remote HTTP service setup by you'?
  Do you need to setup some kind of script (PHP or whatever) and the library POSTs your data to the script - i can't see any code examples of this library or the server side script it expects to communicate with.

If we can establish if it's worth my time completing the library then i'll complete it but don't want to waste time if it's a non-starter.

Martin.

 

[Harris]

This is a complicated topic.

I don't pretend to understand the above, but simply put:

I have a writable DB. Many users require timely updates (to the device) when changes occur.

*************** - What I think would be the process...

User inserts (and possibly updates) entries into several tables...
User device POSTS to server. (inserts are fairly easy - updates require more...)

Server then syncs the device... (with all that is known currently). This would be nice...

Push:
If another device updates the DB, how does this PUSH changes to others in the group with new data (without the other device requesting changes (connecting to update))?


Maybe I am not understanding the "Sync" process... (most likely)

 

[warwound]

So after a few updates the QCDBSync library methods now all accept and return data types that can be used in b4a.

The library is a complete working implementation of the java QCDBSync library BUT we do not know how to use it (yet).
We need to put our heads together to figure it all out.

I've not been able to find any java code examples that'd help and the javadoc reference for the java library is pretty brief so not a lot of help.

Here's the b4a library reference:

QCDBSync
Comment: The SynchronizedDB class provides a safe way of keeping a SQLite file on an Android device in sync with a remote server.

Transactions are started and stopped using the SynchronizedDB StartTransaction and EndTransaction methods.
If any error occurs while executing the SQL found within these two transaction method calls then a roll back of the changes made will be executed.
If your database is not going to be used any longer you can use the SynchronizedDB CleanUp method to free the resources.

quickconnectfamily
Version: alpha version

• DataAccessResult
  Methods:
  • GetColumnNames As String[]
    Accessor for the field names of the resultant table of a query.
  • GetErrorDescription As String
    Accessor for any error that may have occurred as a result of a call to the DataAccessObject GetData or SetData methods.
  • GetResults As List
    Accessor for the data 'table' that is the result of executing a query against a database.
    Returns a List of Lists of Strings.
  • IsInitialized As Boolean
  • SetColumnNames (ColumnNames() As String)
  • SetErrorDescription (ErrorDescription As String)
  • SetResults (Results As List)
• SynchronizedDB
  Methods:
  • CleanUp
    This method sends a logout command to the remote HTTP service and closes down the HTTP client on the Android device.
  • ClearSync
    This is a method that should rarely, if ever, be used.
  • EndTransaction
    This method is called after the StartTransaction method and any number of SetData calls.
  • GetData (SqlKey As String, Parameters() As Object) As DataAccessResult
    This method is used to retrieve data from the SQLite database on the device.
  • Initialize (DatabaseName As String, RemoteURL As String, Port As Int, Username As String, Password As String, SyncTimeout As Long)
    Creates a SynchronizedDB object used to interact with a local database and a remote HTTP service.
  • IsInitialized As Boolean
  • RegisterSyncStatements (KeySqlMap As Map)
    This method is used to associate a series of key - SQL pairs as if they had been registered individually using the RegisterSynchedStatement method.
  • RegisterSynchedStatement (SqlKey As String, SQL As String)
    This method is used to associate a representative key String with a String containing SQL.
  • SetData (SqlKey As String, Parameters() As Object) As DataAccessResult
    This method is used to insert data into the SQLite database on the device or do any other type of database modification.
  • StartTransaction
    This method is called prior to making multiple SetData calls.
  • Sync
    This method pushes any stored SetData parameters to the HTTP service, waits for any data from the service, and then inserts any data received from the service into the appropriate tables in the local SQLite database.

It's a lovely bright afternoon here in Norfolk, UK so i am going to take a walk.
The library files are attached and also attached is the javadoc reference for the java library.

If anyone wants to make a start trying to use the library then please do and keep this thead updated.
I shall make some time later or tomorrow to put some b4a code together and see what it can do.
Probably best to just get the library basic usage working first, but it may be that it will raise an exception if it can't connect to a script on a server (SynchronizedDB Initialize RemoteUrl parameter).
Creating an instance of the SynchronizedDB object and Initializing it is the first thing to do, then we can look at how to make queries, get data and finally sync the device and remote databases.

@Harris Sound like you need some way for your server to push messages to devices to notify them that a update has been made to the online database. I'm not sure how you'd do that, maybe the Google Cloud Messaging service is what you require?

Martin.

 

[warwound]

I have done some basic tests but not made any real progress...

The SynchronizedDB Initialize method requires the name of a database file that exists in your app's assests folder - you cannot use a database that is located elsewhere.
It then copies this database file to File.DirInternal where it can be read from and written to.

I took the SQL tutorial code from here: http://www.b4x.com/forum/basic4android-getting-started-tutorials/6736-sql-tutorial.html#post39108, and executed it.
That created a 24KB database file located at File.DirDefaultExternal:

I copied this database file from the device into the Files folder of this new application:

Sub Process_Globals
   '   check that SynchronizedDB can be created as a process object
   Dim SynchronizedDB1 As SynchronizedDB
End Sub

Sub Globals

End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then 
      '   what units are used for SyncTimeout - assuming seconds here
      SynchronizedDB1.Initialize("test1.db", "http://localhost/", 80, "username", "password", 30)
   End If
End Sub

I uninstalled the SQL tutorial code and installed this new code, then copied the database file from the internal memory across to my computer, the database file was now 40KBs in size.

These log entries for the QCDBSync code shows the library adding these extras tables to enable it to maintain it's last sync times:

** Activity (main) Create, isFirst = true **
claiming entire semaphore
claimed
SQL: CREATE TABLE IF NOT EXISTS sync_info(int id PRIMARY KEY NOT NULL, last_sync TIMESTAMP); isChanging: true
after getting to database. Is changing: true
SQL: CREATE TABLE IF NOT EXISTS sync_values(timeStamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, sql_key TEXT, sql_params TEXT) isChanging: true
after getting to database. Is changing: true
releasing entire semaphore
released
** Activity (main) Resume **
Displayed anywheresoftware.b4a.samples.sql/.main: +371ms

So now we have to determine which methods to use to execute SQL queries and how the library will communicate with the remote database.

The QCDBSync website has no code examples that i can find so it's looks like trial and error is the only way forward.

I'll work more on the code over the next few days - feel free to help with the development!

Martin.

 

[Harris]

Excellent Martin,

I saw the post yesterday, reading over new stuff while watching my daughter ski and snowboard at our local ski resort (here in British Columbia, Canada).

I have been busy of late trying to get the web server side up and running using DHTMLX components for viewing data on my site.

I shall check it out soon.

Thanks again.

 

[Wien.Mart]

Dear Warwound,

I was looking for a way to synchronize sqlite and sqlserver. I will try your quickconnect. I hope it works. MAyI contact you for some information in case i face some blank wall?

Thanks in advance.

Warm regards,

Martin

 

[Richard Calienes]

Hi Martin,

For SQLite to MS SQL Server sync take a look at SyncStudio. It works with Basic4android and is fully documented and supported. Below is a link to the library page on the B4A forum

http://www.b4x.com/android/forum/th...e-to-sql-server-synchronization-solved.40673/

The website is www.dbsyncstudio.com

Feel free to contact me with any questions.

Thanks,

Richard

 

[Wien.Mart]

Dear Richard,

May I have your contact email? I have some issues that are not for everyone's eyes.

Thanks in advance,

Martin

 

[Richard Calienes]

Hi Martin,

My email is [email protected].

Regards,

Rick