Save large array in Sqlite database using javascript

Question

I delete data on the Internet with the Google Chrome extension. I store them in a multidimensional array. I want to save all the data in a Sqlite database.

I am reading this page How to speed up the process of inserting 1000's of records in sqlite using HTML5 , but the answer does not seem to work.

When I open a transaction for each INSERT , it works

i=0; while(i<n){ (function(aa){ db.transaction(function (tx) { tx.executeSql("INSERT INTO Links2 (c1, c2, c3, c4, c5, c6) VALUES ('"+aa[0]+"', "+parseInt(aa[1])+", '"+parseInt(aa[2])+"', '"+aa[3]+"', '"+aa[4]+"', '"+aa[5]+"')"); }); })(myDataArray[i]); i++; } 

The problem is when I try to open a transaction before Bye

 db.transaction(function (tx) { i=0; while(i<n){ aa = myDataArray[i]; txquer(tx, i, aa[0], parseInt(aa[1]), parseInt(aa[2]), aa[3], aa[4], aa[5]); i++; } }); function txquer(tx,i,a,b,c,d,e,f){ console.log("INSERT INTO Links2 (c1, c2, c3, c4, c5, c6) VALUES ('"+a+"', "+b+", '"+c+"', '"+d+"', '"+e+"', '"+f+"')"); tx.executeSql("INSERT INTO Links2 (c1, c2, c3, c4, c5, c6) VALUES ('"+a+"', "+b+", '"+c+"', '"+d+"', '"+e+"', '"+f+"')"); } 

When I individually test each console.log output, I work. But the script does not save data in the database.

Answer 1

I have found a solution! This is not the best way to do this, but it is much faster than opening every transaction.

The basic idea is to execute the next executeSql when the previous one is executing. I implemented a recursive function.

 n=mainDataArray.length; console.log(mainDataArray); //Saving the array in the db db.transaction(function(tx) { saveData = function(dataArray,k){ var aa = dataArray[k]; console.log(aa); tx.executeSql("INSERT INTO Links2 (c1, c2, c3, c4, c5, c6) VALUES ('"+aa[0]+"', "+parseInt(aa[1])+", '"+parseInt(aa[2])+"', '"+aa[3]+"', '"+aa[4]+"', '"+aa[5]+"')", [], function (tx, callback) { console.log("ok "+dataArray.length+" | k="+k); if(k<(dataArray.length-1)){ saveData(dataArray,(k+1)); } }, function (tx, error) { console.log("error "+dataArray.length+" | k="+k); if(k<(dataArray.length-1)){ saveData(dataArray,(k+1)); } }); } saveData(mainDataArray,0); }); 

You can find more information on this site. Saving an array to a Sqlite database using javascript

If you find the best solution, post it here :)

Answer 2

Each of tx.executeSql runs asynchronously, so the while loop will already be completed when each request i == n and aa == myDataArray[i-1] .

[EDIT]

You should leave the tx.executeSql call inside closure to avoid aa :

 db.transaction(function (tx) { i=0; while(i<n){ (function(aa){ txquer(tx, i, aa[0], parseInt(aa[1]), parseInt(aa[2]), aa[3], aa[4], aa[5]); })(myDataArray[i]); i++; } }); function txquer(tx,i,a,b,c,d,e,f){ console.log("INSERT INTO Links2 (c1, c2, c3, c4, c5, c6) VALUES ('"+a+"', "+b+", '"+c+"', '"+d+"', '"+e+"', '"+f+"')"); tx.executeSql("INSERT INTO Links2 (c1, c2, c3, c4, c5, c6) VALUES ('"+a+"', "+b+", '"+c+"', '"+d+"', '"+e+"', '"+f+"')"); } 

This code could not be verified, so I'm not sure if it works, but I hope you can get this idea.