Prepopulate SQLite DataBase in PhoneGap Application

It is really hard to wait for too much time when a phonegap application runs for first time and create all its database stuff, which gives a really bad impression to the user who just downloaded the app for its iPhone or Android device. I have a little approach to save the long time taken for database creation at very first run of the app, that is; we should copy a Pre-Populated Database file to the native location of device.

Source code: https://github.com/gauravstomar/PrepopulateDB

You can create a basic database for app with the help of tools like SQLite Database Browser or if you already have a database in your app you can directly get the database files (you need Databases.db and file__0/0000000000000001.db files) from the following ways:


In case of Android: in File Explore(in Eclipse) go to  data > data > [YOUR APP NAME] > app_database


In case of iOS: go to Finder (in MAC) go to Library > Application Support > iPhone Simulator > [YOUR SIMULATOR VERSION] > Applications > [YOUR UNIQUE APP ID] > Library > WebKit > Databases

NOTE: for iOS and Android apps both of files are same we don't need to grab both files for same phonegap apps.


With these files all what you need to do is; just put them in to bundle of app that is assets folder in case of Android and Root folder in case of iOS.
in Eclipse: in XCode:


Now you have to copy the files at the native location of application at the time of first boot of application, and make sure that the files should copy before your very first SQLite query. To copy these files you can use the following code snippet based on your environment. JAVA (Android)
//Use this code in your bootstrapping steps like in onCreate()
        try
     {
         String pName = this.getClass().getPackage().getName();
         this.copy("Databases.db","/data/data/"+pName+"/app_database/");
   this.copy("0000000000000001.db","/data/data/"+pName+"/app_database/file__0/");
 }
     catch (IOException e)
 {
  e.printStackTrace();
 }


        //Copy Paste this function in the class where you used above part
 void copy(String file, String folder) throws IOException 
 {

  File CheckDirectory;
  CheckDirectory = new File(folder);
  if (!CheckDirectory.exists())
  { 
   CheckDirectory.mkdir();
  }

     InputStream in = getApplicationContext().getAssets().open(file);
     OutputStream out = new FileOutputStream(folder+file);

     // Transfer bytes from in to out
     byte[] buf = new byte[1024];
     int len; while ((len = in.read(buf)) > 0) out.write(buf, 0, len);
     in.close(); out.close();
     
 }


Objective C (iOS)
//For PhoneGapDelegate.m
- (void)webViewDidStartLoad:(UIWebView *)theWebView 
{
 NSString *databaseName = @"0000000000000001.db";
 NSString *masterName = @"Databases.db"; 
 
 NSArray *libraryPaths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
 NSString *libraryDir = [libraryPaths objectAtIndex:0];
 
 NSString *masterPath = [libraryDir stringByAppendingPathComponent:@"WebKit/Databases/"];
 NSString *databasePath = [libraryDir stringByAppendingPathComponent:@"WebKit/Databases/file__0/"];
 NSString *masterFile = [masterPath stringByAppendingPathComponent:masterName];
 NSString *databaseFile = [databasePath stringByAppendingPathComponent:databaseName];

 BOOL success;
 NSFileManager *fileManager = [NSFileManager defaultManager];
 
 success = [fileManager fileExistsAtPath:databasePath];
 if(success) return;
 
 NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
 NSString *masterPathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:masterName];
 
 [fileManager createDirectoryAtPath:databasePath withIntermediateDirectories:YES attributes:nil error:NULL];
 [fileManager copyItemAtPath:databasePathFromApp toPath:databaseFile error:nil];
 [fileManager copyItemAtPath:masterPathFromApp toPath:masterFile error:nil];
 [fileManager release];
}

