Values do not go at the same row for MSSQL

boarak · 3 April 2023 08:35

Hello. I am sending the values of my electric screwdriver to MSSQL. I have four different values. These are speed, torque, turns, and total time. I also sent them to MSSQL. Every value is creating a different row. I would like to see them in the same row. I know that the tip is also wrong in this photo. However, I don't know which one is closest to the right path.

[
    {
        "id": "a8a6c407ff14b8a6",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "a02d3bcd02e18b48",
        "type": "MSSQL",
        "z": "a8a6c407ff14b8a6",
        "mssqlCN": "df8c0b88.91b0a8",
        "name": "",
        "outField": "payload",
        "returnType": "1",
        "throwErrors": "1",
        "query": "INSERT INTO [Machine].[dbo].[ScrewDrivers]\r\n(Speed,Torque,Turns,TotalTime)\r\nVALUES(@sp,@to,@tu,@tt)\r\n",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "",
        "paramsOptType": "editor",
        "rows": "rows",
        "rowsType": "msg",
        "parseMustache": true,
        "params": [
            {
                "output": false,
                "name": "sp",
                "type": "Float",
                "valueType": "msg",
                "value": "payload",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "to",
                "type": "Float",
                "valueType": "msg",
                "value": "payload",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "tu",
                "type": "Float",
                "valueType": "msg",
                "value": "payload",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "tt",
                "type": "Float",
                "valueType": "msg",
                "value": "payload",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            }
        ],
        "x": 600,
        "y": 300,
        "wires": [
            [
                "a36d4141066d4c6d"
            ]
        ]
    },
    {
        "id": "4a856c77f3323fe0",
        "type": "modbus-flex-sequencer",
        "z": "a8a6c407ff14b8a6",
        "name": "",
        "sequences": [
            {
                "name": "Current Speed",
                "unitid": "1",
                "fc": "FC4",
                "address": "47",
                "quantity": "1"
            },
            {
                "name": "Current Torque",
                "unitid": "1",
                "fc": "FC4",
                "address": "58",
                "quantity": "1"
            },
            {
                "name": "Current Turns",
                "unitid": "1",
                "fc": "FC4",
                "address": "56",
                "quantity": "1"
            },
            {
                "name": "Current Total Time",
                "unitid": "1",
                "fc": "FC4",
                "address": "59",
                "quantity": "1"
            }
        ],
        "server": "531e73b33af62948",
        "showStatusActivities": false,
        "showErrors": false,
        "showWarnings": true,
        "logIOActivities": false,
        "useIOFile": false,
        "ioFile": "",
        "useIOForPayload": false,
        "emptyMsgOnFail": false,
        "keepMsgProperties": false,
        "delayOnStart": false,
        "startDelayTime": "",
        "x": 240,
        "y": 300,
        "wires": [
            [
                "a02d3bcd02e18b48"
            ],
            []
        ]
    },
    {
        "id": "a36d4141066d4c6d",
        "type": "debug",
        "z": "a8a6c407ff14b8a6",
        "name": "debug 11",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 860,
        "y": 300,
        "wires": []
    },
    {
        "id": "df8c0b88.91b0a8",
        "type": "MSSQL-CN",
        "name": "Trial",
        "server": "127.0.0.1",
        "port": "",
        "encyption": false,
        "trustServerCertificate": false,
        "database": "Machine",
        "useUTC": false,
        "connectTimeout": "",
        "requestTimeout": "",
        "cancelTimeout": "",
        "pool": "",
        "parseJSON": false,
        "enableArithAbort": true
    },
    {
        "id": "531e73b33af62948",
        "type": "modbus-client",
        "name": "",
        "clienttype": "serial",
        "bufferCommands": true,
        "stateLogEnabled": false,
        "queueLogEnabled": false,
        "failureLogEnabled": true,
        "tcpHost": "127.0.0.1",
        "tcpPort": "502",
        "tcpType": "DEFAULT",
        "serialPort": "COM3",
        "serialType": "RTU-BUFFERD",
        "serialBaudrate": "115200",
        "serialDatabits": "8",
        "serialStopbits": "1",
        "serialParity": "none",
        "serialConnectionDelay": "100",
        "serialAsciiResponseStartDelimiter": "0x3A",
        "unit_id": 1,
        "commandDelay": 1,
        "clientTimeout": 1000,
        "reconnectOnTimeout": true,
        "reconnectTimeout": 2000,
        "parallelUnitIdsAllowed": true,
        "showWarnings": true,
        "showLogs": true
    }
]

marcus-j-davies · 3 April 2023 09:06

Hi @boarak - Welcome to the forums.

I assume you know the SQL language? I ask, as your query is:

