Android Question Best paging approach with a remote DB

[Informatix]

I have often the same question from my ULV users:
"How can I read n records from my remote database, then read another n records and so on? How can I display all the records in my view (and not only the last query)?"

I see at least two methods to retrieve the DB contents:
- you execute a query of n records and you save the result in a local database, which is the main DB used by your view; then, when the records are downloaded (or when the user hits the bottom of your listview), you redo another query to get the next n records that you add to your local table, and so on;
- you retrieve all records with a single query and you use directly the recordset (a cursor or a DB result object). EDIT: that does not answer to the question above of course but it's what most people do.

At work, we are used to deal with huge databases and we never apply the second solution because it has important drawbacks (transfer time, no data availabilty while offline) so, from this professional perspective, I usually recommend the first one (a local SQlite DB), but it implies to synchronize the two DBs and that seems a bit complicated for most developers I talk to. So is there a better/easier approach under Android?

 

[Informatix]

I'm not sure if fully understand your question.

If I understand correctly, I will use this method
1. Table structure of my local & remote database must have a field ID as primary key & timestamp
2. I will use sqlite in memory for local database
3. Retrieve most recent n records from remote database, seek its ID in local database, if found, replaced data in local database with data from remote database, if not found, insert data to local database.

The idea is the same as the one here but you don't mention how to load the records page by page.
Unless you need extreme speed, using SQlite in memory is IMHO a waste of memory. The performance on disk is already excellent.

 

[Informatix]

Hi Informatix, not sure if this would help in what you're asking but i created a wrapper for a DB library for a customer some time ago, anyhow this library is what they call a NoSQL database, and Replication & Sync database, i've found it very useful and much faster and it will do what you are trying to accomplish in less time than what the SQLite database takes.

Here's a link to the project and documentation in case you want to take a look at it.

I really liked using this approach as it will keep all the data synchronized, meaning whatever change you make on the database on the server side will be sent to your local database on your device, i found it to be much much faster than SQLite, take a look, the only downside i see is that you will need to implement a service on the server side, but once that is done everything else is a piece of cake. I've never really stored images in any database, with this approach, the images are stored as Base64, once you retrieve a record with an image is very simple to just decode the image in B4A and convert it back to a .jpg, .bmp, or whatever type of image you are using. I will look for the library if you are interested, I will have to go back on the documentation and try to remember how to log in into the cloudant dashboard and take a look at the sample database i had created.

Anyhow, just thought i'd give my two cents.

Cheers,
Walter

I knew Cloudant Sync yet but never tried it. It's not directly useful for B4A users but thanks for the link. It could give an idea to our serial wrappers.

 

[LucaMs]

to our serial wrappers.