TAGS

Recent Posts

Archives

Getting Started with The Oracle Node.js Connector
Posted on January 25, 2017
Author: Tony Tauro, Performance Architects

In an earlier blog post, we offered a brief introduction to Node.js and a few use cases. Now, let’s get our paws dirty and use Node.js to run SQL against an Oracle database.

Setup Development Environment

First, the prerequisites. The official github site has a complete list but here’s the gist (for a Windows machine):

  1. Install Python 2.7 (it needs to be 2.7, not 3.5)
  2. Install a C Compiler with support for C++ 11 (this comes standard with Visual Studio 2012, but with 2015 you’d need to install the Visual C++ Template via NuGet)
  3. Install the small and free Oracle Instant Client. You don’t need the Instant Client if you have the Full Client or the database installed on your machine, but it should be version 11.2 or greater.
  4. Finally, you also need Node.js. download from here. To save yourself some CLA SSPATH jiu-jitsu later, follow this instruction: “Make sure the option to add the Node and npm directories to the path is selected.” Choose 32-bit or 64-bit to match your Oracle client.

Finally, we are done with the downloads and installs. Well, almost!  All of that was to set up your development environment. We’ll install the Oracle Node.js connector after we start our project.

Create A Project Folder

Create a folder called “node” anywhere you want. I recommend to not put this folder into an automatically backed-up folder. In a node project, we install npm modules which can take up space. There’s some debate as to whether these should be backed up with your source code or not. For this kind of practice exercise, I’d say don’t back them up. So, to keep things simple, let’s do this in the command line:

cd \

mkdir node

cd node

mkdir oracledb01

cd oracledb01

The very last thing we need to install is the Oracledb module. Since we’ve installed Node already, just type in:

npm install oracledb

That will take some time to run, so go get a coffee or have a light saber fight. Better still, do both. Once that is complete, we are ready to write some code.

Writing the Code

First, let’s get a reference to the Oracledb module we just imported (during the light-saber fight). This object is our link to the Oracle database:

var oracledb = require(‘oracledb’);

Then let’s put our database credentials into an object:

var dbConfig = {

            user: ‘testuser’,                                   // TODO: Replace with actual user

            password: ‘testpassword’,                  // TODO: Replace with actual password

            connectString: ‘localhost/orcl’ // TODO: Replace … you know the drill

};

Next, add a simple select statement (with a placeholder for the actual department id):

var selectStatement = “SELECT department_id, department_name FROM departments WHERE department_id = :did”;

Now, we are going open a connection using the “getConnection()” method, execute the SQL, and get the results. One thing that is different about Node that regularly trips up newcomers is that it is asynchronous. So, if you simply called the getConnection() method and tried to access the connection in the next line, it would fail. The getConnection() method is asynchronous, so if you want to ensure code is executed only after the connection is established, then you have to pass that code as callback to the getConnection() method. The method would look like this:

oracledb.getConnection(<credentials object>, <callback function>) bxc

Notice that the callback function is the second parameter to the getConnection() method. Thus, this is how we code our getConnection:

oracledb.getConnection(

  dbConfig,

  // This function is called once getConnection returns

  function(err, connection)

  {

// Check the error object. If its not null, then…

    if (err) {

              // …connection failed. Write to console and exit

      console.error(err.message);

      return;

    }

            // Now use the connection object you got to execute your SQL

    connection.execute(

      selectStatement,   // The select statement

      [180],                                 // The department id we want to retrieve

              // This function is called once connection.execute returns

      function(err, result)                                                                                                      

      {

                // Check the error object. If it’s not null, then…

        if (err) {

                          // …SQL execution failed. Write error message to console…

          console.error(err.message);

                          // … RELEASE the connection and then exit

          doRelease(connection);

          return;

        }

                        // SQL execution was successful. Write the meta-data and data to console

        console.log(result.metaData);

        console.log(result.rows);

                        // RELEASE the connection

        doRelease(connection);

      });

  });

 

The code above should be self-explanatory because of the comments. The “RELEASE” lines need a follow up. To release a connection, we just call “release()” on the connection object. So, we need one last function to complete our simple program:

function doRelease(connection)

{

  connection.release(

    function(err) {

      if (err) {

        console.error(err.message);

      }

    });

}

Save this as “select01.js” in the “oracledb01” folder. To execute it, simply type in:

node select01.js

You should see two lines in output:

[ { name: ‘DEPARTMENT_ID’ }, { name: ‘DEPARTMENT_NAME’ } ]

[ [ 180, ‘Construction’ ] ]

That’s the first step, of course. This entire example is based on the “select1.js” example from the github download (it should be in the examples folder in “oracledb01”). I have just simplified a little and added comments. Note also that this is supposed to be quick and dirty. You won’t put credentials in your source code (it’s better to keep them in the Node Environment variables or a config file). You don’t hard-code your selection criteria. Also, you can recycle your connections instead of opening and closing them each time because the node connector supports connection pooling. You can use “Promises” to streamline the async calls. Most importantly, you can plug this right into an enterprise-grade framework like Express.js or Sails.js and build an enterprise application on an Oracle back-end.

 

Share
This post was posted in Technical and tagged Oracle , Oracle Database , Oracle Node.js Connector , Tony Tauro .
© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Leave a Reply

Your email address will not be published. Required fields are marked *