How to setup external sqlite database for an Android app.

Guides | Tutorial | Uncategorized By 4 years ago

In this blog we will be talking about importing pre-existing database to an app. I learned it through an another blog but while i was doing it i found some tricks that wasn’t mention in that blog. Let’s start digging it.

1. Install a good Sqlite manager on your computer. If you don’t prefer to buy one, you can simply install an extension called sqlite manager on your firefox browser which is not as good as native software but still serves the purpose for us.

2. Make sure you create android_metadata table in your database before you import it. In my case i did not need to do this step because as soon as i imported my database i opened it using read write access which automatically created android metadata table in my database. Your case might be different than me so it is better to follow this step before it becomes a problem in later steps.

3. When you’ll done with second step, put your sqlite database file in your app’s assets folder.

4. Now before you copy the database from your assets folder to app’s database folder you need to perform few things.

a) Check if database exist or not.
b) If database do not exist create an empty database file by calling

getReadableDatabase() 

function which we will rewrite with our own database later.

5. Now You need to execute following code in order to copy it from assets folder to your app’s data folder to access it in your app.

private void copyDatabase() throws IOException {
    InputStream inputStream = mContext.getAssets().open("databasefilename");
    //Create the file before
    OutputStream outputStream = new FileOutputStream(mDbPath);
    byte[] buffer = new byte[4096];
    int length;
    while ((length = inputStream.read(buffer)) > 0) {
        outputStream.write(buffer, 0, length);
    }
    outputStream.flush();
    outputStream.close();
    inputStream.close();
}

6. After step 5 you’ll be able to execute your SQL scripts on top of the newly copied database.

7. In few cases you might lose the android’s auto update and create database functionality so make sure if you lose this functionality, you manage or find other ways to do so.

Rest of the steps are optional. These steps will help you verify that database has been copied successfully and all sql scripts has been executed successfully as well. (These steps can only be followed if you have a root access on your android device)

8. Open terminal

9. Execute “adb shell”. (I’m assuming that you have adb setup on you mac or pc).

10. Execute “su”

11. If your device has root then your device must prompt to grant super user access to adb. Make sure you click grant.

12. Execute “cd data/data/YOUR_APP_PACKAGE_NAME/databases”. (now you can execute “ls” command to just verify that if database has been successfully copied).

13. Now copy database file from databases folder to your sdcard using the following command “cp databasefilename /sdcard”.

14. Execute “exit” twice to come out from adb shell.

15. Now execute “adb pull /sdcard/databasefilename /path/where/file/needs/to/be/copied”

16. After pulling the file, try open it with any Sqlite Manager and verify that everything worked as expected.

That is all for this blog. If you are having any issues importing your external database to your app, feel free to ask question in comments and if you guys know any better way of doing this, let me know that as well.

Cheers!!

  • Ityav Nobles

    I want to write a mobile application the will have and sqlite in it , which i can transfer ones data from my portal running mysql to the app so that it can be view can be viewed without internet connection. Please sir can you guide me on this?

  • varun_b2cloud

    @Ityav Nobles:disqus My apologies for replying so late. Sure i can help you. Let me know if you have any issues with packaging sqlite database in your app.