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