I am having an issue with the following and I guess it's due to SQLite asynchronous mode. The code is pretty long so I'll try to explain briefly. I have 4 inputs
<input id="input0" name="tbleName3">
<input id="input1" name="tbleName4">
<input id="input2" name="tbleName1">
<input id="input3" name="tbleName1">
Actually, in my application the number of inputs is not fixed and is dynamically changed by the user. The order of the input id is fixed, i.e the index is incremental index++... However, the name can be changed dynamically. The name of the input is the name of a table in the db. Here is what I am trying to accomplish: I put all the names in an array. arrInput=array(tbleName3, tbleName4, tbleName1, tbleName1). Now, I want to partially fill each of the db table, whose name is in the array, and get the newly created id values in each of the tables. Note that all tables have the same two first columns: tablePattern[id INT, some TXT, ......]
db.transaction(function(tx) {
for(var i=0; i < arrInput.length; i++){
var thisTble = arrInput[i];
var insertSqlArg = "INSERT INTO "+thisTble+" (some) VALUES (?)";
tx.executeSql(insertSqlArg, ["thing"], (function(i, thisTble){return function(tx,resultIdNew){
var selectSqlArg = "SELECT id from "+thisTble+" order by id DESC limit 1";
tx.executeSql(selectSqlArg, [], function(tx,resultId){
var datasetId = resultId.rows;
var itemId = datasetId.item(0);
var idValue = itemId['id'];
alert("i="+i+", tbleName="+thisTble+", New id="+idValue);
}), fnError);
};})(i, thisTble), fnError);
}
}
If the name of the 4 inputs are different (for instance {tbleName1, tbleName2, tbleName3, tbleName4}), the code works great. The bug occurs when 2 or more inputs use the same name (for instance {tbleName1, tbleName2, tbleName3, tbleName3}), i.e 1 row is added to tbleName1 and tbleName2, and 2 rows to tbleName3. In that case, instead of reporting the id of the first row created (for instant 14) and then the 2nd row created (15) in tbleName3, the alert box will show twice the id of the very last row added (15). I don't know how to fix that. It seems that the command to create the 2nd row is fired before the id of the row created first is read.
//The final goal of the code is to insert idValue and thisTble in another table 'flowTble'. The line:
alert("i="+i+", tbleName="+thisTble+", New id="+idValue);
is in fact replaced by:
var insertFlow = "INSERT INTO flowTble (some, tbleName, idTbleName, index) VALUES (thing, ?, ?, ?)";
tx.executeSql(insertFlow , [tbleName, idValue, i], null, onError);
The problem is still the same though (select gives only the very last id created). However, I might have a better chance by using a 'Trigger' that would run just after "INSERT INTO "+thisTble+" (some) VALUES (?)"
, and insert a set of values in flowTble. BUT, I don't know how to create such a Trigger with JS wrapper. Here is a try:
var createTrigger = "CREATE TRIGGER fillFlowTble AFTER INSERT ON "+TbleName+" FOR EACH ROW INSERT INTO flowTble (some, tbleName, idTbleName, index) VALUES (thing, tbleName, idValue, i)";
db.transaction(function(tx) {
tx.executeSql(createTrigger, [], null, onError);
});