Node Red Update SQL

General
1

Hello ,

I am new into Node Red and i learn a lot.
There is one issue wich i cant solve with an online search.
Maybe i am getting something wrong…

I would like to Update a Value on a SQL Database table.

For testing i use an inject node and the node-red-contrib-mssql-plus node.

The payload from the inject node is : " UPDATE [Tablename] SET Value = 1337 WHERE Key = 'Key' " .
The Settings of the SQL Node are done and correct. The database is online.

I dont get any Errors and no changed value.

Perhaps somebody can see my mistake ?

answers are welcome in english and german :slight_smile:

2

image
image

show us some pictures like this, then maybe there is help available.

3

image

image

I hope the screenshots work. But i think there is nothing new in it...

i tryed Tablename in brackets too.

4

Put a debug on the output of the mssql node and show what it says

5

To add to what @zenofmud said, also set the debug node output set to "complete msg". Expand the msg and tell us what is in msg.query

Also, have you checked the table actually has a row with 'W05' on that column?

6

Ps, the first test I always do is to do a simple SELECT TOP 5 * FROM [tablename] to ensure that the SQL node is connected to the right SQL server and the correct database.

(Don't forget to add a debug (complete message) to the output of MSSQL)

7

Thanks for the answers!

I'll check this tomorrow 3 pm.
yes the table has one row with W05 on one column .
I will report tomorrow.

dearly
Chorum

8

Hello,
i couldnt test in the last days but today the work goes on.
image

The debug node after the insert node gives me :
image

The debug node after the MSSQL Node gives me nothing.

there is no Change in the database and no Errors.

i would be happy to bring that to work.

9

The fact it says requesting tells me something is wrong. It must eventually time out.

Try performing a known working select.

Also, can you show me what version of MSSQL you have installed?

Also, please check you do not have another MSSQL node installed? (Look on palette manager "installed nodes" )

10

I installed more then one SQL node.
Today i used a new one:mysql
With that one i get error Messages. The database was moved and changed in the last days so atm i have Access denied Errors. i have to wait for the dataase admin to check the Rights for the mqtt user.

after that i will test again and report.

11

Using the MySQL node, the query must be in msg.topic

12

I think this is using MSSQL not MySQL, my memory suggests that the MSSQL node is different in this regard, but not certain.

13

I was replying to this statement

14

Oh, I had missed that.

@Chorum are you using MySQL or MSSQL, they are very different?

15

So after the Rights where granted everything works fine :slight_smile:
Now i Need to Setup the query for the whole Thing. I think i can write that static , i Need to get it to work today :slight_smile:
Many thanks for your help!
If i stumble over the query Generation i will post again.

I use the mysql node .

16

So here i am back again :slight_smile:

SQL Database Update is working fine but i Need to put msg.payload.variable or context.get('variable') into my query string. I will continue searching the net for it, but if u have a quick example … :slight_smile:

17

Use the mustache syntax in the MSSQL nodes (all info is on the sidebar info panel) or construct your SQL in a function node and send it in msg.payload to the MSSQL node (again all info in the sidebar panel) (assuming you're using MSSQL-plus and it's latest version)

18

Hello,
i am back to thank you all for your help :slight_smile:
I have the next Question but i will make a new thread.

Greetings
Chorum

19

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