Does node-red-node-mssql support Stored Procedure?

[Luiey]

Hi guys,

I have some difficulties and unable to find the script for executing the stored procedure to insert into database by passing the multiple parameter.

This node is develop by our respective and awesome Node-RED team @DaveCJ on version 0.0.5

Currently I'm adding a stored procedure options in HTML interface where as the node can be handle not just direct CRUD operation but also can execute MSSQL storedproc.

From sample I found at internet searching, mostly the NodeJS script sample perform on SP is on request method where to retrieve data from SP. I can't find the correct way to call the sql for SP in script. But I have seen some file in the nodes inside TDS modules having "special-stored-procedure.js" file. Is it have function call for executing SP?

Sample for request from SP found on blog and forum:-

var sql = require('mssql');

var config = {
    user: 'sa',
    password: '---',
    server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
    database: 'Test'
}

var getCities = function() {
  var conn = new sql.Connection(config);
  conn.connect().then(function(conn) {
    var request = new sql.Request(conn);
    request.input('City', sql.VarChar(30), 'Cbe');
    request.input('NameNew', sql.VarChar(30), 'Cbe');
    request.execute('spTest').then(function(err, recordsets, returnValue, affected) {
      console.dir(recordsets);
      console.dir(err);
    }).catch(function(err) {
      console.log(err);
    });
  });
}

getCities();

//1
function executeStoredProc() {
    //2. 
    var dbConn = new sql.Connection(config);
    dbConn.connect().then(function () {

        //3.
        var request = new sql.Request(dbConn);
        request.input('Salary', sql.Int, 50000)
        .execute("GetAllEmployeesBySalary").then(function (recordSet) {
            //4.
            console.log(recordSet);
            dbConn.close();
        }).catch(function (err) {
            //5.
            console.log(err);
            dbConn.close();
        });
    }).catch(function (err) {
        //6.
        console.log(err);
    });
}
//7.
executeStoredProc();

[steve rickus]

You may want to check flow.node-red.org first -- I think there is already a node that does this: called node-red-contrib-mssql-ps

[Luiey]

Hey steve, this is what needed which is dynamic. But I get stuck on the prepare statement. Can you assist me on how it should be done?

"Error Preparing Statement RequestError: Statement(s) could not be prepared."

Below are what I'm doing. Considering as real situation.

MSSQL SP:-

CREATE PROCEDURE [dbo].[datainsertion]
(
@value1 varchar(50),
@value2 varchar(50),
@value3 datetime,
@value4 varchar(50)
)
AS
BEGIN TRANSACTION
 BEGIN TRY
 --SET NOCOUNT ON added to prevent extra result sets from
 --interfering with SELECT statements.
 SET NOCOUNT ON


 -- Insert statements for procedure here
 INSERT INTO [dbo].[thedata] ([Value1],[Value2],[Value3],[Value4]) VALUES (@value1, @value2, @value3, @value4)
COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ErrorNumber,
 ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState,
 ERROR_PROCEDURE() AS ErrorProcedure,
 ERROR_LINE() AS ErrorLine,
 ERROR_MESSAGE() AS ErrorMessage
    -- ROLLBACK if either DELETE and INSERT failed ....
ROLLBACK TRANSACTION
 END CATCH
IF @@ERROR <> 0
    BEGIN
        RETURN 0;
    END
ELSE
    BEGIN            
        RETURN 1;
    END;
GO

On the execution of SP this are the sample in SQL Management Studio:-

DECLARE @return_value int


EXEC @return_value = [dbo].[datainsertion]
 @value1 = N'ACVV',
 @value2 = N'VVC',
 @value3 = N'2017-10-28 09:45:57',
 @value4 = N'VBCC'


SELECT 'Return Value' = @return_value


GO

Below are the node flow script:-

[{"id":"1b2e0846.d9b838","type":"node-red-contrib-mssql-ps","z":"60f12ccc.74e4d4","server":"b801dfcd.bf2c7","params":[{"paramname":"value1","paramtype":"VarChar","paramoptsone":"50","paramoptstwo":""},{"paramname":"value2","paramtype":"VarChar","paramoptsone":"50","paramoptstwo":""},{"paramname":"value3","paramtype":"DateTime","paramoptsone":"","paramoptstwo":""},{"paramname":"value4","paramtype":"VarChar","paramoptsone":"50","paramoptstwo":""}],"sql":"EXEC [dbo].[datainsertion]\nvalue1 = @value1,\nvalue2 = @value2,\nvalue3 = @value3,\nvalue4 = @value4","name":"","x":610,"y":240,"wires":[["8918cd18.04a86"]]},{"id":"8918cd18.04a86","type":"debug","z":"60f12ccc.74e4d4","name":"","active":true,"console":"false","complete":"false","x":810,"y":240,"wires":[]},{"id":"7010361a.dc3898","type":"inject","z":"60f12ccc.74e4d4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":240,"y":240,"wires":[["77de031c.d2e3cc"]]},{"id":"77de031c.d2e3cc","type":"function","z":"60f12ccc.74e4d4","name":"","func":"function pad(val, padlength, specialchar) { //val: value, padlength: padding length, specialchar: padding other than number e.g. -#@!\n  specialchar = specialchar || '0';         //sample: pad(10,4) return 0010\n  val = val + '';                           //sample: pad(10,4,*) return ****10\n  return val.length >= padlength ? val : new Array(padlength - val.length + 1).join(specialchar) + val;\n}\nfunction Value3DT(){\n    var time = new Date();\n    var year = time.getFullYear();\n    var month = pad(time.getMonth()+1,2);\n    var date1 = pad(time.getDate(),2);\n    var hour = pad(time.getHours(),2);\n    var minutes = pad(time.getMinutes(),2);\n    var seconds = pad(time.getSeconds(),2);\n    return year + \"-\" + month+\"-\"+date1+\" \"+hour+\":\"+minutes+\":\"+seconds;\n}\n\nvar Value4Gen = function (len, bits)\n{\n    bits = bits || 36;\n    var outStr = \"\", newStr;\n    while (outStr.length < len)\n    {\n        newStr = Math.random().toString(bits).slice(2);\n        outStr += newStr.slice(0, Math.min(newStr.length, (len - outStr.length)));\n    }\n    return outStr.toUpperCase();\n};\nfunction Value1Gen() {\n  return 'xxx-xxx-xxx'.replace(/[xy]/g, function(c) {\n    var r = Math.random() * 16 | 0, v = c == 'x' ? r : (r & 0x3 | 0x8);\n    return v.toString(16).toUpperCase();\n  });\n}\nmsg.params =\n{\n    value1: Value1Gen(),\n    value2: \"ZTRX182H\",\n    value3: Value3DT(),\n    value4: Value4Gen(11)\n};\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":240,"wires":[["1b2e0846.d9b838","7faca119.8b8a8"]]},{"id":"7faca119.8b8a8","type":"debug","z":"60f12ccc.74e4d4","name":"","active":true,"console":"false","complete":"params","x":630,"y":300,"wires":[]},{"id":"b801dfcd.bf2c7","type":"mssql-ps-serverconf","z":"","name":"","host":"DEKSTOP","encryption":true,"database":"thesample"}]

I was just confuse the statement is problem on which side and how to cater it.