INSERT INTO [Machine].[dbo].[ScrewDrivers]

Therefore every execution is going to... INSERT a new row.

1st
You will need to change your table design to create an identity column (primary key) - say RowID (INT) - and give it an Auto Increment flag.

Make sure your columns are nullable (apart from RowID).

2nd
You may need to create a dummy entry to get the resulting RowID

3rd
Change your query (ensure you set @ID to the RowID obtained in point 2)

UPDATE [Machine].[dbo].[ScrewDrivers]
SET Speed = @sp, Torque = @to, Turns = @tu, TotalTime = @tt
WHERE RowID = @ID

EDIT
I would combine all the retrieved values using the JOIN node - so you can update all values in 1 - instead of 4 queries, if doing single updates - then you only update the value you have retrieved

UPDATE [Machine].[dbo].[ScrewDrivers]
SET Speed = @sp
WHERE RowID = @ID

boarak · 3 April 2023 10:30

First of all, Thank you very much for your answer.
I can say that my SQL knowledge is between beginner and intermediate level.

But firstly I am thinking to use the Insert Into command and after the second and extra values I should use Update, is not it?

The other question is
2nd You may need to create a dummy entry to get the resulting RowID
I am sorry but did not understand what you mean.

I created 5 columns. These are ID, Speed, Torque, Turns, and Total Time. As you said I did allow nulls without ID. The ID column is set primary. All data are int.

I think I'm making a mistake in the code part. When I do it without using the update command, the values come.

When I run this flow chart, I can see the result below.
Screenshot (6)

The correct values are
Speed=0
Torque=199
Turns=46
Totaltime=491

boarak · 3 April 2023 10:39

Steve-Mcl · 3 April 2023 11:14

A most basic understanding to get the best from node-red is to remember that messages NEVER arrive from multiple wires at the same time.

In short, those 4 readings will arrive at 4 separate times & the SQL node will be executed 4 times.

There are a few solutions (using a JOIN node or storing values in context) however my preferred solution when reading from remote devices is to get all values in one go. This ensures consistency between the measured values (i.e. they are read from the controller at the same instant in time).

boarak · 3 April 2023 12:25

Thank you very much for your idea. I am trying to do like your plan (without JOIN). But I have some issues. Sorry for that.

Firstly, How to grab all registers(speed address, torque address, turns address, time address) in a Modbus read? Because These are different addresses (ports).

The second question is related to the buffer-parser node. I did the same as you did above. I took a mistake.

Steve-Mcl · 3 April 2023 12:34

As stated before:

Do the values come from the SAME modbus device or multiple devices?

Can you tell us the "registers" the 4 values come from?

boarak · 3 April 2023 12:45

I am taking my values from 4 different Modbus-read nodes. You can see below.

I am using Electric Screwdriver. You can see values on the screen below.

Maybe I should use Modbus-Flex Sequencer?
Thanks a lot!

Steve-Mcl · 3 April 2023 13:04

If you answer my questions, I will show you how.

boarak · 3 April 2023 13:08

Same Modbus but 4 different channels.

Steve-Mcl · 3 April 2023 13:10

ok, one last attempt to help you.

Can you tell us the "registers" the 4 values come from?

boarak · 3 April 2023 13:21

I am so sorry, but I don't understand what you mean by "registers" I have Modbus device on port:Com3.
And I'm connected separately to 4 channels from where I take values.

I hope this is correct.

Steve-Mcl · 3 April 2023 13:33

As in Which ModBus address is which value?

Typically the accompanying manual will state a list of registers avaiable - something like:

Input Registers:

Address	Type	Description
1	Int16	Running time
2	UInt32	Operation Count
4	UInt16	Max Speed ( divide by 10 for real value)
5	UInt16	Current Speed ( divide by 10 for real value)
6	Float32	Final Angle

etc etc etc

So what is the ModBus addresses of the 4 items you need (the Speed, the Torque, the Turns, the Total Time). Also, if you can find these in the manual, what data type are they (Float? Int16? UINT16? any multipliers etc)

Steve-Mcl · 3 April 2023 14:05

Looking at the part flow you posted in your first post, I can see the ModBus registers you need are 47, 58, 56, 59

So using that info, and the example I posted earier, you would need to READ (or rather GET) enough values to cover all of those addresses - e.g. address 47 ~ 59.

e.g. Reading 13 values, starting from address 47, would give you 13 separate values. The first value would be the value of Register 47. The 2nd value would be the value of Register 48 (but we dont care about that). And since ModBus returns 16 bit values, each register is 2 bytes. So the buffer parser needs to know which BYTE each value of interest is at. Here is how you would work that out:

	Register	Byte Position	Description
