Sunday May 01, 2016

Getting a C++11 compiler for Node 4, 5 and 6 on Oracle Linux 6

A newer compiler is needed on Oracle Linux 6 when you want to use add-ons like node-oracledb with Node 4 or later. This is because add-ons for those versions need to be built with a C++11 compatibile compiler. The default compiler on OL 6 doesn't have this support. OL 7 does have such a compiler, so these instructions are not needed for that version.

For OL 6 the easist way to get a new compiler is from the Software Collection Library (SCL). You enable the software collection yum channel, run a yum install command, and then the compiler is immediately available to use. Detailed installation SCL instructions are in the manual.

The steps below show how to install node-oracledb on Oracle Linux 6 for Node.js 4 or later.

Enabling the Software Collection Library

If you are using yum.oracle.com (formerly known as public-yum.oracle.com) then edit /etc/yum.repos.d/public-yum-ol6.repo and enable the ol6_software_collections channel:

  [ol6_software_collections]
  name=Software Collection Library release 1.2 packages for Oracle Linux 6 (x86_64)
  baseurl=http://yum.oracle.com/repo/OracleLinux/OL6/SoftwareCollections12/x86_64/
  gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
  gpgcheck=1
  enabled=1

If necessary, you can get the latest channel list from http://yum.oracle.com/public-yum-ol6.repo and merge any updates from it into your existing /etc/yum.repos.d/public-yum-ol6.repo file.

Alternatively, if your machine has a ULN support subscription, you can subscribe to the Oracle Software Collections 1.2 for Oracle Linux 6 channel in the Manage Subscription page on linux.oracle.com.

Installing the Updated Compiler

Once the channel is enabled, install the updated compiler with:

  yum install scl-utils devtoolset-3

This will install a number of packages that comprise the complete, updated tool set.

Installing node-oracledb

Installing node-oracledb on Node 4 (or later) is the same as in install instuctions, but using the new compiler. The Oracle Linux manual chapter Using the Software Collection Version of a Command shows various ways to enable the dev toolset.

In summary, to install node-oracledb on Node 4 or later using Oracle Linux 6, first install an Oracle client such as Instant Client. If you have anything except the Instant Client RPM packages, tell the installer where the libraries and header files are located, for example:

  export OCI_LIB_DIR=$HOME/instantclient
  export OCI_INC_DIR=$HOME/instantclient/sdk/include

If you are behind a firewall, set your proxy:

  export http_proxy=http://my.proxy.example.com:80/

In my development environments I often find some cleanup helps:

  which npm && rm -rf $(npm root)/oracledb $(npm root)/nan $HOME/.node-gyp $HOME/.npm \
        && npm cache clean

Now node-oracledb can be installed using the newer compiler:

  scl enable devtoolset-3 -- npm install oracledb

Using Node

Now you can use Node:

  $ node version.js 
  node.js version: v4.4.3
  node-oracledb version: 10900
  node-oracledb text format: 1.9.0
  oracle client library version: 1201000200
  oracle client library text format: 12.1.0.2.0
  oracle database version: 1201000200
  oracle database text format: 12.1.0.2.0

  $ cat /etc/oracle-release 
  oracle linux server release 6.7

Monday Apr 18, 2016

node-oracledb 1.9.0-Dev Branch with Promises is on GitHub

Top features: Promise support

node-oracledb 1.9.0-Development is now available only as a development-only branch on GitHub. It adds Promise support and some other goodies mentioned below. The plan is to incorporate any user feedback, stabilize the features, improve documentation, improve test covereage, and run stress tests. Once this is all done, then a production release to npm will be made. Personally I think this will take 2-4 weeks, but it is totally dependent on what is uncovered by you, the user.

Since this is a development branch, features are subject to change. Use GitHub Issue #410 to ask design questions and discuss the branch.

Install by setting OCI_LIB_DIR and OCI_INC_DIR as normal, and running npm install oracle/node-oracledb.git#dev-1.9. Or install from GitHub by cloning the repository, checking out the dev-1.9 branch, setting OCI_LIB_DIR and OCI_INC_DIR, and install with npm install

Anyone is welcome to report test results, contribute new tests or update documentation (or code!) to help us get a production release out faster. You do need to have your OCA accepted first before we can look at any pull requests.

As you can see, most of the doc is ready, and there are a solid number of tests for new functionality. You can help us by testing your own favorite Promise library, as we are only planning on testing the default Promise implementation in Node 0.12, 4 and 5. Also check out how connections are handled as you may like a different style - let us know.

