X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

Node-oracledb 1.8 has a streaming mode for queries (Node.js add-on for Oracle Database)

Christopher Jones
Senior Principal Product Manager

Node-oracledb 1.8.0, the Node.js add-on for Oracle Database, is on NPM.

Top new changes: New Query Result Streaming. Use DRCP connect strings only with a connection pool.

I want to start by saying thanks to all contributors past and current! It is the community that defines the product.

Query Result Streaming

Node Streams can now be used for queries after a pull request from Sagie Gur-Ari was merged. A new connection.queryStream() method returns a Readable Stream. Of course, if you prefer callbacks, the existing ResultSet feature can continue to be used.

The new query stream feature is implemented as a wrapper around the ResultSet Class. In particular it uses resultset.getRows() to fetch a subset of data, each row of which will generate a data event. The only reason to understand this is that getRows() takes a parameter to specify how many rows to fetch. We had some (well, a lot of) debate about to set this value and keep the API simple. For the moment, the value of oracle.maxRows is used. Note the value does not affect how many rows are returned by streamQuery() events because getRows() will be repeatedly called when more rows are needed. Instead, this parameter is used to tune stream performance. In the near future, when PR 361 is reviewed, we may introduce a specialized tuning parameter.

We also want to look at ways of interrupting the streams early. But, in node-oracledb 1.8, make sure to read to the end of the query to avoid leaking a cursor. The ResultSet close() is executed internally; you do not need to call it to release ResultSet resources.

An example of query streaming is:

  var stream = connection.queryStream(
'SELECT first_name, last_name FROM employees ORDER BY employee_id'
);
stream.on('error', function (error) {
console.error(error);
return;
});
stream.on('metadata', function (metadata) {
console.log(metadata);
});
stream.on('data', function (data) {
console.log(data);
});
stream.on(end, function () {
connection.release(
function(err) {

if (err) {

console.error(err.message);

}
});
});

There is a runnable example in examples/selectstream.js. Other useful examples are in the test file test/stream1.js.

Use DRCP Connect Strings Only With a Connection Pool

In node-oracledb 1.8 you must now use a connection pool if your connect string requests a DRCP connection. Previously this was just a best practice. Now it is enforced.

Connect strings that request DRCP connections look either like:

  connectString : "mymachine/mydbservice:pooled"

or

  connectString : "SALES"

where the SALES connect identifier maps to a tnsnames.ora entry specifying SERVER=POOLED, for example:

  SALES=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=mymachine)
(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mydbservice)
(SERVER=POOLED)))

If you try oracledb.getConnection(...) you will get an error ORA-56609: Usage not supported with DRCP.

Instead use a connection pool, see node-oracledb Connection Pooling documentation:

  oracledb.createPool (
{
user : "hr"
password : "welcome"
connectString : "mymachine/mydbservice:pooled"
},
function(err, pool)
{
pool.getConnection (

function(err, connection)

{

. . . // use connection

});
});

In the unlikely case where the Node process is short-lived and you really, really just want a single connection, create a pool with a single session; the createPool() option attributes can be: poolMax: 1, poolMin: 1, poolIncrement: 0

Millisecond Precisions

A pull request from Antonio Bustos has helped make some tests more portable by removing some time ambiguity. The Oracle DATE data type does not contain milliseconds. If an application inserts a JavaScript date with milliseconds, the DATE tests will now only compare the non-millisecond date components to validate results.

Windows Debug Builds

Kubo Takehiro (who is the maintainer of the popular Ruby ruby-oci8 extension) spotted a hierarchy problem with node-oracledb Windows Debug build options. The binding.gyp entry has now been fixed.

Other Changes

The driver name is now set to "node-oracledb : 1.8.0". This is visible to DBAs, for example in the V$SESSION_CONNECT_INFO view. It lets DBAs see what apps, and what versions, are connecting to the database.

The pool queue stats now show the start time of the pool. A couple of parameter check bugs were also fixed. See CHANGELOG for details.

Resources

Issues and questions about node-oracledb can be posted on GitHub. We value your input to help prioritize work on the add-on. Drop us a line!

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.