FairCom SQL for Node-RED

Installing SQL Node-RED

Note: The included ctsql.node and ctsqlapi.dll are 64-bit only!

Prerequisites

Make sure you have Node.js, npm, and Python installed on your computer. Python is used for building ffi-napi. To verify this, type the following commands into a shell:

node -v

npm -v

python -V

Node.js and npm are installed from the same package, which is available from Node.js.org. Node.js version 10 or later is required.

Python can be downloaded from the Python.org website.

The first time you use this tutorial, npm may need to build the ffi-napi module. For this to work properly, your computer should have a compiler installed on it. On Linux, this is usually g++. On Windows this is usually the Visual C++ Build Tools. Note that the Windows version of the Node.js installer might have already installed these tools for you.

Installing the Node-RED Node

To install the FairCom SQL Node-RED node:

1. First, make sure Node.js, npm and Python are installed on your system. To verify this, type the following commands into a shell:

   node -v

   npm -v

   python -V (upper-case “V”)

   Node.js and npm are installed from the same package, which is available from Node.js.org. Node.js version 12 or later is required. We are not compatible with Node.js version 8 or 10.

   Python can be downloaded from Python.org.

2. Make sure Node-RED is installed on your system. To install it on Linux you could use:

   sudo npm install -g --unsafe-perm node-red

3. Windows users can use the same command, without the “sudo” at the front. Additional details can be found at Node-RED.org. Note that your computer should have an Internet connection when you issue this command.
4. If Node-RED is running, shut it down to install the FairCom node. Then go into the Node-RED home directory with a command prompt.

   On Windows, this is usually the following folder: C:\Users\my_name\.node-red

   On Linux, this is usually the following folder: ~\.node-red

   If this folder is missing, you might have to start and stop Node-RED once, by using the following command in a command shell, for example:

   > node-red

   After Node-RED fully starts up, press <Ctrl+C> or close the command prompt to terminate it.

5. Issue the following command to install the FairCom SQL Node-RED node:

   npm --prefix . install <path to node-red-contrib-ctreesql folder>

   In the above command, <path to node-red-contrib-ctreesql> is the full path to the node-red-contrib-ctreesql folder which is in the drivers\node-red.sql folder. For example:

   C:\FairCom\c-treeEDGE.Win.Server.x86.64bit.v11.8.1.580\drivers\node-red.sql\node-red-contrib-ctreesql.

   The first time you run this command, it will download packages, so be sure your computer is connected to the Internet. Also, the first time, it may need to build ffi-napi.

   On Windows, this will make a "node-red-contrib-ctreesql" item appear in your C:\Users\my_name\.node-red\node_modules folder.

6. Start Node-RED. For example, enter the following in the command prompt:

   > node-red

   Watch the console output for an error that looks like the following:

   [warn] [node-red-contrib-ctreesql/ctreeSQL] Could not find ctsqlapi library. Neither on path nor locally.

   If you see this error, you will have to make a DLL or shared library available to the OS. On Windows, you will have to edit the PATH to contain a reference to the drivers\ctree.drivers\bin directory of your FairCom product. For example:

   set PATH=%PATH;%C:\FairCom\c-treeEDGE.Win.Server.x86.64bit.v11.8.1.580\drivers\ctree.drivers\bin

   On many Linux-based operating systems, copying drivers/ctree.drivers/lib/libctsqlapi.so into /usr/lib (/usr/lib64 for CentOS) and running ldconfig will make the library available. Make sure Read and Execute permissions are granted to the library.

7. Open a web browser to the following URL (for example): http://localhost:1880

   You should now see a new node, called ctreeSQL, under the "storage" section.

8. Add the new ctreeSQL node from the storage section to your flow.
9. Double-click the ctreeSQL node in your flow:

   

10. Press the pencil icon next to “Add new ctreeSQL_connection…” See the mouse cursor in the following image:

11. Here you can edit the connection settings:

    Set Host to 127.0.0.1.

    Set Port to 6597, or to whatever the SQL_PORT is in your FairCom product's ctsrvr.cfg configuration file.

    Set User to “ADMIN” and set Password to “ADMIN”.

    Set Database to “ctreeSQL”.

    

12. Click Add, Save, or Update.

    (You can ignore the Query Type setting for now.)

13. Click on Done.
14. Next add an "inject" node from the "common" group. Double-click on the "inject" node to edit it and set the topic to your favorite SQL statement, such as:

    select * from admin.syscolumns

    You can leave the payload set to "timestamp".

    Click DONE.

    

15. Add a "debug" node to print the result and connect the three nodes as shown above. Deploy and click on the little button left of the inject node.
16. Open up the debug window and you should see the results of the SQL statement you used in the "inject" node. If you used the suggested "select * from admin.syscolumns", you should see the following:

    

Troubleshooting:

Error "ctsqlConnectError: ctsql failed to connect with error -20212"

The most common cause of this run-time error is the FairCom Database Engine is not running on your machine. The most likely cause is that the c-tree evaluation license times out after 3 hours and shuts down the server. The solution is to restart the server. See Starting the FairCom Database Engine.

Note: If you are using a self-signed certificate, Node-RED may reject the connection because it is considered unauthorized and Node-RED will show a SELF_SIGNED_CERT_IN_CHAIN error on the console. To work around this, set the NODE_TLS_REJECT_UNAUTHORIZED environment variable to 0, and restart Node-RED.

Example for Unix:

$ export NODE_TLS_REJECT_UNAUTHORIZED=0

$ node red.js -v push2omnibus.json