search

xdenser / node-firebird-libfbclient

Firebird SQL binding
MIT License
82 stars 34 forks source link

readme

C++ NodeJS module to work with Firebird SQL Server. Uses fbclient library and with a few tweaks it can use libfbembedded

Firebird Logo

NPM version NPM downloads Mozilla License Build Status Build status

NPM NPM

Features

As for now in plans are:

Getting Started

Under Linux, Windows and MacOS X

You will need: NodeJS (tested with v8.x or more) Firebird (tested with v2.5.x and v3.x)

On Windows you need the same requirements like Atom editor for building native C++ extensions

Get Firebird.

Update your path: export PATH=$PATH:/opt/firebird/bin

Create some Database: 

isql -user sysdba -password masterkey
CREATE DATABASE 'test.fdb';
CONNECT 'test.fdb';
CREATE TABLE TEST (id integer, name varchar(50));
exit;

Clone repository and build module

git clone git://github.com/xdenser/node-firebird-libfbclient.git
npm install

Alternate way is to install directly from github

npm install xdenser/node-firebird-libfbclient

or use the version from npmjs:

npm install firebird

To run tests update ./tests/config.js with your test database connection parameters and

npm install nodeunit
./node_modules/.bin/nodeunit tests/def

Play with it from node:

var fb  = require("./firebird");
sys = require("sys"); 
var con = fb.createConnection();
con.connectSync('test.fdb','sysdba','masterkey','');
con.querySync("insert into test (id,name) values (5, 'new one')");
var res = con.querySync("select * from test");
con.commitSync();
var rows = res.fetchSync("all",true);
console.log(sys.inspect(rows));

Check also samples directory and this sample application.

Links

Reference

createConnection([options]) method will create Firebird Connection object for you.

Optional options object may contain charset setting and conversion function

options = {
   lc_ctype: string // alternative connection charset - default is "UTF8", see FB/IB documentation for possible values
   lc_ctype_decode: (Buffer) => string // conversion function for text/varchar fields - this allows only to read fields, if not provided and lc_ctype is not UTF8 text fields will be returned as Buffer from FBResult
}

Connection object

Handles database connection and queries. Supports Synchronous and Asynchronous operation.

Connection object members

function connectSync(database, username, password, role);

where

Connects you to database, raises exception on error (try to catch it). Returns undefined.

function connect(database, username, password, role, callback);

where first four parameters same as in connectSync()

Asynchronously connects you to Database. Returns udefined.

function disconnect();

Dconnects from database. Returns udefined.

connected;

A boolean readonly property indicating if Connection object is connected to database

function querySync(sql);

Executes SQL query. Returns FBResult object in case of success. Raises error otherwise.

function query(sql, callback);

Asynchronously executes query. Returns undefined. 

function addFBevent(name);

Registers connection to listen for firebird event name, called from PL\SQL (in stored procedures or triggers) with post_event 'name'. You may set callback for event with connection.on('fbevent', function(name, count){ <your code>));. Where name is event name, and count is number of times event were posted.

function deleteFBevent(name);

Unsubscribes connection from getting events for name.

function commitSync();

Synchronously commits current transaction.

Notes: There is only one transaction associated with connection. Transacation is automatically started before any query if connection does not have active transaction (check inTransaction property). You also should note that DDL statements (altering database structure) are commited automatically. To run quieries in context of other transaction use Transaction object.

function commit(callback);

Asynchronous commit transaction.Read notes in commitSync();.

function rollbackSync();

Synchronously rollbacks current transaction. Read notes in commitSync();.

function rollback(callback);

Asynchronously rollbacks current transaction. Read notes in commitSync();.

function startSync();

Synchronously starts new default transaction. The default transaction should be not in started state before call to this method. Read notes in commitSync();.

function start(callback);

Asynchronously starts new default transaction.. Read notes in commitSync();.

function prepareSync(sql);

Synchronously prepares SQL statement and returns FBStatement object.

inTransaction;

A boolean readonly property indicating if connection is in started transaction state.

function newBlobSync();

Creates new FBblob object and opens it for write. After finishing write operation and closing blob one may insert it in database passing as parameter to exec, execSync methods of FBStatement object.

function startNewTransactionSync();

Creates new Transaction object and starts new transaction. Returns created object.

function startNewTransaction(callback);

Creates new Transaction object and starts new transaction. Returns created transaction object in callback.

Transaction object

