Can Titanium do a Batch insert into a database
Greetings Titanium coders!
I am trying to perform a bunch of INSERT into a database, following a bunch of SELECT, this within a serie of nested loops.
To my great demise, after a certain number of loops, never the same quantity, the app crashes silently. I tried to run the loops trough a setInterval, which helped a little, but even with a delay of 2 seconds in between the loops, the app will still crash, .
I suspect that there is a weird bug in the sql-lite system, so, I would like to create a batch INSERT in the db, this to avoid a "locked database" crash.
QUESTION : HOW CAN WE PERFORM A BATCH INSERT STATEMENT OR A SERIE OF PREPARED SQL INTO A TITANIUM DATABASE?
Thanks a lot for any help!
// fake code
// myArray.length is large ++1000 !
var done = false;
var myArray = [];
myArray = jsonResponse; //"lots of data from a json http request";
var iterator = 0;
var interval_x = setInterval(function()
{
if(myArray.length === iterator)
{
done = true;
{
if(done === false)
{
var myDb = Titanium.Database.open('someDatabase');
for(var item in myArray[iterator])
{
var aValue = myArray[item];
var rows = myDb.execute("SELECT somefield FROM aTable WHERE ID = ?", aValue);
while(rows.isValidRow())
{
var newValue = rows.fieldByName('savedValue');
// the app will crash here after some intervals
myDb.execute("REPLACE INTO someOtherTable (newField) VALUES (?)", newValue);
rows.next();
}
}
else
{
removeInterval(interval_x);
}
++iterator;
myDb.close();
}, 1000);
2 Answers
-
Accepted Answer
First of all, even comparatively small loops (2/3 thousand cycles) will crash in Titanium. I had crashes in XML processing, SQL inserting, filesystem checks, etc. All random, all without expanation. This is annoying and some word from the Appcelerator team would be great.
Now, yes, you can perform a single command for multiple inserts:
db.execute("BEGIN IMMEDIATE TRANSACTION"); //INSERT COMMANDS FROM YOUR LOOP HERE db.execute("COMMIT TRANSACTION");This will not crash. Point is, if you need to select something and then insert, I don't think that's gonna work (never tried but I'm pretty sure that you can't get results between queries this way). My solution would be to select what you need, store it in an array and then do multiple inserts querying your array instead of the database.
-
This is what I do. In this example, I only insert if each value in the items_array is bigger than 100:
db.execute("BEGIN IMMEDIATE TRANSACTION"); for (var d=0;d<items_array.length;d++) { var id = items_array[d]; if(id > 100){ db.execute('INSERT INTO users (userid) VALUES(?)', id); } } db.execute("COMMIT TRANSACTION");