Oracle DB | Node.js - Insertion Event Tracking-CodePudding

DB Team inserts new data into a table. If new data is inserted I do need to send messages.

Is there any way I could track new data using Nodejs. There is no specific duration for data insertion.

CodePudding user response：

If your DB is remoteDB then you do require full-duplex connection.

After getting successful connections from the telnet from both servers do as follows:

const connection = await oracledb.getConnection({
    user          : user,
    password      : password,  
    connectString : connectString,
    events        : true
});

function myCallback(message) {
    console.log('CQN Triggered');
}

const options = {
     callback: myCallback,                     // method called by notifications        
     sql: `SELECT ID FROM table where STATUS= 0`,   // query
     timeout: 600,
     qos :  oracledb.SUBSCR_QOS_ROWIDS, // SUBSCR_QOS_QUERY: generate notifications when new rows with STATUS= 0 are found
     clientInitiated : true           //By Default it's false.
};

await connection.subscribe('mysub', options);

CodePudding user response：

See the node-oracledb documentation on Continuous Query Notification, which lets your Node.js app be notified if data has changed in the database.

There are examples in cqn1.js and cqn2.js. When using Oracle Database and Oracle client libraries 19.4, or later, you'll find testing easier if you set the optional subscriptions attribute clientInitiated property to true:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "localhost/XEPDB1",
    events        : true
});

function myCallback(message) {
    console.log(message);
}

const options = {
    sql            : `SELECT * FROM mytable`,   // query of interest
    callback       : myCallback,                // method called by notifications
    clientInitated : true
};

await connection.subscribe('mysub', options);

You could also look at Advanced Queuing as another way to propagate messages, though you would still need to use something like a table trigger or CQN to initiate an AQ message.