NodeRED and SQL data fetching problem

General
1

flow.json (2.9 KB)

Hey, I am having problem while fetching the data from SQL database using MySQL node. The flow i used is attached. The date picker generates the same SQL syntax as the manual entered "from" and "to" date, but the manual one returns the data while the date picker one doesn't.

2

since they are both running the same function and you are hardcoding the dates, it should be the same. Put a debug node (display complete msg object) of the output of the function node, change the debug on the output of the mysql node to display the complete msg object and run it again.
You might want to add a catch node with a debug node so see if it shows anything,

3

hard coding of the node is to check the SQL command. The hard coding is commented while using the date picker template. Both hard coding and template generate the same SQL command, but the later returns an empty string.

4

So with the hard coded dates, do both paths work because that is what you imply in your original post.

if that is not the case then what does the flow look like that is giving you the issue?

5

Actually you can see the output coming out of the MySQL node when used with hard coded date and date picker. both of them returns the same command (for SQL) in the debug node used after the MySQL node. can not figure out what is happening. It works only with hard coded dates.have you checked the flow ?

Screenshot%20(13)
Screenshot (13).png1366Ă—768 63 KB
Screenshot%20(14)
Screenshot (14).png1366Ă—768 67.8 KB

6

Please put a debug node on the output of the function node - set to display the complete msg object - and run both cases and paste the results here.

7

Check the results. the upper one is with datepicker and the lower one is with hardcoded dates.

8

That's not from the function node, and it is not showing the complete message.

9

I notice the dates in the two examples are different - might it be that there is no data for one of the time frames???

10

Your eyes must be better than mine, I can hardly read that, in fact it looks to me as if the BETWEEN is specifying the same date/time for both ends so not likely to find many.

11

There are data for 13th April.

12

It doesn't say 13th it says 12th to 12th doesn't it?

13

I have a bunch of data for 13th April

14

It has data for 12 April too.

Screenshot%20(15)
Screenshot (15).png1366Ă—768 81.7 KB

15

You would have to specify the start of the day and the end of the day to get the data for the day. You are just specifying the start of the day for both.

Change your working one to show the same as the other and see what you get.

16

Screenshot%20(16)
Screenshot (16).png1366Ă—768 79.3 KB

See the results for the same date selection. BTW the start date and end date both can be same. isn't it?

17

Ah, I see you are only storing dates, not date/time.
You still haven't done what has been asked multiple times. Put a debug node showing what is going into the sql node for the working and failing messages. Don't forget to show the complete message. If you can copy/paste the result from the debug window it will be easier to read than a screenshot.

18

////////This is what going inside the SQL node with hardcodes dates.

_msgid: "794172d1.c38e7c"

topic: "SELECT Sensordata FROM timeseries WHERE Time BETWEEN '2019-4-12'AND'2019-4-12';"

payload: ""

///////////////This is what goes inside with datepicker

fromDate: "2019-04-12"

toDate: "2019-04-12"

socketid: "682hhxrKVGSfBURaAAAB"

_msgid: "b7640199.2a8fe"

topic: "SELECT Sensordata FROM timeseries WHERE Time BETWEEN '2019-04-12' AND'2019-04-12';"

19

Well I must admit I can't see any difference, assuming that the missing payload is not an issue.
What happens if, in the non-working case, you don't put the WHERE clause in at all so it should return everything?
Also have you told us which mssql node you are using? node-red-contrib-something probably.

20

removing WHERE means getting all data using the * stuff. But my goal is to get the data for the range of dates.

I'm using node-red-contrib-mysql node.