In node-oracledb 1.9-dev:

  • Promise support was added. All asynchronous functions can now return promises. The default Promise library is used for Node 0.12, 4 and 5. It can be easily overridden if you wish to incorporate your own implementation.

    The current implemention typically requires two promise chains. Let us know what you think. There are solutions that could be made. What do you want?

  • A new toQueryStream() method was added. It is for ResultSets. It lets REF CURSORS be transformed into Readable Streams. It can also be used to convert ResultSets from top-level queries to streams, however the existing connection.queryStream() method will probably be easier to use for these queries.

  • An experimental query Stream _close() method was added. It allows query streams to be closed without needing to fetch all the data. It is marked experimental because there isn't good information about whether the current Node Streams implementation really allows interruptions.

    Open question: Should _close() invoke pause() internally?

  • Aliases pool.close() and connection.close() have been added for pool.terminate() and connection.release() respectively. This should make it easier to remember which method to use for releasing a connection, terminating a connection pool, or closing a ResultSet: you can just use close().

  • Some method parameter validation checks, such as the number or types of parameters, will now throw errors synchronously instead of returning errors via the callback.

  • Removed an extra call to getRows() made by queryStream() at end-of-fetch.

  • Some annoying, seemingly random crashes caused by connections being garbage collected while still in use should no longer occur. These were more common in smaller scripts that finished quickly, but could be seen in systems under load.

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.

Sunday Apr 17, 2016

PHP OCI8 2.0.11 and 2.1.1 are available on PECL

I've released PHP OCI8 2.0.11 (for supported PHP 5.x versions) and 2.1.1 (for PHP 7) to PECL. Windows DLLs on PECL been built by the PHP release team. The updated OCI8 code has also been merged to the PHP source branches and should land in the future PHP 5.6.21 and PHP 7.0.7 source bundles, respectively.

PHP OCI8 2.1.1 fixes a bug triggered by using oci_fetch_all() with a query having more than eight columns. To install on PHP 7 via PECL, use pecl install oci8

PHP OCI8 2.0.11 has one fix for a bind regression with 32-bit PHP. To install on PHP 5.x use pecl install oci8-2.0.11

My old Underground PHP and Oracle Manual still contains a lot of useful information about using PHP with Oracle Database. Check it out!

Thursday Mar 24, 2016

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

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.

Monday Feb 29, 2016

node-oracledb 1.7.0 has a connection pool queue (Node.js add-on for Oracle Database)

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

Top features: a new connection pool queue to make apps more resilient, and "Bind by position" syntax for PL/SQL Index-by array binds.

This release has a couple of interesting changes as well as some small bind fixes. A few reported build warnings with some compilers were also squashed.

Extended PL/SQL Index-by Array Bind Syntax

To start with, a followup PR from @doberkofler completes his PL/SQL Index-by array binding support project. In node-oracledb 1.7 he has added "bind by position" syntax to the already existing "bind by name" support. Thanks Dieter! The "bind by position" syntax looks like:

connection.execute(
  "BEGIN mypkg.myinproc(:id, :vals); END;",
  [
    1234,
    { type: oracledb.NUMBER,
       dir: oracledb.BIND_IN,
       val: [1, 2, 23, 4, 10]
    }
  ],
  function (err) { . . . });

Personally I'd recommend using bind by name for clarity, but this PR makes the feature congruent with binding scalar values, which is always a good thing.

Documentation is at PL/SQL Collection Associative Array (Index-by) Bind Parameters.

New Transparent JavaScript Wrapper for Existing Classes

The other major change in 1.7 is a new JavaScript wrapper over the current node-oracledb C++ API implementation, courtesy of some community discussion and the direction that users seemed to have been heading in: creating similar wrappers. It was also the result of some 'above and beyond' overtime from Dan McGhan who did the project. This wrapper should be transparent to most users. It gives a framework that will make it easier to extend node-oracledb in a consistent way and also let developers who know JavaScript better than C++ contribute to node-oracledb.

New Connection Pool Queue Enabled by Default

The layer has let Dan add his first new user feature: a request queue for connection pooling. It is enabled by a new Boolean pool attribute queueRequests. If a pool.getConnection() request is made but there are no free connections (aka sessions) in the pool, the request will now be queued until an in-use connection is released back to the pool. At this time the first request in the queue will be dequeued, and the underlying C++ implementation of pool.getConnection() will be called to return the now available connection to the waiting requester.

A second new pool attribute queueTimeout uses setTimeout to automatically dequeue and return an error for any request that has been waiting in the queue too long. The default value is 60000 milliseconds, i.e. 60 seconds. In normal cases, when requests are dequeued because a connection does become available, the timer is stopped before the underlying C++ layer gets called to return the connection.

The pool queue is enabled by default. If it is turned off, you get pre-1.7 behavior. For example if more requests are concurrently thrown at an app than the poolMax value, then some of the pool.getConnection() calls would likely return an error ORA-24418: Cannot open further sessions. When enabled, the new queue nicely stops this error occurring and lets apps be more resilient.

