How to avoid a broken connection with ORACLEDB? Nodejs

Question

I have this database connection. Inside the function where the comment is located, there is a data update cycle for rest api. The data is updated, but when the data in the Oracle database is updated, the connection may fail and after that all subsequent updated data will get undefined. How can you properly connect to the database so that there are no failures?

oracledb.getConnection(
    {
        user: db.user,
        password: db.password,
        connectString: db.connectString
    },
    connExecute
);

function connExecute(err, connection) {
    if (err) {
        console.error(err.message);
        return;
    }
    sql = `SELECT * FROM db.test`;
    connection.execute(sql, {}, { outFormat: oracledb.OBJECT },
        function (err, db) {
            if (err) {
                console.error(err.message);
                connRelease(connection);
                return;
            }
            // data update loop

            connRelease(connection);
        });
}

function connRelease(connection) {
    connection.close(
        function (err) {
            if (err) {
                console.error(err.message);
            }
        });
}

Dan McGhan · Accepted Answer · 2019-03-18 17:40:14Z

1

You should be using a connection pool. Connection pools have built-in logic to detect connections with issues and create new connections transparently. See this series on creating a REST API for more details: https://jsao.io/2018/03/creating-a-rest-api-with-node-js-and-oracle-database/

Keep in mind that issues can still happen, so you have to handle errors as needed for your application.

answered Mar 18, 2019 at 17:40

Dan McGhan

4,6691 gold badge14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Azeem Aslam · Accepted Answer · 2019-03-18 11:55:24Z

Mostly you add listener on connection object and on dissociation or failure again create connection. With minor changes you can adopt this approach and use listeners to check if connection is available if not connect again. There could be several reason that results in connection closing better handle exceptions, check if still connected and reconnect in case of error.

Or you can try this NPM this will do reconnection for you https://www.npmjs.com/package/oracledb-autoreconnect

Ping me if you need calcification.

var dbConfig = {
    host: '----',
    user: '----',
    password: '----',
    database: '----',
    port: ----
};

var connection;
function handleDisconnect() {
connection = <obj>.getConnection(dbConfig);  
// Recreate the connection, since the old one cannot be reused.
connection.connect( function onConnect(err) {   
// The server is either down
    if (err) {                                  
// or restarting (takes a while sometimes).
        console.log('error when connecting to db:', err);
        setTimeout(handleDisconnect, 10000);
// We introduce a delay before attempting to reconnect,
    }                                           


 // to avoid a hot loop, and to allow our node script to
    });                                        
     // process asynchronous requests in the meantime.

     // If you're also serving http, display a 503 error.
        connection.on('error', function onError(err) {
        console.log('db error', err);
        if (err.code == 'PROTOCOL_CONNECTION_LOST') {
            handleDisconnect();                       
      // lost due to either server restart, or a
        } else {                                  
          // connnection idle timeout (the wait_timeout
            throw err;                          
            // server variable configures this)
            }
        });
    }
    handleDisconnect();

Collectives™ on Stack Overflow

How to avoid a broken connection with ORACLEDB? Nodejs

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related