MSSQL connection and programming in a 'Function' vs. using MSSQL nodes

General
1

I've had some trouble in the recent past with the pre-definded MSSQL nodes and connection errors. Errors are recent with newer versions installed. One option is to revert to prior versions. I'm not sure if these are problems with versions or Microsoft updates etc... Errors exist where they did not before. All this leaves my Node-RED development in limbo until I can resolve issues around the errors.

So, I need a robust means to control this environment outside version status of the node developers. Can I use a Javascript Function Node to query using Node.js.

The following are instructions from https://www.dotnetcurry.com/nodejs/1238/connect-sql-server-nodejs-mssql-package.

npm install -g tsd

This will install a Typescript definition for the VSCode so that we can get the help of the intellisense for various packages.

tsd query node --action install
The above command provides Node.js intellisense for the application.
npm install mssql -g
This command will install mssql package in node-modules folder.
tsd query mssql --action install
This will install mssql intellisense for the current application.

There are examples of Javascript and running queries. Can I expect good results from this or am I mixing apples and oranges with Node-RED and node.js?

Please help.

HP_APCC.

2

The nodes generally rely on an underlying library, just as you will be using this route.

3

ukmoose,
Thanks for the reply. I would need to run "npm install mssql -g" in order for the subsequent calls to "mssql" and run the connection and thus queries. Will this run as expected assuming the Function node is used with similar script? this is copied from the aforementioned web page. as an example.

//1.
var sql = require('mssql');
//2.
var config = {
    server: 'localhost',
    database: 'Company',
    user: 'sa',
    password: 'sa',
    port: 1433
};
//3.
function loadEmployees() {
    //4.
    var dbConn = new sql.Connection(config);
    //5.
    dbConn.connect().then(function () {
        //6.
        var request = new sql.Request(dbConn);
        //7.
        request.query("select * from EmployeeInfo").then(function (recordSet) {
            console.log(recordSet);
            dbConn.close();
        }).catch(function (err) {
            //8.
            console.log(err);
            dbConn.close();
        });
    }).catch(function (err) {
        //9.
        console.log(err);
    });
}
//10.
loadEmployees();
4

Not out of the box.

You would need to include the require command in your settings is file and follow how to get it in a function node as per the documentation.

You will also need to ensure that the function node returns an object, and handle all the different error cases.

Also the chances are you wil be using the same library as the nodes you want to avoid.

5

ukmoose,
Thank you for your reply. The reason which I ask these questions is that I'm having trouble with "node-red-contrib-mssql-plus" after upgrading Node-RED and/or both. with the node approach I cannot dictate connections, they seem to be controlled internally and maintained. The connection is then lost to one of the databases defined and then instead of re-connecting, it just issues an error. I've had to re-start Node-RED in order to recover. That is not tolerable.

I absolutely need a reliable means to read and write data to my network MSSQL Server(s) and maintain multiple connections for data collection on multiple processes.

So, I was thinking that I would try a "Function" to do these sql connections and queries that way I could program in my own space the responses to errors, end connections at completion of tasks, etc.

Am I on the right track if I just uninstall those current nodes of "node-red-contrib-mssql-plus" etc. and then script in javascript function connections, queries and disconnect. It seems that all that would be in conflict would be the current installation of "node-red-contrib-mssql-plus" or the like.

Kind Regards,

6

So why not work with the author of "node-red-contrib-mssql-plus" to improve the node that exists?

7

Very good question. I've gone to Gethub and left messages and outlined the issue. Others have as well contributed to the issue comments. It is marked with a "Bug". There has been activity, but none on the issue we outlined. There are other nodes which I have tried, but they all either crash Node-RED, or have similarly error producing results. I'm was hoping that using the features in "mssql" available in node.js, the timeline for usable code would be less. : (
I need to dable in advanced node creation or just get the database access (js mssql) working in a function node.

8

Have you considered forking the project. Perhaps create a pull request for the author?

If he doesn't accept it, publish your fork with alternative name.

Perhaps node-red-contrib-mssql-ex or something?

Would be good for you and the community. Win win :slight_smile:

Just a suggestion.

9

Steve-Mcl,
I was in the code a few minutes ago looking at it. I've got to get more familiar with javascript and what is going on with this function. I think there is something with the connect and disconnect. It needs a means of disconnecting and re-connecting if an error exist under circumstances. This would entail signals from the flow and might be more complex than I had in mind. At least for my level at this point. I definitely need the assistance of someone more in the know than me. The flows with errors, just stay in error. You need to re-start Node-RED in order to recover.