Trivial problem getting INPUTs as UPDATE TO MSSQL

Post Reply
Aimovoria
Posts: 1
Joined: 24 Jan 2022, 10:52

Trivial problem getting INPUTs as UPDATE TO MSSQL

Post by Aimovoria »

Hi there guys,

I'm completely new to the world of Revolution PI and started to work my way thru and already got stopped on a kinda trivial problem that I can't pass :),

BACKGROUND:
I created database - RevPi with three tables for each woodworking machine:
- dbo.Klipsiarka (Wood clipper) with two columns "Plyta","Klips" (translated to "Board","Clips") - both accepts null values,
- dbo.Klownica (Wood riveter) with two columns "KlowaCzyPlyta","KlowaUderzenie" (tranlated to "RivetOrBoard","RivetHit") - both accepts null values,
- dbo.Wielowrzecionowka (Multi-spindle for wood) with one column "Wiercenie" (translated to "Drilling")

Equipment:
- Revolution RevPi Connect+ 16GB,
- Revolution RevPi DI (digital inputs expansion for 16 inputs)

For:
1) dbo.Klipsiarka has two inputs (I_1 / I_2)
a) I want to check if there is any board (0,1) if there is then when "I_1" is 1 - update value from "I_1" to dbo.[Klipsiarka].[Plyta] as int (1),
b) also when "I_1" == 1, and "I_2" occurs as state 1 then - update value from "I_2" to dbo.[Klipsiarka].[Klips] as int (1) -- this one will change states to 0 and 1 (I want to update value with int (1) everytime when "I_1" == 1 and "I_2 == 1)

2) dbo.Klownica (similar to above dbo.Klipsiarka) has two inputs (I_3 / I_4)
a) same to above (1.a) but update everytime table when "I_3" is 1 - update value to dbo.[Klownica].[KlowaCzyPlyta] as int (1),
b) similar to above (1.b) but update everytime table when "I_3" == 1 and "I_4" == 1 - update value from "I_4" to dbo.[Klownica].[KlowaUderzenie] as int (1)

3) dbo.Wielowrzecionowka has only one input "I_5"
a) when "I_5" == 1 then - update value to dbo.[Wielowrzecionowka].[Wiercenie] as int (1).

I tried defining as I_1 (Input1) as var [something], let [something], msg.payload as I_1 and I cannot get it to work to update all values into my table/column because it doesn't know what "I_1" is, same to other inputs.
Please help, I have kinda deadline in a few days and I need to extend this for other things as I stopped on this probably very trivial for You guys problem which I cannot solve.. I would really appreciate for a solution or maybe getting into this simple project and correcting what I did wrong.

Thank You for every solution or criticism.. ;)

CLIPBOARD LINK BELOW:

Code: Select all

