javascriptsqlitecordovaweb-sql

SQLite INSERT and DELETE query is giving error, but not SELECt query


I'm using SQLite(WebSQL) for saving form data locally in my Cordova mobile app.
The code below works well with SELECT query for the same table, but not with INSERT or DELETE queries.

It outputs error as: could not prepare statement

const usersDB = {
    add: function (firstname, lastname, dob, email, phone, city, state, country) {
        databaseHandler.db.readTransaction(
            function (tx) {
                tx.executeSql(
                    "INSERT INTO users(firstname, lastname, dob, email, phone, city, state, country) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
                    [firstname, lastname, dob, email, phone, city, state, country],
                    function (tx, results) {
                        console.log('User has been saved');
                    },
                    function (tx, error) {
                        console.log('ERROR: Failed to add user', error);
                    }
                );
            }
        );
    },
    selectAll: function (listUsers) {
        databaseHandler.db.readTransaction(
            function (tx) {
                tx.executeSql(
                    "SELECT * FROM users ORDER BY firstname",
                    [],
                    function (tx, results) {
                        listUsers(results);
                    },
                    function (tx, error) {
                        console.log('ERROR: Failed to list users', error);
                    }
                );
            }
        );
    }
}

I searched dozens of discussion forms, but couldn't find a post with a solution about this.
Your help will be appreciated and thanks in advance.


Solution

  • A read transaction is used for reading only. A write transaction allows both reading and writing. A read transaction is started by a SELECT statement, and a write transaction is started by statements like CREATE, DELETE, DROP, INSERT, or UPDATE (collectively "write statements").

    The .readTransaction() is used for read only queries and .transaction() is used for write queries.
    So in my case I should use them in such way:

    const usersDB = {
        add: function (firstname, lastname, dob, email, phone, city, state, country) {
            databaseHandler.db.transaction( // Write mode
                function (tx) {
                    tx.executeSql(
                        "INSERT INTO users(firstname, lastname, dob, email, phone, city, state, country) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
                        [firstname, lastname, dob, email, phone, city, state, country],
                        function (tx, results) {
                            console.log('User has been saved');
                        },
                        function (tx, error) {
                            console.log('ERROR: Failed to add user', error);
                        }
                    );
                }
            );
        },
        selectAll: function (listUsers) {
            databaseHandler.db.readTransaction( // read-only mode
                function (tx) {
                    tx.executeSql(
                        "SELECT * FROM users ORDER BY firstname",
                        [],
                        function (tx, results) {
                            listUsers(results);
                        },
                        function (tx, error) {
                            console.log('ERROR: Failed to list users', error);
                        }
                    );
                }
            );
        }
    }