Skip to main content

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.

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
  6. Hi man! Good article! I have one question, its possible connect to Teiid with PHP.. im use PHP a lot.... indeed, i use Codeigniter, you thinks that the connection its posible?
    Thanks! :D

    ReplyDelete
  7. PHP should be possible using their pg connectivity. We have not explicitly tested that path though. If you run into issues, please use the forums or JIRA - https://issues.jboss.org/projects/TEIID

    ReplyDelete

Post a Comment

Popular posts from this blog

Teiid 8.11 Beta1 and 8.10.1 Released

Teiid 8.11 Beta1 is now available from the  downloads  and maven.  Feature highlights since Alpha2 include: TEIID-3434 More caching control over ttls in the result set cache down to the schema/table level. TEIID-3412 MS Access support via the UCanAccess driver. The UCanAccess support is necessary for those running on Java 1.8 as the JDBC ODBC bridge has been removed from the JRE. The waiting continues on EAP 6.4 Alpha1 - it still should be available shortly and should be the platform target for Teiid 8.11 Beta2. Of course, let us know if you find any issues with these early releases.  There's still plenty of time to get fixes into the final release. Teiid 8.10.1 is also available.  It addresses 5 important issues discovered since 8.10 was released: [ TEIID-3409 ] - PostgreSQLExecutionFactory TranslatorProperty annotation in wrong place [ TEIID-3437 ] - Inconsistencies with row count handling [ TEIID-3438 ] - Null value returned from BlobImpl

Tech Tip: Teiid SQL Language MAKEDEP Hint Explained

In this article I will explain what a MAKEDEP hint is, how and when, why it should be used in Teiid. What: MAKEDEP is query hint.  When a query hint is defined in the SQL query it influences the Teiid query planner to optimize the query in a way that is driven by the user. MAKEDEP means "make this as a dependent join". What is a Dependent Join? For example if we have query like: SELECT * FROM X INNER JOIN Y ON X.PK = Y.FK Where the data for X, and Y are coming from two different sources like Oracle and WebService in Teiid, so in relational algebra you can represent above query as Here the result tuples from node X and node Y are being simultaneously fetched by Teiid query engine, then it joins the both the results inside Teiid engine based on the specified X.PK = Y.PK condition and returns the filtered resulted to the user. simple.. Now, what if, if X table has 5 rows and Y table has 100K rows? In order to do the JOIN naively Teiid need sto read all the 5

Teiid 8.13.3 Released

Teiid 8.13.3 is now  available .  In total 8.13.3 addresses 10 issues since 8.13.2: [ TEIID-4028 ] - adding salesforce-34 resource adapter does not work through the cli [ TEIID-4066 ] - Odata translator ClassNotFoundException: com.sun.ws.rs.ext.RuntimeDelegateImpl [ TEIID-4070 ] - Issues with resource adapters with api modules in wildfly [ TEIID-4089 ] - Teiid JDBC driver does not reset the update count when calling getMoreResults(int) [ TEIID-4093 ] - OData authentication fails with NPE when gss-pattern related properties are included in VDB [ TEIID-4096 ] - AssertionError with independent side of a dependent join that has an ordered limit [ TEIID-3050 ] - allow for more incremental insert with iterator [ TEIID-4075 ] - Netezza translator to support common table expressions [ TEIID-4098 ] - Always preserve columns order in google spreadsheets models [ TEIID-4046 ] - OData - $skip is beeing ignor