Bulk Insert for cordovaSQLite

Question

Is there a Bulk insert for cordovaSQLite? I have a lot of Data, which I want to save in a sqlite Db.

My code now is

var query = "INSERT INTO Card (CardId, CardSetId, FrontText, BackText, ControlType, CardLevel, IsDirty, ChangedAt, Active) VALUES (?,?,?,?,?,?,?,?,?)";

for (var i = 0; i < cardList.length; i++) {
    $cordovaSQLite.execute(db, query, [cardList[i].CardId, cardList[i].CardSetId, cardList[i].FrontText, cardList[i].BackText, cardList[i].ControlType, cardList[i].CardLevel, cardList[i].IsDirty, cardList[i].ChangedAt, cardList[i].Active]);
}

It works but is very very slow!

When I use this code :

var query = "INSERT INTO Card (CardId, CardSetId, FrontText, BackText, ControlType, CardLevel, IsDirty, ChangedAt, Active) VALUES ";
var data = [];
var rowArgs = [];
cardList.forEach(function (card) {
    rowArgs.push("(?,?,?,?,?,?,?,?,?)");
    data.push(card.CardId);
    data.push(card.CardSetId);
    console.log(card.CardSetId);
    data.push(card.FrontText);
    data.push(card.BackText);
    data.push(card.ControlType);
    data.push(card.CardLevel);
    data.push(card.IsDirty);
    data.push(card.ChangedAt);
    data.push(card.Active);
});
query += rowArgs.join(", ");
$cordovaSQLite.execute(db, query, [data]).then(function (res) {
    console.log("inserted");
    }, function(err) {
          console.dir(err);
});

Then I get the following error: sqlite3_step failure: NOT NULL constraint failed: Card.CardSetId

But in the data array CardSetId is not empty!

Is there a way to make it faster, or do it with a bulk insert?

Thanks

I added some indexes to the table and it made it a little bit faster, but not enough... Any ideas? — Dario M., Feb 12 '16 at 17:04
http://stackoverflow.com/questions/3501516/android-sqlite-database-slow-insertion — Dave Pile, Feb 13 '16 at 09:47

score 4 · Accepted Answer · edited May 23 '17 at 11:44

4

You can try using cordova-sqlite-porter. Pass it your inserts as a JSON structure using importJsonToDb() and it will optimise the insertion into the SQLite DB.

The example project illustrates insertion of 15,000+ records. On a Samsung Galaxy S4, performing this using single SQL insert statements takes around 5 minutes/300 seconds, but the optimised JSON equivalent (using UNION SELECT - see here for info) takes around 3 seconds on the same device - 100X faster.

edited May 23 '17 at 11:44

Community

1
1

answered Feb 13 '16 at 22:15

DaveAlden

30,083
11
93
155

Thanks for your answer, thats would be great, becouse I get the Data anyway in JSON... – Dario M. Feb 18 '16 at 13:39
I have to use the sqlite-porter with importSqlToDb(). But now I have problem with single quota or with back slash in the string. Is there a solution for that? Becouse I have to write the sql query like this : "INSERT INTO Artist(Id,Title) VALUES ('1','Fred's ball \ shoe');"; and can not use parameter... – Dario M. Feb 19 '16 at 14:56
@user1861065 Can the plugin prevent SQL Injection? I couldn't see stuff getting escaped in the examples... – JGFMK Jul 23 '17 at 09:25

Bulk Insert for cordovaSQLite

1 Answers1

Linked