Skip to content

Commit b7dccd6

Browse files
authored
Update node-oracledb examples for the latest 3.1 release (oracle-samples#52)
1 parent bab5f84 commit b7dccd6

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

49 files changed

+853
-115
lines changed

javascript/node-oracledb/README.md

Lines changed: 17 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
# Node-oracledb Examples
22

3-
This directory contains [node-oracledb 3.0](https://www.npmjs.com/package/oracledb) examples.
3+
This directory contains [node-oracledb 3.1](https://www.npmjs.com/package/oracledb) examples.
44

55
The node-oracledb add-on for Node.js powers high performance Oracle Database applications.
66

@@ -11,7 +11,7 @@ The node-oracledb add-on for Node.js powers high performance Oracle Database app
1111
Issues and questions about node-oracledb can be posted on
1212
[GitHub](https://github.com/oracle/node-oracledb/issues) or
1313
[Slack](https://node-oracledb.slack.com/) ([link to join
14-
Slack](https://join.slack.com/t/node-oracledb/shared_invite/enQtNDI4NTUyNjMzMDA5LWRiZWRkZjQ3NjBhNDUwOGJlNDFiZWJhZTIzYTJkMWQ5N2UwNTg5NzNmNmY1YmZjZGYxNmRhOTkyOTlhMmViNjY)).
14+
Slack](https://node-oracledb.slack.com/join/shared_invite/enQtNDU4Mjc2NzM5OTA2LTdkMzczODY3OGY3MGI0Yjk3NmQ4NDU4MTI2OGVjNTYzMjE5OGY5YzVkNDY4MWNkNjFiMDM2ZDMwOWRjNWVhNTg).
1515

1616
To run the examples:
1717

@@ -22,19 +22,31 @@ To run the examples:
2222
example, to load them in the HR schema run:
2323

2424
```
25-
sqlplus hr/welcome@localhost/orclpdb @demo.sql
25+
sqlplus hr
26+
SQL> @demo.sql
2627
```
2728

28-
- Edit `dbconfig.js` and set your username, password and the database
29+
- Edit `dbconfig.js` and set your username and the database
2930
connection string:
3031

3132
```
3233
module.exports = {
3334
user: "hr",
34-
password: "welcome",
35+
password: process.env.NODE_ORACLEDB_PASSWORD,
3536
connectString:"localhost/orclpdb"
3637
};
38+
```
39+
40+
- Set the environment variable `NODE_ORACLEDB_PASSWORD` to your database schema password.
41+
42+
On Windows:
43+
```
44+
set NODE_ORACLEDB_PASSWORD=...
45+
```
3746

47+
On Linux:
48+
```
49+
export NODE_ORACLEDB_PASSWORD=...
3850
```
3951

4052
- Then run the samples like:

javascript/node-oracledb/calltimeout.js

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2018, Oracle and/or its affiliates. All rights reserved. */
1+
/* Copyright (c) 2018, 2019, Oracle and/or its affiliates. All rights reserved. */
22

33
/******************************************************************************
44
*
@@ -21,10 +21,12 @@
2121
* DESCRIPTION
2222
* Shows how to time out long running database calls.
2323
* See https://oracle.github.io/node-oracledb/doc/api.html#dbcalltimeouts
24-
* Node-oracledb must be using Oracle Client 18c libraries, or greater.
2524
*
2625
* This example uses Async/Await of Node 8.
2726
*
27+
* This example requires node-oracledb 3 or later.
28+
* Node-oracledb must be using Oracle Client 18c libraries, or greater.
29+
*
2830
*****************************************************************************/
2931

3032
let oracledb = require("oracledb");

javascript/node-oracledb/connect.js

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,8 @@
2222
* Tests a basic connection to the database.
2323
* See dbconfig.js for information on connectString formats.
2424
*
25+
* For a connection pool example see connectionpool.js
26+
*
2527
*****************************************************************************/
2628

2729
var oracledb = require('oracledb');
Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,118 @@
1+
/* Copyright (c) 2018, 2019, Oracle and/or its affiliates. All rights reserved. */
2+
3+
/******************************************************************************
4+
*
5+
* You may not use the identified files except in compliance with the Apache
6+
* License, Version 2.0 (the "License.")
7+
*
8+
* You may obtain a copy of the License at
9+
* http://www.apache.org/licenses/LICENSE-2.0.
10+
*
11+
* Unless required by applicable law or agreed to in writing, software
12+
* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
13+
* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14+
*
15+
* See the License for the specific language governing permissions and
16+
* limitations under the License.
17+
*
18+
* NAME
19+
* connectionpool.js
20+
*
21+
* DESCRIPTION
22+
* Shows connection pool usage. Connection pools are recommended
23+
* for applications that use a lot of connections for short periods.
24+
*
25+
* This example uses Node 8's async/await syntax.
26+
*
27+
* Other connection pool examples are in sessionfixup.js, webapp.js,
28+
* webapppromises.js and webappawait.js
29+
*
30+
* For a standalone connection example, see connect.js
31+
*
32+
* In some networks forced pool termination may hang unless you have
33+
* 'disable_oob=on' in sqlnet.ora, see
34+
* https://oracle.github.io/node-oracledb/doc/api.html#tnsadmin
35+
*
36+
*****************************************************************************/
37+
38+
const oracledb = require('oracledb');
39+
const dbConfig = require('./dbconfig.js');
40+
41+
async function init() {
42+
try {
43+
// Create a connection pool which will later be accessed via the
44+
// pool cache as the 'default' pool.
45+
await oracledb.createPool({
46+
user: dbConfig.user,
47+
password: dbConfig.password,
48+
connectString: dbConfig.connectString
49+
// edition: 'ORA$BASE', // used for Edition Based Redefintion
50+
// events: false, // whether to handle Oracle Database FAN and RLB events or support CQN
51+
// externalAuth: false, // whether connections should be established using External Authentication
52+
// homogeneous: true, // all connections in the pool have the same credentials
53+
// poolAlias: 'default', // set an alias to allow access to the pool via a name.
54+
// poolIncrement: 1, // only grow the pool by one connection at a time
55+
// poolMax: 4, // maximum size of the pool. Increase UV_THREADPOOL_SIZE if you increase poolMax
56+
// poolMin: 0, // start with no connections; let the pool shrink completely
57+
// poolPingInterval: 60, // check aliveness of connection if idle in the pool for 60 seconds
58+
// poolTimeout: 60, // terminate connections that are idle in the pool for 60 seconds
59+
// queueTimeout: 60000, // terminate getConnection() calls in the queue longer than 60000 milliseconds
60+
// sessionCallback: myFunction, // function invoked for brand new connections or by a connection tag mismatch
61+
// stmtCacheSize: 30 // number of statements that are cached in the statement cache of each connection
62+
});
63+
console.log('Connection pool started');
64+
65+
// Now the pool is running, it can be used
66+
await dostuff();
67+
68+
} catch (err) {
69+
console.error('init() error: ' + err.message);
70+
} finally {
71+
await closePoolAndExit();
72+
}
73+
}
74+
75+
async function dostuff() {
76+
let connection;
77+
try {
78+
// Get a connection from the default pool
79+
connection = await oracledb.getConnection();
80+
let sql = `SELECT sysdate FROM dual WHERE :b = 1`;
81+
let binds = [1];
82+
let options = { outFormat: oracledb.OBJECT };
83+
let result = await connection.execute(sql, binds, options);
84+
console.log(result);
85+
} catch (err) {
86+
console.error(err);
87+
} finally {
88+
if (connection) {
89+
try {
90+
// Put the connection back in the pool
91+
await connection.close();
92+
} catch (err) {
93+
console.error(err);
94+
}
95+
}
96+
}
97+
}
98+
99+
async function closePoolAndExit() {
100+
console.log('\nTerminating');
101+
try {
102+
// Get the pool from the pool cache and close it when no
103+
// connections are in use, or force it closed after 10 seconds
104+
// If this hangs, you may need DISABLE_OOB=ON in a sqlnet.ora file
105+
await oracledb.getPool().close(10);
106+
console.log('Pool closed');
107+
process.exit(0);
108+
} catch(err) {
109+
console.error(err.message);
110+
process.exit(1);
111+
}
112+
}
113+
114+
process
115+
.once('SIGTERM', closePoolAndExit)
116+
.once('SIGINT', closePoolAndExit);
117+
118+
init();

javascript/node-oracledb/cqn1.js

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2018, Oracle and/or its affiliates. All rights reserved. */
1+
/* Copyright (c) 2018, 2019, Oracle and/or its affiliates. All rights reserved. */
22

33
/******************************************************************************
44
*
@@ -27,6 +27,8 @@
2727
*
2828
* This example uses Node 8 syntax, but could be written to use callbacks.
2929
*
30+
* This example requires node-oracledb 2.3 or later.
31+
*
3032
*****************************************************************************/
3133

3234
const oracledb = require("oracledb");

javascript/node-oracledb/cqn2.js

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2018, Oracle and/or its affiliates. All rights reserved. */
1+
/* Copyright (c) 2018, 2019, Oracle and/or its affiliates. All rights reserved. */
22

33
/******************************************************************************
44
*
@@ -28,6 +28,8 @@
2828
*
2929
* This example uses Node 8 syntax, but could be written to use callbacks.
3030
*
31+
* This example requires node-oracledb 2.3 or later.
32+
*
3133
*****************************************************************************/
3234

3335
const oracledb = require("oracledb");

javascript/node-oracledb/date.js

Lines changed: 21 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2015, 2017, Oracle and/or its affiliates. All rights reserved. */
1+
/* Copyright (c) 2015, 2019, Oracle and/or its affiliates. All rights reserved. */
22

33
/******************************************************************************
44
*
@@ -27,10 +27,15 @@
2727
*
2828
*****************************************************************************///
2929

30+
// Using a fixed Oracle time zone helps avoid machine and deployment differences
31+
process.env.ORA_SDTZ = 'UTC';
32+
3033
var async = require('async');
3134
var oracledb = require('oracledb');
3235
var dbConfig = require('./dbconfig.js');
3336

37+
oracledb.outFormat = oracledb.OBJECT;
38+
3439
var doconnect = function(cb) {
3540
oracledb.getConnection(
3641
{
@@ -68,18 +73,20 @@ var docleanup = function (conn, cb) {
6873

6974
var docreate = function(conn, cb) {
7075
conn.execute(
71-
"CREATE TABLE datetest(timestampcol TIMESTAMP, datecol DATE)",
76+
`CREATE TABLE datetest(
77+
timestampcol TIMESTAMP,
78+
timestamptz TIMESTAMP WITH TIME ZONE,
79+
timestampltz TIMESTAMP WITH LOCAL TIME ZONE,
80+
datecol DATE)`,
7281
function(err) {
7382
return cb(err, conn);
7483
});
7584
};
7685

77-
// Setting a local timezone in applications is recommended.
78-
// Note setting the environment variable ORA_SDTZ is an efficient alternative.
7986
var doalter = function(conn, cb) {
8087
console.log('Altering session time zone');
8188
conn.execute(
82-
"ALTER SESSION SET TIME_ZONE='UTC'",
89+
"ALTER SESSION SET TIME_ZONE='+5:00'", // resets ORA_SDTZ value
8390
function(err) {
8491
return cb(err, conn);
8592
});
@@ -92,9 +99,9 @@ var doinsert = function(conn, cb) {
9299
console.log("Inserting JavaScript date: " + date);
93100

94101
conn.execute(
95-
"INSERT INTO datetest (timestampcol, datecol) VALUES (:ts, :td)",
96-
{ ts: date,
97-
td: date },
102+
`INSERT INTO datetest (timestampcol, timestamptz, timestampltz, datecol)
103+
VALUES (:ts, :tstz, :tsltz, :td)`,
104+
{ ts: date, tstz: date, tsltz: date, td: date },
98105
function(err, result) {
99106
if (err)
100107
return cb(err, conn);
@@ -108,24 +115,21 @@ var doinsert = function(conn, cb) {
108115
// Fetch the dates
109116
var doselect = function(conn, cb) {
110117
conn.execute(
111-
"SELECT timestampcol, datecol FROM datetest",
118+
`SELECT timestampcol, timestamptz, timestampltz, datecol,
119+
TO_CHAR(CURRENT_DATE, 'DD-Mon-YYYY HH24:MI') AS CD
120+
FROM datetest`,
112121
function(err, result) {
113122
if (err) {
114123
return cb(err, conn);
115124
}
116125

117126
console.log("Query Results:");
118-
console.log(result.rows);
127+
console.log(result.rows[0]);
119128

120129
// Show the queried dates are of type Date
121-
console.log("Result Manipulation in JavaScript:");
122-
var ts = result.rows[0][0];
130+
var ts = result.rows[0]['TIMESTAMPCOL'];
123131
ts.setDate(ts.getDate() + 5);
124-
console.log(ts);
125-
126-
var d = result.rows[0][1];
127-
d.setDate(d.getDate() - 5);
128-
console.log(d);
132+
console.log("TIMESTAMP manipulation in JavaScript:", ts);
129133

130134
return cb(null, conn);
131135
});

javascript/node-oracledb/dbconfig.js

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2015, 2017, Oracle and/or its affiliates. All rights reserved. */
1+
/* Copyright (c) 2015, 2019, Oracle and/or its affiliates. All rights reserved. */
22

33
/******************************************************************************
44
*
@@ -36,7 +36,7 @@
3636
* [//]host_name[:port][/service_name][:server_type][/instance_name]
3737
*
3838
* Commonly just the host_name and service_name are needed
39-
* e.g. "localhost/orclpdb" or "localhost/XE"
39+
* e.g. "localhost/orclpdb" or "localhost/XEPDB1"
4040
*
4141
* If using a tnsnames.ora file, the file can be in a default
4242
* location such as $ORACLE_HOME/network/admin/tnsnames.ora or
@@ -74,7 +74,7 @@ module.exports = {
7474
// Instead of hard coding the password, consider prompting for it,
7575
// passing it in an environment variable via process.env, or using
7676
// External Authentication.
77-
password : process.env.NODE_ORACLEDB_PASSWORD || "welcome",
77+
password : process.env.NODE_ORACLEDB_PASSWORD,
7878

7979
// For information on connection strings see:
8080
// https://oracle.github.io/node-oracledb/doc/api.html#connectionstrings

javascript/node-oracledb/em_batcherrors.js

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2018, Oracle and/or its affiliates. All rights reserved. */
1+
/* Copyright (c) 2018, 2019, Oracle and/or its affiliates. All rights reserved. */
22

33
/******************************************************************************
44
*
@@ -25,6 +25,8 @@
2525
* desired.
2626
* Use demo.sql to create the required schema.
2727
*
28+
* This example requires node-oracledb 2.2 or later.
29+
*
2830
*****************************************************************************/
2931

3032
var async = require('async');

javascript/node-oracledb/em_batcherrors_aa.js

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2018, Oracle and/or its affiliates. All rights reserved. */
1+
/* Copyright (c) 2018, 2019, Oracle and/or its affiliates. All rights reserved. */
22

33
/******************************************************************************
44
*
@@ -26,6 +26,8 @@
2626
* This example also uses Async/Await of Node 8.
2727
* Use demo.sql to create the required schema.
2828
*
29+
* This example requires node-oracledb 2.2 or later.
30+
*
2931
*****************************************************************************/
3032

3133
var oracledb = require('oracledb');

0 commit comments

Comments
 (0)