1	47	0	Current Speed
2	48	2
3	49	4
4	50	6
5	51	8
6	52	10
7	53	12
8	54	14
9	55	16
10	56	18	Total Turns
11	57	20
12	58	22	Current Torque
13	59	24	Current Total Time

And here is how you would tag those parts of the data

Flow Demo (use CTRL-I to import)

[{"id":"e7a7cf14c30d9179","type":"modbus-getter","z":"a8a6c407ff14b8a6","name":"Get registers 47 ~ 59","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"unitid":"","dataType":"InputRegister","adr":"47","quantity":"13","server":"531e73b33af62948","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":460,"y":500,"wires":[["3582e6fa5a8215b3","9fc439297d1c73c2"],[]]},{"id":"5ff5f831396d4b66","type":"inject","z":"a8a6c407ff14b8a6","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":260,"y":500,"wires":[["e7a7cf14c30d9179"]]},{"id":"3582e6fa5a8215b3","type":"buffer-parser","z":"a8a6c407ff14b8a6","name":"Buffer-Parser: make sensible values","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"uint16be","name":"Speed","offset":0,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"uint16be","name":"Torque","offset":22,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"uint16be","name":"Turns","offset":18,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"uint16be","name":"Time","offset":24,"length":1,"offsetbit":0,"scale":"1","mask":""}],"swap1":"","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"keyvalue","resultTypeType":"return","multipleResult":false,"fanOutMultipleResult":false,"setTopic":true,"outputs":1,"x":750,"y":500,"wires":[["fdc45054be533c71","0757b686338d3bc4"]]},{"id":"fdc45054be533c71","type":"MSSQL","z":"a8a6c407ff14b8a6","mssqlCN":"df8c0b88.91b0a8","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO [Machine].[dbo].[ScrewDrivers]\r\n  (Speed,Torque,Turns,TotalTime)\r\nVALUES\r\n  (@sp,@to,@tu,@tt)\r\n","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"sp","type":"int","valueType":"msg","value":"payload.Speed","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"tu","type":"int","valueType":"msg","value":"payload.Turns","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"to","type":"int","valueType":"msg","value":"payload.Torque","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"tt","type":"int","valueType":"msg","value":"payload.Time","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":1040,"y":500,"wires":[[]]},{"id":"0757b686338d3bc4","type":"debug","z":"a8a6c407ff14b8a6","name":"CHECK ME - should be a nice object","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1070,"y":560,"wires":[]},{"id":"9fc439297d1c73c2","type":"debug","z":"a8a6c407ff14b8a6","name":"CHECK ME - should be 13 values","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":620,"y":560,"wires":[]},{"id":"531e73b33af62948","type":"modbus-client","name":"","clienttype":"serial","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"failureLogEnabled":true,"tcpHost":"127.0.0.1","tcpPort":"502","tcpType":"DEFAULT","serialPort":"COM3","serialType":"RTU-BUFFERD","serialBaudrate":"115200","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","serialAsciiResponseStartDelimiter":"0x3A","unit_id":"1","commandDelay":"1","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true},{"id":"df8c0b88.91b0a8","type":"MSSQL-CN","name":"Trial","server":"127.0.0.1","port":"","encyption":false,"trustServerCertificate":false,"database":"Machine","useUTC":false,"connectTimeout":"","requestTimeout":"","cancelTimeout":"","pool":"","parseJSON":false,"enableArithAbort":true}]

boarak · 4 April 2023 06:20

Thank you very much. I understood my mistakes. For example, my time is showing 453. But I would like to see 4.53. For this issue what should I do? Can I open a new query or continue here for the next questions?

UnborN · 4 April 2023 07:15

The Buffer-parser node conveniently provides a Scale column, which is the last column, for calculations on the value. in this case divide by /100

boarak · 4 April 2023 07:59

Oh! I was adding different function nodes for it. It is super easy. Thanks a lot!

jmorris644 · 5 April 2023 14:02

another possible answer that might work for you is to use the ON DUPLICATE KEY UPDATE clause in your SQL INSERT command. I use this all of the time when I get info from the same source at different times and do not want to create multiple rows.

system · 19 April 2023 14:02

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Inserting into MSSQL-Plus Node through Dashboard Form Inputs Dashboard	5	283	13 December 2022
MSSQL, two input problem General	7	1715	13 August 2019
Trivial problem getting INPUTs as UPDATE TO MSSQL General	2	234	28 March 2022
Insert array contents into MSSQL table General	24	3828	27 July 2020
Flow to insert into MySQL table General	6	1965	3 September 2020

Values do not go at the same row for MSSQL

Input Registers:

Related Topics