How to Update a MSSQL Database with Node-RED


#1

I’m trying to update my MSSQL database using Node-RED. Here is the query which works in the MS SQL Server Management Studio:
image

However when I use Node-RED I get this:

Here is the syntax in my MSSQL node:
image

I’m sure it’s some kind of syntax error. I can do an INSERT just fine with the same data. I’ve tried adding single and double quotes around the {{{payload}}} but it doesn’t help.

I realize this is a MS SQL Server issue, not an Opto22 one, but since I’m pulling data from a PAC strategy table I’m hoping you can help. :slight_smile:

Thanks,

Dave


#2

Remove the parenthesis in the upstream node so that it spits out ‘50’ instead of (‘50’) - looks like you copied and pasted the code from your insert question. Is Ranch a varchar? If it is an integer, then remove the apostrophes too.


#3

varchar(50) but I tried nchar(50) doesn’t make a difference.

Now I get this:

Why does my Insert work if there’s some kind of syntax error?

My problem is really understanding how the mustache/payload works. Where can I find out about this stuff? I looked at the Mustache Template docs and they’re not the most informative things I’ve ever read. Do I need to study node.js?? For that matter is there some other way to pull data from a PAC strategy and put it into a database? the Optolink product will not work for me.


#4

Since it is a varchar, put the apostrophe’s back.

SQL - inserts and updates are different and the syntax is different - just the way it was designed.

No, but they are not the worst docs either. They are brief, and leave things out (like the index trick), but at least you can read through them relatively quickly. All you care about is the placeholder syntax anyways.

You want UPDATE dbo.PlanHours SET Ranch = ‘50’;

The payload coming in is a string with the value 50 - so, you have two choices - modify the string coming into the sql node to be ‘50’ OR, modify the sql node to UPDATE dbo.PlanHours SET Ranch = ‘{{{payload}}}’;


#5

OK. So that works and I’m beginning to see the light although it’s pretty dim.

Please explain this to me:

Why are there double quotes and a carriage return. I assume the CR is there to tell the SQL server to add or update another record. Why the " " ?. Would those not be there if I’m updating/inserting numbers? That doesn’t seem to be the case as these are floats:


#6

The quotes are node-red telling you that the value it is displaying to you in the debug sidebar are strings. The CR probably means you have a CR in a template somewhere that is being included in the string that is outputted. It doesn’t need to be there - SQL server doesn’t care about it since it is outside the apostrophe (If it were inside the apostrophe then it would be part of the string getting passed to sql server, and the CR would be part of the column getting inserted).

Your second table is still an array of strings, not floats. Remember, you are using node-red to build a string command that will be passed to SQL server, so everything in Node-red will be a string. Anything inside apostrophes (not quotes) will be interpreted by SQL server as a string. The second array when those values are passed to sql server, they will be numerics (since they are not surrounded with apostrophes).


#7

You have the debug node connected to the upstream node, NOT the MSSQL node, so its all got nothing to do with MSSQL at this point, but everything to do with how the data is being formatted BEFORE it gets to the MSSQL node.
In other words, we cant answer your question, because we don’t know what the debug and MSSQL nodes are connected to or how its formatting the msg.payload.

Stick with it, I think you are seeing the power of Node-RED and its difficult to learn a few new ‘languages’ all at once, but the best way is to have a project, and a clear goal, which you seem to have.


#8

Remember, you are using node-red to build a string command that will be passed to SQL server, so everything in Node-red will be a string

Is that always the case? I’m pulling float values from a float table in my PAC and updating a float table in my database. Nowhere am I converting it to a string that I know of unless NR does it. Maybe that’s why I have a hard time understand this.


#9

Yes. The SQL passed to SQL server is a string (much the same way that Optoscript is all a string).


#10

Aaahh. The light is getting brighter.

Thanks!


#11

Here we go again.

Why does this:

Only give me the first element 19 times:

Here is the SQL syntax:
image

Here is the flow. It works great with string tables and INSERT commands:

And why is the Mustache template putting in these carriage return characters??? They’re not there before that node.

You can see why i was hoping ODL woul d make my life easier.


#12

Your update has no criteria (where clause). You are updating every record 18 times.


#13

Which exposes another problem which is that I have no unique field for my where clause. I can set my db to auto-increment and use that. Then I can loop through my 19 records and update the field.

The chances of me pulling that off given my expertise are slim to none…

We’ll see what happens.

Thanks


#14

You really should have a unique primary key on your table (if there isn’t one already). You will then need a way to map the table indexes from Opto22 to your database. This can be done in Node-Red.

Also, your node red flow you have for updates is more complicated than it needs to be. You probably don’t need the template node and the join node.


#15

I have a unique id field. The template and join nodes are there because that’s what we used earlier to make this work. I’m all for simplicity. I removed the template, split and join nodes, and I can update but they’re all the same value because of the missing WHERE.

Correct me if I’m wrong but to map match the id field with my strategy index I’m going to have to write some kind of loop to match the id field with my strategy index. Is that something done in the MSSQL node? I have examples of how that might work using transact-sql and CURSOR, or can I use a function node?


#16

How you approach this depends on how your database IDs correlate with the array indexes. Since you stated you don’t want to modify the schema, it would probably be best to do this in a function node. I don’t think a loop would be necessary - probably some sort of conversion formula (if there is a correlation) or a javascript switch statement if there is no correlation.