Logging data in to MSSQL

Industrial
1

Hi ,
I am facing issues in logging data (inserting) data in to mssql server.I am posting my flow..can someone please help me find issue..

[
    {
        "id": "196afc4293e110c7",
        "type": "function",
        "z": "6f4fb60b82629334",
        "name": "",
        "func": "temp = Number(global.get(\"rtdpco2\")).toFixed(2);\ndoorstatus = global.get(\"dipco2lwr\");\nupper_limit = global.get(\"pco2-up\");\nlower_limit = global.get(\"pco2-low\");\nmsg.topic = \"INSERT INTO pco2 (door_status,temp,upper_limit,lower_limit) VALUES(\" + doorstatus + \",\" + temp +\",\" + upper_limit +\",\" + lower_limit +\");\"\nreturn msg;",
        "outputs": 1,
        "noerr": 8,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 580,
        "y": 360,
        "wires": [
            [
                "ee57a1d4778d19aa"
            ]
        ]
    },
    {
        "id": "ee57a1d4778d19aa",
        "type": "MSSQL",
        "z": "6f4fb60b82629334",
        "mssqlCN": "358e71158db79bf8",
        "name": "read",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "",
        "paramsOptType": "editor",
        "rows": "rows",
        "rowsType": "msg",
        "params": [],
        "x": 770,
        "y": 360,
        "wires": [
            []
        ]
    },
    {
        "id": "255a54677492919b",
        "type": "inject",
        "z": "6f4fb60b82629334",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 420,
        "y": 360,
        "wires": [
            [
                "196afc4293e110c7"
            ]
        ]
    },
    {
        "id": "358e71158db79bf8",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "",
        "server": "localhost",
        "port": "1433",
        "encyption": false,
        "trustServerCertificate": false,
        "database": "DATA",
        "useUTC": false,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "5",
        "parseJSON": false,
        "enableArithAbort": true
    }
]

""TypeError: this.tail.search is not a function"---thats the error I am getting

2

In order to make code and flows readable and usable it is necessary to surround your code with three backticks (also known as a left quote or backquote ```)

``` 
   code goes here 
```

You can edit and correct your post by clicking the pencil :pencil2: icon.

See this post for more details - How to share code or flow json

Which mssql node are you using? I understand the the mssql-plus node is the one recommended currently.

3

I am using mssql plus node with mssql 2019 connected to it.

Edited the previous post as requested.

4

Those are not backticks, they are backslashes. I have fixed it for you.

5

oops!..sorry for being so dumb!! :disappointed:

6

Where are you seeing that error and which node is generating it? If it is in the debug pane then it should show the node name or id above it. If you click on that it should take you to the node.

7

The debug node is connected to the output of sql node.

8

I am also getting a small red triangle on top of the function node. I guess something is wrong with syntax.

9

Can you show us a screenshot of the error output please? Give the debug node a name first if it has not got a name.
Also add a debug node showing what is coming out of the function node and show us that.

10

You need to use a 'let, var, or const' to define variables. Like this:

let temp = Number(global.get("rtdpco2")).toFixed(2);
let doorstatus = global.get("dipco2lwr");
let upper_limit = global.get("pco2-up");
let lower_limit = global.get("pco2-low");
msg.topic = "INSERT INTO pco2 (door_status,temp,upper_limit,lower_limit) VALUES(" + doorstatus + "," + temp +"," + upper_limit +"," + lower_limit +");"
return msg;

That will get rid of the red triangle ion the function node.

1 Like
11

image
image1366Ă—768 58 KB

12

So it is not from the debug node, it is direct from the SQL node. And the output of the function node?

13

You have several mistakes in your flow.

  1. As @zenofmud points out, you have undeclared variables in your flow
    • NB: The function is utterly pointless here
  2. You have the MSSQL-PLUS node set to use the SQL in the UI - BUT - you are attempting to pass a SQL STRING in via msg.topic
    • NB: This is the least safe way of doing SQL - you should use parameters in the UI to avoid SQL injection

image

image
image697Ă—733 36.4 KB

Demo flow...

[{"id":"ee57a1d4778d19aa","type":"MSSQL","z":"49f61d916c8f6022","mssqlCN":"358e71158db79bf8","name":"read","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO pco2 \r\n    (door_status,temp,upper_limit,lower_limit) \r\nVALUES\r\n    (@doorstatus, @temp, @upper_limit, @lower_limit)\r\n","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"door_status","type":"VarChar","valueType":"global","value":"dipco2lwr","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"temp","type":"Float","valueType":"global","value":"rtdpco2","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"upper_limit","type":"Float","valueType":"global","value":"pco2-low","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"lower_limit","type":"Float","valueType":"global","value":"pco2-up","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":1490,"y":100,"wires":[[]]},{"id":"358e71158db79bf8","type":"MSSQL-CN","tdsVersion":"7_4","name":"","server":"localhost","port":"1433","encyption":false,"trustServerCertificate":false,"database":"DATA","useUTC":false,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]
1 Like
14

Thanks a lot Steve!!.. that worked!

Just began working with node- red 5 days ago, so struggling a bit with the codes and syntaxes!

Would also like to commend all the responses I received on this forum .I am part of a lot forums but this level of hand-holding is rarely provided!

Happy to be here!! :grinning:

1 Like