The pool option attribute _enableStats turns on lightweight gathering of basic pool and queue statistics. It is false by default. If it is enabled, applications can output stats to the console by calling pool._logStats() whenever needed. I think it will be wise to monitor the queue statistics to make sure your pool configuration is suitable for the load. You don't want the queue to be an invisible bottle neck when too many pool.getConnection() requests end up in the queue for too long. Statistics and the API may change in future, so the attribute and method have an underscore prefix to indicate they are internal.

Connection Queue Example

To look at an example, I used ab to throw some load at an app based on examples/webapp.js I used a load concurrency of 25 parallel requests. The pool had a maximum of 20 sessions in its pool. The extra load was nicely handled by the connection queue without the application experiencing any connection failures.

I'd modified the app to check for a particular URL and dump statistics on request:

    . . .
    var hs = http.createServer (
      function(request, response)
      {
        var urlparts = request.url.split("/");
        var arg = urlparts[1];
        if (arg === 'stats') {
          pool._logStats();
        }
    . . .

Here is snapshot of the output from _logStats() at one point during the test:

Pool statistics:
...total connection requests: 26624147
...total requests enqueued: 5821874
...total requests dequeued: 5821874
...total requests failed: 0
...total request timeouts: 0
...max queue length: 6
...sum of time in queue (milliseconds): 13920717
...min time in queue (milliseconds): 0
...max time in queue (milliseconds): 1506
...avg time in queue (milliseconds): 2
...pool connections in use: 12
...pool connections open: 20
Related pool attributes:
...queueRequests: true
...queueTimeout (milliseconds): 0
...poolMin: 10
...poolMax: 20
...poolIncrement: 10
...poolTimeout: 0
...stmtCacheSize: 30
Related environment variables:
...process.env.UV_THREADPOOL_SIZE: undefined

The connection pool was semi-arbitrarily configured for testing. It started out with 10 sessions open (poolMin) and as soon as they were in use, the pool would have grown by another 10 sessions (poolIncrement) to the maximum of 20 (poolMax).

What the stats show is that not all pool.getConnection() requests could get a pooled connection immediately. About 20% of requests ended up waiting in the queue. The connection pool poolMax is smaller than optimal for this load.

The queue was never large; it never had more than 6 requests in it. This is within expectations since there are at least 5 more concurrent requests at a time than there are connections available in the pool.

If this were a real app, I might decide to increase poolMax so no pool.getConnection() call ever waited. (I might also want to set poolTimeout so that when the pool was quiet, it could shrink, freeing up DB resources.) However the average wait time of 2 milliseconds is small. If I don't have DB resources to handle the extra sessions from a bigger pool, I might decide that a 2 millisecond wait is OK and that the pool size is fine.

At least one connection spent 1.5 seconds in the queue. Since I know my test infrastructure I'm guessing this was when the pool ramped up in size and my small, loaded DB took some time to create the second set of 10 sessions. Maybe I should experiment with a smaller poolIncrement or bigger poolMin?

Another important variable shown in the stats is UV_THREADPOOL_SIZE. I'd not set it so there were the default four worker threads in the Node process. Blindly increasing poolMax may not always help throughput. If DB operations take some time, you might find all threads get blocked waiting for their respective DB response. Increasing UV_THREADPOOL_SIZE may help improve application throughput.

The best settings for pool configuration, UV_THREADPOOL_SIZE, and any DRCP pool size will depend on your application and environment.

Connection Pool Queue Statistics

The table below shows the node-oracledb 1.7 pool statistics descriptions. These stats and the APIs to enable and log them may change in future versions of node-oracledb. I look forward to getting some PRs, for example to add a standard logging capability which the stats generation can be part of.

Connection Pool MetricDescription
total connection requests

Number of pool.getConnection() calls made for this pool

total requests enqueued

Number of connections that couldn't be satisfied because every session in the the pool was already being used, and so they had to be queued waiting for a session to be returned to the pool

total requests dequeued

Number of connection requests that were removed from the queue when a free connection has become available. This is triggered when the app has finished with a connection and calls release() to return it to the queue

total requests failed

Number of connection calls that invoked the underlying C++ pool.getConnection() callback with an error state. Does not include queue request timeout errors.

total request timeouts

Number of connection requests that were waiting in the queue but exceeded the queueTimeout setting. The timeout is triggered with a JavaScript setTimeout call

max queue length

Maximum number of connection requests that were ever waiting at one time

sum of time in queue

Total sum of time that connection requests have been waiting in the queue

min time in queue

Smallest amount of time that any request waited in the queue

max time in queue

Longest amount of time that any request waited in the queue

avg time in queue

Derived from the sum of time value divided by the number of requests enqueued

pool connections in use

A metric returned by the underlying Oracle C client session pool implementation. It is the number of currently active connections in the connection pool

pool connections open

Also returned by the underlying library. It shows the number of currently open sessions in the underlying connection pool

Note that the sum of time in queue, the min time in queue and the max time in queue values are calculated when queued requests are removed from the queue, so they don't record the amount of time for requests still waiting in the queue.

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.

Monday Feb 08, 2016

Node in the Cloud: Oracle DBaaS, App Container Cloud and node-oracle

The node-oracledb driver is pre-installed on the Oracle Application Container Cloud when you create a Node service! Yay!

I've posted a video on deploying a Node application to the cloud and connecting to Oracle Database Cloud Service. (Blatent plug: subscribe to the YouTube channel!)

The brief summary is that I developed a Node application in my local environment. I then created a database service, I zipped all the JavaScript files along with a manifest telling the App Container Cloud which source file to run, amd this zip was uploaded to a Node cloud service. DB credentials are referenced in the app by environment variables; the variables are made available by the App Container Cloud when a DBaaS instance is associated with it.

You can try it all out by applying for a 30 day free trial on the Oracle Cloud.

All the JavaScript modules except native add-ons like node-oracledb should be included in your application zip bundle - you might have been developing on a different OS than used in the container so native adds-on won't work. The container simply unzips your bundle and runs. It will find the node-oracledb installed globally on the container just fine.

Monday Dec 21, 2015

node-oracledb 1.5.0 is on NPM (Node.js add-on for Oracle Database)

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

A number of bugs have been squashed in this release.

  • We now treat Oracle Database 'Success With Info' warnings as success.

    Thanks to Francisco Trevino for his pull request. After investigating and discussing, we decided for 1.5 to pick up the straightforward fix proposed. In a future release we will revisit allowing these warnings to be caught and handled.

  • Extended rollback-on-connection-release with 11g Oracle Clients to occur for all non-query executions.

    The natural behavior of OCI is to commit when a connection is released. This is the opposite of node-oracledb, which therefore has to determine whether to rollback or not.

    When node-oracledb is linked with 11g client a heuristic is used to guess whether to rollback when a connection is released. This heuristic needed to be changed to cover more cases. The result is that there will be sometimes be some unnecessary rollbacks issued.

    The bug didn't occur node-oracledb was linked with 12c client libraries due to this code that uses a new API available in 12c to indicate whether a connection has a transaction open.

    Bottom line: use Oracle 12c client libraries if possible to get optimal behavior.

  • Updated OS X install instructions to work on El Capitan.

    The instructions now use symbolic links in /usr/local/lib for the Oracle client libraries. This removes the need to set DYLD_LIBRARY_PATH, which has some restrictions on it introduced in El Capitan.

  • Display an error and prevent connection release while database calls are in progress.

    This was a bigger transaction, that 'fixed' a number of seemingly random crashes which were occurring when applications released connections that were in fact still in use. Node-oracledb will now print an error and not release the connection, thus preventing a crash. Note that since the release fails, connection pools can max out in this scenario. If you experience the errors NJS-030, NJS-031 or NJS-032 you should fix your app so the connection release occurs after all database operations have concluded.

    The new messages are:

    "NJS-030: Connection cannot be released because Lob operations are in  progress"
    "NJS-031: Connection cannot be released because ResultSet operations are in progress"
    "NJS-032: Connection cannot be released because a database call is in progress"
    
  • Fixed an intermittent crash while selecting data from CLOB column.

    We had an incorrect buffer expansion ratio in use. This has been fixed.

  • Fixed crash when trying to set invalid values for connection properties.

    Enough said.

Work for node-oracledb 1.6 will begin. We are trying to reproduce and understand some reported LOB issues and memory growth reports. We're also looking forward to evaluating a big pull request from Dieter Oberkofler that adds PL/SQL bind support.

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.

Monday Nov 16, 2015

node-oracledb 1.4.0 supports Node 0.10, 0.12, 4.2 and 5 (Node.js add-on for Oracle Database)

Version 1.4 of node-oracledb, the add-on for Node.js that powers high performance Oracle Database applications, is available on NPM.

Since the recent releases of Node 4 LTS and Node 5, after the reconciliation and merge of the io.js and Node.js forks, there has been strong interest in a compatible node-oracledb driver. This is now possible. Node-oracledb 1.4 works with Node.js 0.10, 0.12, 4.2, and 5.0. Thanks to everyone for their perseverance.

The code change making this possible was a patch contributed by Richard Natal bumping the dependency on NAN from v1 to v2. Note: a compiler with support for C++11 is required to build with Node 4.2 and 5. (Oracle Linux 6 users will need to move to Oracle Linux 7 or install a newer compiler, such as from the Software Collection Library for Oracle Linux).

Other changes in this release are:

  • Fixed a statement cursor leak occuring when statements failed.

  • Fixed a crash accessing Pool properties on Windows.

  • A new testWindows target to help run the tests on Windows was added to package.json. Instructions on how to run tests are in test/README.md.

  • Fixed compilation warnings seen on some platforms with newer compilers.

Issues and questions about node-oracledb can be posted on GitHub.

node-oracledb installation instructions are here.

node-oracledb documentation is here.

Wednesday Oct 14, 2015

node-oracledb 1.3.0 is on NPM (Node.js add-on for Oracle Database)

Version 1.3 of node-oracledb, the add-on for Node.js that powers high performance Oracle Database applications, is available on NPM

We kept the changes minimal in this release for several reasons. It has two small enhancements and a few bug fixes.

  • A new oracledb.oracleClientVersion attributes gives the version of the Oracle client libraries that node-oracledb is linked with.

    A connection.oracleServerVersion attribute gives the Oracle Database version used by the connection.

    These attributes are handy for code that needs to run in multiple environments. See examples/version.js for usage.

  • The major bug fix resolves some corruption with result.outBinds when calling PL/SQL blocks. This was sometimes causing a crash.

See CHANGELOG for the other changes.

Issues and questions about node-oracledb can be posted on GitHub.

node-oracledb installation instructions are here.

node-oracledb documentation is here.

Friday Sep 25, 2015

node-oracledb 1.2.0 is on NPM (Node.js add-on for Oracle Database)

Version 1.2 of node-oracledb, the add-on for Node.js that powers high performance Oracle Database applications, is available on NPM

A lot of good changes have been made.

Our thanks to Bruno Jouhier from Sage for his work on adding RAW support and for fixes for LOB stability. Thanks also go to Bill Christo for pushing us on some Installation topics - look out for his full article on Windows Installation that OTN will be publishing soon.

An annotated list of the changes in this releases are:

  • Added support for RAW data type.

    Bruno contributed a patch to add support for the Oracle RAW datatype. This data type maps to a JavaScript Buffer for inserts, queries and for binding to PL/SQL. Binding RAW for DML RETURNING is not supported. There is an example showing inserting and querying in examples/raw1.js

  • Added a type property to the Lob class to distinguish CLOB and BLOB types.

    This small change will allow introspection on Lob instances so applications can more easily decide how to handle the data.

  • Changed write-only attributes of Connection objects to work with console.log().

    The Connection object had three write-only attributes (action, module, clientId) used for end-to-end tracing and mid-tier authentication. Because they were write-only, anyone doing a simple console.log() on the connection object got a confusing message often leading to the impression that connection had failed. The attributes are write-only for the reasons described in the documentation. With the change in v1.2, a Connection object can now be displayed. The three attributes will show as null (see the doc) while the non- write-only attribute stmtCacheSize will show an actual value. With hindsight the three attributes should have be set via a setter, but they aren't.

  • Added a check to make sure maxRows is greater than zero for non-ResultSet queries.

    If you want to get metaData for a query without getting rows, specify resultSet:true and prefetchRows:0 in the query options (and remember to close the ResultSet).

  • Improved installer messages for Oracle client header and library detection on Linux, OS X and Solaris.

    Some upfront checks now aid detection of invalid environments earlier.

  • Optimized CLOB memory allocation to account for different database-to-client character set expansions.

    In line with the optimization for string buffers in v1.1, users of AL32UTF8 databases will see reduced memory consumption when fetching CLOBs.

  • Fixed a crash while reading a LOB from a closed connection

  • Fixed a crash when selecting multiple rows with LOB values.

    Another fix by Bruno.

  • Corrected the order of Stream 'end' and 'close' events when reading a LOB.

    Bruno was busy this release and sent in a pull request for this too.

  • Fixed AIX-specific REF CURSOR related failures.

  • Fixed intermittent crash while setting fetchAsString, and incorrect output while reading the value.

  • Added a check to return an NJS error when an invalid DML RETURN statement does not give an ORA error.

  • Removed non-portable memory allocation for queries that return NULL.

  • Fixed encoding issues with several files that caused compilation warnings in some Windows environments.

  • Made installation halt sooner for Node.js versions currently known to be unusable.

  • Fixed typo in examples/dbmsoutputgetline.js

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!

Installation instructions are here.

Node-oracledb documentation is here.

Thursday Sep 03, 2015

node-oracledb 1.1.0 is on NPM (Node.js add-on for Oracle Database)

Version 1.1 of node-oracledb, the add-on for Node.js that powers high performance Oracle Database applications, is available on NPM

This is a stabilization release, with one improvement to the behavior of the local connection pool. The add-on now checks whether pool.release() should automatically drop sessions from the connection pool. This is triggered by conditions where the connection is deemed to have become unusable. A subsequent pool.getConnection() will, of course, create a new, replacement session if the pool needs to grow.

Immediately as we were about to release, we identified an issue with lobPrefetchSize. Instead of delaying the release, we have temporarily made setting this attribute a no-op.

The changes in this release are:

  • Enhanced pool.release() to drop the session if it is known to be unusable, allowing a new session to be created.

  • Optimized query memory allocation to account for different database-to-client character set expansions.

  • Fixed build warnings on Windows with VS 2015.

  • Fixed truncation issue while fetching numbers as strings.

  • Fixed AIX-specific failures with queries and RETURNING INTO clauses.

  • Fixed a crash with NULL or uninitialized REF CURSOR OUT bind variables.

  • Fixed potential memory leak when connecting throws an error.

  • Added a check to throw an error sooner when a CURSOR type is used for IN or IN OUT binds. (Support is pending).

  • Temporarily disabled setting lobPrefetchSize

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

Installation instructions are here.

Monday Aug 17, 2015

How to install node-oracledb on Windows

Bill Christo, one of our valued community members, has created a great YouTube video showing how to install node-oracledb on Windows.

The official installation manual is also handy. See Node-oracledb Installation on Windows.

Update: also see Bill's article on Installing node-oracledb on Microsoft Windows on OTN.

Node-oracledb goes 1.0: The Node.js add-on for Oracle Database

Announcement

Today Oracle released node-oracledb 1.0, the Node.js add-on to enable high performance Oracle Database applications.

Node-oracledb is available from npmjs.com and GitHub.

Each month or so, since our first code bundle was pushed to GitHub earlier this year, we released a node-oracledb update with new functionality. The adoption has been exciting, with important applications already in production. This is our eighth release of node-oracledb and promises to be our best received so far.

The node-oracledb 1.0 add-on for Node.js supports standard and advanced features:

Oracle enhances, maintains and supports node-oracledb via open source channels (i.e. GitHub), similar to Oracle Database drivers for other open source languages. The add-on is under the Apache 2.0 license.

Where to get node-oracledb

The Oracle Technology Network Node.js Developer Center has all the links and information you need to start using node-oracledb.

To jump start, follow these instructions to install node-oracledb.

Changes since the previous release

The major changes in node-oracledb 1.0 since the previous release are:

  • The Stream interface for CLOB and BLOB types was implemented, adding support for LOB queries, inserts, and PL/SQL LOB bind variables. As well as being needed for working with many legacy schemas, having LOB support lets application developers use Oracle Database 12.1.0.2's JSON data type without running into the length limitation of VARCHAR2 storage.

    Customers have been contacting me what seems like every day, asking when LOB support would be available, and pleading for early access. Here it is, and it looks great. We'll be continuing to run load tests, benchmark it, and to enhance it.

    To see how to use LOBs with node-oracledb, checkout the node-oracledb Lob documentation and LOB examples

    General information about Oracle Database JSON support can be found in the documentation or on the JSON team blog.

  • Added Oracledb.fetchAsString and a new execute() property fetchInfo to allow numbers, dates, and ROWIDs to be fetched as strings. These features, available at the application level (for dates and numbers), and per-statement level (for dates, numbers and ROWIDs), can help overcome JavaScript limitations of representation and conversion.

  • Added support for binding DATE, TIMESTAMP, and TIMESTAMP WITH LOCAL TIME ZONE as DATE to DML RETURNING (aka RETURNING INTO) type. You can also bind these types as STRING.

  • The internal Oracle client character set is now always set to AL32UTF8. There's no longer a need to set it externally via NLS_LANG. A related bug with multibyte data reported by users was fixed by correcting the allocation of some internal buffers. Overall the NLS experience is much more consistent.

  • The test suite's and example database credentials can now be set via environment variables. A small change to help testing in automatically provisioned environments. Our test suite already has great coverage numbers, and will continue to be enhanced in future releases.

  • Bug fixes to node-oracledb. These are listed in the CHANGELOG.

What next?

Being an open source project in a dynamically changing environment, our statement of direction has been a brief, flexible goal: We are actively working on supporting Oracle Database features, and on functionality requests from users involved in the project. Our priority list is re-evaluated for each point release.

So now we have version 1.0, what next? This is just the start. There are plenty of important and interesting tasks in front of us. We will begin with a review of the project, from our development processes, the driver functionality, right through to distribution. This review will determine our next tasks. Hearing from users is crucial for prioritization, so don't hesitate to comment at GitHub.

Node.js is undergoing a surge of change at the moment, with the io.js re-merger, and the formation of the Node.js Foundation. As the merged Node.js code base stabilizes and the Foundation's LTS plans solidify, we will be able to be more formal about node-oracledb's schedule. We will work with Node.js and with partners to bring you the best experience. (On a technical note, the V2 release of the compatibility layer NAN was made in the last few days, too late for us to incorporate in node-oracledb 1.0. So, support of the latest, bleeding edge io.js will be in a future node-oracledb version.)

Let me wrap up this announcement by appreciating the growing node-oracledb community, particularly those who have contributed to node-oracledb with code, suggestions and discussions.

Sunday Jul 26, 2015

Installing node-oracledb on OS X with Oracle Instant Client 11.2.0.4


----> This note and script is obsolete. The current node-oracledb OS X install instructions contain nicer solutions <----- .

I've been hacking an Apple OS X shell script to install node-oracledb. You tell it where your Instant Client libraries and headers ZIP packages are. It then installs node-oracledb, resulting in an instantclient directory and a node_modules directory. This automates the instructions Node-oracledb Installation on OS X with Instant Client.

My osxinstall.sh script can be seen here. [Now removed]

I was investigating how to avoid needing to set DYLD_LIBRARY_PATH. I wanted to find how to replicate the use of rpath, which is available for node-oracledb on Linux. A standard install on OS X needs DYLD_LIBRARY_PATH set, otherwise Node.js will fail with the error:

   cjones@cjones-mac:~/n$ node select1.js

   /Users/cjones/n/node_modules/oracledb/lib/oracledb.js:28
       throw err;
	     ^
   Error: dlopen(/Users/cjones/n/node_modules/oracledb/build/Release/oracledb.node, 1):
           Library not loaded: /ade/b/3071542110/oracle/rdbms/lib/libclntsh.dylib.11.1
     Referenced from: /Users/cjones/n/node_modules/oracledb/build/Release/oracledb.node
     Reason: image not found
       at Module.load (module.js:356:32)
       at Function.Module._load (module.js:312:12)
       at Module.require (module.js:364:17)
       at require (module.js:380:17)
       at Object.<anonymous> (/Users/cjones/n/node_modules/oracledb/lib/oracledb.js:23:15)
       at Module._compile (module.js:456:26)
       at Object.Module._extensions..js (module.js:474:10)
       at Module.load (module.js:356:32)
       at Function.Module._load (module.js:312:12)
       at Module.require (module.js:364:17)

So, I was playing with osxinstall.sh to see how to circumvent this. Before running osxinstall.sh, edit it and set the paths to where the Instant Client 11.2.0.4 'basic' and 'sdk' ZIP files are located on your filesystem, see IC_BASIC_ZIP and IC_SDK_ZIP. (You can download Instant Client from OTN. Use the 64-bit packages). You also specify the target application directory you are using, see TARGET_DIR. This is where the components are installed into. Update https_proxy if you are behind a firewall, otherwise comment it out.

If you have various node_modules directories around, then npm might end up installing oracledb in an unexpected place and the script will error.

The key bit of osxinstall.sh that I was interested in is:

    # For Oracle Instant Client 11.2.0.4: these are the default paths we will change
    IC_DEF1=/ade/b/3071542110/oracle/rdbms/lib
    IC_DEF2=/ade/dosulliv_ldapmac/oracle/ldap/lib

    . . .

    # Warning: work in progress - may not be optimal
    chmod 755 $OCI_LIB_DIR/*dylib $OCI_LIB_DIR/*dylib.11.1
    install_name_tool -id libclntsh.dylib.11.1 $OCI_LIB_DIR/libclntsh.dylib.11.1
    install_name_tool -change $IC_DEF2/libnnz11.dylib $OCI_LIB_DIR/libnnz11.dylib \
                 $OCI_LIB_DIR/libclntsh.dylib.11.1
    install_name_tool -id libnnz11.dylib $OCI_LIB_DIR/libnnz11.dylib
    install_name_tool -change $IC_DEF1/libclntsh.dylib.11.1 \
                 $OCI_LIB_DIR/libclntsh.dylib.11.1 $OCI_LIB_DIR/libociei.dylib
    install_name_tool -change $IC_DEF1/libclntsh.dylib.11.1 \
                 $OCI_LIB_DIR/libclntsh.dylib.11.1 $NODE_ORACLEDB_LIB
    chmod 555 $OCI_LIB_DIR/*dylib $OCI_LIB_DIR/*dylib.11.1

This changes the library install and identification names using install_name_tool. Note this tool cannot allocate more space for path names than currently exists. My code is a work in progress; I may work out a better way, perhaps using libtool. Comments & suggestions welcome.

The script does more than most people probably need. In future even I might only run parts extracted from it.

If you are new to node-oracledb, check out its install and API documentation on GitHub. You may also be interested in reading The Easiest Way to Install Oracle Database on Mac OS X.

Monday Jul 20, 2015

node-oracledb 0.7.0 now supports Result Sets and REF CURSORS

A new release of the Node.js driver for Oracle Database is now on npmjs.com and GitHub.

node-oracledb 0.7 connects Node.js 0.10, Node.js 0.12, and io.js to Oracle Database. It runs on a number of platforms. For more information about node-oracledb see the node-oracledb GitHub page.

The changes in 0.7 are:

  • Added result set support for fetching large data sets. Rows from queries can now be fetched in batches using a ResultSet class. This allows large query results to be fetched without requiring all values to be in memory at once. New getRow() and getRows() methods can be called repeatedly to scroll through the query results.

    The original node-oracledb behavior of returning all rows at once remains the default. To return a resultSet, use the new execute() option { resultSet: true }. For example:

    //  (See the full code in examples/resultset2.js)
    
    . . .
    
    var numRows = 10;  // number of rows to return from each call to getRows()
    
    connection.execute(
      "SELECT employee_id, last_name FROM employees ORDER BY employee_id",
      [], // no bind variables
      { resultSet: true }, // return a result set.  Default is false
      function(err, result)
      {
        if (err) { . . . }
        fetchRowsFromRS(connection, result.resultSet, numRows);
      });
    });
    
    . . .
    
    function fetchRowsFromRS(connection, resultSet, numRows)
    {
      resultSet.getRows( // get numRows rows
        numRows,
        function (err, rows)
        {
          if (err) {
             . . .                        // close the result set and release the connection
          } else if (rows.length == 0) {  // no rows, or no more rows
            . . .                         // close the result set and release the connection
          } else if (rows.length > 0) {
            console.log(rows);
            fetchRowsFromRS(connection, resultSet, numRows);  // get next set of rows
          }
        });
    }
    

    It's important to use the new resultSet close() method to close the result set when no more data is available or required.

    There is more information on Result Sets in the manual.

  • Added REF CURSOR support for returning query results from PL/SQL. PL/SQL code that returns REFCURSOR results via bind parameters can now bind a new node-oracledb type Oracledb.CURSOR and fetch the results using the new ResultSet class.

    //  (See the full code in examples/refcursor.js)
    
    var oracledb = require('oracledb');
    
    . . .
    
    var numRows = 10;  // number of rows to return from each call to getRows()
    
    var bindvars = {
      sal:  6000,
      cursor:  { type: oracledb.CURSOR, dir: oracledb.BIND_OUT }
    }
    
    connection.execute(
      "BEGIN get_emp_rs(:sal, :cursor); END;",  // The PL/SQL has an OUT bind of type SYS_REFCURSOR
      bindvars,
      function(err, result)
      {
        if (err) { . . . }
        fetchRowsFromRS(connection, result.outBinds.cursor, numRows);
      });
    
    . . .
    
    function fetchRowsFromRS(connection, resultSet, numRows)
    {
      resultSet.getRows( // get numRows rows
        numRows,
        function (err, rows)
        {
          if (err) {
             . . .                        // close the result set and release the connection
          } else if (rows.length == 0) {  // no rows, or no more rows
            . . .                         // close the result set and release the connection
          } else if (rows.length > 0) {
            console.log(rows);
            fetchRowsFromRS(connection, resultSet, numRows);  // get next set of rows
          }
        });
    }
    

    There is more information on using REF CURSORS in the manual.

  • Added row prefetching support. The new ResultSet class supports prefetching via a new attribute oracledb.prefetchRows and a new execute() option prefetchRows. Each time the application fetches query or REF CURSOR rows in a ResultSet from Oracle Database, prefetching allows the underlying Oracle libraries to transfer extra rows. This allows better use of database and network resources, improving performance and scalability. Regardless of the prefetch size, the number of rows returned to the application does not change. Buffering is handled by the underlying Oracle client library.

    The default prefetch size is 100 extra rows. Applications should tune the prefetch size used by each execute() for desired performance and/or to avoid allocating and initializing unused memory. There are some more tips in the manual.

    With node-oracledb 0.7.0, non-ResultSet queries now use prefetching with a fixed size of 2. This should reduce the number of round trips required for these queries.

  • Added a test suite. Yay! See the README in the tests directory for how to run the tests. When you run the test suite, you'll notice each test has a unique number for ease of identification. The numbers are not necessarily sequential.

    We do most testing on Linux and Windows. If you see test output differences due to environment or version differences, please sign the OCA and submit a pull request with the fix and an explanation of why it is needed. See CONTRIBUTING.

    If you submit new tests (after signing the OCA), assign each one a unique number in the documented range that applies to the area being tested.

  • Fixed error handling for SQL statements using RETURNING INTO. A bug causing all errors with DML RETURNING statements to report the same error message was fixed.

  • Fixed INSERT of a date when the SQL has a RETURNING INTO clause. When using an INSERT to insert a date or timestamp and the SQL clause had a RETURNING INTO clause for character or number columns, then an error was being thrown. This has been fixed.

  • Renumbered the values used by the Oracledb Constants. If your application uses constant names such as Oracledb.OBJECT or Oracledb.BIND_INOUT then you won't notice the change. However if, for some reason, code has hardcoded numbers like 2, then you will have to update to use the new numbers, see lib/oracledb.js. Or, better, change the code to use the constants' names.

About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Mail: christopher.jones@oracle.com
Twitter: @ghrd
OTN: Scripting Languages
Book: Free PHP Oracle book

Follow:
Blaine Carter
Dan McGhan

Search

Archives
« May 2016
SunMonTueWedThuFriSat
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
    
       
Today