Node.js & Oracle db - Not so bad union

Overview

When I was playing around with Node.js at work in scripting data files I thought about how nice it would be to directly use Oracle rather then run my queries in TOAD and export the data to a file for processing.   I can't say this idea was mine but I do think it's easier to get your hands around then this article (but then I'm slow) Combining java and Node.js through redis pub/sub and a json remote interface

Basic Idea

This proof of concept project is to provide access to a Oracle database from a Node.js client.


A java app is run from the command line and subscribes to 2 channels 
(query and storedProc) with redis using java lettuce client. It waits for messages. 


When it receives a message, the message contains the query or storedProc information and arguments.  The oracle database is accessed and the data is published as JSONARRAY of JSONObjects.  It continues to wait for more messages.


The redis node.js client (oraclePubSub.js) has 2 subscribers and 2 publishers.  The publishers send query or storedProc requests.  The subscribers listen for the messages containing the results.  The test client (testPubSub.js) creates two requests and processes the responses.

To run


 -  Open new terminal window

• Install node.js
• Install redis-2.4.2 - (I used the windows binary https://github.com/dmajkic/redis)
• Enter "redis-server"

  - Open new terminal window

• Install redis - this is the node.js redis client use https://github.com/mranney/node_redis 
• do "npm install redis"
• git clone git://github.com/bartonhammond/OraclePubSub.git
• cd OraclePubSub\java\src\main\java
• edit jdbc.properites - this assumes the sample HR schema is available
• cd OraclePubSub\sql 
• define hr.get_emp_rs.sql in hr.schema.  Otherwise test will fail
• cd OraclePubSub\java
• mvn package
• cd target
• enter "java -jar oraclepubsub-0.0.1-jar-with-dependencies.jar"  - This will hang waiting for messages.

   - Open different terminal window

• cd OraclePubSub
• node testPubSub.js

Code review

Java

The Java part of this is rather straight forward.  I won't go into the Oracle JDBC stored procedure or query except to say it's a toy program and I only want to prove to myself it could be done.   The interesting part here is in two methods:

public void processMessages() throws InterruptedException, Exception {
    Jdbcquery jdbcQuery = new JdbcQuery();
    while (true) {
        ChannelMessage cm = channelMessages.take();
        if (cm.channel.equals("query")) {
            JSONArray json = jdbcQuery.performQuery(cm.message);
            redis.publish("query-output", json.toString());
            
        } else if (cm.channel.equals("storedProc")) {
            JSONArray json = jdbcQuery.performRefCursor(cm.message);
            redis.publish("storedProc-output", json.toString());
        }
    } 
}

The ChannelMessage class has only 2 attributes - the channel listening to and the message.  The message contains the query or stored procedure. Depending on the channel, in line 6 or 10 a JSONArray is returned with the data ready to be published to the queue that the Node.js client is subscribed to.

Node.js

var redis = require("redis");

//Pair for storedProc
var storedProcSubscribe = redis.createClient();
var storedProcPublish = redis.createClient();

//Pair for query
var querySubscribe = redis.createClient();
var queryPublish = redis.createClient();

exports.setup = function () {
   storedProcSubscribe.on("ready", function () {
       storedProcSubscribe.subscribe("storedProc-output");
    });
   querySubscribe.on("ready", function () {
       querySubscribe.subscribe("query-output");
    });

}

exports.performStoredProc = function(proc, cb) {
    storedProcSubscribe.on("message", function (channel, message) {
        cb(message);
    });
    storedProcPublish.publish("storedProc",proc);
};

exports.performQuery = function(query, cb) {
    querySubscribe.on("message", function (channel, message) {
        cb(message);
    });
    queryPublish.publish("query",query);
};

//Client call to end the listening
exports.end = function end() {
    querySubscribe.end();
    queryPublish.end();
    storedProcSubscribe.end();
    storedProcPublish.end();

}

This code is really just 2 pairs of subscribers and listeners.  One listener and subscriber each for the query and storedProc.  Lines 4 - 9 are creating the redis clients.  

The setup function on line 11 subscribes for the output from Java.   For the storedProc, when  the message comes in it will be on line 22.  For the query, on line 29.  When the client Node call performStoredProc or performQuery,  they will publish their sql in the channels "storedProc" and "query" respectively.  

Finally, the end function on line 36 ends all the pub sub.

Let's look at the Node client.

//Setup the pub & sub
oraclePubSub.setup();

//Use async so that both can run in parallel
//and at conclusion the end can be invoked
//so script ends gracefully
async.parallel({

    //This query is same as stored proc so that testing was easier
    query: function(callback) {
        oraclePubSub.performQuery("select first_name, last_name, email,employee_id from HR.EMPLOYEES where department_id = 60 order by last_name, first_name asc",function(data){
            callback(null,data);
        });
    },
    storedProc: function(callback) {
        var foo = {};
        foo.sp = "BEGIN hr.get_emp_rs(?, ?); END;";
        //Do arg_types/args in parallel
        foo.arg_types = [oraclePubSub.sqlType.INTEGER];
        foo.args = [60];
        //What column contains the ResultSet
        foo.rs = 2;
        var fooStr = JSON.stringify(foo);
        oraclePubSub.performStoredProc(fooStr,function(data){
            callback(null,data);
        });
    },
},
               /**
                  * Results contains array 
                  */
               function(err,results) {
                   processQueryResults(results.query);
                   processStoredProc(results.storedProc);
                   oraclePubSub.end();
               });


function processQueryResults(queryResults){
    var qrArray = eval(queryResults);
    qrArray.forEach(function(employee) {
        console.log(employee);
    });
    
}
function processStoredProc(storedProcResults) {
    var sprArr = eval(storedProcResults);
    sprArr.forEach(function(employee) {
        console.log(employee);
    });
}

On line 2, I'm calling the setup we discussed earlier.  Notice on line 7 I'm using async.parrallel https://github.com/caolan/async- this allows my two queries to run in any order and I don't call my "end" function until both queries are done.  But I do want to call my end function when I am done.  That way I can merge the results if I choose first and then cleanly end the session.

On line 10, I'm performing a simple query against the demo database provided with Oracle.   Following the pattern of async parallel processing the results will be provided in line 32, the anonymous function.  Notice that the results object has two attributes - query and storedProc.  After processing these results, I can successfully end the session.


There are other async opportunities in this example but it's working well enough that I was able to improve on it and use it in my daily work.  Imagine that!