INSERT msg.payload.object into postgres table

[gooroo]

Evening all,
I hate to do it but I've exhausted my google, forum, and phone-a-friend resources.

Flow Description:
poll a http endpoint which returns a json string, and push that into a postgres table.

I just can't get the variable to insert into a text field in the DB. It will write any string I hardcode so I know the connector is working, I think the syntax just isn't right.

Thanks!

[thebaldgeek]

So a few things.....
Firstly, awsome you are doing ADSB stuff in Node-RED. I do a lot of aircraft tracking 'stuff' in Node-RED.
Secondly, whats the error you are getting? Its helpful that you show what you are doing, but you don't show the error you are getting.
Looking at your insert statement you would need to have icao values to be a 'varchar(45)' in your postgres (I use MySQL for my aircraft data, so its a little different) as you are inserting a string?

EDIT. I need " around my entire insert statement, but its done in function Node....

Second EDIT. How do you know its a 'mil' aircraft from just the icao?
Here is part of my function node before my insert node;
if (msg.payload.acList[i].Mil === true && msg.payload.acList[i].Icao.length > 0)

[gooroo]

Not getting an error message per se...mainly it’s just not inserting anything in the field...it create a new row, which is good....just there is a blank entry in the icao field, not null, just empty. I’ll try populating a varchar(45) datatype and see what happens.

[zenofmud]

Put a debug node (set to display the Complete msg object) on the output of the second split node so you can see the complete sql statement.

[gooroo]

Thanks zenofmud. Here's what I've got so far. I've tried playing around with the outputs and still not getting data entry into the postgres fields. I've attached the screenshots for the complete message. The "mydb" node allows you to craft the sql inside the node, vs having to pass it to the node. I'm thinking that there might be an issue with calling object variables inside that node....but i don't know enough to confirm that.

Thanks thebaldgeek, just saw your edits...adsb exchange has a binary field for mil, 1 is mil 0 is not. The json I get is when I query asb_exchange

[Steve-Mcl]

I don't know that db node but I doubt the format is correct. (I.e you've written js like syntax but I highly doubt it will evaluate the string concatenation with msg.payload)

Try putting a function node before the SQL node and build the SQL and pass it in.

Alternatively - perhaps that node mentions mustache format in its help info?

[zenofmud]

@gooroo

The "mydb" node allows you to craft the sql inside the node, vs having to pass it to the node

What is the exact node you are using? i.e. node-red-contrib-?????

[gooroo]

@open-kappa/node-red-contrib-mydb but I'm about to switch....just was able to get the template node to craft the INSERT message appropriately, but working through the "DB" nodes syntax at the moment.

[gooroo]

Got it to work...Using the template node I had to make the sql query, then use the function query to build the full message that has to get passed to the postgres node. Thanks all for your help.

[thebaldgeek]

Awesome. Thanks for following up and letting us know you got it working and what you did.
Great for the community to see the problem and solution.