Hi guys, I've been trying to copy the datetime from a table to another table (mssql).
Issue: the datetime data type is being output as string.
Anyone knows how to counter the issue?
You will need to share the following info...
- The flow part that generates the above
- The name of the MSSQL node (node-red-contrib-????)
- Example data (i.e. if there is something dynamic like a ModBus or HTTP API providing the date, we will not have this - you must provide sample data instead)
[
{
"id": "8cd81203fdd0b810",
"type": "tab",
"label": "Flow 3",
"disabled": false,
"info": "",
"env": []
},
{
"id": "db30356beffb06d1",
"type": "inject",
"z": "8cd81203fdd0b810",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 200,
"y": 100,
"wires": [
[
"275ac3bc4209aeed",
"73c20ce674f86930"
]
]
},
{
"id": "43b9ebe4429d0281",
"type": "function",
"z": "8cd81203fdd0b810",
"name": "function 19",
"func": "const NoOperator = msg.payload[\"W3.0\"];\nconst NoMaterial = msg.payload[\"W3.1\"];\nconst NoSchedule = msg.payload[\"W3.2\"];\nconst QualityIssue = msg.payload[\"W3.3\"];\nconst ScheduledMaintenance = msg.payload[\"W3.4\"];\nconst MachineBreakdown = msg.payload[\"W3.5\"];\nconst MaterialDrying = msg.payload[\"W3.6\"];\nconst MouldChange = msg.payload[\"W10.3\"];\nconst Trip = msg.payload[\"W9.1\"];\nconst TechBuyoff = msg.payload[\"W15.0\"];\nconst ProdRun = msg.payload[\"W15.2\"];\nconst TroubleShoot = msg.payload[\"W15.3\"];\nconst RunSample = msg.payload[\"W15.4\"];\n\nif (NoOperator == 1) {\n msg.payload = \"NO OPERATOR\";\n}\nelse if (NoMaterial == 1) {\n msg.payload = \"NO MATERIAL\";\n}\n\nelse if (NoSchedule == 1) {\n msg.payload = \"NO SCHEDULE\";\n}\nelse if (QualityIssue == 1) {\n msg.payload = \"QUALITY ISSUE\";\n}\nelse if (ScheduledMaintenance == 1) {\n msg.payload = \"SCHEDULED MAINTENANCE\";\n}\nelse if (MachineBreakdown == 1) {\n msg.payload = \"MACHINE BREAKDOWN\";\n}\nelse if (MaterialDrying == 1) {\n msg.payload = \"MATERIAL DRYING\";\n}\nelse if (MouldChange == 1) {\n msg.payload = \"MOULD CHANGE\";\n}\nelse if (Trip == 1) {\n msg.payload = \"MACHINE TRIP\";\n}\nelse if (TechBuyoff == 1) {\n msg.payload = \"TECHNICAL BUYOFF\";\n}\n\nelse if (TroubleShoot == 1) {\n msg.payload = \"TROUBLESHOOTING\";\n}\nelse if (RunSample == 1) {\n msg.payload = \"SAMPLE RUN\";\n}\nelse {\n msg.payload = 0;\n}\n\n\nreturn msg;\n\n//payload.category\n//payload.start.id_machine",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 570,
"y": 140,
"wires": [
[]
]
},
{
"id": "73c20ce674f86930",
"type": "change",
"z": "8cd81203fdd0b810",
"name": "",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "No Operator",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 340,
"y": 220,
"wires": [
[
"cb1eef534c2fdcd0"
]
]
},
{
"id": "275ac3bc4209aeed",
"type": "MSSQL",
"z": "8cd81203fdd0b810",
"mssqlCN": "75a62ffff4c55db2",
"name": "Start Time",
"outField": "payload",
"returnType": 0,
"throwErrors": 1,
"query": "select * from trans_time where id_machine ='1';",
"modeOpt": "queryMode",
"modeOptType": "query",
"queryOpt": "payload",
"queryOptType": "editor",
"paramsOpt": "queryParams",
"paramsOptType": "none",
"rows": "rows",
"rowsType": "msg",
"params": [],
"x": 370,
"y": 140,
"wires": [
[
"f7d1eb556833dcdc"
]
]
},
{
"id": "0d98e780fa3ef624",
"type": "change",
"z": "8cd81203fdd0b810",
"name": "trans_time",
"rules": [
{
"t": "set",
"p": "topic",
"pt": "msg",
"to": "time",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 690,
"y": 260,
"wires": [
[
"8195c8281b400b36"
]
]
},
{
"id": "f7d1eb556833dcdc",
"type": "change",
"z": "8cd81203fdd0b810",
"name": "",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "payload[0]",
"tot": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 540,
"y": 200,
"wires": [
[
"0d98e780fa3ef624",
"ad557ce11dbf4832"
]
]
},
{
"id": "cb1eef534c2fdcd0",
"type": "change",
"z": "8cd81203fdd0b810",
"name": "category",
"rules": [
{
"t": "set",
"p": "topic",
"pt": "msg",
"to": "category",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 480,
"y": 280,
"wires": [
[
"8195c8281b400b36"
]
]
},
{
"id": "8195c8281b400b36",
"type": "join",
"z": "8cd81203fdd0b810",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "2",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 830,
"y": 320,
"wires": [
[
"ca542206a9977318",
"ad557ce11dbf4832"
]
]
},
{
"id": "ca542206a9977318",
"type": "change",
"z": "8cd81203fdd0b810",
"name": "",
"rules": [
{
"t": "set",
"p": "category",
"pt": "msg",
"to": "payload.category",
"tot": "msg"
},
{
"t": "set",
"p": "start",
"pt": "msg",
"to": "payload.time.start",
"tot": "msg"
},
{
"t": "set",
"p": "stop",
"pt": "msg",
"to": "payload.time.stop",
"tot": "msg"
},
{
"t": "set",
"p": "id_machine",
"pt": "msg",
"to": "payload.time.id_machine",
"tot": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 880,
"y": 400,
"wires": [
[
"df1850f112b05c82",
"1547581edda91397",
"5667dd82def58045"
]
]
},
{
"id": "1547581edda91397",
"type": "MSSQL",
"z": "8cd81203fdd0b810",
"mssqlCN": "75a62ffff4c55db2",
"name": "Stop Category",
"outField": "payload",
"returnType": 0,
"throwErrors": 1,
"query": "update machine_master set status_stop = 'True' where id_machine = 1\r\n\r\nIF NOT EXISTS (SELECT * FROM dbo.trans_stop WHERE (finish is null and id_machine = '1'))\r\nBEGIN\r\ninsert into trans_stop(id_machine) values (1)\r\nEND",
"modeOpt": "queryMode",
"modeOptType": "query",
"queryOpt": "payload",
"queryOptType": "editor",
"paramsOpt": "queryParams",
"paramsOptType": "none",
"rows": "rows",
"rowsType": "msg",
"params": [],
"x": 1200,
"y": 400,
"wires": [
[]
]
},
{
"id": "6c45c54d852417a8",
"type": "MSSQL",
"z": "8cd81203fdd0b810",
"mssqlCN": "75a62ffff4c55db2",
"name": "Stop Category",
"outField": "payload",
"returnType": 0,
"throwErrors": 1,
"query": "update trans_stop set category='{{{category}}}' where id_machine={{{id_machine}}} and start is NULL\r\n",
"modeOpt": "queryMode",
"modeOptType": "query",
"queryOpt": "payload",
"queryOptType": "editor",
"paramsOpt": "queryParams",
"paramsOptType": "none",
"rows": "rows",
"rowsType": "msg",
"params": [],
"x": 1160,
"y": 500,
"wires": [
[]
]
},
{
"id": "df1850f112b05c82",
"type": "debug",
"z": "8cd81203fdd0b810",
"name": "debug 25",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload.time.start",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1080,
"y": 360,
"wires": []
},
{
"id": "ad557ce11dbf4832",
"type": "debug",
"z": "8cd81203fdd0b810",
"name": "debug 26",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 1000,
"y": 240,
"wires": []
},
{
"id": "5667dd82def58045",
"type": "delay",
"z": "8cd81203fdd0b810",
"name": "",
"pauseType": "delay",
"timeout": "5",
"timeoutUnits": "seconds",
"rate": "1",
"nbRateUnits": "1",
"rateUnits": "second",
"randomFirst": "1",
"randomLast": "5",
"randomUnits": "seconds",
"drop": false,
"allowrate": false,
"outputs": 1,
"x": 960,
"y": 460,
"wires": [
[
"6c45c54d852417a8"
]
]
},
{
"id": "75a62ffff4c55db2",
"type": "MSSQL-CN",
"tdsVersion": "7_4",
"name": "",
"server": "localhost",
"port": "1433",
"encyption": true,
"trustServerCertificate": true,
"database": "OEE",
"useUTC": true,
"connectTimeout": "1000",
"requestTimeout": "10000",
"cancelTimeout": "1000",
"pool": "5",
"parseJSON": false,
"enableArithAbort": true
}
]
Hi Steve,
1.
2. MSSQL-PLUS
3. I'm triggering the datetime using OMRON FINS, if ON = trigger datetime (start), if OFF = trigger datetime (finish)
I am trying to copy the (start) data to another SQL table.
Hi, no offence @cubical but I guess you are not very proficient with node-red?
You seem to be branching off then re-joining when infact you can simply do everything in series...
What you had...
What I propose
Also, you appear to be avoiding the SQL Params & using mustache instead! why? Params are safe from SQL Injection hacks and IMO easer to use/understand.
Test Flow
use CTRL-I to import (NOTE: Untested since I dont have your DB)
[{"id":"db30356beffb06d1","type":"inject","z":"8cd81203fdd0b810","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"No Operator","payload":"1","payloadType":"num","x":210,"y":140,"wires":[["275ac3bc4209aeed"]]},{"id":"275ac3bc4209aeed","type":"MSSQL","z":"8cd81203fdd0b810","mssqlCN":"75a62ffff4c55db2","name":"Start Time","outField":"payload","returnType":0,"throwErrors":1,"query":"select * from trans_time where id_machine = @machineID;","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"machineID","type":"int","valueType":"msg","value":"payload","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":370,"y":140,"wires":[["1547581edda91397","a36af554d806823b"]]},{"id":"1547581edda91397","type":"MSSQL","z":"8cd81203fdd0b810","mssqlCN":"75a62ffff4c55db2","name":"Stop Category","outField":"payload","returnType":0,"throwErrors":1,"query":"update machine_master \r\nset start = @start, category = @category\r\nwhere id_machine = @machineID\r\n\r\n","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"machineID","type":"Int","valueType":"msg","value":"payload[0].id_machine","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"start","type":"DateTime","valueType":"msg","value":"payload[0].start","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"category","type":"VarChar","valueType":"msg","value":"topic","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":540,"y":140,"wires":[["ad557ce11dbf4832"]]},{"id":"ad557ce11dbf4832","type":"debug","z":"8cd81203fdd0b810","name":"test#1 result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":710,"y":140,"wires":[]},{"id":"4120a6262867b6d2","type":"inject","z":"8cd81203fdd0b810","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"No Operator","payload":"1","payloadType":"num","x":210,"y":300,"wires":[["330e9050c608466c"]]},{"id":"330e9050c608466c","type":"MSSQL","z":"8cd81203fdd0b810","mssqlCN":"75a62ffff4c55db2","name":"Stop Category","outField":"payload","returnType":0,"throwErrors":1,"query":"declare @t datetime\r\nselect @t = start from trans_time where id_machine = @machineID\r\n\r\nupdate machine_master\r\nset start = @start, category = @category\r\nwhere id_machine = @machineID\r\n\r\n","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"machineID","type":"Int","valueType":"msg","value":"payload","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"category","type":"VarChar","valueType":"msg","value":"topic","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":380,"y":300,"wires":[["3036d374372b0f82"]]},{"id":"3036d374372b0f82","type":"debug","z":"8cd81203fdd0b810","name":"test#2 result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":550,"y":300,"wires":[]},{"id":"681ec23c81c08610","type":"comment","z":"8cd81203fdd0b810","name":"TEST # 1: get from DB, do some property moving/reshuffling, do the update query","info":"","x":420,"y":100,"wires":[]},{"id":"e60f84c64d27e1e9","type":"comment","z":"8cd81203fdd0b810","name":"TEST # 2: Do everything in one go","info":"","x":280,"y":260,"wires":[]},{"id":"a36af554d806823b","type":"debug","z":"8cd81203fdd0b810","name":"What do you see in here?","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":570,"y":200,"wires":[]},{"id":"75a62ffff4c55db2","type":"MSSQL-CN","tdsVersion":"7_4","name":"","server":"localhost","port":"1433","encyption":true,"trustServerCertificate":true,"database":"OEE","useUTC":true,"connectTimeout":"1000","requestTimeout":"10000","cancelTimeout":"1000","pool":"5","parseJSON":false,"enableArithAbort":true}]
1 Like
Damn Steve, Thanks so much! And this is so much easier. Thanks again!
1 Like
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
