Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I am new in database development, sorry if this is inconvenient to ask. I am developing an Android application, the database of which has a table with thousands of entries. This table is generated from a backend Oracle database and has to be updated with the changes in the backend database every time an update button is clicked on the Android app.

The procedure that I came up with is here:

  1. A text file is generated from the Oracle database by running an SQL script every three hours.

  2. Each time it is needed, the Android application downloads this text file, dumps the old table, and parses it into the new table.

My problem is that since the text file is very big with thousands of lines (around 5MB), downloading and parsing takes a very long time, but it needs to be (almost) instantaneous.

My questions are:

  1. Is there a better way to update the SQLite database from the Oracle backend efficiently?

  2. Since parsing takes too long, is there a way to setup SQLite to work with the text file, skipping parsing?

share|improve this question
add comment (requires an account with 50 reputation)

1 Answer

up vote 2 down vote

If possible, you should expose a web service that connects to the database instead of downloading the entire contents each time. This web service should have a GET method that allows you to query for the rows that have updated within a specified amount of time. In your android app, you can keep the Date of each update and then the web service call will restrict the rows returned based on this WHERE clause. Something along the lines of this:

SELECT MT.* FROM MY_TABLE AS MT
WHERE MT.last_modified > last_requested_time;

This allows you to only download the updates. Once you have the updates in your android app, you can make SQL calls to the SQLite engine and update the necessary rows based on the primary key.

In regard to your second question, I don't think that file-based loading is the proper approach (after the initial load) because it causes a large amount of mobile data to be consumed potentially plus it creates a high CPU load on the phone which is not desirable for mobile.

share|improve this answer
1  
SQLite's file format is portable; you could just let the server generate the database file itself (compressing it might be useful). For copying the updates over, ATTACH the update DB to the actual DB. – CL. yesterday
1  
I wrote up a way to import a .db file in Android that you may find useful: stackoverflow.com/questions/6540906/… – Androidy yesterday
Thanks for the responses. The comments of CL. and Androidy look promising. An answer to the following question will resolve my problem, I hope: What would be the proper way to create an SQLite database from Oracle database, that I can create periodically on Oracle server side and download to Android device to use directly? – Kerli Ferli 9 hours ago
Kerli, you may still run into the problem of having a long-running update operation. 5MB can take anywhere from 5 seconds to a few minutes depending on the user's mobile network quality. An HTTP request for the changes only can be as fast as just a few hundred milliseconds depending on the size of the payload. For your approach, write a server-side application to read the contents of the oracle database, create the schema in a new sqlite database, and then insert the records. If I were trying to accomplish this I'd first try using system calls to create the db and then JDBC to execute SQL. – haventchecked 8 hours ago
Thanks, haventchecked. Using HTTP request is an option that I already implemented. The reason of my being after other options is that we have to keep the application alive even if there is no data connection at all. As for using the JDBC, I have no clue, and should do some readings about it. I'll update this page with my solution. – Kerli Ferli 1 hour ago
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.