Select Data from SQLite and insert into Azure SQL Server

#1

Hello,

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

Regards

Tim

#2

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.

#3

Julian,

Thank you....It’s just a VM of SQL in a Azure domain. So it’s your normal SQL statements you use.

#4

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.

#5

** 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.**

#6

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.

#7

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.

#8

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.

#9

Here is the function node. It reaches my SQL but it insets and undefined record.

pld = "INSERT INTO [SMA].[dbo].[test] "
pld = pld + "(test) "
pld = pld + "VALUES ('" + msg.payload.Pdc1 + "')";
msg.topic = ''
msg.payload = pld
return msg;

image

image

#11

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.

#12

Looks like that payload is an array so msg.payload.Pdc1 would be msg.payload[0].Pdc1 where 0 is the number of the array index you want.