Connecting LX with Node-RED using ODBC

This is a quick guide on how to configure the graphic ETL tool Node-RED base on Node.js with LeanXcale Database using ODBC.

Configure ODBC

LeanXcale’s ODBC driver is platform dependent and for this guide the plaftorm used has been Ubuntu 20.04. In case of wanting to replicate this using another platform, you can follow the how to configure ODBC

Download the latest released ODBC driver from the drivers download page

With the three libraries downloaded we just need to create two configuration files: odbc.ini and odbcinst.ini. The following is an example configuration:

odbc.ini

This configuration is assuming the LX database to be running on the same node as the ODBC client.

[lxodbcdsn]
Description     = test odbc
Driver          = Lxodbc
Trace           = TRACE
TraceFile       = sql.log
Host 		= localhost
Database        = db
Port            = 1529

UserName        = APP
Password        = APP
Schema 		= APP
NonoTotal	= 1024

TZAdjust        = null

odbcinst.ini

[Lxodbc]
Description     = LX driver
Driver          = path to libodbclx.so
Setup           = path to libodbclxS.so

Along with this we need to configure some env variables:

export ODBCINI= path to odbc.ini
export ODBCSYSINI=path to folder with dowloaded libraries
export LD_LIBRARY_PATH=path to folder with dowloaded libraries

LX setup

For this test, a LX database has been prepared with a single table and few data on it. It can be created using the following SQL:

CREATE TABLE Persons (
  ID int NOT NULL,
  PRIMARY KEY (ID));

INSERT INTO Persons (ID)
 values (1);
INSERT INTO Persons (ID)
 values (2);
INSERT INTO Persons (ID)
 values (3);
INSERT INTO Persons (ID)
 values (4);
INSERT INTO Persons (ID)
 values (5);
INSERT INTO Persons (ID)
 values (6);
INSERT INTO Persons (ID)
 values (7);
INSERT INTO Persons (ID)
 values (8);
INSERT INTO Persons (ID)
 values (9);
INSERT INTO Persons (ID)
 values (10);
INSERT INTO Persons (ID)
 values (11);
INSERT INTO Persons (ID)
 values (12);

select * from Persons;

Node-RED configuration

This setup has used the lastest stable version of node red v3.0.1 and been installed using npm with this documentation.

Add ODBC extension node

In order to create an ODBC connection to a database, an extension must be added to Node-RED. It can be installed directly for the UI using the command palette or installing it with npm. In order to install it in the correct node environment, we will install it from the user interface.

In settings → manage palette, we will install in the install tab the package @ais_automation/node-red-contrib-odbc.

install-odbc

This will add a new node in the storage section called ODBC.

Configure ODBC Node

We will create a very simple flow that just executes a select \* from a table. We add a inject node, then the mentioned ODBC and then a debug node to print the result from the query. The structure of the flow is the following.

flow

In order to configure the ODBC node, first we create a connection. This connection will rely on the configuration used in the odbc.ini file created previously, nevertheless we can override properties in the connection string if wanted.

odbc-conf

The specific values used for this example are:

  • Connection: DSN=lxodbcdsn;Driver=Lxodbc;UID=APP;PWD=APP;Trace=DEBUG;TraceFile=/home/jesus/POCS/CTT/odbc/sql.log;UserName=APP;Password=APP;Host=localhost;Port=1529;Database=db

  • Username: APP

  • Password: APP

At last, we just write the sql query we want to be executed and click Done.

odbc-conf2

As it can be seen, the result of the query is stored in msg.paylad, that will be printed afterwards to check the query.

Finally, if we Deploy and execute the flow, we will see the results in the debug console.

execution