King Foo

King Foo

You are in:

King Foo > Blog > RDBMS > Firebird > Catch Firebird events with Node.js

Blog

Catch Firebird events with Node.js

Posted by
/ / 4 Comments


In this post, I’ll try to explain how you can use Node.js to catch events on Firebird.
Although Microsoft announced last week to contribute resources towards porting Node to Windows, it currently does not run out of the box on Windows. You can try to run it with Cygwin.
In this example I use Ubuntu as the server where Node runs on. Firebird on the other hand can be installed on Linux, Windows and MacOSX.

I first start with installing Node. Ubuntu does have a package nodejs in his repository but when using this, nmp will not work because it needs the command node, which is not included in the Ubuntu package. So I recommend installing node from source:


wget http://nodejs.org/dist/node-v0.4.9.tar.gz
tar xzvf node-v0.4.9.tar.gz
cd node-v0.4.9
./configure
make
make install

When installing Node, some errors can arise
error:


Checking for program g++ or c++          : not found
Checking for program icpc                : not found
Checking for program c++                 : not found
/home/wim/node-v0.4.8/wscript:225: error: could not configure a cxx compiler!

solution:


apt-get install build-essential

error:


error: Could not autodetect OpenSSL support. Make sure OpenSSL development packages are installed. Use configure --without-ssl to disable this message.

solution:


apt-get install libssl-dev

Node should be installed correctly now.
Type in ‘node -v’ to check if the installation succeeded and node is in your path


wim@kingfoo:/$ node -v
v0.4.9

Now the installation of Firebird. In this example, it doesn’t matter if you install the super, classic or superclassic version


sudo apt-get install firebird2.5-superclassic firebird2.5-dev

Set the password for SYSDBA:


dpkg-reconfigure firebird2.5-superclassic

Firebird config

Create a new database for our newly installed Firebird. If you would use a graphical interface for interacting with Firebird, I would recommend FlameRobin


wim@kingfoo:/$ sudo isql-fb 
Use CONNECT or CREATE DATABASE to specify a database
SQL> CREATE DATABASE '/home/wim/kingfoo.fdb'
CON> USER 'SYSDBA' password 'masterkey';

Create a new table:


SQL> CREATE TABLE monkeys
CON> (name varchar(200) not null,
CON> created timestamp);

We want to update the datefield automatically on an insert. This can be done with a trigger:


SQL> SET TERM !! ;
SQL> CREATE TRIGGER CHANGE_CREATED FOR monkeys
CON> ACTIVE 
CON> BEFORE INSERT OR UPDATE
CON> AS
CON> BEGIN
CON> NEW.CREATED = current_timestamp;
CON> END!!
SQL> SET TERM ; !!

A nice feature of Firebird is the support of events. This means that you can fire an event when some action in the database occurs.
To interact with Node, I create a new event:


SQL> SET TERM !! ;
SQL> CREATE TRIGGER NEW_MONKEY_EVENT FOR monkeys
CON> ACTIVE
CON> AFTER INSERT
CON> AS
CON> BEGIN
CON> POST_EVENT 'new_monkey_created';
CON> END!!
SQL> SET TERM ; !!

Luckily, there is a node module available which can interact with Firebird so I’ll install this:


git clone git://github.com/xdenser/node-firebird-libfbclient.git
cd node-firebird-libfbclient/
node-waf configure build
node-waf configure install

Changes are big that, when you’re also using Ubuntu, you get the following error message:


Checking for program g++ or c++          : /usr/bin/g++ 
Checking for program cpp                 : /usr/bin/cpp 
Checking for program ar                  : /usr/bin/ar 
Checking for program ranlib              : /usr/bin/ranlib 
Checking for g++                         : ok  
Checking for node path                   : not found 
Checking for node prefix                 : ok /usr/local 
Checking for program fb_config           : not found 
/home/wim/node-firebird-libfbclient/wscript:16: error: The program ['fb_config'] is required

In the Ubuntuversion of Firebird, there is no program fb_config anymore but we found a solution for this problem.
Go to the directory where you installed node-firebird-libfbclient (if you followed this tutorial, you should already be there) and locate the file ‘wscript’. Open this file and change:


  fb_config = conf.find_program('fb_config', var='FB_CONFIG', mandatory=True)
  fb_libdir = popen("%s --libs" % fb_config).readline().strip()
  conf.env.append_value("LIBPATH_FB", fb_libdir)
  conf.env.append_value("LIB_FB", "fbclient")
  fb_includedir = popen("%s --cflags" % fb_config).readline().strip()
  conf.env.append_value("CPPPATH_FB", fb_includedir)

with:


#fb_config = conf.find_program('fb_config', var='FB_CONFIG', mandatory=True)
#fb_libdir = popen("%s --libs" % fb_config).readline().strip()
conf.env.append_value("LIBPATH_FB", "-L/usr/lib -lfbclient")
conf.env.append_value("LIB_FB", "fbclient")
#fb_includedir = popen("%s --cflags" % fb_config).readline().strip()
conf.env.append_value("CPPPATH_FB", "-I/usr/include/")

Both Firebird and Node are ready, the only thing we have to do is write some code to let Node interact with Firebird


var fb  = require("./node-firebird-libfbclient/firebird");
var http=require('http');
var sys=require('sys');
var con = fb.createConnection();

con.connectSync('/home/wim/kingfoo.fdb','sysdba','masterkey','');
con.addFBevent("new_monkey_created");
con.on("fbevent",function(event,count){

    var rows = null;
    rows = con.querySync("select * from monkeys order by created desc;").fetchSync(1,true);
    con.commitSync();
    console.log("New monkey inserted in the databse:");
    console.log(rows);

});

WaitForFinish(function(){ return finished; },
  function(){
       con.disconnect();
       CleanUp();
       test.done();
  }, 20000);

var finished = false;

function WaitForFinish(finished,clean,timeout){
  var timedout = false;
  var tid = setTimeout(function(){
    timedout = true;
  },timeout);
  process.nextTick(function loop(){
     if(finished.call()||timedout){
       clearTimeout(tid);
       clean.call();
     }
     else process.nextTick(loop);
  });
}

now start the file on the server:


node example.js

Insert some new values in the db:


insert into monkeys(name) values('wim');commit;

When entering the query, node imediately fetch the last record and outputs it:


New monkey inserted in the databse:
[ { NAME: 'wim',
    CREATED: Fri, 01 Jul 2011 09:01:58 GMT } ]

Et voila, when updating a db record, you can push updated data to the browser for example.

  1. 03/11/2011

    Marc Fasel

    Hi Wimm,

    thanks for the write-up. What do you think about the synchronous access the Firebird driver gives you to the database? i thought on of the cool things in Node.js is that you are forced to write asynchronous code because all I/O modules are/ should be asynchronous. The Firebird driver waters this down by offering both synchronous and asynchronous methods of access.

    Anyway keep up the good work!

    Cheers

    Marc

    • 07/11/2011

      wimm

      Hi Marc,

      Although not used in the example above, the module to connect to the database both supports synchronous and asynchronous operations so I don’t think that’s a real issue.

      But if you do use the driver in a synchronous way, it can have a serious impact on the performance when there are many hits on the database.

  2. 03/11/2011

    Jasmine

    Outside the box? Do you mean out of the box?

    • 07/11/2011

      admin

      Yes, indeed. Text above was corrected.

      In the meantime, it does run out of the box on Windows.

Leave a comment

Please wrap all source codes with [code][/code] tags.