Cordova SQLite multiple records to insert advice needed

Question

I am in the process of learning my way around cordova, I am using visual studio and am slight confused with how to code an insert function for adding multiple records to a sqlite database. I can do this for a single record with no problems however when i try to do this for multiple records things go slightly astray.

i had this code which i know isnt great but as i said i am learning so try to build on what i have learnt to find the right soltion.

 $.ajax({
    type: "post",
    url: baseURI
}).then(function (r) {
    var items = [];
    var db = window.openDatabase("BlissData", "1.0", "Bliss Data", 200000);
    $.each(r.aaData, function (line) {
        localStorage.customerid = r.aaData[line].Customerid;
        localStorage.customername = r.aaData[line].CustomerName;
        localStorage.address1 = r.aaData[line].siteAddress1;
        localStorage.address2 = r.aaData[line].siteAddress2;
        localStorage.address3 = r.aaData[line].siteAddress3;
        localStorage.address4 = r.aaData[line].siteAddress4;
        localStorage.mobile = r.aaData[line].mobilephone;
        localStorage.telephone = r.aaData[line].Telephone;
        localStorage.email = r.aaData[line].Email;
        db.transaction(insertCustomers, errorCB);
    });
});

function insertCustomers(tx) {

tx.executeSql('INSERT INTO BlissCustomers (CustomerId,CustomerName,Address1,Address2,Address3,Address4,Telephone,Mobile,Email) VALUES ("' + localStorage.customerid + '","' + localStorage.customername + '","' + localStorage.address1 + '","' + localStorage.address2 + '","' + localStorage.address3 + '","' + localStorage.address4 + '","' + localStorage.mobile + '","' + localStorage.telephone + '","' + localStorage.email + '")',[],nullHandler,errorHandler);

}

when this executes i get 10 rows added to the table but all rows have the same data. SO i guess there is an understanding issue here!

So then i found a different example which looks like a better solution. However this returns an error - Uncaught ReferenceError: dbExecution is not defined

db.transaction(function (tx) {

        $.each(r.aaData, function (line) {
            dbExecution.databaseVar.transaction(function (tx) {
                tx.executeSql("INSERT INTO BlissCustomers (CustomerId,CustomerName,Address1,Address2,Address3,Address4,Telephone,Mobile,Email) VALUES (?,?)", [r.aaData[line].Customerid, r.aaData[line].CustomerName, r.aaData[line].siteAddress1, r.aaData[line].siteAddress2, r.aaData[line].siteAddress3, r.aaData[line].siteAddress4, r.aaData[line].mobilephone, r.aaData[line].telephone, r.aaData[line].email]);
            });
        }, error, success);

    });

Is there a simple tutorial anywhere that i can read to understand what I'm doing wrong or can anyone point me in the right direction here?

Answer 1

Your problem is that each cycle of your $.each loop opens a new transaction, without waiting for the previous one to finish. You could chain the transactions together, waiting for each to finish before moving on to the next, but it would be more efficient to insert all the data in one tranasction in a single block of SQL. For example:

$.ajax({
    type: "post",
    url: baseURI
}).then(function (r) {
    var sql = "";
    var db = window.openDatabase("BlissData", "1.0", "Bliss Data", 200000);
    $.each(r.aaData, function (line, row) {
        sql += 'INSERT INTO BlissCustomers (CustomerId,CustomerName,Address1,Address2,Address3,Address4,Telephone,Mobile,Email) VALUES ("' + row.Customerid + '","' + row.CustomerName + '","' + row.siteAddress1 + '","' + row.siteAddress2 + '","' + row.siteAddress3 + '","' + row.siteAddress4 + '","' + row.mobilephone + '","' + row.Telephone + '","' + row.Email + '");';
    });
    db.transaction(insertCustomers.bind(this, sql), errorCB);
});

function insertCustomers(sql, tx) {
    tx.executeSql(sql,[],successHandler,errorHandler);
}

If you have a large amount of data, it would be even more efficient to use UNION SELECT syntax in your SQL inserts (see this SO answer for details].

You might consider using cordova-sqlite-porter which is a little plugin I created for importing/exporting data into a SQLite DB in a Cordova project. You can pass it a JSON structure and it will perform the inserts in an optimal way. For example:

$.ajax({
    type: "post",
    url: baseURI
}).then(function (r) {
    var items = [], item;
    var db = window.openDatabase("BlissData", "1.0", "Bliss Data", 200000);
    $.each(r.aaData, function (line, row) {
        item = {};
        item.CustomerId = row.Customerid;
        item.CustomerName = row.CustomerName;
        item.Address1 = row.siteAddress1;
        item.Address2 = row.siteAddress2;
        item.Address3 = row.siteAddress3;
        item.Address4 = row.siteAddress4;
        item.Mobile = row.mobilephone;
        item.Telephone = row.Telephone;
        item.Email = row.Email;
        items.push(item);
    });

    var json = {
        "data":{
            "inserts":{
                "BlissCustomers": items
            }
        }
    };

    cordova.plugins.sqlitePorter.importJsonToDb(db, json, {
        successFn: successFn,
        errorFn: errorFn
    });
});