Installing Chris Brody’s SQLite Database with Cordova CLI (Android)


The HTML5 SQLite spec results in a database with a limit of 5MB. Chris Brody’s SQLitePlugin, however, breaks this barrier. Here is how to implement it. These steps assume you’ve already created the Cordova project and are adding this database project to it. I recommend you build for v19.

 If you just want a simple prepopulated DB, try this article instead.

My development environment:
Mac OS X 10.9.1 on latest Mac Mini
Cordova CLI 3.4.0
for Android 4.3, 4.4 v19
Testing in device: Android-19, Nexus 7 with 4.3
Not for PG Build; no Jquery or other JS or CSS packages used

Cordova-SQLitePlugin:
https://github.com/brodysoft/Cordova-SQLitePlugin

Benefits:
http://brodyspark.blogspot.in/2012/12/cordovaphonegap-sqlite-plugins-offer.html

Google Groups forum:
https://groups.google.com/forum/#!forum/Cordova-SQLitePlugin

Create a Cordova  or PhoneGap project (if you’re new):
https://iphonedevlog.wordpress.com/2014/01/31/adding-cordova-apis-to-android-via-cli-accelerometer-and-camera/

Installation

This article assumes you’ve already created a project and added the Android platform to it (cordova create app, cd myapp, cordova platform add android).

1. Create subfolder structure under platforms/android/src/ as:

platforms/android/src/android/org/pgsqlite/

2. Visit the plugin site on Github at https://github.com/brodysoft/Cordova-SQLitePlugin.

3. On the Github site, navigate step 1’s folder structure (starting with /src) and download the SQLitePlugin.java file contents to the pgsqlite/ folder among the project files. (To do this, right-click on the file, select Save Link As, and select the folder to save in.)

4. On the Github site, enter the www/ folder and download the SQLitePlugin.js file to the project’s www/ folder the same way.

5. On the Github site, download the plugin.xml and SQLitePlugin.coffee.md to the www/folder the same way.

6. In your project’s site, open www/config.xml with a text editor (I use TextWrangler) and add these lines among the other plugins (or right above the </widget> line):

<feature name="SQLitePlugin">
 <param name="android-package" value="org.pgsqlite.SQLitePlugin" />
 </feature>
 <plugin name="SQLitePlugin" value="org.pgsqlite.SQLitePlugin"/>

7. Copy and paste the lines in step 6 into platforms/android/res/xml/config.xml

8. Install the plugin:

cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin

9. Create an html page (open a document in a text editor and save as sqlite.html, or just replace the index.html page if its the default page) and paste the following example into it.

Here are the complete contents of the HTML page:

<!-- ********************* BEGIN *********************** -->
<!DOCTYPE html>
<html>
 <head>
 <meta charset="utf-8" />
 <meta name="format-detection" content="telephone=no" />
 <!-- WARNING from Cordova: for iOS 7, remove the width=device-width and height=device-height attributes. See https://issues.apache.org/jira/browse/CB-4323 -->
 <meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1, width=device-width, height=device-height, target-densitydpi=device-dpi" />
 <title></title>

<script type="text/javascript" charset="utf-8" src="cordova.js"></script> 
<script type="text/javascript" charset="utf-8" src="SQLitePlugin.js"></script>

 <style type="text/css">
 .buttonClass, .buttonClass2, h2, p {
 font-family: "Helvetica"; color: #000; font-size:1em;
 }
 .buttonClass, .buttonClass2 {
 border-radius:8px; background-color:#fff;
 border:#878787 solid 1px; padding:0 1em;margin:.5em;
 height: 3em; width: 46%;
 text-align:center;
 -webkit-appearance:none;
 } 
 .buttonClass2 {
 background-color: silver;
 }
 .segment, .segment2 {
 display:block; border-radius:8px; background-color:#eee;
 border:#878787 solid 1px; padding:1em; margin:.5em;
 text-align:left;
 -webkit-appearance:none; 
 }
 .segment2 {
 
 }
 h2 {
 font-size:1.3em; font-weight: bold;
 }
