CODEWITHSUNDEEP
jqueryajaxjsonjquery-mobilecordova
tipssch
tipssch

Reputation: 59

Populating a database table with returned JSON

I'm looking to store some JSON content from a Drupal website to a database table in a PhoneGap app. I am using ajax to do this. When I run the following code and check the table, I am told that it is empty. Does anybody know how I could populate this table? 

$('#newDiv').live('pageshow',function(){

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

    function querySuccess(tx, results) {
        var len = results.rows.length;
        alert("Table: " + len + " rows were found.");
        }
    }

    function createTable(tx) {

        tx.executeSql('DROP TABLE IF EXISTS "tableA"');
        var sql = "CREATE TABLE IF NOT EXISTS 'tableA' (id INTEGER PRIMARY KEY AUTOINCREMENT, step VARCHAR(50), text VARCHAR(50))";
        tx.executeSql(sql, [], successCB, errorCB); 
    }

    db.transaction(createTable, errorCB, successCB);

   $.ajax({
        url: myURL,
        type: 'post',
        data: 'tid=4',
        dataType: 'json',
        error: function (XMLHttpRequest, textStatus, errorThrown) {
            alert('new_page_node_view - failed to retrieve page node');
            console.log(JSON.stringify(XMLHttpRequest));
            console.log(JSON.stringify(textStatus));
            console.log(JSON.stringify(errorThrown));
        },     
        success: function (data) {

            $.each(data, function (node_index,node_value) {
               db.transaction(function(tx){
                       tx.executeSql("INSERT INTO 'tableA' (step, text) VALUES (?, ?)",[node_value.title, node_value.body]);
               });
            });
          }
      });

    db.transaction(queryDB, errorCB, successCB);


});

Upvotes: 1

Views: 1136

Answers (1)

Ross
Ross

Reputation: 3330

Hey tipssch try this -

  var numInserts = 0;

  $.ajax({
        url: myURL,
        type: 'post',
        data: 'tid=4',
        dataType: 'json',
        error: function (XMLHttpRequest, textStatus, errorThrown) {
            alert('new_page_node_view - failed to retrieve page node');
            console.log(JSON.stringify(XMLHttpRequest));
            console.log(JSON.stringify(textStatus));
            console.log(JSON.stringify(errorThrown));
        },     
        success: function (data) {
            numInserts = data.length; // num of inserts to expect (assumes data is in array of objects)

            $.each(data, function (node_index,node_value) {
               db.transaction(function(tx){
                       tx.executeSql("INSERT INTO 'tableA' (step, text) VALUES (?, ?)",[node_value.title, node_value.body],
                       function(tx, results){
                            numInserts--; // decrement 1 from expected num of inserts
                            if (numInserts == 0){
                                db.transaction(queryDB, errorCB, successCB);
                            }
                       }, 
                       function(tx, err){
                        console.log("insert error");
                        if (err){
                            console.log(err);
                        }
                       });
               });
            });
        }
  });

/Update - Answering why the above approach is necessary.

Ok, so you might wonder why you don't just call queryDB right away in the success callback of the AJAX call after your $.each loop, like this below -

   $.ajax({
        url: myURL,
        type: 'post',
        data: 'tid=4',
        dataType: 'json',
        error: function (XMLHttpRequest, textStatus, errorThrown) {
            alert('new_page_node_view - failed to retrieve page node');
            console.log(JSON.stringify(XMLHttpRequest));
            console.log(JSON.stringify(textStatus));
            console.log(JSON.stringify(errorThrown));
        },     
        success: function (data) {
            $.each(data, function (node_index,node_value) {
               db.transaction(function(tx){
                    tx.executeSql("INSERT INTO 'tableA' (step, text) VALUES (?, ?)",[node_value.title, node_value.body]);
               });
            });
            db.transaction(queryDB, errorCB, successCB); // <-- the inserts could not all be finished when this gets called
        }
   });

Let's say you have 1000 records you are going to insert from your JSON result. These inserts will not be finished when you call db.transaction(queryDB, errorCB, successCB); in the success of the AJAX call. You must implement a success callback to your tx.executeSql insert to know when an insert has finished.

What I did instead in my first approach above will allow for all the inserts to be fully completed before you call the queryDB function. I added an inline success and fail callback to the tx.executeSql("INSERT INTO 'tableA' (step, text) VALUES (?, ?)",[node_value.title, node_value.body]);. I always suggest doing this for any of your WebSQL in PhoneGap (they don't have to be inline either).

I did extensive WebSQL transactions in PhoneGap during my previous job. Much of this involved looping through JSON and writing it into local database tables. The real difficult issue occurs when you have many inserts and trying to figure out when they are all completely done before notifying the user or in your case pulling the data out that was just written.

So... the trick you will learn to do in the case of inserting is -

  1. Count the number of inserts your are expecting to perform and store it in a variable.
  2. In the success callback of your tx.executeSql insert subtract 1 from your variable that holds the number of inserts you are expecting.
  3. Once the variable gets to 0 in the tx.executeSql success callback then you know all of your inserts have finished.

Simple example with 1 insert -

// wrong approach here
db.transaction(function(tx){
    tx.executeSql("INSERT INTO 'tableA' (step, text) VALUES (?, ?)",["foo", "bar"]);
    // it will start the select query before the insert has completed
    tx.executeSql("SELECT * FROM 'tableA'", [], querySuccess, errorCB);
});

// the right approach here
db.transaction(function(tx){
    tx.executeSql("INSERT INTO 'tableA' (step, text) VALUES (?, ?)",["foo", "bar"], insertDone, insertFail);
});

function insertDone(tx, results){
    // insert is done, now you can run the queryDB
    tx.executeSql("SELECT * FROM 'tableA'", [], querySuccess, errorCB);
}

function insertFail(tx, err){
    console.log("insert error");
    if (err){
        console.log(err);
    }
}

I hope this helps clarify why I used the first approach instead of just running the queryDB in the success callback of the AJAX call.

Hopefully all this will help someone in the future, lol.

Upvotes: 4

Related Questions