# Sir ,I want to copy 2 msg.id in my sql DB
**URL:** https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912
**Category:** General
**Created:** [15 September 2020 12:12 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912 "2020-09-15T12:12:07Z")
**Posts on this page:** 20
**Page:** 3
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [16 September 2020 16:21 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/41 "2020-09-16T16:21:58Z")
What logic do you want the Switch node to perform? What are the different cases you want to separate out?
---
### Author:  [@shipra](https://discourse.nodered.org/u/shipra)
#### Post date: [16 September 2020 16:26 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/42 "2020-09-16T16:26:17Z")
I want to seperate that case 1 and case 3
---
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [16 September 2020 16:27 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/43 "2020-09-16T16:27:54Z")
So create a case 1 message and create a case 3 message. Then look at how they differ and think what the Switch node could test to tell the difference.
---
### Author:  [@shipra](https://discourse.nodered.org/u/shipra)
#### Post date: [16 September 2020 16:29 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/44 "2020-09-16T16:29:54Z")
Sorry for troubling again,but can you tell which one i can use
---
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [16 September 2020 16:31 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/45 "2020-09-16T16:31:53Z")
I don't know what test you are trying to create, so no, I cannot tell you what you should use.
My understanding is that you have `msg.payload` that contains one or more values. So you should create a rule that looks at `msg.payload` to tests to see if the values you expect exist.
---
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [16 September 2020 16:44 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/46 "2020-09-16T16:44:28Z")
Going back to something you shared earlier that I missed. You say you have the Join node in place and its output looks like this:
```auto
msg.payload = {
"SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436'" : [
{ Fob_Tag_ID: "04222436" }
],
"SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814'": {
{ Trade_ID: "2814"
]
}
```
That isn't what I was expecting. I thought I suggested to wire the MQTT nodes to the Join node - but you appear to have wired the output of the SQL nodes instead - which is why the keys of the object are the SQL queries (because the Join node used msg.topic to set the keys).
That isn't very useful in that form. If you are going to ignore my suggestion and put the Join node after the SQL nodes (you may have a good reason for that... I wouldn't know), then you need to do some more work to get the message into a useful form.
I suggest you add a Function node after each SQL node to tidy things up. For one branch it would look like:
```auto
msg.topic = "Fob_Tag_ID";
msg.payload = msg.payload[0].Fob_tag_ID;
return msg;
```
and I hope you can see what to do to modify that for the Function node after the `Trade_ID` sql node.
You will also have to add code to the Function to handle the case where nothing is returned (I assume... I don't know... it's your app, you need to own and understand the logic).
If you get that right, then the Join node output will look like:
```auto
msg.payload = {
"Fob_Tag_ID" : "04222436",
"Trade_ID": "2814"
}
```
You can then add rules to the Switch node to see if `msg.payload.Fob_tag_ID` is null or not - and similarly `msg.payload.Trade_ID`.
At this point, I'm really at my limit of how much of this I can do for you.
Node-RED is all about working with messages. Changing the structures of messages to make it easier to work with them. Testing the values of messages to make decisions. You need to understand this stuff as its fundamental to working in Node-RED. If I keep building your app for you, you won't achieve anything.
---
### Author:  [@shipra](https://discourse.nodered.org/u/shipra)
#### Post date: [17 September 2020 07:11 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/47 "2020-09-17T07:11:03Z")
> [@knolleary](#):
>
> I suggest you add a Function node after each SQL node to tidy things up. For one branch it would look like:
Sir can you check this I tried it ,can you please suggest where I am wrong
```auto
9/17/2020, 12:39:37 PMnode: 10101SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436' : msg : Object
object
topic: "SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436'"
_msgid: "67c3234e.3f985c"
payload: array[1]
0: "CR_SUCCESS"
9/17/2020, 12:39:38 PMnode: 22222Fob_Tag_ID : msg : Object
{ topic: "Fob_Tag_ID", _msgid: "67c3234e.3f985c" }
9/17/2020, 12:39:39 PMnode: 00012TRADE_ID : msg : Object
{ topic: "TRADE_ID", _msgid: "67c3234e.3f985c" }
9/17/2020, 12:39:40 PMnode: 10101SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814' : msg : Object
object
topic: "SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814'"
_msgid: "842ee3b9.928eb"
payload: array[1]
0: "cr_success"
9/17/2020, 12:39:41 PMnode: 22222Fob_Tag_ID : msg : Object
{ topic: "Fob_Tag_ID", _msgid: "842ee3b9.928eb" }
9/17/2020, 12:39:42 PMnode: 00012
TRADE_ID : msg : Object
{ topic: "TRADE_ID", _msgid: "842ee3b9.928eb" }
```
---
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [17 September 2020 07:21 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/48 "2020-09-17T07:21:33Z")
No, I cannot help given just a screenshot.
You need to tell is what you have actually done.
I would start by explaining why there are two Join nodes.
---
### Author:  [@shipra](https://discourse.nodered.org/u/shipra)
#### Post date: [17 September 2020 07:37 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/49 "2020-09-17T07:37:53Z")
> [@knolleary](#):
>
> I would start by explaining why there are two Join nodes.
Two joines nodes are there one is joining both ID (04222436 and 2814) and another join node is there which is taking that CR\_SUCCESS and cr\_success from FOB and TRADE1
---
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [17 September 2020 08:00 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/50 "2020-09-17T08:00:02Z")
I'm sorry Shipra, I cannot have another long day of trying to help you, but you providing cryptic responses that don't actually help move things forward.
I tried to help yesterday by suggesting you add _one_ Join node to help combine the MQTT messages. You have chosen to add two Join nodes after the sql nodes. I don't know why you have chosen to ignore my advice and do something different. I don't know how you think having two Join nodes will help you separate out Use Case 1/2 from Use Case 3.
At this point, I think you need to start from scratch. Stop trying to randomly change the flows you have created to add more logic. Learn from what you have done and think about how to build the flows from scratch using all you have learnt.
I suggest the most useful thing you could do is to write down the series of steps your application needs to take - not as a long block of text, but as a list of individual steps. Here is how I understand your requirements:
1. A message arrives on the FOB MQTT topic
1. The FOB id is checked against the database.
1. if it is invalid, _what happens next?_
2. if it is valid, the message passes on to a Join node.
2. A message arrives on the TAG MQTT topic
1. The TAG id is checked against the database
1. if it is invalid, _what happens next?_
2. if it is valid, the message passes on to a Join node
3. The Join node passes on a message containing the FOB and/or TAG payloads
1. If the message contains just the FOB value
1. Update the database with the FOB value
2. send back a success message
2. If the message contains just the TAG value
1. Update the database with the TAG value
2. send back a success message
3. If the message contains both a valid FOB and TAG value
1. Update the database with both values
2. Send back a success message
Is that an accurate description of the logic you are trying to build?
If it is not accurate, then update the list - add the steps I have missed. There is no point carrying on developing this application until you have a clear description of the logic you are trying to build.
Once you have that list of steps, it will be much easier to understand the flow of messages you need and what logic is required.
---
### Author:  [@shipra](https://discourse.nodered.org/u/shipra)
#### Post date: [17 September 2020 08:03 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/51 "2020-09-17T08:03:51Z")
> [@knolleary](#):
>
> - A message arrives on the FOB MQTT topic
>
> 1. The FOB id is checked against the database.
> 2. if it is invalid, _what happens next?_
> 3. if it is valid, the message passes on to a Join node.
>
> - A message arrives on the TAG MQTT topic
>
> 1. The TAG id is checked against the database
> 2. if it is invalid, _what happens next?_
> 3. if it is valid, the message passes on to a Join node
>
> - The Join node passes on a message containing the FOB and/or TAG payloads
>
> 1. If the message contains just the FOB value
> 2. Update the database with the FOB value
> 3. send back a success message
> 4. If the message contains just the TAG value
> 5. Update the database with the TAG value
> 6. send back a success message
> 7. If the message contains both a valid FOB and TAG value
> 8. Update the database with both values
> 9. Send back a success message
Correct Flow this all i want
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [17 September 2020 08:13 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/52 "2020-09-17T08:13:35Z")
@shipra - In Nicks last post section 1.1.1 is a question. You did not provide an answer to it. The same thing for section 2.1.1 - no answer.
But with these two answers, you should have all you need to code your flow. I would do as Nick suggested and using the knowledge you have gained, start your flow all over.
I would add one more item before ou start. Write out an overview of what you are trying to accomplish with this project as it it seems to keep changing as time passed. You need to have a clear description of the task you are trying to complete or how will you know when it is done?
---
### Author:  [@shipra](https://discourse.nodered.org/u/shipra)
#### Post date: [17 September 2020 08:22 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/53 "2020-09-17T08:22:51Z")
> [@knolleary](#):
>
> tried to help yesterday by suggesting you add _one_ Join node to help combine the MQTT messages.
I read it thrice and try to follow your steps till late midnight but i was unsuccessful because little bit I am not getting which node has to connect to which,I am really in a trouble now.
---
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [17 September 2020 08:35 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/54 "2020-09-17T08:35:03Z")
> [@shipra](#):
>
> I am not getting which node has to connect to which
And this is exactly why I suggested taking a step back, and writing down a clear set of steps you want the application to follow.
So, let's take that list and think about how it translates to nodes in a flow:
Can you see how that represents the logic of the steps?
The two "CHECK IT EXISTS" Function nodes are placeholders for the Change/SQL nodes you are using to do the database lookup.
Once you have this basic structure, you can then replace each of the Debug nodes with the nodes to perform the actions you want to perform in each of those cases - such as writing to the database, or publishing message back over MQTT.
Does this make sense to you?
---
### Author:  [@shipra](https://discourse.nodered.org/u/shipra)
#### Post date: [17 September 2020 08:54 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/55 "2020-09-17T08:54:33Z")
yes that makes sense ,so like this i have ti make it from scratch?Do i have to replace all my nodes I am asking this because in coding part i.e gateway side in 3rd case first FOB id pub work then it goes to MATCH right that MATCH if gives me CR\_SUCCESS then only I will pub TAG code then further code.... So if i change all the nodes then my code will give me error because he cant find MATCH and MATCH1 Sub node(MATCH 1 is for TAG(which i had taken as another node named TRADE1 ). Then Suggest something in this?
---
### Author:  [@shipra](https://discourse.nodered.org/u/shipra)
#### Post date: [17 September 2020 08:56 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/56 "2020-09-17T08:56:01Z")
```auto
./Gateway.out
*************NCS_GATEWAY_START*********************
socket opening sucessfully in rugged Board
vcan0 at index 5
Wrote 16 bytes
Recieved DATA=0x076 [1] 0x51
Recieved DATA=0x076 [1] 0x03
0x03
FOB+Treade if loop
3,0
Recieved DATA=0x076 [8] 0 4 2 2 2 4 3 6
testing the buffer3=0x076 [8] 0 4 2 2 2 4 3 6 26
Recieved DATA=0x076 [4] 2 8 1 4
l3=18
0 4 2 2 2 4 3 6
buff3 printing= 2 8 1 4
Message ' 0 4 2 2 2 4 3 6 ' with delivery token 1 delivered
Message ' 2 8 1 4 ' with delivery token 2 delivered
publishing to 2 8 1 4 TRADE1 0 4 2 2 2 4 3 6 FOBtopic
Subscribe Successfully
Subscribing to topic MATCH
for client NCS_Client_01 using QoS1
Press Q to quit
Message received
topic: MATCH
message: CR_SUCCESS
Subscribe Successfully
Connection lost
cause: (null)
Subscribing to topic MATCH1
for client NCS_Client_01 using QoS1
Press Q to quit
Message received
topic: MATCH1
message: CR_SUCCESS
ID and FOB match with database
```
This is my gateway.o file output
---
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [17 September 2020 09:05 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/57 "2020-09-17T09:05:59Z")
I have no idea about your gateway. Sorry, I am not building this solution for you.
I have spent over an hour this morning of my time trying to help you. I have tried to get you to think about the logical steps your application needs to take. I have tried to get you to understand what the flow needs to do.
If at this point you still can't see what needs to be done, then I'm afraid I just cannot keep going.
* * *
One last time.
I suggest you put to one side what you have in Node-RED so far - disable the tab, so you don't delete anything.
Then start rebuilding the flow logic using the outline I have given about. Get to the point where the Debug nodes are being triggered when you expect them to be triggered.
_Then_ you can replace the Debug nodes with whatever database updates and mqtt publishes you want.
If that means changing your gateway code, then so be it.
---
### Author:  [@shipra](https://discourse.nodered.org/u/shipra)
#### Post date: [17 September 2020 09:50 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/58 "2020-09-17T09:50:19Z")
> [@knolleary](#):
>
> The two "CHECK IT EXISTS" Function nodes are placeholders for the Change/SQL nodes you are using to do the database lookup.
Sir just tell me Switch node edit properties
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [17 September 2020 09:53 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/59 "2020-09-17T09:53:58Z")
> [@shipra](#):
>
> just tell me Switch node edit properties
Shipra, what do you think the switch node should be doing, what 'edit properties' have you tried, and what was the result?
---
### Author:  [@shipra](https://discourse.nodered.org/u/shipra)
#### Post date: [17 September 2020 11:18 UTC](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912/60 "2020-09-17T11:18:45Z")
Sir I tried like this,
 
```auto
9/17/2020, 4:45:14 PMnode: FobINSERT INTO NCS_DATABASE2 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status) VALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB','DR OPEN BY FOB ',' ','Shipra Nigam','undefined',' ',' ',' ','DR1 ON') : msg : Object
{ topic: "INSERT INTO NCS_DATABASE2 (Da…", _msgid: "1c7ce197.631b8e" }
9/17/2020, 4:45:14 PMnode: tag
INSERT INTO NCS_DATABASE2(Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status) VALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'TAG','DR OPEN BY TAG',' ','Shipra Nigam',' ','undefined',' ',' ','DR1 ON') : msg : Object
```
I know i have made some error thats why output is not coming Sir please suggest.
what i have tired in this screen shot is I just have entered only FOB this time to check this with a fresh start.
[Previous page](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912.md?page=2)
[Next page](https://discourse.nodered.org/t/sir-i-want-to-copy-2-msg-id-in-my-sql-db/32912.md?page=4)