table {
 width:100%;
}
td {
 font-size:.8em;
 padding: .5em;
 width:25%;
 border:1px gray solid;
 overflow:auto; 
 border-radius: 4px;
 background-color: #fff;
 height:2em;
}
 @media screen and (max-width:800px) {
 .buttonClass { width: 100%;}
 }
 </style>
 <script type="text/javascript" src="cordova.js"></script> 
 <script type="text/javascript" src="SQLitePlugin.js"></script> 
<script type="text/javascript" charset="utf-8">
document.addEventListener("deviceready", onDeviceReady, false);
function onDeviceReady() {
 var db = window.sqlitePlugin.openDatabase({name: "my.db"});
 db.transaction(populateDB, errorCB, successCB);
}

// create table
function populateDB(tx) {
 tx.executeSql('DROP TABLE IF EXISTS test_table');
 tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data1 text, data2 integer, svgImage text)');
 tx.executeSql('INSERT INTO test_table (data1, data2, svgImage) VALUES (?,?,?)', ['test1', 100, '<svg version="1.1" x="0px" y="0px" width="100px" height="100px" viewBox="0 0 100 100" enable-background="new 0 0 100 100" xml:space="preserve"><rect x="4.815" y="4.815" fill="#039BF9" stroke="" width="100" height="100"></svg>']);
 tx.executeSql("INSERT INTO test_table (data1, data2, svgImage) VALUES (?,?,?)", ['test2', 200, '<svg version="1.1" x="0px" y="0px" width="100px" height="100px" viewBox="0 0 100 100" enable-background="new 0 0 100 100" xml:space="preserve"><rect x="4.815" y="4.815" fill="#039BF9" stroke="" width="100" height="100"></svg>']);
 queryDB(tx);
}
// form the query
function queryDB(tx) {
 tx.executeSql("SELECT id, data1, data2, svgImage from test_table;", [], querySuccess, errorCB);
}
// Display the results
function querySuccess(tx, results) {
 var len = results.rows.length;
 alert("results.rows.length: " + results.rows.length + " [should be 2]"); 
 for (var i = 0; i < len; i++) { // loop as many times as there are row results
 document.getElementById("output").innerHTML +=
 "<table><tr><td>ID = " + results.rows.item(i).id + 
 "</td><td>data1 = " + results.rows.item(i).data1 + 
 "</td><td>data2 = " + results.rows.item(i).data2 + 
 "</td><td>svgImage = " + results.rows.item(i).svgImage + "</td></tr></table>";
 } 
}
// Transaction error callback
function errorCB(err) {
console.log("Error processing SQL: " + err.code);
}
// Success error callback
function successCB() {
}
</script>
 
 </head>
<body>
 <input type="button" class="buttonClass" onclick='window.location="index.html"' value="return">
<div class="segment">
<h2>SQLitePlugin Test</h2>
<div id="output">&nbsp;</div>
<input type="button" class="buttonClass" onclick='window.location="https://iphonedevlog.wordpress.com/2014/04/07/installing-chris-brodys-sqlite-database-with-cordova-cli-android/"' value="View Code On iPhoneDevLog">
</div>
</body>
 </html>
<!-- ********************* END *********************** -->

Test the Page

1. In Terminal, build the app, making sure the path starts with your project folder:

cordova build

2. Install in device and test. You should get an alert saying how many rows of records are found in the Select query, and the output will be in table form. You should see a blue square (an SVG image) in each column.

Alert firing:

SQLite Plugin Alert

 

Final loading: 

 

SQLite Plugin complete

General Notes

  • Android & iOS versions are working with Cordova 3.0 tooling.
  • Drop-in replacement for HTML5 SQL API. The only major change is to use window.sqlitePlugin.openDatabase() or sqlitePlugin.openDatabase() instead of window.openDatabase().
  • Keeps sqlite database in a user data location that is known, can be reconfigured, and iOS will be backed up by iCloud.
  • No 5MB maximum! More information at: http://www.sqlite.org/limits.html
  • Problem with Android 4.4 [unspecified].
  • Issue buiding with Android SDK < 16.
  • The db version, display name, and size parameter values are not supported and will be ignored.
  • The sqlite plugin will not work before the callback for the “deviceready” event has been fired.
  • The plugin class name starts with “SQL” in capital letters, but in JavaScript the sqlitePlugin object name starts with “sql” in small letters.
