Write variables in MSSQL

[wagullo]

Hi, I'm new to the forum and to this Node-red, I am creating a flow that writes me 2 variables (one from the PLC and another given in node red) in a MSSQL database.

I have a problem when defining the given variable in node red (it identifies the PLC that writes to the DB, more PLCs will be added in the future). The variable from the PLC is returned and written correctly, but the one given in node-red is not (maqui). I need two different outputs I think.

The way of writing in MSSQL it also I think is poorly defined.

I think it's a rookie thing ... sorry. Your help will be well received, thank you.

[Steve-Mcl]

If you use node-red-contrib-mssql-plus you can add variable in the UI.

Also, the old MSSQL node is both buggy and abandoned.

Also, returning an array from the function is not the right thing to do. You simply set extra properties of the msg object.

If you share your flow I will demonstrate what I mean.

[wagullo]

Thank you very much Steve, I prefer you show me, I am new and I get lost with so many concepts. This is my flow:

[{"id":"d381fc46.a9b57","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"bae1ef9f.e7aec","type":"s7 control","z":"d381fc46.a9b57","endpoint":"ab1b0b4c.23e","function":"trigger","name":"lectura PLC","x":310,"y":320,"wires":[["a6b880f3.4da9a","caef5eb7.67442"]]},{"id":"2b668d2f.1a8512","type":"inject","z":"d381fc46.a9b57","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"60","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":320,"wires":[["bae1ef9f.e7aec"]]},{"id":"a6b880f3.4da9a","type":"delay","z":"d381fc46.a9b57","name":"","pauseType":"delay","timeout":"3","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":460,"y":320,"wires":[["146bfe2d.e9f892"]]},{"id":"caef5eb7.67442","type":"debug","z":"d381fc46.a9b57","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":470,"y":280,"wires":[]},{"id":"22729ddf.b16102","type":"s7 in","z":"d381fc46.a9b57","endpoint":"ab1b0b4c.23e","mode":"single","variable":"Bobina","diff":true,"name":"","x":110,"y":100,"wires":[["2a0fffc0.3f985"]]},{"id":"146bfe2d.e9f892","type":"MSSQL","z":"d381fc46.a9b57","mssqlCN":"c2b5f2da.cf8d","name":"MSSQL","outField":"payload","returnType":0,"throwErrors":1,"query":"\nDECLARE @Maquina VARCHAR\nDECLARE @Bobina BIT\n\nSET @Bobina = {{{flow.Bobina}}};\nSET @Maquina = '{{{msg.maqui}}}';/*here the problem*/\n\nIF not exists(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tablaLogo')\n    CREATE TABLE tablaLogo\n\n        (\n            id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,\n            FechaHora DATETIME,\n            Maquina VARCHAR,\n            Bobina INT\n        )\n\nELSE INSERT INTO tablaLogo\n            (\n                FechaHora,\n                Maquina,\n                Bobina\n               \n            )\n            VALUES\n            (\n                GETUTCDATE(), /*para la hora local poner CURRENT_TIMESTAMP */\n                @Maquina,\n                @Bobina\n               \n            )\n\n","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"rows","rowsType":"msg","params":[],"x":620,"y":320,"wires":[[]]},{"id":"2a0fffc0.3f985","type":"change","z":"d381fc46.a9b57","name":"","rules":[{"t":"change","p":"payload","pt":"msg","from":"true","fromt":"bool","to":"1","tot":"num"},{"t":"change","p":"payload","pt":"msg","from":"false","fromt":"bool","to":"0","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":100,"wires":[["694eac0f.308ba4"]]},{"id":"3809eee7.ef8312","type":"debug","z":"d381fc46.a9b57","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":590,"y":60,"wires":[]},{"id":"694eac0f.308ba4","type":"function","z":"d381fc46.a9b57","name":"","func":"var maqui = {payload:'novoflex'}; /*this variable*/\nflow.set(\"Bobina\", msg.payload); /*the variable of PLC is correct*/\n\nreturn [msg, maqui];\n","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":440,"y":100,"wires":[["3809eee7.ef8312"],["50340b1e.fc96a4"]]},{"id":"50340b1e.fc96a4","type":"debug","z":"d381fc46.a9b57","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":590,"y":120,"wires":[]},{"id":"ab1b0b4c.23e","type":"s7 endpoint","transport":"iso-on-tcp","address":"192.168.0.3","port":"102","rack":"0","slot":"2","localtsaphi":"01","localtsaplo":"00","remotetsaphi":"02","remotetsaplo":"00","connmode":"tsap","adapter":"","busaddr":"","cycletime":"0","timeout":"1500","name":"S7-LOGO8","vartable":[{"addr":"DB1,X1064.0","name":"Bobina"},{"addr":"DB1,INT1118","name":"Altura"}]},{"id":"c2b5f2da.cf8d","type":"MSSQL-CN","tdsVersion":"7_4","name":"MSSQL","server":"192.168.0.4","port":"1433","encyption":true,"trustServerCertificate":true,"database":"LOGO","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]

Admin edit - wrap code with three backticks

[Steve-Mcl]

Hi, i dont have 9and not going to install) S7 nodes.

Also, I dont have your PLC or its data - so to test this I will need a copy of the data to simulate.

Add "complete message" debugs to the indicated places

copy the values using the copy value button

Paste the 2 copied values into a reply

```
like this - between backticks
```

[Steve-Mcl]

This might be beter - but i cannot read your language and I dont know what the data should be as you did not reply to my previous message.

Demo flow...

[{"id":"22729ddf.b16102","type":"s7 in","z":"d381fc46.a9b57","endpoint":"ab1b0b4c.23e","mode":"single","variable":"Bobina","diff":true,"name":"","x":120,"y":100,"wires":[["09b01ed1630b1e23","694eac0f.308ba4"]]},{"id":"694eac0f.308ba4","type":"function","z":"d381fc46.a9b57","name":"preapre data","func":"\nmsg.payload = {\n    name: \"novoflex\",\n    value: msg.payload == true ? 1 : 0\n}\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":100,"wires":[["c2a0a1e9e3da0ff9"]]},{"id":"09b01ed1630b1e23","type":"debug","z":"d381fc46.a9b57","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":310,"y":160,"wires":[]},{"id":"c2a0a1e9e3da0ff9","type":"MSSQL","z":"d381fc46.a9b57","mssqlCN":"c2b5f2da.cf8d","name":"INSERT INTO tablaLogo","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO tablaLogo\n            (\n                FechaHora,\n                Maquina,\n                Bobina\n               \n            )\n            VALUES\n            (\n                GETUTCDATE(), /*para la hora local poner CURRENT_TIMESTAMP */\n                @Maquina,\n                @Bobina\n               \n            )\n\n","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"Maquina","type":"VARCHAR","valueType":"msg","value":"payload.name","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Bobina","type":"Bit","valueType":"msg","value":"payload.value","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":570,"y":100,"wires":[[]]},{"id":"e22ea2f63cb0d99a","type":"MSSQL","z":"d381fc46.a9b57","mssqlCN":"c2b5f2da.cf8d","name":"Create Table if not exist","outField":"payload","returnType":0,"throwErrors":1,"query":"\nIF not exists(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tablaLogo')\n    CREATE TABLE tablaLogo\n        (\n            id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,\n            FechaHora DATETIME,\n            Maquina VARCHAR,\n            Bobina INT\n        )\n","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"rows","rowsType":"msg","params":[],"x":570,"y":40,"wires":[[]]},{"id":"2d6626128e418432","type":"inject","z":"d381fc46.a9b57","name":"Initialise","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"60","crontab":"","once":true,"onceDelay":"0.2","topic":"","payload":"true","payloadType":"bool","x":120,"y":40,"wires":[["e22ea2f63cb0d99a"]]},{"id":"c2b5f2da.cf8d","type":"MSSQL-CN","tdsVersion":"7_4","name":"MSSQL","server":"192.168.0.4","port":"1433","encyption":true,"trustServerCertificate":true,"database":"LOGO","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]