I have a flow that selects data from a table and inserts the data into a backup table in another database, This has been working just fine for over 6 months. After I select the data from the source, a function node converts the data into a SQL statement to insert into the target, Today I started getting an error in the function "getFullYear" where I am parsing datetime data from the source and building a date string. The error that I get is "m.created.getFullYear() is not a function".
The code in the function node is:
var m = msg.payload
var t =""
for(x=0;x<m.length;x++){
t = "INSERT INTO events SET row_id="+m[x].row_id+", source='"+m[x].source+"',name =' "+m[x].name
t=t+ "', displayName='"+m[x].displayName+"', value='"+m[x].value+"', unit='"+m[x].unit+"', deviceId="+m[x].deviceId
t = t+", hubId="+m[x].hubId+", locationId="+m[x].locationId+", installedAppId="+m[x].installedAppId
t= t+ ", descriptionText='"+m[x].descriptionText+"', created="
var year=m[x].created.getFullYear() **<= Error here**
var month = m[x].created.getMonth()+1
var date = m[x].created.getDate()
var hour = m[x].created.getHours()
var minute = m[x].created.getMinutes()
var seconds = m[x].created.getSeconds()
var sqlDate = year+"-"+month+"-"+date+" "+hour+":"+minute+":"+seconds
t=t+"STR_TO_DATE('"+sqlDate+"','%Y-%m-%d %H:%i:%s')"
node.send({topic:t})
//x=(m.length)+1
}
Any idea what is going on? NR version is 1.2.7 on RPi4. Thanks for your help.
Have you updated any contrib nodes or node-red or nodejs or the database table structure?
Also, what SQL DB (e.g. mySQL, MSSQL, sqllite?) and what SQL contrib node are you using?
On you fixing what you have
To understand what is happening, add the following on the first line after the for(...) {
node.warn(`type of created: ${typeof m[x].created}, value of created: ${m[x].created}`);
I have not updated node-red or node.js recently (at least since last week when this was working). Also, I have not changed the table structure of this particular table.
I'm using mySQL and node-red-node-mysql (v 0.1.4). I did update this node from v0.1.2 yesterday
Very strange - I reverted to 0.1.2 on a test instance of NR and I still get the same error. I did restart NR and confirmed that I the version of node-red-node-mysql is 0.1.2. I was going to create and issue but now I'm not sure what is causing this.
I'm trying to remember - but the basic platform has not changed. I updated the mySQL node and some other nodes that were showing updates (unfortunately I can't recall which ones).
I meant what is it that the SQL node does that it is now different? The code you posted suggested that it used to return a Date object, but now it returns a String.
I think this comes from a naive fix from this thread - Splitting array - from MySQL query
I have now tried to do a better object copy in version 0.1.5 so hopefully anything is now preserved, though my simplistic testing always seems to return strings anyway.
Yes - that's what I was hoping to see when I reverted to 0.1.2 but it is typed as "string" now. I put in a debug to get the type and this is what is shows:
type of created: string, value of created: 2020-06-01T10:15:00.000Z
I would agree that it seemed to be returning a date type before (or at least the function node was able to work with it like a date object), but now I can't get it to work even with 0.1.2
Similar code was working last week as I have a scheduled flow that copies data from one source to a backup and it stopped working yesterday. I have worked around this by converting the date returned from the query into a java date (using new Date()). Please let me know if you need any other information. Thanks.
In particular from that post
"this feature can be turned off by setting dateStrings to true in the connections:"
So that suggests that maybe something has changed and that flag is now set to true where it was set to false (or maybe defaulting to false) previously.
Maybe a feature request for adding that option to the mysql connection config node is in order.
Another option would be the ability to add any additional flags you desire (that the mysql lib supports) via a JSON only typedInput that are then passed to the connection?
