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.
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:
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
[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
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;
This setup has used the lastest stable version of node red v3.0.1 and been installed using npm with this documentation.
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.
This will add a new node in the storage section called ODBC.
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.
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.
The specific values used for this example are:
At last, we just write the sql query we want to be executed and click Done.
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.