"tx.executeSql" Not Working Properly In Expo-sqlite-storage

Question

I am adding a feature to my app that allows users to include recurring expenses. I am storing this information in a table, which is created using the following code:

// Creating or opening a table to store recurring expenses.
  try {
    db.transaction((tx) => {
      tx.executeSql(
        "CREATE TABLE IF NOT EXISTS rexpenses (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, amount REAL, category TEXT, startdate DATETIME DEFAULT CURRENT_TIMESTAMP, recurrancedate DATETIME, repeattype STRING);",
        [],
        () => {
          console.log("rexpenses table created successfully.");
        },
        (error) => {
          console.log("Error creating rexpenses table:", error);
        }
      );
    });
  } catch (error) {
    console.log("Error executing SQL statement in addExpense.js:", error);
  }

To add these expenses to the table, I am using the following function:

function setRecurringExpense (expense) {
    
    
    const recurringInterval = expense.recurringInterval
    const date = new Date()
    let recurranceDate;

    if (recurringInterval === 'Daily') {
        recurranceDate = new Date(date.getTime() + 24 * 60 * 60 * 1000);
    } else if (recurringInterval === 'Monthly') {
        recurranceDate = new Date(date.getTime());
        recurranceDate.setMonth(date.getMonth() + 1);
    } else {
        recurranceDate = new Date(date.getTime());
        recurranceDate.setFullYear(date.getFullYear() + 1);
    }

    try {
      db.transaction((tx) => {
        tx.executeSql(
          "INSERT INTO rexpenses (name, amount, category, recurrancedate, repeattype) VALUES (?, ?, ?, ?, ?)",
          [expense.Name, expense.Amount, expense.Category, recurranceDate, recurringInterval],
          (_, { rowsAffected, insertId }) => {
            if (rowsAffected > 0) {
              console.log("Expense record inserted with ID in rexpenses:", { insertId });
            }
          },
          (_, error) => {
            console.log("Error inserting rexpense record:", error);
          }
        );
      });
    } catch(error) {
        console.log('Error adding data: ', error)
    }
}

Although I have checked that all variables are properly set, the tx.executeSql statement is not working correctly. Despite not logging any errors, it is not inserting the expense into the table. Also, anything outside the tx.executeSql statement is being logged so the function is being called properly

I have tried to select data from the table to check if the information is being added correctly. However, it seems that the tx.executeSql statement is not adding anything to the table. I have spent two days trying to identify the root of the problem, but have yet to find a solution. Any suggestions or assistance would be greatly appreciated. Thank you.

Answer 1

const TABLE_NAME = 'rexpenses';

export const createTablesSql = async () => {
  return await new Promise((res, rej) => {
    db.transaction(tx => {
      tx.executeSql(
        `CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (
            id TEXT PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            amount REAL,
            category TEXT,
            startdate DATETIME DEFAULT CURRENT_TIMESTAMP,
            recurrancedate DATETIME,
            repeattype STRING,
          );`,
        [],
        (_, result) => {
          // Table created successfully or already exists
          console.log(`Table ${TABLE_NAME} created successfully`, result);
        },
        (_, error) => {
          console.log(`Error creating table ${TABLE_NAME}`, error);
          rej(false);
        },
      );
    });
    res(true);
  });
};

export const insertDataIntoSql = async (data) => {
  return await new Promise((res, rej) => {
    db.transaction(tx => {
      tx.executeSql(
        `INSERT INTO ${TABLE_NAME} (id, name, amount, category, startdate, recurrancedate, repeattype) 
          VALUES (?, ?, ?, ?, ?, ?, ?);`,
        [
          data.id,
          data.name,
          data.amount,
          data.category,
          data.startdate,
          data.recurrancedate
          data.repeattype            
        ],
        () => res(true),
        (_, error) => {
          console.error('Insert error', error);
          rej(false);
        },
      );
    });
  });
};

// Usage:

await createTablesSql()
const inserted = await insertDataIntoSql({}) // your data here

Answer 2

Previously I have had a similar problem with my project regarding Expo-SQLite, and is that the new SDK 49 is conflicting when debugging with the JSC engine, when they stop debugging remotely it works normally. If you can provide more information regarding the SDK version and the Reac Native version it might give you a clearer solution.

Additionally if it has nothing to do with the SDK version or React Native, there was another problem that I encountered on Android and it had to do with the type of data I was inserting into the table. On iOS it worked normally but failed on Android. Try changing the data type of the columns to just string and test with that.