Unable to run a loop to update Object Array in SQLite with React Native

Question

So this has been troubling me for a while, I have an array of objects that I want to insert into my SQLite DB. Each of the objects have 5 parameters and I have the SQL Query in place to run it. I was using a loop to iterate through the array and populate each of the objects via db transactions to SQLite. However, the db tasks are asynchronous which leads to the loop being completed before the task is run and incorrect data being populated into the db. The while loop in the code below doesn't work and I have tried the same thing with a for loop to no avail.

var i=0;
while(i<rawData.length){
  console.log(rawData[i],i)
   db.transaction(function (tx) {
  console.log(rawData,i," YAY")
          tx.executeSql(
              'Update all_passwords SET title=?,userID=?,password=?,notes=?,category=? WHERE ID =? ',
              [rawData[i].title,rawData[i].userID,rawData[i].password,rawData[i].notes,rawData[i].category,rawData[i].id],
              (tx, results) => {
                console.log("saved all data")
                 tx.executeSql(
                  "SELECT * FROM all_passwords ORDER BY id desc",
                  [],
                  function (tx, res) {
                    i++
                    console.log("Print Out Correct Data")
                    for(var i=0;i<res.rows.length;i++){

                      console.log(res.rows.item(i), i )
                    }
                      });
              }
            );
          console.log("EXIT")
          }

          ,
          (error) => {
                    console.log(error);
                  }
          );
}

I'm not familiar using async tasks with hooks but I believe that might be a potential solution. My intention is to populate the rawaData array of objects into the SQLDb in one go while I use a state to maintain the loading screen.

I did refer the below sources but wasn't able to come up with anything concrete.

react native insertion of array values using react-native-sqlite-storage

https://medium.com/javascript-in-plain-english/how-to-use-async-function-in-react-hook-useeffect-typescript-js-6204a788a435

Thanks in advance!

Answer 1

I made a little write up for you on how I would solve it. Read the comments in the code. If anything is unclear feel free to ask!

const rawData = [
    { title: "title", userID: "userID", password: "password", notes: "notes", category: "category", id: "id" },
    { title: "title_1", userID: "userID_1", password: "password_1", notes: "notes_1", category: "category_1", id: "id_1" },
    { title: "title_2", userID: "userID_2", password: "password_2", notes: "notes_2", category: "category_2", id: "id_2" }
];

// You can mostly ignore this. It's just a mock for the db
const db = {
    tx: {
        // AFAIK if there is a transaction it's possible to execute multiple statements
        executeSql: function(sql, params, success, error) {
            // just for simulating an error
            if (params.title === "title_2") {
                error(new Error("Some sql error"));
            } else {
                console.log(sql, params.title);
                success();
            }
        }
    },
    transaction: function(tx, error) {
        // simulating async
        setTimeout(() => {
            return tx(this.tx);
        }, parseInt(Math.random() * 1000));
    }
}

// Lets make a class which handles our dataccess in an async way
class DataAccess {
    // as transaction has callback functions it's wrapped in a promise
    // on success the transaction is resolved
    // if there is an error it will be thrown
    transaction = () => {
        return new Promise(resolve => {
            db.transaction(tx => resolve(tx), error => {
                throw error;
            });
        });
    }

    // the actual executeSql function which "hides" all the transaction stuff
    // awaits a transaction and executes the sql on it
    // if the execution was successfull resolve
    // if not throw the error
    executeSql = async(sql, params) => {
        const tx = await this.transaction();
        tx.executeSql(sql, params, () => Promise.resolve(), error => {
            throw error;
        });
    }
}

const dal = new DataAccess();
// all sql execute tha was possible
async function insert_with_execute() {
    // promise all does not guarantee execution order
    // but it is a possibility to await an array of promises (async functions)
    await Promise.all(rawData.map(async rd => {
        try {
            await dal.executeSql("sql_execute", rd);
        } catch (error) {
            console.log(error.message);
        }
    }));
}
// no sql executed cause of error and all in the same transaction
async function insert_with_transaction() {
    const tx = await dal.transaction();
    for (let i = 0; i < rawData.length; i++) {
        tx.executeSql("sql_transaction", rawData[i], () => console.log("success"), error => console.log(error.message));
    }
}

async function test() {
    await insert_with_execute();
    console.log("---------------------------------")
    await insert_with_transaction();
}

test();

Answer 2

Apparently the best approach to take is using anonymous functions that create a separate instance of execution for each value of i. This is a good example of how to do it....

Javascript SQL Insert Loop