Skip to content

Commit bc63c21

Browse files
cjbjcjbj
authored
Merge pull request oracle-samples#24 from oracle/node-oracledb-v2
Update examples for node-oracledb version 2
2 parents 6284b63 + 9782cd4 commit bc63c21

34 files changed

+417
-289
lines changed

‎javascript/node-oracledb/README.md

Lines changed: 38 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,45 @@
1-
These are examples for the [node-oracledb 1.13 driver](https://www.npmjs.com/package/oracledb)
1+
# Node-oracledb Examples
22

3-
The node-oracledb add-on for Node.js powers high performance Oracle Database applications.
3+
This directory contains [node-oracledb 2.0](https://www.npmjs.com/package/oracledb) examples.
44

5-
[node-oracledb installation instructions](https://github.com/oracle/node-oracledb/blob/master/INSTALL.md)
5+
The node-oracledb add-on for Node.js powers high performance Oracle Database applications.
66

77
[Node-oracledb documentation](https://github.com/oracle/node-oracledb/blob/master/doc/api.md)
88

99
[Issues and questions](https://github.com/oracle/node-oracledb/issues)
1010

11-
After installing node-oracledb, the demos can be run by editing
12-
dbconfig.js to set your database credentials, creating the schema
13-
objects with demo.sql, and then executing each JavaScript file with
14-
Node.js.
11+
To run the examples:
12+
13+
- [Install node-oracledb](https://github.com/oracle/node-oracledb/blob/master/INSTALL.md).
14+
15+
16+
- Use `demo.sql` to create schema objects used by the samples. For
17+
example, to load them in the HR schema run:
18+
19+
```
20+
sqlplus hr/welcome@localhost/orclpdb @demo.sql
21+
```
22+
23+
- Edit `dbconfig.js` and set your username, password and the database
24+
connection string:
25+
26+
```
27+
module.exports = {
28+
user: "hr",
29+
password: "welcome",
30+
connectString:"localhost/orclpdb"
31+
};
32+
33+
```
34+
35+
- Then run the samples like:
36+
37+
```
38+
node select1.js
39+
```
40+
41+
The demonstration objects can be dropped with `demodrop.sql`:
42+
43+
```
44+
sqlplus hr/welcome@localhost/orclpdb @demodrop.sql
45+
```

‎javascript/node-oracledb/blobhttp.js

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -57,10 +57,10 @@ function init() {
5757

5858
// Create HTTP server and listen on port 'httpPort'
5959
http
60-
.createServer(function(request, response) {
61-
handleRequest(request, response);
62-
})
63-
.listen(httpPort);
60+
.createServer(function(request, response) {
61+
handleRequest(request, response);
62+
})
63+
.listen(httpPort);
6464

6565
console.log("Server running. Try requesting: http://localhost:" + httpPort + "/getimage");
6666
});
@@ -140,13 +140,13 @@ function handleRequest(request, response) {
140140
}
141141

142142
process
143-
.on('SIGTERM', function() {
144-
console.log("\nTerminating");
145-
process.exit(0);
146-
})
147-
.on('SIGINT', function() {
148-
console.log("\nTerminating");
149-
process.exit(0);
150-
});
143+
.on('SIGTERM', function() {
144+
console.log("\nTerminating");
145+
process.exit(0);
146+
})
147+
.on('SIGINT', function() {
148+
console.log("\nTerminating");
149+
process.exit(0);
150+
});
151151

152152
init();

‎javascript/node-oracledb/date.js

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

33
/******************************************************************************
44
*
@@ -50,17 +50,17 @@ var dorelease = function(conn) {
5050

5151
var docleanup = function (conn, cb) {
5252
conn.execute(
53-
"BEGIN " +
54-
" DECLARE" +
55-
" e_table_exists EXCEPTION;" +
56-
" PRAGMA EXCEPTION_INIT(e_table_exists, -00942);" +
57-
" BEGIN" +
58-
" EXECUTE IMMEDIATE ('DROP TABLE datetest');" +
59-
" EXCEPTION" +
60-
" WHEN e_table_exists" +
61-
" THEN NULL;" +
62-
" END; " +
63-
"END;",
53+
`BEGIN
54+
DECLARE
55+
e_table_exists EXCEPTION;
56+
PRAGMA EXCEPTION_INIT(e_table_exists, -00942);
57+
BEGIN
58+
EXECUTE IMMEDIATE ('DROP TABLE datetest');
59+
EXCEPTION
60+
WHEN e_table_exists
61+
THEN NULL;
62+
END;
63+
END;`,
6464
function(err) {
6565
return cb(err, conn);
6666
});

‎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, Oracle and/or its affiliates. All rights reserved. */
1+
/* Copyright (c) 2015, 2017, Oracle and/or its affiliates. All rights reserved. */
22

33
/******************************************************************************
44
*
@@ -34,7 +34,7 @@
3434
* [//]host_name[:port][/service_name][:server_type][/instance_name]
3535
*
3636
* Commonly just the host_name and service_name are needed
37-
* e.g. "localhost/orcl" or "localhost/XE"
37+
* e.g. "localhost/orclpdb" or "localhost/XE"
3838
*
3939
* If using a tnsnames.ora file, the file can be in a default
4040
* location such as $ORACLE_HOME/network/admin/tnsnames.ora or
@@ -76,7 +76,7 @@ module.exports = {
7676

7777
// For information on connection strings see:
7878
// https://github.com/oracle/node-oracledb/blob/master/doc/api.md#connectionstrings
79-
connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING || "localhost/orcl",
79+
connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING || "localhost/orclpdb",
8080

8181
// Setting externalAuth is optional. It defaults to false. See:
8282
// https://github.com/oracle/node-oracledb/blob/master/doc/api.md#extauth

‎javascript/node-oracledb/dbmsoutputgetline.js

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

33
/******************************************************************************
44
*
@@ -65,10 +65,10 @@ var enableDbmsOutput = function (conn, cb) {
6565

6666
var createDbmsOutput = function (conn, cb) {
6767
conn.execute(
68-
"BEGIN " +
69-
"DBMS_OUTPUT.PUT_LINE('Hello, Oracle!');" +
70-
"DBMS_OUTPUT.PUT_LINE('Hello, Node!');" +
71-
"END;",
68+
`BEGIN
69+
DBMS_OUTPUT.PUT_LINE('Hello, Oracle!');
70+
DBMS_OUTPUT.PUT_LINE('Hello, Node!');
71+
END;`,
7272
function(err) { return cb(err, conn); });
7373
};
7474

‎javascript/node-oracledb/dbmsoutputpipe.js

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

33
/******************************************************************************
44
*
@@ -68,7 +68,8 @@ var doit = function(pool) {
6868
enableDbmsOutput,
6969
createDbmsOutput,
7070
fetchDbmsOutput,
71-
printDbmsOutput
71+
printDbmsOutput,
72+
closeRS
7273
],
7374
function (err, conn) {
7475
if (err) { console.error("In waterfall error cb: ==>", err, "<=="); }
@@ -84,10 +85,10 @@ var enableDbmsOutput = function (conn, cb) {
8485

8586
var createDbmsOutput = function (conn, cb) {
8687
conn.execute(
87-
"BEGIN " +
88-
"DBMS_OUTPUT.PUT_LINE('Hello, Oracle!');" +
89-
"DBMS_OUTPUT.PUT_LINE('Hello, Node!');" +
90-
"END;",
88+
`BEGIN
89+
DBMS_OUTPUT.PUT_LINE('Hello, Oracle!');
90+
DBMS_OUTPUT.PUT_LINE('Hello, Node!');
91+
END;`,
9192
function(err) { return cb(err, conn); });
9293
};
9394

@@ -118,12 +119,18 @@ var fetchRowsFromRS = function(conn, resultSet, numRows, cb) {
118119
numRows,
119120
function (err, rows) {
120121
if (err) {
121-
return cb(err, conn);
122+
return cb(err, conn, resultSet);
122123
} else if (rows.length > 0) {
123124
console.log(rows);
124125
return fetchRowsFromRS(conn, resultSet, numRows, cb);
125126
} else {
126-
return cb(null, conn);
127+
return cb(null, conn, resultSet);
127128
}
128129
});
129130
};
131+
132+
var closeRS = function(conn, resultSet, cb) {
133+
resultSet.close(function(err) {
134+
return cb(err, conn);
135+
});
136+
};

‎javascript/node-oracledb/demo.sql

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

33
/******************************************************************************
44
*
@@ -60,7 +60,9 @@ END;
6060
SHOW ERRORS
6161

6262
-- For plsqlarray.js example for PL/SQL 'INDEX BY' array binds
63-
DROP TABLE waveheight;
63+
BEGIN EXECUTE IMMEDIATE 'DROP TABLE waveheight'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
64+
/
65+
6466
CREATE TABLE waveheight (beach VARCHAR2(50), depth NUMBER);
6567

6668
CREATE OR REPLACE PACKAGE beachpkg IS
@@ -107,24 +109,29 @@ END;
107109
SHOW ERRORS
108110

109111
-- For selectjson.js example of JSON datatype. Requires Oracle Database 12.1.0.2
110-
DROP TABLE j_purchaseorder;
112+
BEGIN EXECUTE IMMEDIATE 'DROP TABLE j_purchaseorder'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
113+
/
114+
111115
-- Note if your applications always insert valid JSON, you may delete
112116
-- the IS JSON check to remove its additional validation overhead.
113117
CREATE TABLE j_purchaseorder (po_document VARCHAR2(4000) CHECK (po_document IS JSON));
114118

115-
-- For selectjsonclob.js example of JSON datatype. Requires Oracle Database 12.1.0.2
116-
DROP TABLE j_purchaseorder_c;
117-
CREATE TABLE j_purchaseorder_c (po_document CLOB CHECK (po_document IS JSON));
119+
-- For selectjsonblob.js example of JSON datatype. Requires Oracle Database 12.1.0.2
120+
BEGIN EXECUTE IMMEDIATE 'DROP TABLE j_purchaseorder_b'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
121+
/
122+
CREATE TABLE j_purchaseorder_b (po_document BLOB CHECK (po_document IS JSON)) LOB (po_document) STORE AS (CACHE);
118123

119124
-- For DML RETURNING aka RETURNING INTO examples
120-
DROP TABLE dmlrupdtab;
125+
BEGIN EXECUTE IMMEDIATE 'DROP TABLE dmlrupdtab'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
126+
/
121127
CREATE TABLE dmlrupdtab (id NUMBER, name VARCHAR2(40));
122128
INSERT INTO dmlrupdtab VALUES (1001, 'Venkat');
123129
INSERT INTO dmlrupdtab VALUES (1002, 'Neeharika');
124130
COMMIT;
125131

126132
-- For LOB examples
127-
DROP TABLE mylobs;
133+
BEGIN EXECUTE IMMEDIATE 'DROP TABLE mylobs'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
134+
/
128135
CREATE TABLE mylobs (id NUMBER, c CLOB, b BLOB);
129136

130137
-- For lobbinds.js: Procedure to show IN bind support for LOBs
@@ -173,5 +180,6 @@ END;
173180
SHOW ERRORS
174181

175182
-- For raw1.js
176-
DROP TABLE myraw;
183+
BEGIN EXECUTE IMMEDIATE 'DROP TABLE myraw'; EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
184+
/
177185
CREATE TABLE myraw (r RAW(64));

‎javascript/node-oracledb/demodrop.sql

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

33
/******************************************************************************
44
*
@@ -35,7 +35,7 @@ DROP PACKAGE beachpkg;
3535

3636
DROP TABLE j_purchaseorder;
3737

38-
DROP TABLE j_purchaseorder_c;
38+
DROP TABLE j_purchaseorder_b;
3939

4040
DROP TABLE dmlrupdtab;
4141

@@ -49,8 +49,6 @@ DROP TABLE myraw;
4949

5050
DROP TABLE waveheight;
5151

52-
DROP PROCEDURE lob_out;
53-
5452
DROP PROCEDURE lob_in_out;
5553

5654
DROP PROCEDURE lobs_in;

‎javascript/node-oracledb/dmlrupd1.js

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

33
/******************************************************************************
44
*
@@ -19,7 +19,11 @@
1919
* dmlrupd1.js
2020
*
2121
* DESCRIPTION
22-
* Example of 'DML Returning' with a single row match
22+
* Example of 'DML Returning' with a single row match.
23+
* The ROWID of the changed record is returned. This is how to get
24+
* the 'last insert id'.
25+
* Bind names cannot be reused in the DML section and the RETURNING section.
26+
*
2327
* Use demo.sql to create the required table or do:
2428
* DROP TABLE dmlrupdtab;
2529
* CREATE TABLE dmlrupdtab (id NUMBER, name VARCHAR2(40));
@@ -47,12 +51,11 @@ oracledb.getConnection(
4751
}
4852

4953
connection.execute(
50-
"UPDATE DMLRUPDTAB SET NAME = :name WHERE ID = :id RETURNING ID, NAME INTO :RID, :RNAME",
54+
"UPDATE dmlrupdtab SET name = :name WHERE id = :id RETURNING ROWID INTO :rid",
5155
{
5256
id: 1001,
5357
name: "Krishna",
54-
rid: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
55-
rname: { type: oracledb.STRING, dir: oracledb.BIND_OUT }
58+
rid: { type: oracledb.STRING, dir: oracledb.BIND_OUT }
5659
},
5760
{ autoCommit: true },
5861
function(err, result)

‎javascript/node-oracledb/dmlrupd2.js

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

33
/******************************************************************************
44
*
@@ -19,7 +19,11 @@
1919
* dmlrupd2.js
2020
*
2121
* DESCRIPTION
22-
* Example of 'DML Returning' with multiple rows matched
22+
* Example of 'DML Returning' with multiple rows matched.
23+
* The ROWIDs of the changed records are returned. This is how to get
24+
* the 'last insert id'.
25+
* Bind names cannot be reused in the DML section and the RETURNING section.
26+
*
2327
* Use demo.sql to create the required table or do:
2428
* DROP TABLE dmlrupdtab;
2529
* CREATE TABLE dmlrupdtab (id NUMBER, name VARCHAR2(40));
@@ -47,11 +51,13 @@ oracledb.getConnection(
4751
}
4852

4953
connection.execute(
50-
"UPDATE DMLRUPDTAB SET NAME = :name RETURNING ID, NAME INTO :RID, :RNAME",
54+
"UPDATE dmlrupdtab SET name = :name WHERE id IN (:id1, :id2) RETURNING id, ROWID INTO :ids, :rids",
5155
{
56+
id1: 1001,
57+
id2: 1002,
5258
name: "Chris",
53-
rid: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
54-
rname: { type: oracledb.STRING, dir: oracledb.BIND_OUT }
59+
ids: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
60+
rids: { type: oracledb.STRING, dir: oracledb.BIND_OUT }
5561
},
5662
{ autoCommit: true },
5763
function(err, result)

0 commit comments

Comments
 (0)