How to Read From a SQL Database and Write to an Opto Table


#1

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.

Thanks


#2

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.


#3

I did it this way:

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.


#4

When I try multiple items I get to here but nothing I try lets me write to my Opto table


#5

Your change node is wrong.

You should be setting msg.payload to msg.payload.HoursTotal


#6

I did that. Here is what I get:

I join it back and get this:

I write this and get all zeroes in my table:

I write this and I get an error:

What am I supposed to write to get this into my table?


#7

Here is the change node:
image


#8

Use Set:

changenode


#9

Hallelujah!
image

My tombstone is going to read:

No matter how much he tried
No matter how much he read
He never could figure out Node-RED :confounded:


#10

Ha! That’s why I have avoided it thus far.


#11

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.