Hello,
I have a flow where I am receiving data from MQTT, parsing it in a function block (JSON parsing) and then forming an insert statement out of it to feed it to a database. Attached the flow.
[
{
"id": "b190f6b08d42b688",
"type": "tab",
"label": "Flow 4",
"disabled": false,
"info": "",
"env": []
},
{
"id": "2b72837f2767c939",
"type": "MSSQL",
"z": "b190f6b08d42b688",
"mssqlCN": "14ebf81614eaa7be",
"name": "",
"outField": "payload",
"returnType": 0,
"throwErrors": 1,
"query": "",
"modeOpt": "queryMode",
"modeOptType": "query",
"queryOpt": "payload",
"queryOptType": "msg",
"paramsOpt": "",
"paramsOptType": "none",
"rows": "rows",
"rowsType": "msg",
"params": [
{
"output": false,
"name": "name",
"type": "NVarChar(50)",
"valueType": "msg",
"value": "payload",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}
],
"x": 700,
"y": 300,
"wires": [
[
"73860cd6ac08fd64"
]
]
},
{
"id": "73860cd6ac08fd64",
"type": "debug",
"z": "b190f6b08d42b688",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 890,
"y": 300,
"wires": []
},
{
"id": "79950c88eba525a3",
"type": "inject",
"z": "b190f6b08d42b688",
"name": "SELECT",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "SELECT * FROM [DSDB].[dbo].[ATMEndProduct]",
"payloadType": "str",
"x": 480,
"y": 300,
"wires": [
[
"2b72837f2767c939"
]
]
},
{
"id": "14582b8d5c908e2d",
"type": "mqtt in",
"z": "b190f6b08d42b688",
"name": "MQTT_ATM",
"topic": "/Krohne assemblage ATM flowmeter/production/product/removeProduct",
"qos": "0",
"datatype": "json",
"broker": "7f92728b10ba1af4",
"nl": false,
"rap": true,
"rh": 0,
"inputs": 0,
"x": 310,
"y": 120,
"wires": [
[
"856e1aebc9648b16",
"650d0e7503ae5c8a"
]
]
},
{
"id": "856e1aebc9648b16",
"type": "debug",
"z": "b190f6b08d42b688",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 510,
"y": 120,
"wires": []
},
{
"id": "650d0e7503ae5c8a",
"type": "function",
"z": "b190f6b08d42b688",
"name": "",
"func": "try{\n var product=msg.payload[\"->\"].product.value;\n var articleCode=msg.payload[\"->\"].articleCode.value;\n var revision=msg.payload[\"->\"].revision.value;\n var recipe=msg.payload[\"->\"].recipe.value;\n var recipeRevision=msg.payload[\"->\"].recipeRevision.value;\n var serialNumber=msg.payload[\"->\"].serialNumber.value;\n var orderNumber=msg.payload[\"->\"].orderNumber.value;\n var labelCode=msg.payload[\"->\"].labelCode.value;\n var readCode=msg.payload[\"->\"].readCode.value;\n var status=msg.payload[\"->\"].status.value;\n var solderStation=msg.payload[\"->\"].solderingStation.value;\n var solderTime=msg.payload[\"->\"].solderingStation[\"->\"].solderTime.value;\n var cooldownTime=msg.payload[\"->\"].solderingStation[\"->\"].cooldownTime.value;\n var power=msg.payload[\"->\"].solderingStation[\"->\"].power.value;\n\n var weld=msg.payload[\"->\"].weld.value;\n var rotatorStartPosition=msg.payload[\"->\"].weld[\"->\"].rotatorStartPosition.value;\n var rotatorDegToWeld=msg.payload[\"->\"].weld[\"->\"].rotatorDegToWeld.value;\n var rotatorWeldSpeed=msg.payload[\"->\"].weld[\"->\"].rotatorWeldSpeed.value;\n var laserPowerWatt=msg.payload[\"->\"].weld[\"->\"].laserPowerWatt.value;\n var laserStartDelay=msg.payload[\"->\"].weld[\"->\"].laserStartDelay.value;\n var clampPressure=msg.payload[\"->\"].weld[\"->\"].clampPressure.value;\n\n var testResult=msg.payload[\"->\"].testResult.value;\n var pressurize=msg.payload[\"->\"].pressurize.value;\n var setPressure=msg.payload[\"->\"].pressurize[\"->\"].setPressure.value;\n var allowedError=msg.payload[\"->\"].pressurize[\"->\"].allowedError.value;\n var stabilisationTime=msg.payload[\"->\"].pressurize[\"->\"].stabilisationTime.value;\n var isolationTime=msg.payload[\"->\"].pressurize[\"->\"].isolationTime.value;\n var flowTime=msg.payload[\"->\"].pressurize[\"->\"].flowTime.value;\n var maxTime=msg.payload[\"->\"].pressurize[\"->\"].maxTime.value;\n var returnCode=msg.payload[\"->\"].pressurize[\"->\"].returnCode.value;\n var isolatedPressure=msg.payload[\"->\"].pressurize[\"->\"].isolatedPressure.value;\n\n var pressureDropTest=msg.payload[\"->\"].pressureDropTest.value;\n var testTime=msg.payload[\"->\"].pressureDropTest[\"->\"].testTime.value;\n var allowedStartError=msg.payload[\"->\"].pressureDropTest[\"->\"].allowedStartError.value;\n var setTestPressure=msg.payload[\"->\"].pressureDropTest[\"->\"].setTestPressure.value;\n var allowedDrop=msg.payload[\"->\"].pressureDropTest[\"->\"].allowedDrop.value;\n var returnCodePD=msg.payload[\"->\"].pressureDropTest[\"->\"].returnCode.value;\n var startPressure=msg.payload[\"->\"].pressureDropTest[\"->\"].startPressure.value;\n var endPressure=msg.payload[\"->\"].pressureDropTest[\"->\"].endPressure.value;\n var testResultPD=msg.payload[\"->\"].pressureDropTest[\"->\"].testResult.value;\n\n var impedanceTest=msg.payload[\"->\"].impedanceTest.value;\n var frequency=msg.payload[\"->\"].impedanceTest[\"->\"].frequencies.value;\n var frequency1=frequency.substr(1,6)\n var frequency2=frequency.substr(8,8)\n var voltageLevel=msg.payload[\"->\"].impedanceTest[\"->\"].voltageLevel.value;\n var minImpedances=msg.payload[\"->\"].impedanceTest[\"->\"].minImpedances.value;\n var minImpedance1=minImpedances.substr(1,8)\n var minImpedance2=minImpedances.substr(10,5)\n var maxImpedances=msg.payload[\"->\"].impedanceTest[\"->\"].maxImpedances.value;\n var maxImpedance1=maxImpedances.substr(1,9)\n var maxImpedance2=maxImpedances.substr(11,10)\n var minPhaseAngle=msg.payload[\"->\"].impedanceTest[\"->\"].minPhaseAngle.value;\n var minPhaseAngle1=minPhaseAngle.substr(1,7)\n var minPhaseAngle2=minPhaseAngle.substr(9,7)\n var maxPhaseAngle=msg.payload[\"->\"].impedanceTest[\"->\"].maxPhaseAngle.value;\n var maxPhaseAngle1=maxPhaseAngle.substr(1,5)\n var maxPhaseAngle2=maxPhaseAngle.substr(7,5)\n var requiredSerialNumber=msg.payload[\"->\"].impedanceTest[\"->\"].requiredSerialNumber.value;\n var returnCodeIT=msg.payload[\"->\"].impedanceTest[\"->\"].returnCode.value;\n var testResultIT=msg.payload[\"->\"].impedanceTest[\"->\"].testResult.value;\n var leftImpedances=msg.payload[\"->\"].impedanceTest[\"->\"].leftImpedances.value;\n var leftImpedance1=leftImpedances.substr(1,9)\n var leftImpedance2=leftImpedances.substr(11,8)\n var leftPhaseAngle=msg.payload[\"->\"].impedanceTest[\"->\"].leftPhaseAngle.value;\n var leftPhaseAngle1=leftPhaseAngle.substr(1,7)\n var leftPhaseAngle2=leftPhaseAngle.substr(9,6)\n var rightImpedances=msg.payload[\"->\"].impedanceTest[\"->\"].rightImpedances.value;\n var rightImpedance1=rightImpedances.substr(1,9)\n var rightImpedance2=rightImpedances.substr(11,8)\n var rightPhaseAngle=msg.payload[\"->\"].impedanceTest[\"->\"].rightPhaseAngle.value;\n var rightPhaseAngle1=rightPhaseAngle.substr(1,7)\n var rightPhaseAngle2=rightPhaseAngle.substr(9,6)\n var actualSerialNumber=msg.payload[\"->\"].impedanceTest[\"->\"].actualSerialNumber.value;\n\n var zeroFlowTest=msg.payload[\"->\"].zeroFlowTest.value;\n var numberOfSamples=msg.payload[\"->\"].zeroFlowTest[\"->\"].numberOfSamples.value;\n var ticksPerSample=msg.payload[\"->\"].zeroFlowTest[\"->\"].ticksPerSample.value;\n var dutSerialNumber=msg.payload[\"->\"].zeroFlowTest[\"->\"].dutSerialNumber.value;\n var minDutStdDev=msg.payload[\"->\"].zeroFlowTest[\"->\"].minDutStdDev.value;\n var maxDutStdDev=msg.payload[\"->\"].zeroFlowTest[\"->\"].maxDutStdDev.value;\n var minDutAverage=msg.payload[\"->\"].zeroFlowTest[\"->\"].minDutAverage.value;\n var maxDutAverage=msg.payload[\"->\"].zeroFlowTest[\"->\"].maxDutAverage.value;\n var returnCodeZFT=msg.payload[\"->\"].zeroFlowTest[\"->\"].returnCode.value;\n var testResultZFT=msg.payload[\"->\"].zeroFlowTest[\"->\"].testResult.value;\n var dutSensorStatus=msg.payload[\"->\"].zeroFlowTest[\"->\"].dutSensorStatus.value;\n var standardDeviationDut=msg.payload[\"->\"].zeroFlowTest[\"->\"].standardDeviationDut.value;\n var runningAverageDut=msg.payload[\"->\"].zeroFlowTest[\"->\"].runningAverageDut.value;\n var flowSource=msg.payload[\"->\"].zeroFlowTest[\"->\"].flowSource.value;\n var samplesTaken=msg.payload[\"->\"].zeroFlowTest[\"->\"].samplesTaken.value;\n var sampleTime=msg.payload[\"->\"].zeroFlowTest[\"->\"].sampleTime.value;\n var refSensorStatus=msg.payload[\"->\"].zeroFlowTest[\"->\"].refSensorStatus.value;\n\n var setFlow=msg.payload[\"->\"].setFlow.value;\n var stabilisationTimeSF=msg.payload[\"->\"].setFlow[\"->\"].stabilisationTime.value;\n\n var flowTest=msg.payload[\"->\"].flowTest.value;\n var numberOfSamples1=msg.payload[\"->\"].flowTest[\"->\"].numberOfSamples.value;\n var ticksPerSample1=msg.payload[\"->\"].flowTest[\"->\"].ticksPerSample.value;\n var dutSerialNumber1=msg.payload[\"->\"].flowTest[\"->\"].dutSerialNumber.value;\n var refSerialNumber1=msg.payload[\"->\"].flowTest[\"->\"].refSerialNumber.value;\n var minDutAverageDeviation1=msg.payload[\"->\"].flowTest[\"->\"].minDutAverageDeviation.value;\n var maxDutAverageDeviation1=msg.payload[\"->\"].flowTest[\"->\"].maxDutAverageDeviation.value;\n var minRefAverage1=msg.payload[\"->\"].flowTest[\"->\"].minRefAverage.value;\n var maxRefAverage1=msg.payload[\"->\"].flowTest[\"->\"].maxRefAverage.value;\n var minDutStdDev1=msg.payload[\"->\"].flowTest[\"->\"].minDutStdDev.value;\n var maxDutStdDev1=msg.payload[\"->\"].flowTest[\"->\"].maxDutStdDev.value;\n var minRefStdDev1=msg.payload[\"->\"].flowTest[\"->\"].minRefStdDev.value;\n var maxRefStdDev1=msg.payload[\"->\"].flowTest[\"->\"].maxRefStdDev.value;\n var minDutTemperature1=msg.payload[\"->\"].flowTest[\"->\"].minDutTemperature.value;\n var maxDutTemperature1=msg.payload[\"->\"].flowTest[\"->\"].maxDutTemperature.value;\n var sizeFactor1=msg.payload[\"->\"].flowTest[\"->\"].sizeFactor.value;\n var returnCodeFT1=msg.payload[\"->\"].flowTest[\"->\"].returnCode.value;\n var testResultFT1=msg.payload[\"->\"].flowTest[\"->\"].testResult.value;\n var standardDeviationDut1=msg.payload[\"->\"].flowTest[\"->\"].standardDeviationDut.value;\n var standardDeviationRef1=msg.payload[\"->\"].flowTest[\"->\"].standardDeviationRef.value;\n var temperatureDut1=msg.payload[\"->\"].flowTest[\"->\"].temperatureDut.value;\n var temperatureRef1=msg.payload[\"->\"].flowTest[\"->\"].temperatureRef.value;\n var runningAverageDut1=msg.payload[\"->\"].flowTest[\"->\"].runningAverageDut.value;\n var runningAverageRef1=msg.payload[\"->\"].flowTest[\"->\"].runningAverageRef.value;\n var flowSource1=msg.payload[\"->\"].flowTest[\"->\"].flowSource.value;\n var samplesTaken1=msg.payload[\"->\"].flowTest[\"->\"].samplesTaken.value;\n var sampleTime1=msg.payload[\"->\"].flowTest[\"->\"].sampleTime.value;\n\n var prevSerialNumber=msg.payload[\"->\"].prevSerialNumber.value;\n var dataTimeStamp=msg.payload[\"->\"].dataTimeStamp.value;\n\n var productC=msg.payload[\"/\"].coil[\"->\"].product.value;\n var articleCodeC=msg.payload[\"/\"].coil[\"->\"].articleCode.value;\n var revisionC=msg.payload[\"/\"].coil[\"->\"].revision.value;\n var statusC=msg.payload[\"/\"].coil[\"->\"].status.value;\n var readCodeC=msg.payload[\"/\"].coil[\"->\"].readCode.value;\n var measuredTrayPosition=msg.payload[\"/\"].coil[\"->\"].measuredTrayPosition.value;\n //var measuredTrayPosition1=measuredTrayPosition.substr(1,9)\n //var measuredTrayPosition2=measuredTrayPosition.substr(11,8)\n var x=msg.payload[\"/\"].coil[\"->\"].measuredTrayPosition[\"->\"].x.value;\n var y=msg.payload[\"/\"].coil[\"->\"].measuredTrayPosition[\"->\"].y.value;\n var z=msg.payload[\"/\"].coil[\"->\"].measuredTrayPosition[\"->\"].z.value;\n var trayDeltaC=msg.payload[\"/\"].coil[\"->\"].trayDelta.value;\n var x1=msg.payload[\"/\"].coil[\"->\"].trayDelta[\"->\"].x.value;\n var y1=msg.payload[\"/\"].coil[\"->\"].trayDelta[\"->\"].y.value;\n var z1=msg.payload[\"/\"].coil[\"->\"].trayDelta[\"->\"].rz.value;\n var productUB=msg.payload[\"/\"].coil[\"/\"].uBracket[\"->\"].product.value;\n var statusUB=msg.payload[\"/\"].coil[\"/\"].uBracket[\"->\"].status.value;\n\n var productEC=msg.payload[\"/\"].eCable[\"->\"].product.value;\n var statusEC=msg.payload[\"/\"].eCable[\"->\"].status.value;\n var numberEC=msg.payload[\"/\"].eCable[\"->\"].number.value;\n\n var articleCodeT=msg.payload[\"/\"].tube[\"->\"].articleCode.value;\n var statusT=msg.payload[\"/\"].tube[\"->\"].status.value;\n var productT=msg.payload[\"/\"].tube[\"->\"].product.value;\n var trayCodeT=msg.payload[\"/\"].tube[\"->\"].trayCode.value;\n var cmdDispense1=msg.payload[\"/\"].tube[\"->\"].cmdDispense1.value;\n var dispenseTime1=msg.payload[\"/\"].tube[\"->\"].cmdDispense1[\"->\"].dispenseTime.value;\n var pressureSetPoint1=msg.payload[\"/\"].tube[\"->\"].cmdDispense1[\"->\"].pressureSetPoint.value;\n var cmdDispense2=msg.payload[\"/\"].tube[\"->\"].cmdDispense2.value;\n var dispenseTime2=msg.payload[\"/\"].tube[\"->\"].cmdDispense2[\"->\"].dispenseTime.value;\n var pressureSetPoint2=msg.payload[\"/\"].tube[\"->\"].cmdDispense2[\"->\"].pressureSetPoint.value;\n\n var productG=msg.payload[\"/\"].tube[\"/\"].Grease[\"->\"].product.value;\n var statusG=msg.payload[\"/\"].tube[\"/\"].Grease[\"->\"].status.value;\n\n var productCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].product.value;\n var articleCodeCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].articleCode.value;\n var revisionCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].revision.value;\n var statusCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].status.value;\n var airPressureCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].airPressure.value;\n var airBackPressureCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].airBackPressure.value;\n\n var productOR=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].product.value;\n var articleCodeOR=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].articleCode.value;\n var revisionOR=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].revision.value;\n var statusOR=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].status.value;\n var minGreyValue=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].minGreyValue.value;\n var maxGreyValue=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].maxGreyValue.value;\n var avgGreyScale=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].avgGreyScale.value;\n var lowestGreyScale=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].lowestGreyScale.value;\n var highestGreyScale=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].highestGreyScale.value;\n var stdDevGreyScale=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].stdDevGreyScale.value;\n var batchCode=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].batchCode.value;\n}\ncatch(error) {\n //nothing\n}\n\nvar msg1={};\nvar stringy=\"INSERT INTO [DSDB].[dbo].[ATMRemoveProduct] ([product] ,[articleCode] ,[revision] ,[recipe] ,[recipeRevision] ,[serialNumber] ,[orderNumber] ,[labelCode] ,[readCode] ,[status] ,[solderingStation] ,[solderTime] ,[cooldownTime] ,[power] ,[weld] ,[rotatorStartPosition] ,[rotatorDegToWeld] ,[rotatorWeldSpeed] ,[laserPowerWatt] ,[laserStartDelay] ,[clampPressure] ,[testResult] ,[pressurize] ,[setPressure] ,[allowedError] ,[stabilisationTime] ,[isolationTime] ,[flowTime] ,[maxTime] ,[returnCodeP] ,[isolatedPressure] ,[pressureDropTest] ,[testTime] ,[allowedStartError] ,[setTestPressure] ,[allowedDrop] ,[returnCodePD] ,[startPressure] ,[endPressure] ,[testResultPD] ,[impedanceTest] ,[frequency1] ,[frequency2] ,[voltageLevel] ,[minImpedance1] ,[minImpedance2] ,[maxImpedance1] ,[maxImpedance2] ,[minPhaseAngle1] ,[minPhaseAngle2] ,[maxPhaseAngle1] ,[maxPhaseAngle2] ,[requiredSerialNumber] ,[returnCodeIT] ,[testResultIT] ,[leftImpedance1] ,[leftImpedance2] ,[leftPhaseAngle1] ,[leftPhaseAngle2] ,[rightImpedance1] ,[rightImpedance2] ,[rightPhaseAngle1] ,[rightPhaseAngle2] ,[actualSerialNumber] ,[zeroFlowTest] ,[numberOfSamples] ,[ticksPerSample] ,[dutSerialNumber] ,[minDutStdDev] ,[maxDutStdDev] ,[minDutAverage] ,[maxDutAverage] ,[returnCodeZFT] ,[testResultZFT] ,[dutSensorStatus] ,[standardDeviationDut] ,[runningAverageDut] ,[flowSource] ,[samplesTaken] ,[sampleTime] ,[refSensorStatus] ,[setFlow] ,[stabilitsationTimeSF] ,[flowTest] ,[numberOfSamples1] ,[ticksPerSample1] ,[dutSerialNumber1] ,[refSerialNumber1] ,[minDutAverageDeviation1] ,[maxDutAverageDeviation1] ,[minRefAverage1] ,[maxRefAverage] ,[minDutStdDev1] ,[maxDutStdDev1] ,[minRefStdDev1] ,[maxRefStdDev1] ,[maxDutTemperature1] ,[minDutTemperature1] ,[sizeFactor1] ,[returnCodeFT1] ,[testResultFT1] ,[standardDeviationDut1] ,[standardDeviationRef1] ,[temperatureDut1] ,[temperatureRef1] ,[runningAverageDut1] ,[runningAverageRef1] ,[sampleTime1] ,[flowSource1] ,[samplesTaken1] ,[prevSerialNumber] ,[dataTimeStamp] ,[productC] ,[articleCodeC] ,[revisionC] ,[statusC] ,[readCodeC] ,[measuredTrayPosition1] ,[x] ,[y] ,[z] ,[trayDeltaC] ,[x1] ,[y1] ,[z1] ,[productUB] ,[statusUB] ,[productEC] ,[statusEC] ,[numberEC] ,[articleCodeT] ,[statusT] ,[productT] ,[trayCodeT] ,[cmdDispense1] ,[dispenseTime1] ,[pressureSetPoint1] ,[cmdDispense2] ,[dispenseTime2] ,[pressureSetPoint2] ,[productG] ,[statusG] ,[productCup] ,[articleCodeCup] ,[revisionCup] ,[statusCup] ,[airPressureCup] ,[airBackPressureCup] ,[productOR] ,[articleCodeOR] ,[revisionOR] ,[statusOR] ,[minGreyValue] ,[maxGreyValue] ,[avgGreyScale] ,[lowestGreyScale] ,[highestGreyScale] ,[stdDevGreyScale] ,[batchCode]) VALUES ('\"+product+\"','\"+articleCode+\"','\"+revision+\"','\"+recipe+\"','\"+recipeRevision+\"','\"+serialNumber+\"','\"+orderNumber+\"','\"+labelCode+\"','\"+readCode+\"','\"+status+\"','\"+solderStation+\"','\"+solderTime+\"','\"+cooldownTime+\"','\"+power+\"','\"+weld+\"','\"+rotatorStartPosition+\"','\"+rotatorDegToWeld+\"','\"+rotatorWeldSpeed+\"','\"+laserPowerWatt+\"','\"+laserStartDelay+\"','\"+clampPressure+\"','\"+testResult+\"','\"+pressurize+\"','\"+setPressure+\"','\"+allowedError+\"','\"+stabilisationTime+\"','\"+isolationTime+\"','\"+flowTime+\"','\"+maxTime+\"','\"+returnCode+\"','\"+isolatedPressure+\"','\"+pressureDropTest+\"','\"+testTime+\"','\"+allowedStartError+\"','\"+setTestPressure+\"','\"+allowedDrop+\"','\"+returnCodePD+\"','\"+startPressure+\"','\"+endPressure+\"','\"+testResultPD+\"','\"+impedanceTest+\"','\"+frequency1+\"','\"+frequency2+\"','\"+voltageLevel+\"','\"+minImpedance1+\"','\"+minImpedance2+\"','\"+maxImpedance1+\"','\"+maxImpedance2+\"','\"+minPhaseAngle1+\"','\"+minPhaseAngle2+\"','\"+maxPhaseAngle1+\"','\"+maxPhaseAngle2+\"','\"+requiredSerialNumber+\"','\"+returnCodeIT+\"','\"+testResultIT+\"','\"+leftImpedance1+\"','\"+leftImpedance2+\"','\"+leftPhaseAngle1+\"','\"+leftPhaseAngle2+\"','\"+rightImpedance1+\"','\"+rightImpedance2+\"','\"+rightPhaseAngle1+\"','\"+rightPhaseAngle2+\"','\"+actualSerialNumber+\"','\"+zeroFlowTest+\"','\"+numberOfSamples+\"','\"+ticksPerSample+\"','\"+dutSerialNumber+\"','\"+minDutStdDev+\"','\"+maxDutStdDev+\"','\"+minDutAverage+\"','\"+maxDutAverage+\"','\"+returnCodeZFT+\"','\"+testResultZFT+\"','\"+dutSensorStatus+\"','\"+standardDeviationDut+\"','\"+runningAverageDut+\"','\"+flowSource+\"','\"+samplesTaken+\"','\"+sampleTime+\"','\"+refSensorStatus+\"','\"+setFlow+\"','\"+stabilisationTimeSF+\"','\"+flowTest+\"','\"+numberOfSamples1+\"','\"+ticksPerSample1+\"','\"+dutSerialNumber1+\"','\"+refSerialNumber1+\"','\"+minDutAverageDeviation1+\"','\"+maxDutAverageDeviation1+\"','\"+minRefAverage1+\"','\"+maxRefAverage1+\"','\"+minDutStdDev1+\"','\"+maxDutStdDev1+\"','\"+minRefStdDev1+\"','\"+maxRefStdDev1+\"','\"+minDutTemperature1+\"','\"+maxDutTemperature1+\"','\"+sizeFactor1+\"','\"+returnCodeFT1+\"','\"+testResultFT1+\"','\"+standardDeviationDut1+\"','\"+standardDeviationRef1+\"','\"+temperatureDut1+\"','\"+temperatureRef1+\"','\"+runningAverageDut1+\"','\"+runningAverageRef1+\"','\"+flowSource1+\"','\"+samplesTaken1+\"','\"+sampleTime1+\"','\"+prevSerialNumber+\"','\"+dataTimeStamp+\"','\"+productC+\"','\"+articleCodeC+\"','\"+revisionC+\"','\"+statusC+\"','\"+readCodeC+\"','\"+measuredTrayPosition+\"','\"+x+\"','\"+y+\"','\"+z+\"','\"+trayDeltaC+\"','\"+x1+\"','\"+y1+\"','\"+z1+\"','\"+productUB+\"','\"+statusUB+\"','\"+productEC+\"','\"+statusEC+\"','\"+numberEC+\"','\"+articleCodeT+\"','\"+statusT+\"','\"+productT+\"','\"+trayCodeT+\"','\"+cmdDispense1+\"','\"+dispenseTime1+\"','\"+pressureSetPoint1+\"','\"+cmdDispense2+\"','\"+dispenseTime2+\"','\"+pressureSetPoint2+\"','\"+productG+\"','\"+statusG+\"','\"+productCup+\"','\"+articleCodeCup+\"','\"+revisionCup+\"','\"+statusCup+\"','\"+airPressureCup+\"','\"+airBackPressureCup+\"','\"+productOR+\"','\"+articleCodeOR+\"','\"+revisionOR+\"','\"+statusOR+\"','\"+minGreyValue+\"','\"+maxGreyValue+\"','\"+avgGreyScale+\"','\"+lowestGreyScale+\"','\"+highestGreyScale+\"','\"+stdDevGreyScale+\"','\"+batchCode+\"')\";\nmsg1.payload=stringy;\nreturn msg1;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 520,
"y": 200,
"wires": [
[
"9cd3cc5264450047",
"2b72837f2767c939"
]
]
},
{
"id": "9cd3cc5264450047",
"type": "debug",
"z": "b190f6b08d42b688",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 690,
"y": 200,
"wires": []
},
{
"id": "14ebf81614eaa7be",
"type": "MSSQL-CN",
"tdsVersion": "7_4",
"name": "GauravTest",
"server": "10.51.1.16",
"port": "1433",
"encyption": false,
"trustServerCertificate": true,
"database": "DSDB",
"useUTC": false,
"connectTimeout": "15000",
"requestTimeout": "15000",
"cancelTimeout": "5000",
"pool": "100",
"parseJSON": true,
"enableArithAbort": true
},
{
"id": "7f92728b10ba1af4",
"type": "mqtt-broker",
"name": "ATM Machine",
"broker": "10.51.171.10",
"port": "1883",
"clientid": "node-red",
"autoConnect": true,
"usetls": false,
"protocolVersion": "4",
"keepalive": "60",
"cleansession": true,
"birthTopic": "",
"birthQos": "0",
"birthPayload": "",
"birthMsg": {},
"closeTopic": "",
"closeQos": "0",
"closePayload": "",
"closeMsg": {},
"willTopic": "",
"willQos": "0",
"willPayload": "",
"willMsg": {},
"sessionExpiry": ""
}
]
The problem I have is that the JSON that I receive is not the same everytime. I have a standard format. But sometimes it is incomplete. In such cases, while parsing the JSON, I get an error saying that the value cannot be read (obviously because it was not received). But I would like to form an insert statement leaving the values which I dont receive as blank.
I tried to do this with a try and catch statement. I thought it worked. But try catch stops with the first unavailable value while parsing the json.
Can someone suggest a way around this ? Or a better way of doing this ? Attached also a complete format of the JSON. In case of an incomplete JSON, leave some values out.
{"->":{"product":{"value":"ATM"},"articleCode":{"value":"4006110801"},"revision":{"value":"003"},"recipe":{"value":"4006110801 ATM DN25 FKM"},"recipeRevision":{"value":"001"},"serialNumber":{"value":"22814740"},"orderNumber":{"value":"dn25fkm 4-10-2022"},"labelCode":{"value":"4006110801_003_22814740_X"},"readCode":{"value":"4006110801_003_22814740_X"},"status":{"value":20},"solderingStation":{"value":"0093561","->":{"solderTime":{"value":4000},"cooldownTime":{"value":2000},"power":{"value":99.000000}}},"weld":{"value":925.000000,"->":{"rotatorStartPosition":{"value":4.000000},"rotatorDegToWeld":{"value":-7.000000},"rotatorWeldSpeed":{"value":31.800000},"laserPowerWatt":{"value":600.000000},"laserStartDelay":{"value":10},"clampPressure":{"value":2.500000}}},"testResult":{"value":2},"pressurize":{"value":"4006110801_003_22814738_X","->":{"setPressure":{"value":24.000000},"allowedError":{"value":5.000000},"stabilisationTime":{"value":1000},"isolationTime":{"value":15000},"flowTime":{"value":10000},"maxTime":{"value":5000},"returnCode":{"value":2},"isolatedPressure":{"value":24.861416}}},"pressureDropTest":{"value":"22814736","->":{"testTime":{"value":30000},"allowedStartError":{"value":5.000000},"setTestPressure":{"value":24.000000},"allowedDrop":{"value":2.500000},"returnCode":{"value":2},"startPressure":{"value":24.860900},"endPressure":{"value":24.572014},"testResult":{"value":2}}},"impedanceTest":{"value":0.385815,"->":{"frequencies":{"value":"[20.000,2000.000]"},"voltageLevel":{"value":0.100000},"minImpedances":{"value":"[3000.000,0.000]"},"maxImpedances":{"value":"[25000.000,100000.000]"},"minPhaseAngle":{"value":"[-90.000,-15.000]"},"maxPhaseAngle":{"value":"[0.000,0.000]"},"requiredSerialNumber":{"value":""},"returnCode":{"value":2},"testResult":{"value":2},"leftImpedances":{"value":"[19013.599,5855.769]"},"leftPhaseAngle":{"value":"[-47.437,-7.655]"},"rightImpedances":{"value":"[20653.100,6082.069]"},"rightPhaseAngle":{"value":"[-46.358,-8.447]"},"actualSerialNumber":{"value":"GER880566"}}},"zeroFlowTest":{"value":0.350000,"->":{"numberOfSamples":{"value":50.000000},"ticksPerSample":{"value":25},"dutSerialNumber":{"value":"TESTATM01"},"minDutStdDev":{"value":0.000000},"maxDutStdDev":{"value":0.008000},"minDutAverage":{"value":-0.002000},"maxDutAverage":{"value":0.002000},"returnCode":{"value":2},"testResult":{"value":2},"dutSensorStatus":{"value":100512},"standardDeviationDut":{"value":0.001696},"runningAverageDut":{"value":-0.000081},"flowSource":{"value":"ATMObj1200"},"samplesTaken":{"value":50},"sampleTime":{"value":12860},"refSensorStatus":{"value":0}}},"setFlow":{"value":24.000000,"->":{"stabilisationTime":{"value":8000}}},"flowTest":{"value":30000,"->":{"numberOfSamples":{"value":50.000000},"ticksPerSample":{"value":25},"dutSerialNumber":{"value":"TESTATM01"},"refSerialNumber":{"value":"20180612_30"},"minDutAverageDeviation":{"value":-15.000000},"maxDutAverageDeviation":{"value":10.000000},"minRefAverage":{"value":0.350000},"maxRefAverage":{"value":0.450000},"minDutStdDev":{"value":0.000100},"maxDutStdDev":{"value":0.020000},"minRefStdDev":{"value":0.000100},"maxRefStdDev":{"value":0.010000},"maxDutTemperature":{"value":999999999999.900024},"minDutTemperature":{"value":-999999999999.900024},"sizeFactor":{"value":4.468500},"returnCode":{"value":2},"testResult":{"value":2},"standardDeviationDut":{"value":0.006166},"standardDeviationRef":{"value":0.002666},"temperatureDut":{"value":-55.000000},"temperatureRef":{"value":26.591309},"runningAverageDut":{"value":0.384683},"runningAverageRef":{"value":0.416008},"sampleTime":{"value":16290},"flowSource":{"value":"ATMObj1200"},"samplesTaken":{"value":50}}},"prevSerialNumber":{"value":"22814739"},"dataTimeStamp":{"value":"2022-10-04T08:16:57.043Z"}},"/":{"coil":{"->":{"product":{"value":"Coil"},"articleCode":{"value":"4008043701"},"revision":{"value":"000"},"status":{"value":4},"readCode":{"value":"4008043701 000 2108 23691 63@ 1600"},"measuredTrayPosition":{"value":36.500000,"->":{"x":{"value":252.000000},"y":{"value":36.500000},"z":{"value":55.000000}}},"trayDelta":{"value":"000","->":{"x":{"value":1.637711},"y":{"value":-1.864510},"rz":{"value":2.076050}}}},"/":{"uBracket":{"->":{"product":{"value":"Ubracket"},"status":{"value":1}}}}},"eCable":{"->":{"product":{"value":"Ecable"},"status":{"value":2},"number":{"value":13}}},"tube":{"->":{"articleCode":{"value":"4005945302"},"status":{"value":6},"product":{"value":"Tube"},"trayCode":{"value":"04379 4005945302 003"},"rawAlignPosition":{"value":940.000000},"maxAlignPosition":{"value":950.000000},"minAlignPosition":{"value":925.000000},"rawPressPosition":{"value":967.000000},"maxPressPosition":{"value":970.000000},"minPressPosition":{"value":960.000000},"cmdDispense1":{"value":2,"->":{"dispenseTime":{"value":50},"pressureSetPoint":{"value":4.100000}}},"cmdDispense2":{"value":1000,"->":{"dispenseTime":{"value":45},"pressureSetPoint":{"value":4.100000}}}},"/":{"Grease":{"->":{"product":{"value":"Grease"},"status":{"value":1}}},"cup":{"->":{"product":{"value":"Temperature insert"},"articleCode":{"value":""},"revision":{"value":""},"status":{"value":2},"airPressure":{"value":5.000000},"airBackPressure":{"value":2.500000}},"/":{"oRing":{"->":{"product":{"value":"oRing"},"articleCode":{"value":"060018V8836S190"},"revision":{"value":"000"},"status":{"value":2},"minGreyValue":{"value":90},"maxGreyValue":{"value":225},"avgGreyScale":{"value":135},"lowestGreyScale":{"value":92},"highestGreyScale":{"value":218},"stdDevGreyScale":{"value":1518},"batchCode":{"value":"0093561"}}}}}}}}}
Thanks in advance!