Android Question SQLite & MySQL Connection

[Fizzy]

Hello, today is my 3rd day using this wonderful tool, and I would like to create a simple program, for example, an Product Inventory. Let me explain:

1. The program should have the capability to establish a connection via jrdc2 to a server, enabling the seamless transfer and creation of a table ("Products Inventory") in SQLite. This functionality is essential to ensure smooth offline operation as well. The user will primarily be responsible for entering quantities.
2. Once the user completes the item counting process, they should have the convenience of transferring all the data from SQLite back to the MySQL "Inventory table".

I have created a basic program that works offline, but now I'm not sure how to establish this connection. Could you give me some advice or direction? Thank you for your kindness!


I am open to all suggestions and critiques, which I will gladly accept. I apologize if I haven't posted something as I should have, so please do point that out to me, as I will be posting many more things in the future!

 

[aeric]

Synchronizing offline data to remote server can be complicated.
What I did is having 2 ids, one is SQLite autoincrement id, another one is id of the database table in server.
Synchronizing can be done by checking on the last modified date.
You may also use a temporary table for new created rows when offline but I never use this method.
The problem is if you have deleted a row at one side, synchronizing the data may copy the data back again. Using a delete flag column maybe a solution.

 

[Erel]

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

 

[Fizzy]

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

Is there a video (or more detailed) tutorial how to use this?

 

[Fizzy]

Synchronizing offline data to remote server can be complicated.
What I did is having 2 ids, one is SQLite autoincrement id, another one is id of the database table in server.
Synchronizing can be done by checking on the last modified date.
You may also use a temporary table for new created rows when offline but I never use this method.
The problem is if you have deleted a row at one side, synchronizing the data may copy the data back again. Using a delete flag column maybe a solution.

It seems we misunderstood each other; I don't want to have synchronization. Initially, I want to transfer the table from the server to the phone, and in the end, back from the phone to the server.

 

[aeric]

It seems we misunderstood each other; I don't want to have synchronization. Initially, I want to transfer the table from the server to the phone, and in the end, back from the phone to the server.

What happened to the different versions in server and mobile?
Are you familiar with SQL primary keys? That is what I am referring to.
If your data is as simple as key value pairs then CloudKVS is the best solution. If you are using MySQL as backend database, you may need to consider what I have described.

Things will be simpler if you only sending data in one direction. But if two-directions then you need to think how the data is synchronized.

 

[aeric]

You can also check my JRDC Client Template
and modify it to save the new or "downloaded" data into SQLite.
Add a button for uploading the data using jRDC2. Uploading can be mean sending new data or overwriting existing data in the server.

 

[DarkoT]

Hello, today is my 3rd day using this wonderful tool, and I would like to create a simple program, for example, an Product Inventory. Let me explain:

1. The program should have the capability to establish a connection via jrdc2 to a server, enabling the seamless transfer and creation of a table ("Products Inventory") in SQLite. This functionality is essential to ensure smooth offline operation as well. The user will primarily be responsible for entering quantities.
2. Once the user completes the item counting process, they should have the convenience of transferring all the data from SQLite back to the MySQL "Inventory table".

I have created a basic program that works offline, but now I'm not sure how to establish this connection. Could you give me some advice or direction? Thank you for your kindness!


I am open to all suggestions and critiques, which I will gladly accept. I apologize if I haven't posted something as I should have, so please do point that out to me, as I will be posting many more things in the future!

I already created the similar app which can communicate similar way as you described:

1. The system reads data from mysql server (I'm using direct connect with mysql server because app will run just in same wifi network - in company and is just few "questions" on master data tables - customers, products, ... I'm know that @Erel suggests always to use JRDC (which is much more safer methode to get data - but it was easyser for me at the begginning).

This link will help you to understand why: https://www.b4x.com/android/forum/t...-rdc-remote-database-connector.61801/#content

And here is link how to connect B4A app with mysql server directly: https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/

2. Application runs on tablets and data are local stored in sqlite; I have in local sqlite table two columns for ID - local ID and server's ID; when is server ID empty (null) means that record is stored just locally. After some time or after logical action in application (button press) system sends data to server. Server returns ID of record and this will be updated in local storage...

If you need example/help - let me know...

Br, DaT

 

[Fizzy]

You can also check my JRDC Client Template
and modify it to save the new or "downloaded" data into SQLite.
Add a button for uploading the data using jRDC2. Uploading can be mean sending new data or overwriting existing data in the server.

How to open/use this template?

 

[aeric]

How to open/use this template?
View attachment 144004

Just drop the files to B4J Additional Libraries folder and you can select it from menu New.