MYSQL result splitting problem

General
1

HI !

I have problem with the spliting returning sql array result. As you will see down below i got the result from sql node normaly, but soon as pass through the split node i got that.

image
image354×731 13.9 KB

Here is the node, what i use:

[{"id":"8144efff.0ea0e","type":"inject","z":"11e1e4a1.276eeb","name":"","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":90,"y":540,"wires":[["57e4b127.669fa"]]},{"id":"13ab48eb.9794b7","type":"split","z":"11e1e4a1.276eeb","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":610,"y":620,"wires":[["f3f0b316.0f78c"]]},{"id":"f3f0b316.0f78c","type":"debug","z":"11e1e4a1.276eeb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":760,"wires":[]},{"id":"57e4b127.669fa","type":"function","z":"11e1e4a1.276eeb","name":"","func":"var sql=[]\n\nsql.push({topic : \" SELECT device_id FROM devices\"});\n/*sql.push({topic : \" SELECT humidity FROM tempandhum ORDER BY date DESC  LIMIT 1\"});\nsql.push({topic : \"SELECT FROM_UNIXTIME(log_date,'%Y. %m. %d %H:%i:%s') As smoke_date FROM smoke_alert ORDER BY id DESC  LIMIT 1\"});\nsql.push({topic : \"SELECT FROM_UNIXTIME(log_date,'%Y. %m. %d %H:%i:%s') As alert_date FROM alert ORDER BY id DESC  LIMIT 1\"});  */\n\nreturn [sql];","outputs":1,"noerr":0,"initialize":"","finalize":"","x":240,"y":600,"wires":[["39df1aa6.7158b6"]]},{"id":"39df1aa6.7158b6","type":"mysql","z":"11e1e4a1.276eeb","mydb":"d0b63b56.52c6c8","name":"","x":330,"y":660,"wires":[["480d9344.365e7c","13ab48eb.9794b7"]]},{"id":"480d9344.365e7c","type":"debug","z":"11e1e4a1.276eeb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":370,"y":740,"wires":[]},{"id":"d0b63b56.52c6c8","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"smh","tz":"","charset":"UTF8"}]

Can someone help me, how split the array result what i got correctly?

Thank you for your answers !

2
  1. what version of NR and nose.js are you using? (see startup log)
  2. what do you see if you change the debug1 node after the split node to display the 'Complete msg object'?
3

@mac5562

You're trying to split the msg. payload at every \n (newline) when the payload is an array of objects.
Try splitting it using an array fixed length of 1 and leave the string/Buffer and Object Input fields empty.

4

node js version: v12.19.0
node red: 1.2.7

I got this i set the debug node to show the complete msg object
image

5

I tried it, but not solved the problem

6

So, after the split node, you are getting a series of objects each containing a payload that is from a row in the database. This seems to me that it is working exactaly as it is suppose to.

Now the question is, what do you want the data to look like and what do you want to do with it?

7

What i want, the payload object which i got from the split node look like this:

image
image366×514 13 KB

I got that with this nodes.

[{"id":"ea2f1202.bd75f","type":"inject","z":"11e1e4a1.276eeb","name":"Fake DB Result","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[     {         \"EventType\": \"Hei\",         \"TotalTime\": 608     },     {         \"EventType\": \"Production\",         \"TotalTime\": 25405     },     {         \"EventType\": \"Serilizering\",         \"TotalTime\": 6090     } ]   ","payloadType":"json","x":160,"y":800,"wires":[["6fdf0361.bcf9dc","c1c59fdc.8b0f6"]]},{"id":"6fdf0361.bcf9dc","type":"split","z":"11e1e4a1.276eeb","name":"","splt":"\\n","spltType":"str","arraySplt":"1","arraySpltType":"len","stream":true,"addname":"","x":350,"y":800,"wires":[["5e6d5497.a5a52c"]]},{"id":"5e6d5497.a5a52c","type":"debug","z":"11e1e4a1.276eeb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":530,"y":800,"wires":[]},{"id":"c1c59fdc.8b0f6","type":"debug","z":"11e1e4a1.276eeb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":350,"y":860,"wires":[]}]

inject node payload data format is the same which i got from a real database, but in that case, a split is working as i want it

8

That is not the sql statement you used in the flow you posted originally. in your original flow you use SELECT device_id FROM devices.

10

Like i said before, these data not from the database. It's from a inject node, which payload is a array with objects which look like the the data, what i got from sql node after, i give a query.

11

Please try this: run your flow but change the sql to select * from the database and then look at the results.

Copy and paste the results AFTER expanding all the arrows in the debug

Then explain what you want the output to look like.

12

Are you sure the results you receive are all arrays ? By the looks of it you also receive objects, which are not split.

13

@bakman2 the array is the result of the rows returned from the MySQL node. They are all in one msg. The `splits node is used to separate the rows ow results from one msg to a msg per row.

14

Meanwhile i solved it. I use two json node which are connected to sql node, and result is what i want.

15

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.