[{"id":"8a05e5c5.7e79d8","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"15237651.20281a","type":"debug","z":"8a05e5c5.7e79d8","name":"","active":true,"tosidebar":true,"console":false,"complete":"payload","statusVal":"","statusType":"auto","x":650,"y":80,"wires":[]},{"id":"eac3e9b1.b2f0e8","type":"MSSQL","z":"8a05e5c5.7e79d8","mssqlCN":"df8c0b88.91b0a8","name":"MSSQL","query":"","outField":"payload","x":460,"y":80,"wires":[["15237651.20281a"]]},{"id":"f426b3b3.6a382","type":"revpi-single-input","z":"8a05e5c5.7e79d8","server":"36b43edb.a540f2","inputpin":"I_1","x":110,"y":40,"wires":[["f25c5439.2b7208"]]},{"id":"ca767bc4.7061f8","type":"revpi-single-input","z":"8a05e5c5.7e79d8","server":"36b43edb.a540f2","inputpin":"I_2","x":110,"y":120,"wires":[["f25c5439.2b7208"]]},{"id":"f25c5439.2b7208","type":"function","z":"8a05e5c5.7e79d8","name":"Function","func":"if (rpi.io.InputValue_1 == \"1\" && rpi.io.InputValue_1 == \"2\") {\n    msg.payload = \"INSERT INTO [RevPi].[dbo].[Klipsiarka] (Klips, Plyta) VALUES ('1','1')\";\n} else if (rpi.io.InputValue_1 == \"1\" && rpi.io.InputValue_2 == \"0\") {\n    msg.payload = \"INSERT INTO [RevPi].[dbo].[Klipsiarka] (Plyta) VALUES ('1')\";\n} else {\n    msg.payload = \"BRAK DANYCH\";\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":80,"wires":[["eac3e9b1.b2f0e8"]]},{"id":"ea4cfb84.583028","type":"debug","z":"8a05e5c5.7e79d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":650,"y":240,"wires":[]},{"id":"36ee34b.f1a01cc","type":"MSSQL","z":"8a05e5c5.7e79d8","mssqlCN":"df8c0b88.91b0a8","name":"MSSQL","query":"","outField":"payload","x":460,"y":240,"wires":[["ea4cfb84.583028"]]},{"id":"9061def2.f7037","type":"revpi-single-input","z":"8a05e5c5.7e79d8","server":"36b43edb.a540f2","inputpin":"I_3","x":110,"y":200,"wires":[["485aca8d.6403b4"]]},{"id":"9e6d3c1b.2622c","type":"revpi-single-input","z":"8a05e5c5.7e79d8","server":"36b43edb.a540f2","inputpin":"I_4","x":110,"y":280,"wires":[["485aca8d.6403b4"]]},{"id":"485aca8d.6403b4","type":"function","z":"8a05e5c5.7e79d8","name":"Function","func":"let KlowaCzyPlyta = I_3\nlet KlowaUderzenie = I_4\n\nif (KlowaCzyPlyta == \"1\" && KlowaUderzenie == \"1\") {\n    msg.payload = \"PLYTA 1 - UDERZENIE 1\";\n    payload = \"INSERT INTO [RevPi].[dbo].[Klownica] (KlowaCzyPlyta, KlowaUderzenie) VALUES ('1','1')\";\n} else if (KlowaCzyPlyta == \"1\" && KlowaUderzenie == \"0\") {\n    msg.payload = \"PLYTA 1 - UDERZENIE 0\";\n    payload = \"INSERT INTO [RevPi].[dbo].[Klownica] (KlowaCzyPlyta) VALUES ('1')\";\n} else {\n    msg.payload = \"BRAK DANYCH\";\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":240,"wires":[["36ee34b.f1a01cc"]]},{"id":"d6074208.2e918","type":"revpi-single-input","z":"8a05e5c5.7e79d8","server":"36b43edb.a540f2","inputpin":"I_5","x":110,"y":360,"wires":[["8c56dc4.7dda62"]]},{"id":"6e633630.3e51d8","type":"debug","z":"8a05e5c5.7e79d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"informacja","targetType":"msg","statusVal":"","statusType":"auto","x":660,"y":360,"wires":[]},{"id":"876560a5.3480a","type":"MSSQL","z":"8a05e5c5.7e79d8","mssqlCN":"df8c0b88.91b0a8","name":"MSSQL","query":"","outField":"payload","x":460,"y":360,"wires":[["6e633630.3e51d8"]]},{"id":"8c56dc4.7dda62","type":"function","z":"8a05e5c5.7e79d8","name":"Function","func":"let informacja = msg.informacja;\nlet Input5 = msg.payload;\n\nif ( Input5 === 1) {\n    informacja = \"Input5 = 1\";\n    informacja = \"INSERT INTO [RevPi].[dbo].[Wielowrzecionowka] (Wiercenie) VALUES ('1')\"; //set payload to a string of \"1\" as you requested\n} else {\n    informacja = \"Input5 = 0\";\n}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":360,"wires":[["876560a5.3480a"]]},{"id":"df8c0b88.91b0a8","type":"MSSQL-CN","name":"RevPi","server":"192.168.0.240\\EX12","encyption":false,"database":"RevPi"},{"id":"36b43edb.a540f2","type":"revpi-server","host":"localhost","port":"8000","user":"","password":"","rejectUnauthorized":false,"ca":""}]
Just to point anything, I see when states for every input (from I_1 to I_5) changes so connection works, cause NODE-RED shows it is online.
User avatar
crismancich
KUNBUS
Posts: 40
Joined: 05 Jan 2021, 11:25
Location: Hamburg
Contact:

Re: Trivial problem getting INPUTs as UPDATE TO MSSQL

Post by crismancich »

Hi Aimovoria,

welcome to the Revolution Pi Family. Greetings from the RevPi Support Team. Nice to have you on board. :-) We're looking into Forum issues when they don't get an answer in a certain period of time.
First of all your idea to use NodeRed and SQL seems like a valid idea to us. From Support sadly we can't look into software development projects and help developing. Our job is more or less help when RevPi does not work as expected.

If it's an urgent customer project, you could get in touch with our Partner erminas (erminas.de). They have experience with NodeRed. They also developed the Nodes for us and they offer Software Development Services.
As a second thing I'm personally offering Hack Nights every now and then. Normally late in the evening CET. We could make an appointment to do an open hack night and discuss and try to solve your topic.
Viele Grüße / Kind regards / Quapla’ / 此致敬意
Boris Crismancich
Post Reply