Twitter: raymondcamden


Address: Lafayette, LA, USA

Guest Blog Post: Shipping a populated SQLite DB with PhoneGap

07-27-2012 20,892 views Mobile 39 Comments

This is my second "Guest Blog Post" for the month. Sorry I've been a bit slow on my own content lately! Today's post comes from Scott Buckel. He and I shared a conversation earlier this month about databases and PhoneGap. PhoneGap makes it pretty darn easy to create and work with a database in your application. What isn't so trivial is actually shipping a prepopulated database with the app itself. I wasn't able to help him as much as I'd like - but he worked at it until he came up with a solution. Here is what he discovered.

These instructions are very raw, and not optimized.

  1. I used this plugin: https://github.com/chbrody/Cordova-SQLitePlugin/

  2. Copy your sqlite db file to /assets folder of PhoneGap.

  3. Then, follow instructions here: http://gauravstomar.blogspot.com/2011/08/prepopulate-sqlite-in-phonegap.html to copy the file to native storage. Change this.copy("Databases.db","/data/data/"+pName+"/app_database/");
    1. Instead of Databases.db, use your database filename.
    2. Instead of app_database, use "databases"
    3. You'll probably want to delete the file from /assets, since it is duplicated and no longer needed. My app was double the size it needed to be.

  4. (Not sure if this step is necessary, I'm getting out of the office for the day). Edit SQLitePlugin.java
    1. Lines 176, I edited 1==1 so that if statement is always executed. Line 179 I changed this.setStorage(appPackage, false); to this.setStorage(appPackage, true);
  5. You can then use the following command to open the DB and use it as any other PhoneGap database
    1. var db = window.sqlitePlugin.openDatabase("[full_database_name_including_extension]", "1.0", "PhoneGap Demo", 200000);

10 hours of work later, I have a working database!

Feel free to use this information on a blog, I feel it would help a LOT of people out. Step #4 is weird, I'm sure there's a "prettier" way to do it. If used, just give Scott Buckel @ Corporate Zen credit please :).

39 Comments

  • Vitgas #
    Commented on 08-02-2012 at 5:38 AM
    May you upload some screenshots showing how your proyect is organized and those changes you remark?
    Thanks!
  • Commented on 08-03-2012 at 2:19 PM
    Vitgas, feel free to email me directly and I'd be glad to help. I'll have Ray update the blog after I understand your questions.
  • Commented on 08-08-2012 at 3:32 PM
    Here's an updated version on how I did this. Thanks to Ray for all of his help!
  • Commented on 08-08-2012 at 3:34 PM
    Did you mean to post a link?
  • Commented on 08-08-2012 at 3:36 PM
    Here is the link he meant: http://www.corporatezen.com/shippingprepopulateddatabasewithphonegap
  • Jeremy #
    Commented on 09-30-2012 at 9:53 AM
    Thanks so much for this post! It helped point me in the right direction for my own project. For any iOS developers, I found the linked instructions in http://gauravstomar.blogspot.com/2011/08/prepopula... to be a little out of date.

    When using the Cordova-SQLitePlugin, it looks in the Documents directory, and Gaurav Tomar's instructions refers to WebKit/Databases directory (which I believe is no longer used because of an iOS 5.1 change).

    I found the following works instead:

    NSString databaseName = @"Database.db";

    NSArray
    documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString databasePath = [documentPaths objectAtIndex:0];

    NSString
    databaseFile = [databasePath stringByAppendingPathComponent:databaseName];
    NSString databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

    NSFileManager
    fileManager = [NSFileManager defaultManager];

    if ([fileManager fileExistsAtPath:databaseFile] == NO) {
       [fileManager copyItemAtPath:databasePathFromApp toPath:databaseFile error:nil];
       [fileManager release];
    }

    Thanks again for pointing me in the right direction!
  • Commented on 10-05-2012 at 4:17 PM
    You can use the working steps here to create prepopulated databases with PhoneGap:
    http://iphonedevlog.wordpress.com/2012/10/05/insta...
    or
    http://iphonedevlog.wordpress.com/2012/08/21/prepo...

    Regards,
    Steve Husting
  • Vaibhav #
    Commented on 01-22-2013 at 12:07 PM
    What is the best way to use this data to create knockout JS bindings on the underlying HTML. do i need to create separate JSON object first with the data that i get from the queries or is there an easier way to integrate this with knockout.js?
  • Commented on 01-22-2013 at 12:39 PM
    Unfortunately, I have not had a chance yet to play with Knockout. Therefore, I've definitely not used it with PhoneGap either.
  • Kristen #
    Commented on 02-13-2013 at 1:46 PM
    Wow! Thank you so much! I have been trying to get this working forever. Your example worked like a charm and I'm finally up and running.
  • Commented on 03-08-2013 at 4:52 AM
    Wow from me too!! We got this sorted via this post. Thanks a bunch guys!! :-)
  • Commented on 04-08-2013 at 12:26 AM
    Correct me if I am wrong (haven't really tested the code), but the example here would overwrite the database with the preshipped version everytime the app loads. Is that right? If we are building an app with persistent storage, should we check to see if the database files already exist before doing this.copy(...) or perhaps modify this.copy()?
  • Commented on 04-24-2013 at 5:32 PM
    If anyone is looking to use a pre populated db with iOS here is updated plugin SQLitePlugin-iOS. You dont need to do any of the steps above. All you need to do is put your db in the root of your XCode project and it does everything for you. Android you still need to do the steps above.

    https://github.com/jarlehansen/PhoneGap-SQLitePlug...
  • Commented on 04-25-2013 at 8:26 AM
    This is how my MyPhoneGapActivity.java looks like

    package com.rkadukar.database;

    import org.apache.cordova.DroidGap;
    import android.os.Bundle;

    public class MyPhoneGapActivity extends DroidGap {
       @Override
       public void onCreate(Bundle savedInstanceState) {
          super.onCreate(savedInstanceState);
          super.loadUrl("file:///android_asset/www/index.html");
       }
    }

    where do I add the code from Gaurav Tomar's site after I make the changes suggested by you
  • Commented on 04-25-2013 at 8:31 AM
    Thanks for sharing that John.
  • shashank dwivedi #
    Commented on 05-06-2013 at 4:58 AM
    How can I use this with phonegap build? Any idea.....
  • Commented on 05-06-2013 at 7:34 AM
    You can't. PGB supports a limited set of plugins only.
  • Commented on 05-06-2013 at 7:55 AM
    I've successfully implement this on a simple POS app. Now I have a different requirement -- nothing to do with the previous POS app.

    Is there a way for me to download a SQLite file and do SQL operations on that SQLite database file? Here's why I need that. My app will be downloading a huge document, and for searching purpose, I am thinking of implementing the index using the SQLite database file.

    Got ideas on how I can do this?
  • vikas.s #
    Commented on 06-12-2013 at 6:04 AM
    i get error as cordova not defined in SQLiteplugin.js
  • Naresh Ramoliya #
    Commented on 07-15-2013 at 6:35 AM
    I have some issue in installation of SQLite Plugin for both Android and iOS. I have followed exactly given steps but somehow i am not able to run correctly. so Please help me in configuration of SQLite plugin

    I am using Cordova 2.9.0 for phonegap.

    Regards,
  • Commented on 07-15-2013 at 8:13 AM
    @Naresh,

    You did not tell us what error you are receiving, What part is not working correctly? At what step does it fail?
  • William #
    Commented on 07-22-2013 at 6:41 AM
    I have the question as Iszuddin Ismail asked.
    I didn't get it, cause each time I turn off my app and reopen it.
    Then the database got cleaned up.

    So what can I do to prevent my database be cleaned up?
    Thanks~
  • jeff #
    Commented on 07-22-2013 at 9:00 PM
    Following http://gauravstomar.blogspot.com/2011/08/prepopula... copy sqlite db to assets folder, where is "data/data" in my application? is the 'databases' folder automatically created?

    This is what I wrote
    String pName = this.getClass().getPackage().getName();
    this.copy("aaa.db","/data/data/"+pName+"/databases/");       this.copy("aaa.db","/data/data/"+pName+"/databases/file0/");

    Thanks
  • William #
    Commented on 07-25-2013 at 1:03 AM
    Hi Jeff,
    The data/data folder is at the system root.
    I found the folder from eclipse's window.

    I turn on the DDMS (window > open perspective > DDMS) and there is a
    File Explorer tab. When you run your app, you can find the data/data folder
    here.

    And the folder will be created, cause the copy method check if the directory exist. And create it if not.
  • Marco #
    Commented on 08-06-2013 at 5:43 AM
    For me does not work :(.
    I wrote in my Activity:
    try {
    String pName = this.getClass().getPackage().getName();
    this.copy("dbname.db", "/data/data/" + pName + "/databases/");
    } catch (IOException e) {
    e.printStackTrace();
    }
    and the db is copy correctly!!!
    But when I open DB,(this is the javascript code):
    var db = window.sqlitePlugin.openDatabase("dbname.db", "1.0", "PhoneGap Demo", 200000);
    (I tried also with no db extension "dbname")
    For me DB not found?
    Where is the problem? :(
  • Jeff #
    Commented on 08-27-2013 at 6:11 AM
    I copied source from here https://github.com/k4ml/halal-android, its working... my concern now is whenever I use the application from my smartphone, any database transaction I made is gone when I open it again. I know it can write to the sqlite db, I think it somehow overwrite the sqlite file everytime I launch the application. Anyone have solution for this?

    Thanks
  • velissariouc #
    Commented on 09-08-2013 at 10:13 AM
    @ Jeff
    " I think it somehow overwrite the sqlite file everytime I launch the application"

    You are right. The "halal" example always copies the original database from assets to data.
    The code should be like this (replace "mydb" with your database name):
    try
        {
        File dbFile = getDatabasePath("mydb.db");
        if(!dbFile.exists()){
        this.copy("mydb.db",dbFile.getAbsolutePath());
        }
        }
        catch (Exception e)
        {
        e.printStackTrace();
        };
  • Shafi #
    Commented on 11-03-2013 at 11:40 PM
    Hi Raymond
    I tried to use prepopulated db in iOS using phonegap 2.7,3 and 3.1 but all showed same database undefined error. As per instructions (from your source and from the others) i tried to put some native code in to mainviewcontroller.m but the database is still cannot be opened i think.. Please help me
  • Commented on 11-05-2013 at 6:13 AM
    As I said - this is not something I did myself. I was just passing on the tip. This post is now over a year old so it may not apply anymore.
  • Vessie #
    Commented on 11-29-2013 at 10:51 AM
    Thanks for all your posts. They are very helpful.

    I'm trying to use phonegap/cordova3 with SQLite plugin from https://github.com/lite4cordova/Cordova-SQLitePlug... but I have hard time making this work. The plugin seems to be written for cordova 3 but when I run it with the emulator it says "something is missing". It's crashing on crodova.exec.

    Anybody successfully used SQLitePlugin with cordova 3? I also read some posts that the plugin is part of cordova 3 but not sure....
  • Commented on 11-29-2013 at 11:07 AM
    Vessie,

    I think that this post is about a year and a half old now, so a lot of the methods might not apply. This was written for PhoneGap 1.8 or 2.0 (I forget), so it might not be relevant anymore.

    You'll probably have to figure out on your own how to do this in the latest PhoneGap 3.0+. Could you please post a comment here after you figure it out?

    Ray--you should probably put a comment on this blog that it's outdated.

    Thanks!
    -Scott Buckel
  • Vessie #
    Commented on 11-30-2013 at 10:31 AM
    I realized this is an old post but just wondered if somebody got this work with the latest phonegap/cordova 3. I will post a comment if I figure out (so far no luck).
    Thanks!
  • Commented on 12-01-2013 at 7:57 AM
    @Scott: I tend to believe that people are smart enough to pay attention to the date and understand that the older entries may not be relevant/applicable. Maybe I assume too much. ;)
  • Commented on 01-05-2014 at 8:28 PM
    I invented a sqlite js library, would you like to take a look?
    https://github.com/leotsai/html5sqlite
  • Navya Kooram #
    Commented on 02-13-2014 at 2:00 PM
    Raymond,

    I used Gaurav's post to pre-populate my SQLite database and it worked well in iOS and Android platforms using Phonegap 2.8 to 3.3 versions. But it isn't working with Android Kitkat SDK. The app doesn't work for Android 4.4 and above. It looks like the data folder is empty when running on those devices and emulators. Did you face such problems? Does Kitkat handle folder/file permissions differently??

    Thanks and Regards,
    Navya
  • Commented on 02-22-2014 at 8:24 AM
    You may be running into FileSystem changes. See: http://www.raymondcamden.com/index.cfm/2014/2/17/C...
  • David Guerra #
    Commented on 03-11-2014 at 8:01 AM
    Hi Raymond I have the same problem of Navya.
    For 2 years this solution works like a charm but after Android 4.4 the APP give me this error:
    "Error was could not prepare statement (1 no such table reservation) (code 5) "
    I suppose Android create an empty DB instead of copy it... so did not found the table.
    Any idea about it?
  • Commented on 03-11-2014 at 8:04 AM
    Unfortunately no - as I mentioned - this was a guest post - not something I did myself.
  • Ram Gurung #
    Commented on 04-29-2014 at 6:33 AM
    I have following issue. Please help me to get out of it.
    http://stackoverflow.com/questions/23363263/how-to...

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty