CODEWITHSUNDEEP
node.jsnode-oracledb
user11220243
user11220243

Reputation: 55

How to avoid a broken connection with ORACLEDB? Nodejs

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);
            }
        });
}

Upvotes: 2

Views: 7064

Answers (2)

Dan McGhan
Dan McGhan

Reputation: 4659

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.

Upvotes: 1

Azeem Aslam
Azeem Aslam

Reputation: 544

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

Upvotes: 0

Related Questions