I want to pull a value from my MSSQL database and write it to an Opto table in my strategy. So far I’ve been able to pull the data and see it in a msg object:
How do I format what I’ve got so far so I can write it to my Opto table? I only want a single column, HoursTotal. Keep in mind this is a single record but later on I will want to read a number of records into an array. I’ll only need to pull a single column from the database but many rows.
As usual, I have the hardest time with converting from Node-RED to SQL and back again.
For a single value, you can reference it directly from what you got: msg.payload[0].HoursTotal. You are getting an array of objects from the Get Data node, for multiple records, you will need to transform this so you have an array of just the values you want to insert.
You can write a javascript loop in a function node or it may be easier to run a split node, then a change node setting msg.payload to msg.payload.HoursTotal, and then a join node (which will make the payload an array of all the HoursTotal). This will work for a single value as well.
It’s kind of a kludge to get working (for a DB neophyte), but once it’s working it has been very reliable. If you are better versed in DB stuff you will probably struggle less/none.
The trick I’ve found with NR, is to learn javasctipt. As a former programmer it allows you to maniplate the desparite data type and contructs with out having a bunch of the nodes. It would be easier is the groov/pac node were a little more flexible, i.e. be able use wild cards when selecting points/variables.
hmmmm - your table in PAC is no doubt Numeric.
I am trying to do this in Node Red but post back into a PAC string table.
I can easily post the msg.payload back into PAC using a string variable. As soon as you try post it into a PAC string table things go haywire. For some reason I think the msg.payload is not a string.
You managed to get a string from SQL into a string table in PAC?
To post into a string table you can either pass a single string value into a specific index, or write an entire table at once using an array. Which are you trying to do in this case?
Can you post a screenshot of what your debug output looks like? The PAC Control write node should be able to write integers and floats into a string table, but there might be something else going on with the payload.
I am trying to pass a single string value into the PAC string table. My flow is very basic as I do one query at a time, then for each query I would like to pass each single value into the same table in PAC.
I know it’s going to be something silly.
Here is the result from the debug output on my PAC write to string table node:
Try putting quote characters around your string values. It is likely that SQL is just seeing Boschrand as some kind of SQL command rather than an actual value, which is why it’s giving the unexpected token error on that first character “B”.
So in this case, instead of inserting the plain string: "Boschrand Dam"
try putting ' characters on either side of the value: "'Boschrand Dam'"