Advertisements

36 thoughts on “Installing Chris Brody’s SQLite Database with Cordova CLI (Android)

  1. Is there a reason you’re not installing it with cordova plugin add? It works at least for iOS with the latest Cordova, and I find it a much easier way to install the plugin.

  2. Hi, thank you for great tutorial! I have the sqlite plugin working (it returns the message: result.rows.length: 2 etc.). However, I cannot find where my database is stored. I thought it was supposed to be somewhere on my sdcard. I tested with large data as well and when the ‘data used by application – in settings’ reaches about 60 MB the app crashes with each database query.
    I thought this sqlite plugin has no limits (I do have about 11GB free space on the phone I tested on).
    Can you please help me at all?

    • I thought it had no limits too, as their web site says.

      I believe the database is created on the fly each time the page loads. I don’t think it is permanent on the device anywhere. That’s the only thing that makes sense to me.

  3. Hi plugin is working fine using phonegap 3.4 and sencha touch 2.3. But I had a strange behavior on iPhone. When I am building application with architecture armv7s its working fine. But if I am building application using armv7 or arm64 it is showing less data rather in the table rows. What can be the issue?

  4. i’ve got this error emulating with ripple, can you help me?

    Ripple :: Environment Warming Up (Tea. Earl Gray. Hot.) ripple.js:25879
    cordova :: Initialization Finished (Make it so.) ripple.js:25879
    The key “target-densitydpi” is not supported. (index):8
    SQLitePlugin openargs: {“name”:”tper2″} SQLitePlugin.js:8
    missing exec:SQLitePlugin.open ripple.js:34331
    [Object]
    ripple.js:34332
    TypeError {stack: (…), message: “Cannot read property ‘open’ of undefined”}
    ripple.js:34333
    TypeError: Cannot read property ‘open’ of undefined
    at Object.module.exports.exec (http://localhost:4400/ripple/assets/ripple.js:34328:30)
    at SQLitePlugin.open (http://localhost:4400/SQLitePlugin.js:56:15)
    at new SQLitePlugin (http://localhost:4400/SQLitePlugin.js:24:10)
    at Object.SQLiteFactory.opendb [as openDatabase] (http://localhost:4400/SQLitePlugin.js:386:14)
    at onDeviceReady (http://localhost:4400/:58:31)
    at Channel.fire (http://localhost:4400/cordova.js:795:23)
    at http://localhost:4400/cordova.js:223:49 ripple.js:34334
    SQLitePlugin ripple.js:50497
    open ripple.js:50498
    missing exec:SQLitePlugin.executeSqlBatch ripple.js:34331
    [Object]
    ripple.js:34332
    TypeError {stack: (…), message: “Cannot read property ‘executeSqlBatch’ of undefined”}
    ripple.js:34333
    TypeError: Cannot read property ‘executeSqlBatch’ of undefined
    at Object.module.exports.exec (http://localhost:4400/ripple/assets/ripple.js:34328:30)
    at SQLitePluginTransaction.run (http://localhost:4400/SQLitePlugin.js:286:13)
    at SQLitePluginTransaction.start (http://localhost:4400/SQLitePlugin.js:160:12)
    at SQLitePlugin.addTransaction (http://localhost:4400/SQLitePlugin.js:38:9)
    at SQLitePlugin.transaction (http://localhost:4400/SQLitePlugin.js:43:10)
    at onDeviceReady (http://localhost:4400/:59:5)
    at Channel.fire (http://localhost:4400/cordova.js:795:23)
    at http://localhost:4400/cordova.js:223:49 ripple.js:34334
    SQLitePlugin ripple.js:50497
    executeSqlBatch ripple.js:50498

  5. Hi Steve,
    I have facing security issue in integrating this same plugin in my android app.
    After adding the SQLite plugin from cordova command line tool, it modified my Manifest file and added one activity node as below..

    For security purpose, I need to remove this kind of activity without permission.
    Can i remove this node from the manifest and what would be the impact?
    Your advice is appreciated.
    Thanks

  6. Fantastic walk through. .. Finally got it all working after weeks of hair pulling with other ‘demos’…is there any way to open a prepopulated database? Where would I need to store it. ..I guess this is a similar question as to where the db created gets stored. .

    Thanks!

    • I don’t understand what you mean by “open a prepopulated database.” When You code all the information in the database on your HTML page, and it runs in the device, then the page displays the contents of the database. The data is “stored” on the page in which you coded it. (It’s possible I am misunderstanding the concept, however. But this is how I presently understand it.)

  7. I completely understand you Tim, and I have the same question.
    The database is stored on the HTML page in the Javascript functions. Steve, What we are asking/looking for is to open/query a database that is in a separate file altogether eg. a database.db file in the assets folder etc. With Javascript in the HTML to only query/search/view the data.
    My app doesnt write to the database, only looks at it.

  8. so this plugin is only for temporary database creation and usage?

    do you have any recommendations for getting around the 5mb limit that is a more permanent local storage solution for tabular data?

    • I don’t know what you mean by temporary. It is there as long as your code is on the page. Chris says that his plugin breaks the 5MB barrier. If you have reservations about this, you’ll need to address it with him.

  9. Hi! I’m working for my degree thesis. I’ve a prepopulated database. At the beginning I had some problem where to place this db, then I read the Xcode console the following line:
    “2014-10-15 11:55:54.591 FollowMyBus[41862:357738] SQLitePlugin openargs: {“name”:”tper2″,”bgType”:1}
    2014-10-15 11:55:54.592 FollowMyBus[41862:357738] Detected docs path: /Users/federico/Library/Developer/CoreSimulator/Devices/76426BBC-EEBC-4DA2-AE6A-93CA67379039/data/Containers/Data/Application/5E51D61F-F7CA-4557-A013-7DCA8E22D1D2/Documents”
    so i navigate in this path and i find the empty db and i replace with the prepopulated, i launch my app on the ios simulator and its work great.
    But now i don’t know if when install the app into the device take the prepopulated db.
    Do you have any ideas?
    Thanks a lot.

  10. Hi, is there a way to read the data from existing sqlite database using this plugin; I tried doing that with the above code however did not work well.

    This is my code:

    I have eliminated the create db function since I already have the database. The database name is test.sqlite

    document.addEventListener(“deviceready”, onDeviceReady, false);
    function onDeviceReady() {
    var db = window.sqlitePlugin.openDatabase({name: “test.sqlite”});
    db.transaction(queryDB, errorCB, successCB);
    }

    // form the query
    function queryDB(tx) {
    alert(“we are into this function”);
    tx.executeSql(“SELECT name, pop_male, pop_female, pop_total from pop_count;”, [], querySuccess, errorCB);
    }
    // Display the results
    function querySuccess(tx, results) {
    var len = results.rows.length;
    alert(“results.rows.length: ” + results.rows.length + ” [should be 3]”);
    for (var i = 0; i < len; i++) { // loop as many times as there are row results
    document.getElementById("output").innerHTML +=
    "name = ” + results.rows.item(i).name +
    “male = ” + results.rows.item(i).pop_male +
    “female = ” + results.rows.item(i).pop_female +
    “total = ” + results.rows.item(i).pop_total + “”;
    }
    }
    // Transaction error callback
    function errorCB(err) {
    console.log(“Erroressing SQL: ” + err.code);
    document.getElementById(“output1″).innerHTML +=”Error 1”;
    }
    // Success error callback
    function successCB() {

    }

  11. issue: no such file exist in my project – it do exist in “myapp” root folder.
    6. In your project’s site, open www/config.xml and add these lines among the other plugins:

  12. Great! Very usefull guide, I was stuck reading a pre populated sqlite database with ngcordova but with this I got it run.
    Thanks

  13. Hi
    Just wanted to say Hi and Thanks a lot!

    Now that I try it, the cordova build didn’t have the 100 java errors you mentioned on your article, just wanted to let you know that. It works! :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s