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() **

Generally, setting autocommit is a bad practice. I would add a commit; to your statement. — T Gray
– T Gray, Commented May 15, 2016 at 17:31

Christopher Jones · Accepted Answer · 2020-11-24 03:10:52Z

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.

S.Bozzoni · Accepted Answer · 2016-05-16 05:55:56Z

4

I don't know how node.js works, but Oracle need a commit after an insert, usually.

edited May 16, 2016 at 5:55

answered May 15, 2016 at 14:25

S.Bozzoni

1,00210 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related