Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Raj.

Asked: February 18, 2021 - 9:18 pm UTC

Last updated: February 23, 2021 - 7:24 am UTC

Version: 12.2 EBS

Viewed 100+ times

You Asked

We are using Node-oracledb 4.0 driver to develop rest APIs for Oracle EBS 12.2 and 12.1
We followed the sample code snippets from Oracle Node documentation to open the connection pool, close the connection, and execute the Queries.
We are using Express Server and notice some reliability issues with Node Server. We could not find out the exact issues.

Just wanted to ask the following questions:
1. Are there any known reliability issues?
2. Is the Node-OracleDB driver has the capability to deploy in the Production Environment?
3. Currently we have 50 open connections in the Pool. We have some clients with 1000 concurrent users. How do we scale the connection pool to this many users?
4. Any best practices/case studies to make Node reliable?
5. Any comparisons between ISG and Node? Since we are having lot of node issues, we are thinking of developing apps using ISG.

Appreciate your help, Thanks a lot.

Query Execution Code:
const executeSQLQuery = (query, params, options = {}) => {
  return new Promise(async (resolve, reject) => {
      let conn;
      let throwTimeOutError = true;
      console.log('@@@@@  QUERY EXECUTING @@@@: ', query, params);
      try {
        console.log('connecting to pool...',oracledb.poolAlias);
        conn = await oracledb.getConnection('PoolConn122');
        //console.log('connecting to pool...',oracledb.poolAlias);
        console.log('connection details:',conn);  
        let result;
        setTimeout(()=>{
          if(throwTimeOutError){
            //console.log('came');
            reject({message:'Check whether Params and PLSQL Query are valid or not'});
          }
        },60000);
        result = await conn.execute(query, params);
        //console.log('executed');
        throwTimeOutError = false;
        resolve(result);
      } catch (err) {
        reject(err);
        throwTimeOutError = false;
        console.log(err,'coming');
      } finally {
        if (conn) { // conn assignment worked, need to close
          try {
            console.log('conn pool:',conn.getPool);
                       await conn.close();
            
            console.log('closing connection new SQL');
          } catch (err) {
            console.log(err);
          }
        }
      }    
     
  });
}


Connection Pool:
module.exports = {    
    hrPool: {
      user: process.env.user || "xxxx",
      password: process.env.password || "xxxx",
      connectionString: process.env.connectionString || "xxxxxx",
      threadPoolSize: process.env.UV_THREADPOOL_SIZE || 60,
      poolMin: 50,
      poolMax: 50,
      poolIncrement: 0,
      queueTimeout:240000,
      poolAlias:'PoolConn122',
      _enableStats:true
    }
  };

API - Service:
async function getLocators(req, res, next) {
    let output;
    let query;
    let paramsForQuery;
    
    try {       
        const reqParams = req.params;   
        const reqQuery = req.query;
        if(null!=reqParams){
            query = masterDataQueries.getLocatorsQuery20D; 
            let model=getLocatorsModel20D;
            if(reqQuery.hasOwnProperty('system') && Object.is(reqQuery['system'],'CLD')){
                cloudApiResult = await modelutil.callCloudApi(req.url,model);
                  if(!cloudApiResult['error']){
                     res.status(200);
                     res.send(cloudApiResult['response']);
                     }
                 }
                 else{
                    paramsForQuery = modelutil.constructParamsForQuery(reqParams.orgId,
                        reqParams.lastSyncTime,
                        reqParams.isFullRefresh,
                     'Response');  
                   const result =  await sqlutils.executeSQLQuery(query,paramsForQuery);                

                   if(null!=result.outBinds.Response){
                   output = JSON.parse(result.outBinds.Response); 
//console.log("@@@@@@" , output);       

             if(null!=output.data) {                  
              output = output.data;
              output = modelutil.assignOutputToModel(model, output);              
              output = {
              ActiveLocators: output,
              success: true,
              }
               res.status(200);
               res.send(output);
            }else {                   
                 modelutil.showEmptyOutputInResponse(res);                    
                }
                }else { //when output is empty
                 modelutil.showEmptyOutputInResponse(res);                
                  }    
                 }
    
        }      
    } catch (error) {        
        const request = {
        params:req.params,
        body:req.body
    }    
    const errorHandlingDetails = modelutil.showErrorOutputInResponse(error,query,paramsForQuery,request);      
    res.status(errorHandlingDetails['status']);
   req.log.error(errorHandlingDetails['errorDetails']); 
   res.send(errorHandlingDetails['output']);       
    }
}



and we said...

1. Are there any known reliability issues?

The node driver team keep an updated issues document for the driver here

https://github.com/oracle/node-oracledb/issues

2. Is the Node-OracleDB driver has the capability to deploy in the Production Environment?

Yes. Many customer are actively in production with it.

3. Currently we have 50 open connections in the Pool. We have some clients with 1000 concurrent users. How do we scale the connection pool to this many users?

Great set of information here about how to utilise pooling correctly; https://oracle.github.io/node-oracledb/doc/api.html#connpooling

4. Any best practices/case studies to make Node reliable?

Some good Oracle blogs out there on all of this

https://jsao.io/
https://blogs.oracle.com/opal/

5. Any comparisons between ISG and Node? Since we are having lot of node issues, we are thinking of developing apps using ISG.

I don't know anything about ISG, but if you're looking at REST enablement of facilities, the obvious candidate is not Node or ISG but ORDS. That's exactly what it is designed for,

https://www.oracle.com/database/technologies/appdev/rest.html