MySQL data transfer to MSSQL

General
1

Good morning,

Again im here for help!
What Im trying to achieve:
Task is to import all MySQL entries into MSSQL server.
Whenever user fills php form these data must be sent to MySQL and MSSQL.

Where Im stucked now:
I dont know how to push these values (for example payload[0].id ) into MSSQL, and what should be written into function node.
Injection node msg.topic: SELECT * FROM input_data ORDER BY id DESC LIMIT 1;

image
image1205Ă—129 9.91 KB

image

Question:
Is it even possible to make this thing automatic? Something like watchdog for MySQL DB?

Every help will be appreciated! Thanks!

2

Now when i select INJECT, it sends data successfully, but i need to find solution for scenario: if only new ID is created, only then push data into MSSQL. Hope for some ideas! Thanks!

3

If you have an Identity column (hopefully you do :wink: )

You can use IF NOT EXISTS

IF NOT EXISTS (SELECT ID FROM input_data WHERE ID = payload[0].id) INSERT INTO ...

maybe you can use that approach

4

I would also encourage the use of template here - makes the SQL a lot easier to deal with.
using brackets to fill in the place holders {{some.object.path}}

5

Marcus, thanks mate!! Ill
I can see, that youre good at this. May i ask another question?
How can i import all entries which are located in mysql till today? when i try to inject SELECT * FROM input_data ORDER BY id DESC LIMIT 1000 ; it only returns 1 entry instead of 1000.
I need to transfer existing data to MSSQL at first, and then i should implement IF NOT EXISTS .

Thanks mate for reaching your hand in this issue, as im very green in node-red exploration.

6

I can see, that you're good at this

Lets not jump ahead of our self :wink:

I don't do MySQL and I am unfamiliar with LIMIT.
in MsSQL the equivalent is:
SELECT TOP 1000 * FROM input_data order by id DESC

I am not sure of the output from the MySQL node - so a little blind here,
but can you instead employ a where clause WHERE date < today?

7

When I insert WHERE date < today in MySql injector, syntax is incorrect.

SELECT * FROM input_data WHERE date < today

Maybe only soltuion is to reverse asking in this case, when MSSQL should ask MySQL for all entries?

Or inject node should contain i++ variable?
Grrrrr,

8

That should work. Are you sure it only returns one record? Have you got a debug node directly on the output of the Mysql node?
What happens if you take off the limit and the order by?

9

Thanks for idea, but yes, one inject returs only one msg.payload , which contains one Mysql entry

image
image1532Ă—391 27.6 KB

image
image1195Ă—185 9.67 KB

Maybe my node config is really messed up

10

As @Colin highlights - it really should work.

but on this
WHERE date < today

today should be todays date (or the MySQL date function) MsSQL is getDate()

11

Whats this doing?

image

Is it filtering to the last row :laughing: - hence you wont get an array?

12

Flow which Im @ right now

[{"id":"ba997b9ccfe7069d","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"5a7faf4857ebe3de","type":"debug","z":"ba997b9ccfe7069d","name":"DEBUG SHOW ALL","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1220,"y":360,"wires":[]},{"id":"765669b67b92638b","type":"mysql","z":"ba997b9ccfe7069d","mydb":"9c3eddb86f5612e1","name":"","x":570,"y":360,"wires":[["ab9d55f5689a1ece"]]},{"id":"2e1d403b394b363a","type":"inject","z":"ba997b9ccfe7069d","name":"SELECT 1 LIMIT 1;","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":" SELECT * FROM input_data ORDER BY id DESC LIMIT 100","payloadType":"str","x":350,"y":360,"wires":[["765669b67b92638b"]]},{"id":"9095a0d4a61617f7","type":"MSSQL","z":"ba997b9ccfe7069d","mssqlCN":"0e11ab78f066762c","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"\r\
13

just checking if message is reaching debugger...

14

I (think) I see the problem,

you are only passing row 0

msg.payload = {
 id: msg.payload[0].id, 
 kritusie_putni: msg.payload[0].kritusie_putni, 
 izbr_putni: msg.payload[0].izbr_putni, 
 bar_pat:msg.payload[0].bar_pat,
 gaz_pat:msg.payload[0].gaz_pat,
 gaz_pat_day:msg.payload[0].gaz_pat_day,
 gaz_pat_night:msg.payload[0].gaz_pat_night,
 ele_pat:msg.payload[0].ele_pat,
 ude_pat:msg.payload[0].ude_pat,
 location:msg.payload[0].location,
 fordate:msg.payload[0].fordate,
 date:msg.payload[0].date,
 username:msg.payload[0].username
}
return msg;

the result is an array (not a message per row)
here you are only using row 0 from that array

15

Put a split node after the MySQL.

then for each msg the split churns out

msg.payload = {
 id: msg.payload.id, 
 kritusie_putni: msg.payload.kritusie_putni, 
 ....
}
return msg;
16

MARCUS!! YOU DID IT! You found glitch in my nodes!
Now MySQL limit counter is being pushed in MSSQL! If limit is 5 , 5 new entries are pushed into MSSQL!
Ok, now I have to figure it out, how to push only unique ID(latest). And after that i can use timer, for example 1x5min to check MySQL if there is new ID.

17

Put a template node directly after the split.

Then your MsSQL after the template.

Use this as your template.

IF NOT EXISTS (SELECT id FROM input_data WHERE id = '{{payload.id}}')
INSERT INTO input_data (id, other_col, other_col)
VALUES ('{{payload.id}}','{{payload.other_col}}','{{payload.other_col}}')

Disclaimer: there may be other ways, and this may have errors - but this gives you some control

18

Now im getting all fields as NULL

19

capture one of the commands in the debug - and run it within SMS as a new query.

20

Im sorry Marcus, but i dont understand what you mean by SMS.
Here is debug node in each step.
Sorry for my incompetence.