Represents SQL transaction. To get instance of this object call startNewTransactionSync or startNewTransaction methods of Connection object. Transaction objects may be reused after commit or rollback.

function querySync(sql);

Executes SQL query in context of this transaction. Returns FBResult object in case of success. Raises error otherwise.

function query(sql, callback);

Asynchronously executes query in context of this transaction. Returns undefined. 

function commitSync();

Synchronously commits this transaction.

Notes: Transacation is automatically started before any query in context of this object if this object does not have active transaction (check inTransaction property). You also should note that DDL statements (altering database structure) are commited automatically.

function commit(callback);

Asynchronous commit transaction.Read notes in commitSync();.

function rollbackSync();

Synchronously rollbacks transaction. Read notes in commitSync();.

function rollback(callback);

Asynchronously rollbacks transaction. Read notes in commitSync();.

function startSync();

Synchronously starts transaction. The transaction should be not in started state before call to this method. Read notes in commitSync();. See inTransaction property.

function start(callback);

Asynchronously starts new transaction. Read notes in commitSync();.

function prepareSync(sql);

Synchronously prepares SQL statement and returns FBStatement object in context of this transaction.

Note: only prepare operation runs in context of the transaction. To execute result statement in context of this or other transaction use methods execInTrans/execInTransSync of returned statement object and pass started transaction object as argument.

inTransaction;

A boolean readonly property indicating if this transaction is in started state.

FBResult object

Represents results of SQL query if any. You should use this object to fetch rows from database. Each row may be represented as array of field values or as object with named fields.

Data types

Here is Firebird to Node data type accordance:

Firebird Node
DATE -> Date
TIME -> Date
TIMESTAMP -> Date
CHAR -> String
VARCHAR -> String
SMALLINT -> Integer
INTEGER -> Integer
NUMERIC -> Number
DECIMAL -> Number
FLOAT -> Number
DOUBLE -> Number
BLOB -> FBblob

FBResult object members

function fetchSync(rowCount, asObject);

Synchronously fetches result rows. If you pass "all" as rowCount - it will fetch all result rows. If you pass less rowCount than are actually in result, it will return specified number of rows. You may call fetchSync multiple times until all rows will be fetched. If you specify more rowCount than available it will return only actual number of rows.

function fetch(rowCount, asObject, rowCallback, eofCallback);

Asynchronously fetches rows one by one. rowCallback is called for each fetched row. eofCallback is called when whole operation is complete. eof indicates if end of result set was met.

FBStatement object

Represents prepared SQL query (returned by Connection.prepare() and Connection.prepareSync()). FBStatement is derived form FBResult class. So it can fetch rows just like FBresult object after call to execSync, exec methods.

FBStatement object members

function execSync(param1, param2, ..., paramN);

Synchronously executes prepared statement with given parameters. You may fetch rows with methods inherited from FBResult. Statement is executed in context of default connection transaction.

function execInTransSync(transaction, param1, param2, ..., paramN);

Same as execSync but executes statement in context of given Transaction obejct.

function exec(param1, param2, ..., paramN);

Asynchronously executes prepared statement with given parameters. FBStatement emits 'result' or 'error' event. You may fetch rows with methods inherited from FBResult after 'result' event emitted. Statement is executed in context of default connection transaction.

function execInTrans(transaction, param1, param2, ..., paramN);

Same as exec but executes statement in context of given Transaction obejct.

FBblob object

Represents BLOB data type.

FBblob object members

function _openSync();

Synchronously opens blob for reading.

function _closeSync();

Synchronously closes previously opened blob.

function _readSync(buffer);

Synchronously reads BLOB segment (chunk) into buffer. Tries to fill whole buffer with data. Returns actual number of bytes read. 

function _read(buffer, callback);

Asynchronously reads BLOB segment (chunk) into buffer. Tries to fill whole buffer with data. 

function _readAll([[initialSize], [[chunkSize], [callback]]]);

Asynchronously reads all data from BLOB field. Object emits events while reading data error, drain',end`.

function _writeSync(buffer,[len]);

Synchronously writes BLOB segment (chunk) from buffer. Returns number of bytes actually writen.

function _write(buffer,[len],[callback]);

Asynchronously writes BLOB segment (chunk) from buffer and calls callback function if any.

Stream object

Represents BLOB stream. Create BLOB stream using var strm = new fb.Stream(FBblob);. You may pipe strm to/from NodeJS Stream objects (fs or socket). You may also look at NodeJS Streams reference.