70 comments:

  1. hi,

    so, how do we execute opening a SQLite Db in our js files?

    ReplyDelete
  2. hello,

    could you please help me in showing how to access Sqlite in your js file? tnx

    ReplyDelete
  3. can you please show us your html file as well. I keep getting sqlite error that says cannot open the file.

    ReplyDelete
  4. I use your method but I keep getting sqlite error that cannot open file. Can you please show your javascript code as well? thanks

    ReplyDelete
  5. Hi Gaurav, I have a problem. I have pre-populated a .db file thru android native code. I have renamed the db file as 0000000000000001.db. I have placed this file and Databases.db under my Assets folder. Now when I am copying these 2 files to "data/data/"+pName+"/app_database/file__0/" folder, Phonegap is unable to find this file. And a new file is creatd each time Phonegap application loads. the name of this file is 0000000000000002.db in the same folder and it is a blank database.
    So am I missing something? or did I do something wrong.

    regards
    santu ghosh

    ReplyDelete
  6. Hi Gaurav, I have a problem. I have pre-populated a .db file thru android native code. I have renamed the db file as 0000000000000001.db. I have placed this file and Databases.db under my Assets folder. Now when I am copying these 2 files to "data/data/"+pName+"/app_database/file__0/" folder, Phonegap is unable to find this file. And a new file is creatd each time Phonegap application loads. the name of this file is 0000000000000002.db in the same folder and it is a blank database.
    So am I missing something? or did I do something wrong.

    regards
    santu ghosh

    ReplyDelete
  7. Santu. I think you need to copy DB files to the different filders.

    this.copy("Databases.db","/data/data/"+pName+"/app_database/");

    but

    this.copy("0000000000000001.db","/data/data/"+pName+"/app_database/file__0/");

    ReplyDelete
    Replies
    1. Please tell the steps to convert a native DB file to PhoneGap like style(0000000000000001.db and Databases.db)?

      Delete
  8. Please tell the steps to convert the native Android DB file to phonegap like style(0000000000000001.db and Databases.db)?

    ReplyDelete
  9. thanks gaurav for writing this post. its really very useful. keep it up.

    ReplyDelete
  10. Dear Gaurav,

    I am having prepopulated database in SQLITE
    When I am Pushing it to Device, I can see it in File Explorer.
    But, I am not getting how to use this pushed database in javascript code.
    Whenever I am using
    window.openDatabase("DBName", "1.0", "New DB Demo", 200000);

    It creates new databse with the name app_database:DBName

    Any idea about why this is happening?

    Thanks,
    Amol

    ReplyDelete
  11. Dear Gaurav,

    I am having prepopulated database in SQLITE
    When I am Pushing it to Device, I can see it in File Explorer.
    But, I am not getting how to use this pushed database in javascript code.
    Whenever I am using
    window.openDatabase("DBName", "1.0", "New DB Demo", 200000);

    It creates new databse with the name app_database:DBName

    Any idea about why this is happening?

    Thanks,
    Amol

    ReplyDelete
  12. Hi Gaurav, This article is very useful but now i have a problem. I have being developed phonegap application for iOS that is used pre populated .db file. This solution is working fine for below iOS 5.1. But I upgraded to IOS 5.1 , this solution does not to work.
    Can you give me a solution for that. I am stuck now.
    Thank you

    ReplyDelete
  13. Hi Gaurav!

    Thank you very much for share useful code. I have a question, how I can add rows and save in the Prepopulate database?

    ReplyDelete
  14. Thank you Gaurav!
    How I can insert aditional data into a Prepopulate database, and use them later close and open the app?

    ReplyDelete
  15. Hi Gourav, your trick is working fine for high end devices & android 2.3.
    But i have problem low end devices & android 2.2.
    Can you help me?

    ReplyDelete
  16. Hi... Can you tell me where can I put the code listed above. I am developing for Android without any prior knowledge in JAVA. Can you explain in specific details ? Thanks

    ReplyDelete
  17. Thanks for this post.. I'm getting a FileNotFound exception... Please help...

    ReplyDelete
  18. Thanks for this post.. I'm getting a FileNotFound exception... Please help...

    ReplyDelete
  19. Found my 'bug'... Placed the DB files in a wrong folder.. Placed them in 'assets/www' instead of 'assets'... Awesome way to pre-populate a database! Thanks!!!

    ReplyDelete
  20. Is is possible to do the same with BlackBerry WebWorks

    ReplyDelete
  21. Is it possible to do the same with BlackBerry WebWorks?

    ReplyDelete
  22. Hi Gaurav, how do i achieve same task for blackberry webworks platform. plz reply

    ReplyDelete
    Replies
    1. did you find a solution to ur problem
      i also need to do the same thing
      use a pre populated db for blackberry webworks
      any help would be highly appreciated
      thanks

      Delete
    2. did you find a solution to ur problem
      i also need to do the same thing
      use a pre populated db for blackberry webworks
      any help would be highly appreciated
      thanks

      Delete
    3. were you able to do the same thing using blackberry???

      Delete
    4. were you able to do the same thing using blackberry webworks>???

      Delete
  23. Gaurav, this is very nice. Thank you. For your objective C code, can this run every time the app runs or do you somehow need to tell it to only run the first time the app loads?

    ReplyDelete
  24. Hi Guarav,
    This is really great.

    For the Objective C code, do i need to tell this code to only run the first time, or does it automatically know to only run this code when the app first runs?

    ReplyDelete
  25. A more up to date version that I worked with and made a guest post on Ray Camden's website:
    http://www.raymondcamden.com/index.cfm/2012/7/27/Guest-Blog-Post-Shipping-a-populated-SQLite-DB-with-PhoneGap

    ReplyDelete
  26. A more recent version of the methods described here:
    http://www.raymondcamden.com/index.cfm/2012/7/27/Guest-Blog-Post-Shipping-a-populated-SQLite-DB-with-PhoneGap

    ReplyDelete
  27. Hello Gaurav,
    I have integrated above code in my Phonegap application for iOS. Database is successfully loading in my case, as I have compared 0000000000000001.db with my original database and both are same. In javascript, I have open database like this:
    function onBodyLoad() {
    var db = window.openDatabase("0000000000000001", "1.0", "MDStand DB", 1000000);
    db.transaction(queryDB,errorDB);
    }
    function queryDB(tx) {
    tx.executeSql('SELECT * FROM mds_menu',[],querySuccess,errorDB);
    }

    function querySuccess(tx, results) {
    alert('success');
    }
    function errorDB(err) {
    alert("Error processing SQL: "+err.code);
    }

    Everytime it shows me alert "Error processing SQL : undefined"

    Let me know, if I am missing anything over here.

    Thank You.

    ReplyDelete
  28. I have integrated above code in Phonegap app for iOS. In my case, database is successfully loading as 0000000000000001.db is exactly the same as my original database. I am stuck here with retrieving the records from database. Below is my code, I have used for the same:
    function onBodyLoad() {
    var db = window.openDatabase("0000000000000001", "1.0", "MDStand DB", 1000000);
    db.transaction(queryDB,null);
    }
    function queryDB(tx) {
    tx.executeSql('SELECT * FROM mds_menu',[],querySuccess,errorDB);
    }

    function querySuccess(tx, results) {
    alert('success');
    }

    function errorDB(err) {
    alert("Error processing SQL: "+err.code);
    }

    Everytime, I am getting erro alert "Error processing SQL: undefined".

    Please help me out in this. Let me know, if I am missing anything over here.

    Thank You.

    ReplyDelete
  29. Is anyone using a prepopulated database on iOS 5.1 and if so, how are you doing it? Thank you!

    ReplyDelete
  30. Is anyone using a prepopulated database on iOS 5.1 and if so, how are you doing it? Thank you!

    ReplyDelete
  31. Muy interesante este tema, como seria si si estoy trabajando con html5 js y css3 como podria hacer esto mismo algĂșn manual o algo que me recomiendes

    ReplyDelete
  32. how to upload 000000001.db file to server by using file transfer api of phonegap?

    i am uploading 000000001.db file to server by using file transfer api of phonegap but it gives me error
    file not found error code=1
    can anyone help me out how to get rid of this thing.

    ReplyDelete
  33. do you know how to connect remote database with phonegap??can you give an example plssss

    ReplyDelete
  34. its not worked for me for android can u sepicified any method for android

    ReplyDelete
  35. its not worked for me in android can u please specified the methods clearly or any other

    ReplyDelete
  36. Where do you add the Java code, the whole point of using PhoneGap is to avoid using Java, can you at least mention where this code is to be added.

    ReplyDelete
  37. Hi Gaurav,
    I have a problem while using the db file in ios 5.1 and later, I'm not able fetch the records from the db using.

    ReplyDelete
  38. Hi Gaurav,
    I have a problem while using the db file in ios 5.1 and later, I'm not able fetch the records from the db using.

    ReplyDelete
  39. Hi Gaurav,
    I'm unable to fetch the records from the .db file in ios5.1 and later, even though my db is successfully copied to webkit.
    Thanks in Advance

    ReplyDelete
  40. Hi,

    I need a solution regarding phonegap application database..

    I am creating database using this command,
    var db = window.openDatabase(g_dbName, "1.0", "My Databae", 1000000);

    I need to know the path of this database in my mobile device.

    Please help!

    ReplyDelete
  41. Hi Gaurav, I have been trying to get this to work to no avail. I'm using corodova 3.0 and still no dice. I used your code to copy the database but i get a logcat error saying "no such table" and an alert in my app saying "Error Processing SQL: undefined". What could be going wrong? Do you need me to send a snippet of code for you to take a look at briefly.

    Thanks so much for reading.

    ReplyDelete
  42. Hi Gaurav, I have been trying to get this to work to no avail. I'm using corodova 3.0 and still no dice. I used your code to copy the database but i get a logcat error saying "no such table" and an alert in my app saying "Error Processing SQL: undefined". What could be going wrong? Do you need me to send a snippet of code for you to take a look at briefly.

    Thanks so much for reading.

    ReplyDelete
  43. Hi Gaurav,

    I have tried for weeks to get this working to no avail. I get logcat errors saying "no such table" and an SQL error "undefined" when i try to perform queries on the database. Is there anything I should be doing? Can i send a snippet of code for you to look at as I have used your code exactly as it is.

    Thanks

    ReplyDelete
  44. Hi Gaurav,

    I've implemented your code but when i look at my AVD monitor, the file doesnt copy. What do i do?

    ReplyDelete
  45. Database doesnt copy, cant seem to get it working. Have used your code exactly as you've outlined. Please help

    ReplyDelete
  46. Hello,

    Thanks!

    I have used same code to copy Db file to respective folder for ios and android in my local development but when my app is ready to build and upload to build.phonegap.com what should I do to copy same DB file for ios and android.

    Because there is no java files in zip folder which is uploaded to build.phonegap.com.

    Please help me to solve my problem of build app.

    Also Please tell me how to add SQLite plugin when building app.

    Regards,
    Naresh

    ReplyDelete
  47. Hello,


    How to copy SQLite DB file to respective folder of ios and android when uploading on build.phonegap.com.

    Regards,
    Naresh

    ReplyDelete
  48. Just for those on the latest Cordova & IOS 7 /w jQuery Mobile 1.4.0 RC1:

    I have made the above work by adding the DBs (both Database.db & 0000000000000001.db) to
    -(void)webViewDidStartLoad in the MainViewController.m OBJ-C file (PhoneGapDelegate.m is no longer).

    I am not 100% certain if "0000000000000001.db" as the name is important, but when I tried to use a different name it kept creating the 0000000000000001.db file (likely due to the GUID entry in Database.db) so best stick to the program =).

    The below returned the number of rows returned as part of the SELECT for my particular DB without a problem

    ---8<---- test.js file --------

    document.addEventListener("deviceready", onDeviceReady, false);

    // device APIs are available
    //
    function onDeviceReady() {
    db = window.openDatabase("0000000000000001.db", "1.0", "Test DB", 1000000);
    db.transaction(queryDB, errorCB);
    }

    function queryDB(tx) {
    tx.executeSql('SELECT * FROM product', [], querySuccess, errorCB);
    }

    function querySuccess(tx, results) {
    alert(results.rows.length);
    // this will be true since it was a select statement and so rowsAffected was 0
    if (!results.rowsAffected) {
    console.log('No rows affected!');
    return false;
    }
    }

    function errorCB(err) {
    alert("Error processing SQL: "+err.code);
    }

    ReplyDelete
  49. I also invented a js library for html5 sqlite, of course phonegap/cordova, will you take a look?
    https://github.com/leotsai/html5sqlite

    ReplyDelete
  50. Hello gaurav may I know that can we direct store my android database to phonegap database.??
    or can we use one database for both android and phonegap??

    ReplyDelete
  51. Hi Gaurav,
    I used above code, it works fine for android below 4.4 versions. But i got error for 4.4+ versions as "error processing sql" also i found that android 4.4 creates a app_webview folder in which it generates DB.
    can i get the proper solution for this issue.
    Thanks

    ReplyDelete
  52. Hi Gaurav,
    I used above code, it works fine for android below 4.4 versions. But i got error for 4.4+ versions as "error processing sql" also i found that android 4.4 creates a app_webview folder in which it generates DB.
    can i get the proper solution for this issue.
    Thanks

    ReplyDelete
  53. Hi Gaurav,
    I used above code, it works like charm for me for android below 4.4 versions.
    But for 4.4+ it gives error as "error processing SQL" also i found android 4.4+ generates app_webview directory for creating Db.
    can i know the proper solution or how it works for android 4.4+ versions.
    Thanks.

    ReplyDelete
  54. Hi Gaurav,

    I used above code for to set my prepopulated DB in android. Above code works for below abdroid 4.4 versions.
    But 4.4 and above version it gets issues.
    Can i know the solution for this.

    ReplyDelete
  55. Hi Gaurav,

    I used above code for to set my prepopulated DB in android. Above code works for below abdroid 4.4 versions.
    But 4.4 and above version it gets issues.
    Can i know the solution for this.
    Thanks.

    ReplyDelete
  56. Hi Gaurav,

    I used above code for to set my prepopulated DB in android. Above code works for below abdroid 4.4 versions.
    But 4.4 and above version it gets issues.
    Can i know the solution for this.

    ReplyDelete
  57. Hi Gaurav,
    this works like a charm for 2 years but after I update to Android 4.4.2. The APP report this error:
    "Error was could not prepare statment (1 so such table: reservations) (code 5)".
    I believe the database was created but empty so the APP can't find any table reservations...
    Any idea?
    Tks a lot for any reply.

    ReplyDelete
  58. Great post . It takes me almost half an hour to read the whole post. Definitely this one of the informative and useful post to me. Thanks for the share.wordpress development in delhi Elesoftech is a leading offshare web development,mobile application, iphone application

    ReplyDelete
  59. Hi,
    Everyone who has posted questions...
    Can u provide a sample example code for Android and iOS ..??
    So others can get help and no need to post anymore questions..
    Any help is appreciated.

    Thanks in Advance

    ReplyDelete
  60. Good able to copy the database and easily accessible

    ReplyDelete
  61. Is it possible to achieve exactly this using only javascript or JQuery? Please assist

    ReplyDelete
  62. Hi,
    this code works perfectly when I use it in android versions < 19, but, in Android 4.4+, it's not working; the database is not being copied to the right folder. What can I do to solve this problem?

    ReplyDelete