insert two different msg.payload in a database

[Aline Eid]

Hey all,

I want to do the query below into a function:

insert into table values (msg.payload.1, msg.payload.2);

this function has two input (2 different msg.payload) so the code that I am trying to write is:

var m={

    topic: "insert into info values ('"+msg.payload+"','"+msg.payload+"');"

};

return m;

it is adding two rows in the info table. but want to add only one row with two different values.

any idea?

Thanks in advance

[Mark Setrem]

Lets start with the node-red part.  How do you have a function that has 2 different msg.payloads?

If you mean you have 

[part of flow] -|

                |-------[your function here]

[part of flow] -|

you need to change your function to have someone of waiting to combine both parts before continuing ( see http://flows.nodered.org/flow/8ba7f90f3ea8d92b1e01)

if you mean something else can you explain?

On the SQL part I assume you have more than one column in your table?  If so you should specify which column you want the values placed 

eg  

insert into table tablename (firstcolumn, last column) values (msg.data1, msg.data2);

[Dave C-J]

it may be possible to use the new join node in 0.14.0 ;-)

If the scenario is as Mark guessed..

[Ben Hardill]

On Wednesday, 22 June 2016 11:21:45 UTC+1, Dave C-J wrote:

it may be possible to use the new join node in 0.14.0 ;-)

If the scenario is as Mark guessed..

But both of these methods are some what reliant on messages arriving in sensible orders.

input 1 -----|
                |----> function/join node
input 2 -----|

if 'input 1' delivers 2 messages for before 'input 2' delivers it's first message then things will get out of sync and the value would be offset in the database.

[Aline Eid]

well, I use the method of Mark and it works. Thanks all

[Julian Knight]

Note that some database servers allow a hybrid insert/update method which would work nicely here. For example, in MySQL:

INSERT ON DUPLICATE KEY UPDATE

So that you wouldn't have to worry what order things came in and wouldn't have to merge the data in NR. Wouldn't necessarily work with all data schema's but is nice and simple where is does work.

[Ben Hardill]

You still have to worry about order.

Assume the following stream of messages

Input-1 -> A, B, C, D, E, F, G, H...
Input-2 -> 1, 2, 3, 4, 5, 6, 7, 8, 9...

If Input-1 delivers 2 messages before Input-2 delivers it first we end up with context that looks like this

Input-1    Input-2
  A            -
  B            -
  B            1
  C            2

We then combine the context to the get the output message. Which would royally screw up any data stored in the database.

We don't know enough about the input sources to say how this would work for sure, but if they are in any asynchronous or IO based then ordering can not be guaranteed.

You could probably fix this with a FIFO based context but that is starting to get very complicated.

[Julian Knight]

Yes, that's why I commented that it depends on the schema. It would work in some cases.

[Dave C-J]

Murphy's law usually prevails...