How can i insert an array with 5 elements to db table using SQL in javascript?

Question

Currently am inserting one single value into db using SQL in javascript (Phonegap application)

the following code am using to insert a value into db.

But when i tried to run that INSERT query in a loop it showing memory issues like 2.05GB memory used on run time

    function populateDB(tx) {

    var newsArray=new Array();
    newsArray[0]="one";
    newsArray[1]="two";
    newsArray[2]="three";
    newsArray[3]="four";
    newsArray[4]="five";
     tx.executeSql('DROP TABLE IF EXISTS NEWS');
 tx.executeSql('CREATE TABLE IF NOT EXISTS NEWS (id PRIMARY KEY AUTO INCREMENT ,news TEXT NOT NULL)');
var i=0;
while(i<newsArray.length){


 tx.executeSql('INSERT INTO NEWS (news) VALUES ('newsArray[i]')');
    i++;
}

                //Dropping and adding makes sure there is one entry of the news item.
                //You may actually cache multiple copies and make it a feature as well.
}

Well, show us the code causing problem instead of the one running fine :-) — Laurent S., Jan 02 '14 at 08:01
What does the loop look like? The code provided doesn't help diagnose the problem. — NeoNexus DeMortis, Jan 02 '14 at 08:01
Instead of drop and create table you can use REPLACE INTO in mysql and for sql check http://stackoverflow.com/questions/234/sql-server-2005-implementation-of-mysql-replace-into — Damodaran, Jan 02 '14 at 08:05
I am not sure `tx.executeSql('INSERT INTO NEWS (news) VALUES ('newsArray[i]')');` is a valid statement? is it? shouldn't be `tx.executeSql("INSERT INTO NEWS (news) VALUES ('" + newsArray[i] + "')");`? — Tun Zarni Kyaw, Jan 02 '14 at 08:20

score 1 · Answer 1 · edited May 23 '17 at 12:19

1

Check this answer https://stackoverflow.com/a/5009740/1576274 for how to insert multiple rows with one statement in SQLITE. That could give you a big performance bump over the looping insert.

Also the drop and create table might be expensive - is there a way to uniquely identify your source data so that you only have to insert if it doesn't exist?

edited May 23 '17 at 12:19

Community

1
1

answered Jan 02 '14 at 08:35

morefromalan

302
3
9

the number of values in array may vary so iam looking for a dynamic methode – Bangalore Jan 02 '14 at 09:42
That's ok. You can modify your loop to dynamically build the SQL query as a string, and then only execute it once. – morefromalan Jan 02 '14 at 22:51

How can i insert an array with 5 elements to db table using SQL in javascript?

1 Answers1