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