Monday, February 18, 2013

Access Teiid from node.js

Are you writing a "node.js" application and would like to access Teiid VDB from it?

If "yes", this is currently possible using NPM package "pg".  Since, Teiid supports the PG transport, you can use this PostgreSQL client for "node.js" for accessing the Teiid.

For example if you have VDB called "northwind" deployed on your Teiid server, and it has table called "customers" and you are using default configuration such as

user = 'user'
password = 'user'
host = 127.0.0.1
port = 35432

then you can use following to write simple access program to Teiid
    
    var pg = require('pg');
    var connectionString = "pg://user:user@localhost:35432/northwind"
    pg.connect(connectionString, function(err, client) {
        client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers', function(err, result) {
        console.log(result.rows)
    });
    });
If you want access one row at a time, you can also use event mechanism and write
    var pg = require('pg');
    var connectionString = "pg://user:user@localhost:35432/northwind"
    pg.connect(connectionString, function(err, client) {
        var query = client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers');
        query.on('row', function(row) {
            console.log(row);
        });
        query.on('error', function(error) {
            console.log("failed to query");
        });
        query.on('end', function(error) {
            console.log("closing client");
            client.end();
        });
    });
For more information please take look at

https://npmjs.org/package/pg
https://github.com/brianc/node-postgres

If you do write an application using these please do share your experiences with us.

9 comments:

  1. Which version of TEIID support this feature?
    With TEIID 8.2 there are problems.
    Any hint?
    Thanks
    Luca

    ReplyDelete
    Replies
    1. I tested with 8.3, but it should work with 8.2 as I did not find any fixes that related to postgres port in 8.3 cycle. what errors are you seeing?

      Delete
  2. well I'm seeing nothing it seems that the node application does not issue any request.
    Are you testing it just local or also on different machines?
    I have TEIID on host1 and node on host2 and nothing ...

    ReplyDelete
  3. Luca,

    I only tested locally, if there is any trick you had to make it work, please do explain here for others.

    Thanks

    Ramesh..

    ReplyDelete
  4. Hello all,

    Has anyone tried using a parameterized query with node pg and JBoss? Sending a text query works fine just as you explained (client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers').

    However, if we try:
    client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers WHERE CustomerID = $1', [100])

    Then, the JBoss connection is closed. We can confirm that rows of data are returned, however, the node pg module seems to detect that stream has ended, and thus it throws the error "Error: Connection terminated unexpectedly".

    Has anyone else experienced this?

    ReplyDelete
  5. Hi Unknown,

    Could you turn this question into a JIRA issue? Can you provide information such as the Teiid version and the pg package version?

    ReplyDelete
    Replies
    1. Hi Steven!

      Thank you so much for the fast response! We will go ahead and turn the question into a JIRA issue!

      In the meantime, would you happen to have an example of using a parameterized query with the node pg module and Teiid? Are you aware if other people have successfully done this?

      Thank you so much, Steven!

      Brian

      Delete
    2. Brian,

      Thanks for logging https://issues.jboss.org/browse/TEIID-5014 it will be addressed in subsequent releases.

      Delete