how do i SELECT then parse SQLite row values to assign as variables using Cordova-sqlite-storage plugin

Question

I have a multipage app in Cordova using a (for now local only) SQLite database. All I am wanting to do initially is to use a single row to store 2 different values which I would like to occasionally SELECT, modify, then UPDATE the row with again. I think I am able to SELECT a specific a row but I can't figure out how to parse the two values in the row ready for assigning to variables.

I am using the cordova-sqlite-storage plugin.

var myDB;

/first create/open the database
myDB = window.sqlitePlugin.openDatabase({ name: "mySQLite.db", location: 'default' });

//create table and populate
myDB.transaction(function (tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS mcappDB (id text primary key, val1 integer, val2 integer)');
    tx.executeSql('INSERT INTO mcappDB VALUES (?,?,?)', ['SessionNums', 12, 15]);
}, function (error) {
    alert('Transaction ERROR: ' + error.message);
}, function () {
    alert('Table created and populated');
});

//SELECT row and parse (and needing to store parsed data as global variables)
myDB.transaction(function (tx) {
    tx.executeSql('SELECT * FROM mcappDB WHERE id="SessionNums"', [],
    function (tx, rs) {
        alert('Whats in here? ' + rs);
    },
    function (tx, error) {
        alert('select ERROR: ' + error.message);
    });
});

Presently, rs just returns as [object Object] and I am unable to parse it. I have tried using rs.toString(0), rs.getString(0), rs.column.item(1), rs(1), etc to no avail.

I in fact began with WHERE id=SessionNums, i.e. no quotes on the row id, but oddly this returned an error with no such columns: SessionNums, tho I thought using the WHERE id= command as such was for fetching row information?! e.g. http://zetcode.com/db/sqlite/select/

Any and all help would be much appreciated. Cheers, Chris.

Answer 1

First of all, make sure that you have inserted the record before you read it with promises.

Separate insert function and read function and then use this:

$.when(insertFunc()).done(readFunc());

After that you can parse the results as follow:

if (rs.rows.length > 0) {
    for (var i = 0; i < rs.rows.length; i++) {
       console.log("val1: " + rs.rows.item(i).val1 + "\n" + "val2: " + rs.rows.item(i).val2);
    }
}

Best regards, @BernaAsis