Catch Firebird events with Node.js

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

[

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.

King Foo is a SensioLabs partner