newTag: function (req, res, next, callback) {
oracledb.getConnection(LoginConf, function(err, connection) {
if (err) { console.error(err.message); return; }
var param = req.query || req.params;
findTag(param, connection, function(TagList) {
if (!Object.keys(TagList).length) {
addTag(param, connection, function(TagID) {
relateTag(param, TagID, connection)
callback(req, res, next, TagID);
});
} else {
var TagID = TagList[0][0];
checkRelation(param, TagID, connection, function(TagIDList) {
if (!Object.keys(TagIDList).length) {
relateTag(param, TagID, connection)
} else {
unrelateTag(param, TagID, connection);
}
callback(req, res, next, TagID);
});
}
});
connection.close();
});
},
before(function(done){
oracledb.getConnection(dbConfig, function(err, conn){
should.not.exist(err);
connection = conn;
done();
});
});
var executeQueries = function(host, port, user, password, db, queries, options, cb) {
oracledb.getConnection(
{
user : user,
password : password,
connectString : host + '/' + db + ':' + port || 1521
},
function(err, connection)
{
if (err) return cb(err);
var result = [];
async.eachSeries(queries, function(query, callback) {
connection.execute(query, [], function(err, queryResult) {
if (err)
result.push(new Error(err));
else
result.push(formatOutput(queryResult));
return callback();
});
}, function() {
connection.release(function(err){
if (err) return cb(err);
return cb(null, result);
});
});
});
}
router.get('/phonerent', function(req, res, next){
oracledb.maxRows=100;
oracledb.getConnection(
{
user : "******",
password : "******",
connectString : "localhost/DBSERVER"
},
function(err, connection)
{
if (err) { console.error(err.message); return; }
connection.execute(
"SELECT * from phone where status=2000",
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
var phones=result.rows;
connection.execute(
"SELECT * from employee",
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
var renters=result.rows;
res.render('rent/phonerent', {emp:req.session, phones:phones, renters:renters});
});
});
});
});
function(cb) {
oracledb.getConnection(dbConfig, function(err, conn) {
should.not.exist(err);
connection = conn;
cb();
});
},
it('45.5 instanceof works for resultset instances', function(done) {
oracledb.getConnection(credential, function(err, conn) {
should.not.exist(err);
conn.execute(
'select 1 from dual union select 2 from dual',
[], // no binds
{
resultSet: true
},
function(err, result) {
should.not.exist(err);
(result.resultSet instanceof oracledb.ResultSet).should.be.true;
result.resultSet.close(function(err) {
should.not.exist(err);
conn.release(function(err) {
should.not.exist(err);
done();
});
});
}
);
});
});
function getOrders(memberId, callback) {
orders = [];
oracledb.getConnection(
{
user: dbConfig.user,
password: dbConfig.password,
connectString: dbConfig.connectString
},
function (err, connection) {
if (err) {
console.error(err.message);
return;
}
var bindvars = {
p_member: memberId,
cursor: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT}
};
connection.execute(
"BEGIN SP_GETORDERSBYMEMBER(:p_member, :cursor); END;",
bindvars,
function (err, result) {
if (err) {
console.error(err.message);
callback(err, null);
doRelease(connection);
return;
}
fetchRowsFromRS(connection, result.outBinds.cursor, numRows, callback);
});
});
}
router.get('/prize/award/delete/:id', function(req, res, next){
var id = req.params.id;
oracledb.getConnection(
{
user : "******",
password : "******",
connectString : "localhost/DBSERVER"
},
function(err, connection)
{
if (err) { console.error(err.message); return; }
connection.execute(
"delete from prize where prizeno="+id, // bind value for :id
function(err, result)
{
if (err) { console.error(err.message); return; }
connection.commit(function(err){
if(err){
res.send("실패했습니다.");
return;
}
});
res.redirect('/people/prize');
});
});
});
router.post('/prize/award/modify/commit/:id', function(req, res, next){
var id = req.params.id;
var winner = req.body.winner;
var prizename = req.body.prizename;
var prizetype = req.body.prizetype;
var now = moment().format("YYYYMMDDhhmmss");
winner = winner.substr(1,winner.toString().indexOf(")")-1);
prizetype = prizetype.substr(1,prizetype.toString().indexOf(")")-1);
oracledb.getConnection(
{
user : "******",
password : "******",
connectString : "localhost/DBSERVER"
},
function(err, connection)
{
if (err) { console.error(err.message); return; }
connection.execute(
"update prize set prizename='"+prizename+"', winner="+winner+", prizedate=TO_DATE('"+now+"','yyyyMMddhh24miss'), type="+prizetype+" where prizeno="+id,
function(err, result)
{
if (err) { console.error(err.message); return; }
connection.commit(function(err){
if(err){
res.send("실패했습니다.");
return;
}
});
console.log(result.rows);
res.redirect('/people/prize');
});
});
});
router.get('/prize/award/:id', function(req, res, next){
var id = req.params.id;
oracledb.maxRows=100;
oracledb.getConnection(
{
user : "******",
password : "******",
connectString : "localhost/DBSERVER"
},
function(err, connection)
{
if (err) { console.error(err.message); return; }
connection.execute(
"SELECT * from employee where empno="+id,
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
var employees=result.rows[0];
connection.execute(
"SELECT * from totalcode where MAJOR='TYPE'",
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
var codes=result.rows;
res.render('management/award', {emp:req.session, employees:employees, codes:codes});
});
});
});
});
router.post('/prize/award/commit/:id', function(req, res, next){
var id = req.params.id;
var prizename = req.body.prizename;
var prizetype = req.body.prizetype;
prizetype = prizetype.substr(1,prizetype.toString().indexOf(")")-1);
var now = moment().format("YYYYMMDDhhmmss");
oracledb.maxRows=100;
oracledb.getConnection(
{
user : "******",
password : "******",
connectString : "localhost/DBSERVER"
},
function(err, connection)
{
if (err) { console.error(err.message); return; }
connection.execute(
"insert into PRIZE (PRIZENAME, WINNER, TYPE, PRIZEDATE) VALUES('"+prizename+"', "+id+", '"+prizetype+"', TO_DATE('"+now+"','yyyyMMddhh24miss'))",
function(err, result)
{
if (err) { console.error(err.message); return; }
connection.commit(function(err){
if(err){
res.send("실패했습니다.");
return;
}
});
console.log(result.rows);
res.redirect('/people/prize');
});
});
});
function(callback){
oracledb.getConnection(credential, function(err, conn){
if(err) { console.error(err.message); return; }
anotherConnection = conn;
callback();
});
}
function getReservations(callback) {
reservations = [];
oracledb.getConnection(
{
user: dbConfig.user,
password: dbConfig.password,
connectString: dbConfig.connectString
},
function (err, connection) {
if (err) {
console.error(err.message);
return;
}
var bindvars = {
cursor: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT}
};
connection.execute(
"BEGIN SP_GETRESERVATIONS(:cursor); END;",
bindvars,
function (err, result) {
if (err) {
console.error(err.message);
callback(err, null);
doRelease(connection);
return;
}
fetchRowsFromRS(connection, result.outBinds.cursor, numRows, callback);
});
});
}
function(cb) {
oracledb.getConnection(credential, function(err, conn) {
should.not.exist(err);
connection = conn;
cb();
});
},
it('45.5 instanceof works for lob instances', function(done) {
oracledb.getConnection(
{
user: dbConfig.user,
password: dbConfig.password,
connectString: dbConfig.connectString
},
function(err, conn) {
should.not.exist(err);
conn.execute(
'select to_clob(dummy) from dual',
function(err, result) {
should.not.exist(err);
(result.rows[0][0] instanceof oracledb.Lob).should.be.true();
conn.release(function(err) {
should.not.exist(err);
done();
});
}
);
}
);
});
router.get('/', function(req, res, next){
oracledb.maxRows=50;
oracledb.getConnection(
{
user : "******",
password : "******",
connectString : "localhost/DBSERVER"
},
function(err, connection)
{
if (err) { console.error(err.message); return; }
connection.execute(
"SELECT * from employee e, totalcode c where e.auth=c.code", // bind value for :id
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
var peoples=result.rows;
connection.execute(
"SELECT * from totalcode where MAJOR='AUTH'", // bind value for :id
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
var codes=result.rows;
res.render('people', {emp:req.session, peoples:peoples, codes:codes});
});
});
});
});
it('45.4 instanceof works for resultset instances', function(done) {
oracledb.getConnection(
{
user: dbConfig.user,
password: dbConfig.password,
connectString: dbConfig.connectString
},
function(err, conn) {
should.not.exist(err);
conn.execute(
'select 1 from dual union select 2 from dual',
[], // no binds
{
resultSet: true
},
function(err, result) {
should.not.exist(err);
(result.resultSet instanceof oracledb.ResultSet).should.be.true();
result.resultSet.close(function(err) {
should.not.exist(err);
conn.release(function(err) {
should.not.exist(err);
done();
});
});
}
);
}
);
});
function execute(queryDate, params, req, res) {
oracledb.getConnection(
{
user: dbConfig.user,
password: dbConfig.password,
connectString: dbConfig.connectString
},
function (err, connection) {
if (err) {
console.error(err.message);
return;
}
console.log('Connection was successful!');
connection.execute( queryDate , [params],
function (err, result) {
if (err) {
console.error(err.message);
return;
}
res.send(result.rows);
doRelease(connection);
});
});
};
function deleteOrder(id, callback) {
oracledb.getConnection(
{
user: dbConfig.user,
password: dbConfig.password,
connectString: dbConfig.connectString
},
function (err, connection) {
if (err) {
console.error(err.message);
return;
}
var bindvars = {
p_id: id
};
connection.execute(
"BEGIN SP_DELETEORDER(:p_id); END;",
bindvars,
{ autoCommit: true },
function (err, result) {
if (err) {
console.error(err.message);
doRelease(connection);
callback(err,result);
return;
}
callback(err,result);
});
});
}
it('5.1 connection should succeed when setting externalAuth to be false and providing user/password', function(done){
oracledb.getConnection(
{
externalAuth: false,
user: dbConfig.user,
password: dbConfig.password,
connectString: dbConfig.connectString
},
function(err, conn){
should.not.exist(err);
conn.should.be.ok();
conn.execute(
"select (7+8) from dual",
function(err, result){
should.not.exist(err);
(result.rows[0][0]).should.equal(15);
conn.release( function(err){
should.not.exist(err);
done();
});
}
);
}
);
})
router.get('/totallist', function(req, res, next){
oracledb.maxRows=100;
oracledb.getConnection(
{
user : "******",
password : "******",
connectString : "localhost/DBSERVER"
},
function(err, connection)
{
if (err) { console.error(err.message); return; }
connection.execute(
"SELECT r.rentno, r.renter, r.description, r.phone, r.usim, r.rentdate, r.returndate, e.empno, e.empname, e.email, p.phoneno, p.phonename, p.osver from rent r, employee e, phone p where r.renter=e.empno and r.phone=p.phoneno order by rentdate desc", // bind value for :id
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
var phonerent=result.rows;
connection.execute(
"SELECT r.rentno, r.renter, r.description, r.phone, r.usim, r.rentdate, r.returndate, e.empno, e.empname, e.email, u.usimidx, u.usimno from rent r, employee e, usim u where r.renter=e.empno and r.usim=u.usimidx order by rentdate desc", // bind value for :id
function(err, result)
{
if (err) { console.error(err.message); return; }
console.log(result.rows);
var usimrent=result.rows;
res.render('rent/totallist', {emp:req.session, phonerents:phonerent, usimrents:usimrent});
});
});
});
});
it('5.3 can get connection from oracledb', function(done){
// console.log(dbConfig);
if(dbConfig.externalAuth){
oracledb.getConnection(
dbConfig,
function(err, connection){
should.not.exist(err);
connection.should.be.ok();
sql = "select (1+4) from dual";
connection.execute(
sql,
function(err, result){
should.not.exist(err);
(result.rows[0][0]).should.equal(5);
connection.release(function(err){
should.not.exist(err);
done();
});
}
);
}
);
} else {
// console.log("External Authentication Off.");
done();
}
})
before('get one connection', function(done) {
oracledb.getConnection(credential, function(err, conn) {
should.not.exist(err);
connection = conn;
done();
});
})
module.exports = function(config) {
"use strict";
const
oracledb = require('oracledb'),
express = require('express'),
path = require("path");
let
app = express();
oracledb.getConnection(config.oracleServer, function(err, connection) {
connection.execute("SELECT * FROM widgets", function(err, result) {
if (err) { console.error(err.message); return; }
console.log(result.rows.length);
});
});
app.use(express.static(path.join(__dirname, config.webServer.folder)));
app.listen(config.webServer.port, function() {
console.log('Running a web server on port ' + config.webServer.port);
});
};
it('3.2.1 shows the node-oracledb version attributes', function(done){
var addonVer, clientVer, serverVer;
addonVer = oracledb.version;
(addonVer).should.be.a.Number();
(addonVer).should.be.greaterThan(0);
clientVer = oracledb.oracleClientVersion;
(clientVer).should.be.a.Number();
(clientVer).should.be.greaterThan(0);
oracledb.getConnection(
dbConfig,
function(err, connection) {
should.not.exist(err);
serverVer = connection.oracleServerVersion;
(serverVer).should.be.a.Number();
connection.close(function(err) {
should.not.exist(err);
done();
});
}
);
});
function handleDatabaseOperation( request, response, callback) {
//connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING || "140.86.4.91:1521/demos.lucasjellema.oraclecloud.internal",
// var connectString = process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR.replace("PDB1", "demos");
var connectString = "140.86.4.91:1521/demos.lucasjellema.oraclecloud.internal";
if (process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR) { connectString = process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR.replace("PDB1", "demos");}
console.log('ConnectString :' + connectString);
oracledb.getConnection(
{
user : process.env.DB_USER || "hr",
password : process.env.DB_PASSWORD || "hr",
connectString : connectString
},
function(err, connection)
{
if (err) {
console.log('Error in acquiring connection ...');
console.log('Error message '+err.message);
return;
}
// do with the connection whatever was supposed to be done
console.log('Connection acquired ; go execute - call callback ');
callback(request, response, connection);
});
}//handleDatabaseOperation
before(function(done){
oracledb.getConnection(dbConfig, function(err, conn){
if(err) { console.error(err.message); return; }
connection = conn;
done();
});
});
before(function(done) {
oracledb.getConnection(credential, function(err, conn) {
if(err) { console.error(err.message); return; }
connection = conn;
assist.setup(connection, tableName, sqlCreate, strs, done);
});
})
function getTicketsForMovie(movieId, callback) {
tickets = [];
oracledb.getConnection(
{
user: dbConfig.user,
password: dbConfig.password,
connectString: dbConfig.connectString
},
function (err, connection) {
if (err) {
console.error(err.message);
return;
}
var bindvars = {
p_movie: movieId,
cursor: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT}
};
connection.execute(
"BEGIN SP_GETTICKETSFORMOVIE(:p_movie, :cursor); END;",
bindvars,
function (err, result) {
if (err) {
console.error(err.message);
callback(err, null);
doRelease(connection);
return;
}
fetchRowsFromRS(connection, result.outBinds.cursor, numRows, callback);
});
});
}
router.get("/updateScore", function(req, res, next){
var customerNo = req.query.customerNo;
var nextScore = req.query.nextScore;
oracledb.getConnection(
{
user : "******",
password : "******",
connectString : "xxxxx"
},
function(err, connection)
{
if (err) { console.error(err.message); res.render('result', {result: 'Oracle error!'}); return; }
connection.execute(
"UPDATE account_score SET score = :score WHERE account_number = :accNo",
{score: nextScore, accNo: customerNo},
{autoCommit : true},
function(err, result)
{
if (err) {
console.error(err.message);
}
else {
console.log("Rows updated " + result.rowsAffected);
res.render('result', {result: nextScore, customerNo: customerNo});
doRelease(connection); //!!
}
});
});
});