Even though i am releasing connections after finishing the execution of my query, It is throwing error called "TNS listener could not find available handler with matching protocol stack". For the first 2 to 5 times it is working fine.But, After a few requests(random), its throwing this type of error. Can u help me what might be the issue. I am using express, oracledb and oracle 10g express edition.
Note: I am beginner to node and oracledb
Following is my code, when i get request:
oracledb.createPool({
user : "harsha",
password : "harsha",
connectString : "localhost/XE",
poolMax : 44,
poolMin : 2,
poolIncrement : 5,
poolTimeout : 4
}, function(err, pool) {
if(err) {
errorHandler(res);
console.log("Error in pooling with oracledb"+err);
return false;
}else {
console.log("Connected to oracledb successfully");
//select r, v1.* from (select rownum r , v.* from (select * from employee) v) v1 where r>0 and r<=10;
var gen_query = "select r, v1.* from (select rownum r , v.* from ("+query+") v) v1 where r>"+start_sequence+" and r<="+(end_sequence)+"";
// connection.execute("select count(*) c from "+tname, function(err, result) {
// if(err) {
// res.json({
// dbStatus: 'E',
// data:"Invlaid syntax "+err
// });
// return false;
// }else {
// console.log(result);
// totalRows = result.rows[0].C;
// console.log(totalRows);
// connection.execute(gen_query , function(err, result) {
// if(err) {
// console.log("Error in results"+err);
// res.json({
// dbStatus: 'E',
// data:"Invlaid syntax "+err
// });
// //res.status(200).send(err);
// //errorHandler(res);
// return false;
// }else {
// console.log(result);
// console.log(totalRows);
// if(end_sequence < totalRows)
// {
// res.json({
// dbStatus: 'S',
// data: result,
// moreRowsToFetch: true
// });
// }else {
// res.json({
// dbStatus: 'S',
// data: result,
// moreRowsToFetch: false
// });
// }
// }
// });
// }
// })
pool.getConnection(function(error, connection) {
if(err) {
console.log("Error in connecting to oracledb"+err);
}else {
connection.execute("select count(*) c from "+tname, function(err, result) {
if(err) {
res.json({
dbStatus: 'E',
data:"Invlaid syntax "+err
});
connection.release(function(err) {
if(err) {
console.log("Wrong with releasing connection");
}else {
console.log("Connection is released");
//res.end();
}
})
return false;
}else {
console.log(result);
totalRows = result.rows[0].C;
console.log(totalRows);
connection.execute(gen_query , function(err, result) {
if(err) {
console.log("Error in results"+err);
res.json({
dbStatus: 'E',
data:"Invlaid syntax "+err
});
//res.status(200).send(err);
//errorHandler(res);
}else {
if(end_sequence < totalRows) {
res.json({
dbStatus: 'S',
data: result,
moreRowsToFetch: true
});
}else {
res.json({
dbStatus: 'S',
data: result,
moreRowsToFetch: false
});
}
}
connection.release(function(err) {
if(err) {
console.log("Wrong with releasing connection");
}else {
console.log("Connection is released");
//res.end();
}
})
});
}
})
}
})
}
})
Your pool defaults look like the old example/webapp.js defaults. I'd drop the max pool size for initial testing and when hacking around as a single user. Recently the sample app was changed to use the same lower values as the hardcoded defaults.
Make sure you are up to date with node-oracledb.
You may find using something like 'async' makes handling errors easier. See examples/plsqlarray.js for one sample.
If you are throwing load at an under configured DB, you might see the ORA-12516 error. You might need to bump the DB 'processes' setting, e.g. 'alter system set processes=100 scope=spfile;' I used to see your error with the default Oracle XE 11g when running PHP regression tests.