Android Question Updating local SQLite from remote MySQL

Setlodi · Oct 28, 2024

Hi there

I have the following situation. I have a closed user group of users using PDA devices to verify documents. Now the documents database is a remote MySQL hosted on a VPS. Because of unreliable mobile internet connection, this is how the client wants the system to operate.

1) When the users log into the devices in the morning, the database from a remote MySQL must be copied to the devices SQLite
2) No synchronizing, just one way, MySQL to SQLite
3) The client's MySQL database has additions everyday
4) Database search happens locally on SQLite

Now I'm looking ways to update the SQLite database from a remote MySQL regularly without updating the whole app.

Any ideas??

Thanks

Erel · Oct 29, 2024

Check this: [B4X] CloudKVS - synchronized key / value store

Your case is actually simpler as you only need to download the updated data (if I understood correctly). This can be done with jRDC2 quite easily. Add an "updated_at" column. Check the max local value and make a query that downloads the new data. Insert it to the local db.

DonManfred · Oct 29, 2024

Erel said:
This can be done with jRDC2 quite easily.

Yes, but his Client have requirements.

Setlodi said:
Because of unreliable mobile internet connection, this is how the client wants the system to operate.

This applies to jRDC2 too.

walt61 · Oct 29, 2024

Look at method CloneServerToClient here: https://www.b4x.com/android/forum/t...tabases-class-source-included-b4a-b4j.161218/

Erel · Oct 29, 2024

CloudKVS is built for such cases. It stores everything locally and synchronizes when there is a connection.

Same is true for jRDC2. Download the updated data when you have connection and work with the local copy. At some point you must have a connection.

Setlodi · Oct 29, 2024

Thanks Erel, Don and walt61. In the morning when the users log in, they do so at the office where there's wifi connection. They only do updates in the morning before they go out to the field. I'm quite happy to work with jRDC2. I don't know if CloudKVS will be required because this is only one way updating, no sync.

udg · Oct 29, 2024

Hi, based on your requirements and description you could evaluate something like the following.

Assuming users in the field operate each one exclusively on a given subset of data (e.g user1 on data for customers 1 ,2 and 3; users2 on data for customers 4 and 5..) you could add two columns to your master DB: last_modified and last_operation
In the mprning all the users will get the same copy of the DB while in the office
In the field they update, insert and (eventually) soft delete records in their local sqlite DBs
Back in the office a simple SELECT on proper date/time will return touched records and a sub on the server will operate accordingly on the master DB. I mean, insert new records, update existing ones, marking a deleted those that need this kind of status.
Repeating that for each field user, the master DB will be ready for the next loop the following day

If the above could fit your situation, when in the morning for each user you could erase their local sqlites DBs and import the new updated master DB. This should be faster than updating local sqlites. Obviously, id master gets big than will be better to extract and pass locally only the data they are expected to work on during the day.

Setlodi · Oct 29, 2024

My app is used to verify certain permits. Users enter the permit number in the app to verify if the permit is authentic or not. The hosted MySQL database contains details of about 1 million permits.

So here is my thinking:

1) In config.properties file => sql.Get_New_Permits = SELECT * from permits_table WHERE ...
2) In my B4A, using DBCommand => CreateCommand("Get_New_Permits", Null)
3) Put result in a Map
4) Insert Map in a List
5) Use DBUtils to insert List/Map into SQLite
6) Users search local SQLite to verify permits

Am I on the right track?

aeric · Oct 29, 2024

I think in B4A the code should look something like this (without using DBUtils):

B4X:

Sub GetPermitsFromServer
    Try
        indLoading.Show
        Dim SQL1 As SQL
        SQL1.Initialize(File.DirInternal, "data.db", False)
        Dim req As DBRequestManager = CreateRequest
        Dim dbc As DBCommand = CreateCommand("Get_New_Permits", Null)
        Wait For (req.ExecuteQuery(dbc, 0, Null)) JobDone (job As HttpJob)
        If job.Success Then
            req.HandleJobAsync(job, "req")
            Wait For (req) req_Result (res As DBResult)
            'req.PrintTable(res)
            For Each row() As Object In res.Rows
                Dim strSQL As String = "INSERT INTO permits (id, permit_number, issue_by, issue_date) VALUES (?, ?, ?, ?)"
                SQL1.AddNonQueryToBatch(strSQL, Array(row(0), row(1), row(2), row(3)))
            Next
            Dim SenderFilter As Object = SQL1.ExecNonQueryBatch("SQL")
            Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
            Log("Data Insert: " & Success)
        Else
            xui.MsgboxAsync(job.ErrorMessage, "Error")
        End If
    Catch
        xui.MsgboxAsync(LastException.Message, "Error")
    End Try
    SQL1.Close
    job.Release   
    indLoading.Hide
End Sub

byz · Oct 30, 2024

I encountered a similar situation when I was collecting data. The synchronization data needs to be updated periodically. The way I use it is to write a webapi to get and push.

Setlodi · Oct 30, 2024

Thank you so much, aeric. I think this is what Erel would call "elegant". By the way how did you declare indLoading? I'm not sure what it is for?

@byz thank you for your input. Like I said earlier, I'm not looking to synchronize databases. All I want is to get daily updates from the hosted MySQL database into local SQLite database for localized queries....

aeric · Oct 30, 2024

Setlodi said:
indLoading

It is referring to B4XLoadingIndicator.

byz · Oct 30, 2024

Setlodi said:
Thank you so much, aeric. I think this is what Erel would call "elegant". By the way how did you declare indLoading? I'm not sure what it is for?

@byz thank you for your input. Like I said earlier, I'm not looking to synchronize databases. All I want is to get daily updates from the hosted MySQL database into local SQLite database for localized queries....

Because my English is translated, it may not be accurate. I mean the database can filter only the data of a specified date and then return it to the client via webpi.

aeric · Oct 30, 2024

There are many ways to get the data from a server.
What I can think of:

Download the database from server using FTP.
Download the database from server using HTTP (OkHttpUtils2). Delete the old file and load the new file.
Use jRDC2 server
Build a web server to provide web services or Web API in PHP, C#, Java, Express JS, Python, Ruby or B4J to return as CSV, JSON or XML format

It seems the OP has chosen option #3.
For #4, it will take longer time to develop unless this is a requirement.

Android Question Updating local SQLite from remote MySQL

Setlodi

Active Member

Erel

B4X founder

DonManfred

Expert

walt61

Well-Known Member

Erel

B4X founder

Setlodi

Active Member

udg

Expert

Setlodi

Active Member

aeric

Expert

byz

Active Member

Setlodi

Active Member

aeric

Expert

byz

Active Member

aeric

Expert

Similar Threads

Android Question Updating local SQLite from remote MySQL

Active Member

B4X founder

Expert

Well-Known Member

B4X founder

Active Member

Expert

Active Member

Expert

Active Member

Active Member

Expert

Active Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency