Node.js and Oracle DB: Insert statement doesn't work

Question

I have a problem according to SQL query. My insert query returns answer that tuple is inserted successfully but actually there is no tuple in table. So for some reasons it doesn't work. Query:

connection.execute(
                "INSERT INTO "+table+
                " VALUES "+
                "(:0, :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)",
                [objIns.attr1, objIns.attr2, objIns.attr3, objIns.attr4, objIns.attr5, objIns.attr6, objIns.attr7, objIns.attr8, objIns.attr9, objIns.attr10, objIns.attr11, objIns.attr12],
                function(err, result){
                    if (err) {
                        console.error("insert2",err.message); 
                        callback(err.message)
                    } else{
                    console.log("Rows inserted " + result.rowsAffected);
                    }
                });

Thank you.

**UPDATE_SOLUTION1: add to your server script: oracledb.autoCommit = true; **

**UPDATE_SOLUTION2: add { autoCommit: true } to execute() **

Answer 1

If data isn't visible, it is because it wasn't committed. There are several ways to do this.

If you know an INSERT statement should be committed immediately, the most efficient way is to add the execute() option autoCommit like:

const r = await connection.execute(
                "INSERT INTO "+table+
                " VALUES "+
                "(:0, :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)",
                [objIns.attr1, objIns.attr2, objIns.attr3, objIns.attr4, objIns.attr5, objIns.attr6, objIns.attr7, objIns.attr8, objIns.attr9, objIns.attr10, objIns.attr11, objIns.attr12],
                { autoCommit: true });

A common recommendation for a sequence of INSERTS is to use autoCommit only on the last statement.

The equivalent global oracledb.autoCommit = true can result in over-committing, which is a waste of resources and may mean that you can't rollback to the desired data state if some part of your application fails.

If you don't want to immediately commit after an execute() or executeMany(), then you can use an explicit commit() at any desired time later. Note that this requires a 'roundtrip' to the database server unlike the autoCommit option which is 'piggybacked' onto the execute() or executeMany() calls. Unnecessary roundtrips reduce ultimate scalability.

See the documentation Transaction Management.

If you are inserting or updating a number of records, it is more efficient to use executeMany(), which can greatly improve the performance of a series of INSERTs or UPDATEs.