Problems with Phonegap and HTML5 Storage

Question

I am creating a small application where I have to store the recovered driver data from a proprietary API to the application’s local database. After a lot of head-breaking, I chose to use HTML5 Storage. At first I didn’t have any big problems, at first. When I went to make a simple query, it was a mistake. See my code and the error respectively:

onDeviceReady: function() {
    app.returnMsg       = document.getElementById('teste');
    app.btbSubmitLogin  = document.getElementById('submit_login');

    app.db = window.openDatabase("4routes","1.0","Produção",200000);
    app.db.transaction(function(tx){
        tx.executeSql("CREATE TABLE IF NOT EXISTS fornecedor_motorista (id_motorista INT(11) primary key,id_fornecedor INT(11),nome VACHAR(60),email VARCHAR(255),telefone VARCHAR(20),celular VARCHAR(20), cnh VARCHAR(20), validade VARCHAR(10),usuario VARCHAR(20), senha VARCHAR(100), created_at DATETIME)");
    });

    app.btbSubmitLogin.onclick = function(){
        var login       = $('.login').val();
        var senha       = $('.senha').val();
        var teste       = $('#teste').html();
        var idMotorista = '';
        $.getJSON(
            "<Minha API", 
            function(data){
                app.db.transaction(function(tx){
                    tx.executeSql('SELECT * FROM fornecedor_motorista',[],function(tx,res){
                        console.log(res.rows.item.length);
                        console.log(res.rows.item(1));
                    });
                });
            }
        );
    }
},

As you can see, when starting the application the database is already created, if it does not exist. The second point is, when creating the tables it makes a request on our server and returns a JSON with the driver’s data. These data are returned normally, however, when I do just below the query to see the table, obviously, it is empty, but look how strange. Has two console.log as return of the query, one to return its size and the other the data object. This is the return of the first console.log:

08-28 16:36:02.448: I/chromium(14642): [INFO:CONSOLE(62)] "1", source: file:///android_asset/www/js/index.js (62)

And this is the return of the second:

08-28 16:36:02.453: I/chromium(14642): [INFO:CONSOLE(63)] "Uncaught RangeError: Item index is out of range.", source: file:///android_asset/www/js/index.js (63)

I’ve tried insertions in the table fornecedor_motorista, but with this problem of not presenting me the correct size it is difficult to understand the correct size of the data. Could you explain to me and shed some light on this problem?

Thank you in advance.

Some remarks:

When trying to create the database, I use a property of the localStorage class to verify the existence of the database and it returns me null:

console.log(window.localStorage.getItem('dbExists'));

It returns to me:

08-28 17:00:57.566: I/chromium(22007): [INFO:CONSOLE(59)] "null", source: file:///android_asset/www/js/index.js (59)

Answer 1

To get the number of returned elements in the query use:

res.rows.length

Instead of :

res.rows.item.length

It is also worth remembering that the following line is trying to access the element in index 1, and therefore the second element of the query:

res.rows.item(1)

Now, Local Storage and Web SQL are different things. If the getItem returns null means that you have not saved any value with the key dbexists, so it does not find anything with this key. An alternative is you assign this value when creating the database:

app.db.transaction(function(tx){
    tx.executeSql("CREATE TABLE IF NOT EXISTS fornecedor_motorista (id_motorista INT(11) primary key,id_fornecedor INT(11),nome VACHAR(60),email VARCHAR(255),telefone VARCHAR(20),celular VARCHAR(20), cnh VARCHAR(20), validade VARCHAR(10),usuario VARCHAR(20), senha VARCHAR(100), created_at DATETIME)",[],function(tx,res){
        window.localStorage.setItem("dbExists",true);
    });
});