I have a Raspberry Pi that is pulling data from a SMA Solar inverter and storing it in a SQLite database every (1) minute. I want to use Node-Red to copy/sync that data up to a SQL Server I have in Azure.
I have my flow configured and can read the data from SQLite and I also have my connection to Azure SQL working. My issue or lack of knowledge is how do I take that data and insert it into SQL?
If its a Function what do I need in the function? Do I write the insert query in the SQL_Azure node? I am playing around with everything for the past few days and just can't get it. Any help would be so grateful.
Node-Red Version: v0.20.5
SQLite Version: 3.8.7.1
SQL Server Version: 2016
Do you know the Azure SQL command that you would use to insert the data manually? I'd start there. I'm afraid I don't have more specific help as I don't use Azure SQL myself.
Well then haven't you answered your own question? create an sql statement to do the inserts. As I don't have access t Azure, I don't know what options it has. If it follows the lead of the mysql node, you can put the sql in msg.topic. If you want to nsert multiple rows at a time, do a search of the forum, this has come up a couple of times recently.
** Yes the SQL insert side I have down. There is nothing special about this Azure, its your normal SQL server database. My issue I am having is writing the SQL
script in the function NODE to grab the SQLite payload msg. For example.**
** This is my SQLite result. I want to take the Pdc1: value and insert it into my SQL table column (Topic). The insert query is easy. But the query to GRAB the
payload msg I get from the SQLite table I can’t get down.**
See the red X's in the function node - those are errors. You can't split assignment statements across lines. Put the msg.topic = ..... all on one line.
Thank you Paul. That I did not know. That fixed the error on the function. If I run this in SQL it works but trying to adapt it on Node Red gives me the below error. I am just
missing something still.
Paste the function node code in a reply, there is probable an error in it.
You could also attach a debug node (display complete msg object) to the output of th efunction node to see what you are creating.
The query has to be in the topic, as you originally showed. Then follow @zenofmud's advice and put a debug node showing complete message on the function node output to check that it looks ok.
