Wednesday Dec 21, 2016

node-oracledb 1.12: Working with LOBs as String and Buffer. Connection Pinging.

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

Top features: LOBs as JavaScript Strings and Buffers. Pool connection 'aliveness' checking.

The two main features of node-oracledb 1.12 add functionality that aids usability. You may not even notice one working in the background, silently re-establishing pooled connections after network outages. The other is a 'no brainer' that makes working with large objects (LOBs) easier. You'll probably use it and think nothing of it.

This release also contains a number of bug fixes and small changes. Check the CHANGELOG for all the details.

LOB Enhancements

Adding to the existing support for using CLOBs and BLOBs as Node.js Streams, now Strings and Buffers can be bound to CLOBs and BLOBs for IN, IN OUT and OUT binds. CLOBs in queries can also be returned directly as Strings. And streaming to and from Oracle's 'temporary LOBs' is supported. Also in this release, Writeable LOB Streams now conclude with a 'close' event.

The node-oracledb LOB documentation is worth reading. There are runnable examples in the examples directory.

To insert large data, just bind a Node.js String or Buffer to a CLOB or BLOB column, respectively. For example:

  var fs = require('fs');
  var str = fs.readFileSync('example.txt', 'utf 8');

  conn.execute(
    "INSERT INTO mylobs (id, myclobcol) VALUES (:idbv, :cbv)",
    { idbv: 1,
      cbv: str },  // type and direction are optional for IN binds
    function(err, result)
    {
      if (err)
	console.error(err.message);
      else
	console.log('CLOB inserted from example.txt');
      . . .

You can also bind Strings and Buffers to PL/SQL LOB parameters as IN, OUT and IN OUT binds.

To fetch CLOBs as Strings from queries, use fetchAsString or fetchInfo:

  // return all CLOBs as Strings
  oracledb.fetchAsString = [ oracledb.CLOB ];

  conn.execute(
    "SELECT mycol FROM mylobs WHERE id = 1",
    function(err, result) {
      if (err) { console.error(err.message); return; }
      if (result.rows.length === 0)
	console.error("No results");
      else {
	var clob = result.rows[0][0];
	console.log(clob);
      }
    });

Currently BLOBs in queries must be streamed; we didn't want to hold up the 1.12 release.

Use the right tool for the job: use Strings and Buffer for 'small' LOBs, and use Streams for everything else. There is a theoretical limit just under 1 GB in node-oracledb for manipulating LOBs as Strings or Buffers. Luckily you wouldn't want to be allocating that much memory in Node.js applications - it's better to stream. And Node.js itself doesn't cope well with large data. It can get unstable with Strings past the several hundred megabyte mark. (Note that if node-oracledb is linked with Oracle client 11g libraries, than the upper size of Strings and Buffers is just under the 64 KB mark: the moral is use Oracle client 12c libraries - you can still connect to Oracle Database 10.2, 11g and 12c).

For larger data you have multiple options for streaming. To insert into a table column, use the existing RETURNING INTO method where you get an Oracle LOB locator and use Node.js Streams functionality to put data into it. For fetching, use the existing LOB Streaming method and stream data out of the Lob.

If data is too big to bind as a String or Buffer to a PL/SQL parameter, you can create a 'temporary LOB' in node-oracledb, stream into it, and then bind it. This is new in node-oracledb 1.12

First, use the new connection.createLob() method to create a temporary LOB, here of type CLOB:

  conn.createLob(oracledb.CLOB, function(err, templob) {
    if (err) { . . . }
    // ... else use templob
  });

This creates an instance of a node-oracledb Lob class.

Once created, data can be inserted into the Lob. For example to read a text file:

  templob.on('error', function(err) { somecallback(err); });

  // The data was loaded into the temporary LOB, so use it
  templob.on('finish', function() { somecallback(null, templob); });

  // copies the text from 'example.txt' to the temporary LOB
  var inStream = fs.createReadStream('example.txt');
  inStream.on('error', function(err) { . . . });
  inStream.pipe(templob);

Now the LOB has been populated, it can be bound in somecallback() to a PL/SQL IN parameter:

  // For PROCEDURE lobs_in(p_id IN NUMBER, c_in IN CLOB)
  conn.execute(
    "BEGIN lobs_in (:id, :c); END",
    { id: 3,
      c: templob }, // type and direction are optional for IN binds
    function(err)
    {
      if (err) { return cb(err); }
      console.log("Call completed");
      return cb(null, conn, templob);
    });

When the LOB is no longer needed, it must be closed with lob.close():

  templob.close(function (err) {
    if (err)
      . . .
    else
      // success
  });

Connection Pool Enhancements

The other main feature in node-oracledb 1.12 is connection pool session 'aliveness' checking. This is enabled by default. You may never notice it in action but it will improve application behavior where networks are unstable, or database services become temporarily unavailable.

When pooled connections have been established to the database, but are idle in the connection pool not currently being used, there is a chance that a network outage will make those connections unusable. A common scenario is when a developer's laptop is closed for a while. Unless node-oracledb is linked with Oracle 12.2 client libraries, any node-oracledb pooled getConnection() call could return one of the unusable connections. For users of Oracle 11.2 or 12.1 client libraries, a new poolPingInterval setting will do a 'ping' to the database to check the connection is OK before returning that connection to the application. If it isn't OK, then another connection is returned.

There is the overhead of a 'round trip' to the database in doing a ping, so there could be some impact on scalability. But the ping setting is time based so there is no overhead on active pools. The ping will only happen when a connection has been idle in the pool for poolPingInterval seconds. If a connection has been recently used, it will be returned to the application without being checked. The default interval is 60 seconds. The feature can be configured to always-force the ping, to totally disable it, or to be whatever interval meets your performance and quality-of-service requirements. The value can be set when a pool is created, or turned on globally:

  oracledb.poolPingInterval = 60;

Applications should continue to detect and handle execution-time errors, since execution errors may occur, or a network outage may occur between getConnection() and execute() calls, or connections may have been in use and not released back to the pool when the network dropped, or the idle time may have been shorter than the ping interval setting and no ping performed. The new feature is great at improving the reliability of connections in unstable environments but is just one of the options and tools that can be used to provide a highly available application.

The ping feature is a no-op when node-oracledb is linked with Oracle 12.2 client libraries (independent of the database version at the other end of the connection), since those libraries have an always-enabled, lightweight connection check that removes the need for node-oracledb to do its own ping. This adds even more certainty that a connection will be valid at time of first use after a getConnection() call. Oracle 12.2 is currently available in Oracle Cloud.

The pool pinging documentation has more information.

Documentation Updates

If you haven't scanned the documentation for a while, take a look. Each release more and more tips and information gets added.

A couple of community contributions from Leigh Schrandt and Nick Heiner regarding the README were gratefully received. Thank you!

Testing

Testing of node-oracle is also something I wanted to mention. The test suite continues to grow, as you can see when you run it. We also have additional stress and loads tests - overall these extra test take days to complete. There are no guarantees, of course, but node-oracledb is more than solid.

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.

Finally, contributions to node-oracledb are more than welcome, see CONTRIBUTING.

Monday Dec 19, 2016

node-oracledb 1.12.1-dev can fetch CLOBs as JavaScript String

A preview of node-oracledb 1.12.1-dev is available on GitHub and can be installed with:

  npm install oracle/node-oracledb.git#v1.12.1-dev

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

The 1.12.1-dev release introduces fetchAsString support for CLOBs. Now, when CLOB columns are queried, they can be returned directly as JavaScript Strings, without the need to use Streams. To test this in the dev release make sure node-oracledb is linked with Oracle 12c client libraries.

See the extensive manual for details and examples.

Also in this release is improved support for 'temporary LOBs'. Now they can be bound as IN OUT binds, (as well as IN and OUT!)

See my previous post for a brief intro to earlier changes in this 1.12 series. The CHANGELOG has all the updates. I'll blog the features in more detail when a production bundle is released to npmjs.com.

Resources

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

Node-oracledb documentation is here.

Sunday Dec 04, 2016

node-oracledb 1.12.0-dev available for preview

A preview of node-oracledb 1.12.0-dev is available on GitHub and can be installed with:

  npm install oracle/node-oracledb.git#v1.12.0-dev

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

The key things to look at are all the LOB enhancements - we want feedback. There are a couple more LOB enhancements we want to make before we go production, but I know you'll like the direction. Drop us a line via GitHub or email with your comments.

This release also has a connection pool ping feature that will improve application availability when transient network outages occur. It's enabled by default and will be transparent to most users, however it can be tuned or disabled if you have a special situation.

The CHANGELOG has all the other updates in this release. I'll blog all the features in more detail when a production bundle is released to npmjs.com.

Resources

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

Node-oracledb documentation is here.

Friday Aug 19, 2016

Node-oracledb 1.11 Released to NPM

We've pushed out a release of node-oracledb to GitHub and NPM.

Top feature: Connection Pool Cache

The node-oracledb driver connects Node.js to Oracle Database for fast and functional applications.

The changes in node-oracledb 1.11 are:

  • Added a connection pool cache feature allowing pools to be given a string alias at creation. This makes pools easier to use.

    Pools are typically created in one file and later used in other files. Giving a pool a string alias allows these other modules to access the pool by knowing only its alias name.

    When pools are created with oracledb.createPool(), the pool attributes can now optionally contain a new poolAlias string.

    var hrPoolPromise = oracledb.createPool({
      poolAlias: 'pool1',
      users: 'hr',
      password: 'welcome',
      connectString: 'localhost/orcl'
    });

    Pools can be retrieved from the cache using a new oracledb.getPool() method, and then used as before to get connections. Multiple pools, each with a different alias, can be used.

    Interestingly, oracledb.getConnection() has been enhanced to take a pool alias specifying which pool to return a connection from. This bypasses the need to call oracledb.getPool().

    And there's more! The first pool (in a normal code flow) created without a poolAlias attribute is given the alias "default". Connections can be retrieved from this pool by using oracledb.getConnection() without passing an alias at all. This means applications that create only a single pool can simply use oracledb.getConnection() anywhere to get a connection from the pool.

    More information and examples are in the documentation. It is worth checking this since oracledb.getConnection() now has several different behaviors, depending on the use of aliases (and whether callbacks or promises are used).

    We'd recommend using aliases all the time if you create more than one pool - and want to access them via aliases. Using aliases is optional. Don't mix un-aliased and aliased pools unless you want to confuse yourself.

  • Improved the bootstrap error message when the node-oracledb binary cannot be loaded.

  • Fixed memory leaks with DATE and TIMESTAMP bind values.

  • Fixed external authentication which broke in 1.10.

  • Fixed metadata scale and precision values on AIX.

  • A couple of other internal improvements are shown in the CHANGELOG.

Resources

Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

Node-oracledb installation instructions are here.

Node-oracledb API and user documentation is here.

Wednesday Jul 20, 2016

Node-oracledb 1.10.1 Released to NPM

We've pushed out a patch release of node-oracledb to GitHub and NPM. It has a bind fix that didn't land in time for 1.10.0. A memory leak regression with REF CURSORs was also fixed, as was a pre-existing memory leak in the unexpected case of a REF CURSOR failure.

The changes in node-oracledb 1.10.1 are:

  • Fixed a bug that prevented a null value being passed from JavaScript into an IN OUT bind.

  • Fixed a memory leak introduced in 1.10 with REF CURSORs.

  • Fixed an existing memory leak problem if an error happens when fetching REF CURSORs.

  • Added a mocha configuration file for the test suite. The test suite README has updated instructions on how to add tests.

    As a side effect, the tests now run in numeric order.

A couple of other internal improvements are shown in the CHANGELOG.

Resources

Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

Node-oracledb installation instructions are here.

Node-oracledb API and user documentation is here.

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 the install instructions, 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.

Friday Feb 05, 2016

Node-oracledb: Avoiding "ORA-01000: maximum open cursors exceeded"

Developers starting out with Node have to get to grips with the 'different' programming style of JavaScript that seems to cause methods to be called when least expected! While you are still in the initial hacking-around-with-node-oracledb phase you may sometimes encounter the error ORA-01000: maximum open cursors exceeded. A cursor is "a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information"

Here are things to do when you see an ORA-1000:

  • Avoid having too many incompletely processed statements open at one time:

    • Close ResultSets before releasing the connection.

    • If cursors are opened with dbms_sql.open_cursor() in a PL/SQL block, close them before the block returns - except for REF CURSORS being passed back to node-oracledb. (And if a future node-oracledb version supports Oracle Database 12c Implicit Result Sets, these cursors should likewise not be closed in the PL/SQL block)

    • Make sure your application is handling connections and statements in the order you expect.

  • Choose the appropriate Statement Cache size. Node-oracledb has a statement cache per connection. When node-oracledb internally releases a statement it will be put into the statement cache of that connection, but its cursor will remain open. This makes statement re-execution very efficient.

    The cache size is settable with the stmtCacheSize attribute. The appropriate statement cache size you choose will depend on your knowledge of the locality of the statements, and of the resources available to the application: are statements re-executed; will they still be in the cache when they get executed; how many statements do you want to be cached? In rare cases when statements are not re-executed, or are likely not to be in the cache, you might even want to disable the cache to eliminate its management overheads.

    Incorrectly sizing the statement cache will reduce application efficiency. Luckily with Oracle 12.1, the cache can be automatically tuned using an oraaccess.xml file.

    More information on node-oracledb statement caching is here.

  • Don't forget to use bind variables otherwise each variant of the statement will have its own statement cache entry and cursor. With appropriate binding, only one entry and cursor will be needed.

  • Set the database's open_cursors parameter appropriately. This parameter specifies the maximum number of cursors that each "session" (i.e each node-oracle connection) can use. When a connection exceeds the value, the ORA-1000 error is thrown. Documentation on open_cursors is here.

    Along with a cursor per entry in the connection's statement cache, any new statements that a connection is currently executing, or ResultSets that haven't been released (in neither situation are these yet cached), will also consume a cursor. Make sure that open_cursors is large enough to accommodate the maximum open cursors any connection may have. The upper bound required is stmtCacheSize + the maximum number of executing statements in a connection.

    Remember this is all per connection. Also cache management happens when statements are internally released. The majority of your connections may use less than open_cursors cursors, but if one connection is at the limit and it then tries to execute a new statement, that connection will get ORA-1000: maximum open cursors exceeded.

Saturday Jan 30, 2016

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

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

In this release a comprehensive pull request by Dieter Oberkofler adds support for binding PL/SQL Collection Associative Array (Index-by) types. Strings and numbers can now be bound and passed to and from PL/SQL blocks. Dieter tells us that nowadays he only gets to code for a hobby - keep it up Dieter!

Using PL/SQL Associative Arrays can be a very efficient way of transferring database between an application and the database because it can reduce the number of 'round trips' between the two.

As an example, consider this table and PL/SQL package:

  CREATE TABLE mytab (numcol NUMBER);

  CREATE OR REPLACE PACKAGE mypkg IS
    TYPE numtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    PROCEDURE myinproc(p IN numtype);
  END;
  /

  CREATE OR REPLACE PACKAGE BODY mypkg IS
    PROCEDURE myinproc(p IN numtype) IS
    BEGIN
      FORALL i IN INDICES OF p
	INSERT INTO mytab (numcol) VALUES (p(i));
    END;
  END;
  /

With this schema, the following JavaScript will result in mytab containing five rows:

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

There is a fuller example in examples/plsqlarray.sql and check out the documentation.

Other changes in node-oracledb 1.6 are

  • @KevinSheedy sent a GitHub Pull Request for the README to help the first time reader have the right pre-requisites and avoid the resulting pitfalls.

  • Fixed a LOB problem causing an uncaught error to be generated.

  • Removed the 'close' event that was being generated for LOB Writables Streams. The Node.js Streams doc specifies it only for Readable Streams.
  • Updated the LOB examples to show connection release.

  • Extended the OS X install section with a way to install on El Capitan that doesn't need root access for Instant Client 11.2. Thanks to @raymondfeng for pointing this out.

  • Added RPATH to the link line when building on OS X in preparation for future client.

TypeScript users will be happy to hear Richard Natal recently had a node-oracledb TypeScript type definition file added to the DefinitelyTyped project. This is not part of node-oracledb itself but Richard later mentioned he found a way it could be incorporated. Hopefully he will submit a pull request and it will make it directly to the project so it can be kept in sync.

Thanks to everyone who has worked on this release and kept the momentum going.

What's coming up for the next release? There is discussion about adding a JavaScript layer. This was kicked off by a pull request from Sagie Gur-Ari which has lead to some work by Oracle's Dan McGhan. See the discussion and let us know what you think. Having this layer could make it quicker and easier for JavaScript coders to contribute node-oracledb and do things like reduce API inconsistency, make it easier to add a promise API in future, and of course provide a place to directly add Sagie's Streaming query result suggestion that started the whole thing.

I know a few contributors have recently submitted the Oracle Contributor Agreement ready to do big and small things - every bit counts. I look forward to being able to incorporate your work.

I've heard a couple of reports that Node LTS 4.2.6 on Windows is having some issues building native add-ons. 0.10, 0.12, 5.x and 4.2.5 don't have issues. Drop me a line if you encounter a problem.

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 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.

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.

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
« March 2017
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
10
11
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today