Insert to sqlite only runs the last statement

Question

I'm quite frustrated by a seemingly simple sqlite problem. I have a table with 3 records on the client. After collecting these records up and making a trip to the server to insert them, I have a cleanup function that hunts down new records on the server and runs deletes on the client in preparation to add them back in with server-specific ids. I don't necessarily like deleting, when updating would be preferable, however, the web-based app I've written may be accessed from multiple iPad devices and users, and we need a way to keep the client data in sync with the server. I have tried various methods that became problematic once offline possibilities were factored in.

So, to the code. I collect ids and perform a delete (that multiple_records_deleted is a boolean used to indicate to a setTimeout when the delete statement has completed).

ids = ids.substring(0,ids.length-1);
db.transaction(function (tx) {
  tx.executeSql("DELETE FROM " + table + " WHERE id IN (" + ids + ")", [], function (tx, results) {
    console.log("DELETE FROM " + table + " WHERE id IN (" + ids + ")");
    multiple_records_deleted = true;
  });
});

Then, the goal is to insert new records that may or may not be identical (there are keys and timestamp fields and other complex relationships crucial to the app that only the server has.)

sqlInserts[0] = "sql to insert first record";
sqlInserts[1] = "sql to insert second record";
sqlInserts[2] = "sql to insert third record";
function insertMultipleRecords() {
  for (var x = 0; x < sqlInserts.length - 1; x++) {
    db.transaction(function (tx) {
      tx.executeSql(sqlInserts[x],[]);
    }, badCB, goodCB);
  }
}

The long and short of my problem, is that every time I run this code, only the last item in the array seems to execute. I have tried just about every variation of db.transaction I can think of. used timers. used other callbacks. but to no avail. The weird thing is that I run a similar code block in another part of the app and it works fine.

Any ideas why it is only ever the last item in the array that successfully runs, while the rest seem to go nowhere?

Thanks.. Robin

EDIT:

Here is a follow up snip of code.

for (var rec in response.table.records.record) {
  db.transaction(function (tx) {
    console.log("DELETE FROM " + table + " WHERE id = " + response.table.records.record[rec].f[record_idnumber_fld_ctr]);
    tx.executeSql("DELETE FROM " + table + " WHERE id = " + response.table.records.record[rec].f[record_idnumber_fld_ctr], [], function (tx, results) {
      // then insert
   fld_ctr = 0;
      setTableColumns("table_columns",table); // ensure we are using the right table
      sql = "INSERT INTO " + table + " (";
      for (x = 0; x < table_columns.length; x++) {
        sql += table_columns[x] + ",";
      }
      sql = sql.substring(0,sql.length-1) + ") ";
      sql += "SELECT ";
      for (var fld in response.table.fields.field) {
        var obj = response.table.fields.field[fld];
        for (var prop in obj) {
          if (prop === "@attributes") { // cast the data
            sql += formatData(obj[prop].field_type,response.table.records.record[rec].f[fld_ctr]) + ",";
          }
        }
        fld_ctr++;
      }
      // special cases for certain tables
      if (table == "orders") {
        if (response.table.records.record[rec].f[43] != "") sql += formatData("string","YES") + ","; // siggy
        else sql += formatData("string","") + ",";
      }
      if (table == "pictures") {
        sql += formatData("string","") + ","; // datauri
      }
      sql += "'SYNCED',";
      sql += "'NO',";
      sql += formatData("integer",response.table.records.record[rec].f[record_idnumber_fld_ctr]) + ",";
      sql += "'VALID',";
      sql += "'NO ERRORS'";
      console.log(sql);
      db.transaction(insertRecords(sql), errorHandler);
    });
  });
}

I run through a resultset of records from the server. And for each one, I attempt to delete from the table where a key is the same. Both console.logs display the same verbiage all 3 times... matching the last one in the array. I'm sure this is a closure problem, just not sure how to attack it.

http://stackoverflow.com/questions/750486/javascript-closure-inside-loops-simple-practical-example — epascarello, Dec 08 '14 at 19:56

score 0 · Accepted Answer · answered Dec 08 '14 at 20:01

0

It is the classic issue where the variable is just a reference and not a snapshot in time. So by the time the callback is executed, the value of x has been changed. So you need to either pull out the function into a separate function or use a closure.

function runTrans (x) {
    db.transaction(function (tx) {
        tx.executeSql(sqlInserts[x],[]);
    }, badCB, goodCB);
}

function insertMultipleRecords() {
    for (var x = 0; x < sqlInserts.length - 1; x++) {
      runTrans(x);
    }
}

or

function insertMultipleRecords() {
    for (var x = 0; x < sqlInserts.length - 1; x++) {
        (function(x) { 
            db.transaction(function (tx) {
                tx.executeSql(sqlInserts[x],[]);
            }, badCB, goodCB);
        }(x));
    } 
}

answered Dec 08 '14 at 20:01

epascarello

204,599
20
195
236

Gave it a shot, Epascarello. But same result. It only ever runs the last element in the array (or wherever x stops). Not sure what I'm doing wrong. – user2154757 Dec 08 '14 at 20:51
When I throw an alert in there to see what the value of sqlInserts[x] is, it shows me the text I expect to see... it just doesn't seem to want to run the insert statement. It says it goes in fine, but it doesn't. – user2154757 Dec 08 '14 at 20:56
I got it, Epascarello. Thank you for your help. Something so simple... I shouldn't pull all-nighters anymore. :) – user2154757 Dec 09 '14 at 15:37

Insert to sqlite only runs the last statement

1 Answers1