/
webapppromises.js
227 lines (206 loc) · 7.61 KB
/
webapppromises.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
/* Copyright (c) 2015, 2019, Oracle and/or its affiliates. All rights reserved. */
/******************************************************************************
*
* You may not use the identified files except in compliance with the Apache
* License, Version 2.0 (the "License.")
*
* You may obtain a copy of the License at
* http://www.apache.org/licenses/LICENSE-2.0.
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*
* See the License for the specific language governing permissions and
* limitations under the License.
*
* NAME
* webapppromises.js
*
* DESCRIPTION
* Shows a web based query using connections from connection pool. This is
* similar to webapp.js but uses promises.
*
* This displays a table of employees in the specified department.
*
* The script creates an HTTP server listening on port 7000 and
* accepts a URL parameter for the department ID, for example:
* http://localhost:7000/90
*
* In some networks forced pool termination may hang unless you have
* 'disable_oob=on' in sqlnet.ora, see
* https://oracle.github.io/node-oracledb/doc/api.html#tnsadmin
*
* Uses Oracle's sample HR schema. Scripts to create the HR schema
* can be found at: https://github.com/oracle/db-sample-schemas
*
* This example requires node-oracledb 3 or later.
*
*****************************************************************************/
var http = require('http');
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');
var httpPort = 7000;
// Main entry point. Creates a connection pool, on callback creates an
// HTTP server that executes a query based on the URL parameter given.
// The pool values shown are the default values.
function init() {
oracledb.createPool({
user: dbConfig.user,
password: dbConfig.password,
connectString: dbConfig.connectString
// edition: 'ORA$BASE', // used for Edition Based Redefintion
// events: false, // whether to handle Oracle Database FAN and RLB events or support CQN
// externalAuth: false, // whether connections should be established using External Authentication
// homogeneous: true, // all connections in the pool have the same credentials
// poolAlias: 'default', // set an alias to allow access to the pool via a name.
// poolIncrement: 1, // only grow the pool by one connection at a time
// poolMax: 4, // maximum size of the pool. Increase UV_THREADPOOL_SIZE if you increase poolMax
// poolMin: 0, // start with no connections; let the pool shrink completely
// poolPingInterval: 60, // check aliveness of connection if idle in the pool for 60 seconds
// poolTimeout: 60, // terminate connections that are idle in the pool for 60 seconds
// queueTimeout: 60000, // terminate getConnection() calls in the queue longer than 60000 milliseconds
// sessionCallback: myFunction, // function invoked for brand new connections or by a connection tag mismatch
// stmtCacheSize: 30 // number of statements that are cached in the statement cache of each connection
})
.then(function(pool) {
// Create HTTP server and listen on port - httpPort
http
.createServer(function(request, response) {
handleRequest(request, response, pool);
})
.listen(httpPort);
console.log("Server running at http://localhost:" + httpPort);
})
.catch(function(err) {
console.error("createPool() error: " + err.message);
});
}
function handleRequest(request, response, pool) {
var urlparts = request.url.split("/");
var deptid = urlparts[1];
htmlHeader(
response,
"Oracle Database Driver for Node.js",
"Example using node-oracledb driver"
);
if (deptid == 'favicon.ico') {
htmlFooter(response);
return;
}
if (deptid != parseInt(deptid)) {
handleError(
response,
'URL path "' + deptid + '" is not an integer. Try http://localhost:' + httpPort + '/30',
null
);
return;
}
// Checkout a connection from the pool
pool.getConnection()
.then(function(connection) {
// console.log("Connections open: " + pool.connectionsOpen);
// console.log("Connections in use: " + pool.connectionsInUse);
connection.execute(
`SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = :id`,
[deptid] // bind variable value
)
.then(function(result) {
displayResults(response, result, deptid);
/* Release the connection back to the connection pool */
connection.close()
.then(function() {
htmlFooter(response);
})
.catch(function(err) {
handleError(response, "normal release() error", err);
});
})
.catch(function(err) {
connection.close()
.catch(function(err) {
// Just logging because handleError call below will have already
// ended the response.
console.error("execute() error release() error", err);
});
handleError(response, "execute() error", err);
});
})
.catch(function(err) {
handleError(response, "getConnection() error", err);
});
}
// Report an error
function handleError(response, text, err) {
if (err) {
text += ": " + err.message;
}
console.error(text);
response.write("<p>Error: " + text + "</p>");
htmlFooter(response);
}
// Display query results
function displayResults(response, result, deptid) {
response.write("<h2>" + "Employees in Department " + deptid + "</h2>");
response.write("<table>");
// Column Title
response.write("<tr>");
for (var col = 0; col < result.metaData.length; col++) {
response.write("<th>" + result.metaData[col].name + "</th>");
}
response.write("</tr>");
// Rows
for (var row = 0; row < result.rows.length; row++) {
response.write("<tr>");
for (col = 0; col < result.rows[row].length; col++) {
response.write("<td>" + result.rows[row][col] + "</td>");
}
response.write("</tr>");
}
response.write("</table>");
}
// Prepare HTML header
function htmlHeader(response, title, caption) {
response.writeHead(200, {"Content-Type": "text/html"});
response.write("<!DOCTYPE html>");
response.write("<html>");
response.write("<head>");
response.write("<style>" +
"body {background:#FFFFFF;color:#000000;font-family:Arial,sans-serif;margin:40px;padding:10px;font-size:12px;text-align:center;}" +
"h1 {margin:0px;margin-bottom:12px;background:#FF0000;text-align:center;color:#FFFFFF;font-size:28px;}" +
"table {border-collapse: collapse; margin-left:auto; margin-right:auto;}" +
"td, th {padding:8px;border-style:solid}" +
"</style>\n");
response.write("<title>" + caption + "</title>");
response.write("</head>");
response.write("<body>");
response.write("<h1>" + title + "</h1>");
}
// Prepare HTML footer
function htmlFooter(response) {
response.write("</body>\n</html>");
response.end();
}
function closePoolAndExit() {
console.log("\nTerminating");
try {
// Get the pool from the pool cache and close it when no
// connections are in use, or force it closed after 10 seconds
oracledb.getPool().close(10, function(err) {
if (err)
console.error(err.message);
else
console.log("Pool closed");
process.exit(0);
});
} catch(err) {
console.error(err.message);
process.exit(1);
}
}
process
.once('SIGTERM', closePoolAndExit)
.once('SIGINT', closePoolAndExit);
init();