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.
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.
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.
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}}}’;
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:
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).
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.
“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.
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…
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.
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?
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.
