1

I am trying to develop an Android application that will use externally created SQLite database. The required application operational cycle is as follows:

  1. The database is recreated and populated with data (from another database) on the external (Windows desktop) machine on a regular (daily) basis.
  2. That database is then pushed into Android device via USB or WIFI.
  3. The newly pushed database is used to enter new data on the Android device. The entered records are flagged (to distinguish them from the existing ones).
  4. At the end of the day, the database is pulled from Android device back to the Windows desktop machine (this process needs to be initiated by the Windows application, not by Android application).
  5. The new (flagged) records are extracted from the SQLite database on the Windows desktop, validated and added to the main Windows desktop database.
  6. The updated, complete desktop database is then copied into a new SQLite database, which is finally pushed back into the Android device (see point 1 and 2).

Notes:

  1. List item
  2. The SQLite database cannot be created on the Android device by the Android application.
  3. Placing the externally created and populated database in assets directory in the apk is not feasible, as this would only work on the initial application installation, and would not meet the key requirement for a daily database push/pull routine.
  4. It is not feasible to have the SQLite database stored/imported from the web server, as users would normally not have access to the internet while using this application, i.e. the SQLite database push/pull process needs to be done on Android connected to a standalone Windows machine via USB or WIFI.
  5. The preference would be to store and access the database from the Android internal storage area (or SDCard), but outside the hidden/protected internal root storage section, i.e. not in /data/data/package_name/databases/, in order to provide users with free access to the database file stored on the Android device.
  6. The key obstacle appears to be the recognition of the externally created, pushed database by the Android application. I have tested it by copying the database file created by the application into a different folder (still within the data/package_name/.. root section) and then copying it back to the ../databases/ folder. That was sufficient to upset the database access by the application: I would get an "Error opening trace file: no such file or directory (2)", despite trying to open the same unaltered database file. I presume, this restriction is due to the Android 'Sandbox' file security protection ?

How to overcome the above database copy/push/pull issues and how to implement this required database access push/pull model?

323go
  • 14,143
  • 6
  • 33
  • 41
Mario S
  • 291
  • 1
  • 8
  • 18

1 Answers1

1

If you require USB, ADB will do that for you.

adb push <local db> <android db>

adb pull <android db> <local db>

For example:

adb push database.db /sdcard/mypath/database.db
adb push /sdcard/mypath/database.db database.db

You can also have the Windows machine remove the database from the Android device, like so:

adb shell rm /sdcard/mypath/database.db

Additionally, you can use adb to start activities or broadcast intents, so that you could tell the app to close the database and exit prior to pulling the db.

If Wifi is an option (better option), there are various ways to access data on android devices over wifi:

  • NanoHTTPd turns the device into a webserver using just a single .java file.
  • jCIFS lets the Android device access Windows shares.
323go
  • 14,143
  • 6
  • 33
  • 41
  • Thank you very much much for the notes about adb use. Very helpful. – Mario S Feb 05 '13 at 07:59
  • Thank you very much much for the notes about adb use. Very helpful. However, my main issue is two-fold: 1. How to access this externally created database pushed by adb from Windows desktop, and to have my application recognize it as a legitimate sqlite database source. 2. How to change the default database directory from root/data/data/package_name/databases/ to a public, user-accessible Android directory such as /sdcard/ ? Thank you in advance for suggestions how to achieve the above. – Mario S Feb 05 '13 at 08:18
  • P.S. I know that it must be possible to avoid the Android "sandboxing" restrictions, as I have seen an application doing just that (see _** https://play.google.com/store/apps/details?id=com.blackhillock.livestock&hl=en)_** – Mario S Feb 05 '13 at 08:19
  • 1 + 2 seem to be the same question. It's not difficult at all: In `openOrCreateDatabase` simply pass a full path, such as `openOrCreateDatabase( "/sdcard/mypath/database.db", SQLiteDatabase.CREATE_IF_NECESSARY, null );` – 323go Feb 06 '13 at 02:00
  • 1
    Thank you once again for the prompt reply. I am currently using a simple dbAdapter with the instance of DatabaseHelper, a local implementation of the SQLiteOpenHelper class. It calls getWritableDatabase(), which handles creating/opening a database (as per Android Developers Notepad tutorial). I have changed the database path as per your suggestion, but I get an error "Failed to open database /sdcard/database.db' 'could not open the database in read/write mode.' I assume that your code example above would require using the database ContentProvider ? Please advise. – Mario S Feb 06 '13 at 02:54
  • You might want to use a "proper" path rather than hardcoding /sdcard/ -- something like `new File( getExternalFilesDir(), "database.db" ).getAbsolutePath()` or similar. Make sure you have `WRITE_EXTERNAL_STORAGE` permission. – 323go Feb 06 '13 at 03:28
  • Just so others can benefit: – Mario S Feb 06 '13 at 06:33
  • Just so others can benefit from what I have done: I have added the following to the Manifest xml file: Also, the dbBAdapter code is using the following: private static final File externalPath = Environment.getExternalStorageDirectory(); private static final File dbfile = new File(externalPath.getAbsolutePath(), "/mypath/databasename.db"); private static final String DATABASE_NAME = dbfile.toString(); I can now edit the sqlite database externally and my app is happy to accept the edited file. – Mario S Feb 06 '13 at 06:43
  • This was my biggest development worry, that this might not be possible in Android. You have made my day ! Thank you again. – Mario S Feb 06 '13 at 06:44
  • @323go I have back up of my app db file.I referred above answer. I'm trying to push it to mobile device but getting message permission denied.How can i do this. – yuva ツ Dec 11 '14 at 11:03
  • @yuvaツ, I couldn't tell you, as you didn't provide nearly enough information. You might want to create a separate question and provide the paths your pushing to, the OS version, your device, etc. – 323go Dec 11 '14 at 15:03
  • @323go - My question -http://stackoverflow.com/questions/27422062/push-sqlite-database-file-into-phone – yuva ツ Dec 12 '14 at 04:32