I have a pretty basic flow going on and i'm just trying to do and insert on my sql server. I'm having and issue with the insert because of a time field that i'm passing to my insert statement but I get the error thrown that the time is invalid:
{"code":"EPARAM","message":"Validation failed for parameter 'Time'. Invalid time.","details":"Validation failed for parameter 'Time'. Invalid time.","name":"RequestError","number":"EPARAM"}
I'm using a injector that sends this info:
AN ANR 2025-01-06T14:07:45.634-04:00501 204250001 d 127.0 0132625 839999
Which then goes to a function to parse my info. My code to parse is:
let rawdata = msg.payload;
// Define the regular expression patterns
let datePattern = /(\d{4}-\d{2}-\d{2})/;
let timePattern = /(\d{2}:\d{2}:\d{2}\.\d{3})/;
let codePattern = /:(\d{3})/;
let quantityPattern = /(\s\s)(\d+)/;
let programPattern = /(\s\s)(\d+)(.)(\d\d\d)/;
let weightPattern = /(d.{9})/;
// Extract values using the regular expression patterns
let dateMatch = rawdata.match(datePattern);
let timeMatch = rawdata.match(timePattern);
let codeMatch = rawdata.match(codePattern);
let quantityMatch = rawdata.match(quantityPattern);
let programMatch = rawdata.match(programPattern);
let weightMatch = rawdata.match(weightPattern);
// Initialize variables
let date1, time1, code1, run1, program1, weight1;
if (dateMatch) {
date1 = dateMatch[1];
if (timeMatch) {
time1 = timeMatch[1];
if (codeMatch) {
code1 = codeMatch[1];
if (quantityMatch) {
run1 = quantityMatch[2];
if (programMatch) {
program1 = programMatch[4];
}
}
}
}
}
if (weightMatch) {
let test = weightMatch[0].substring(5);
weight1 = test.trim() === '' ? 0 : test;
}
// Create an object with the extracted values
let data = {
Date: date1,
Time: time1,
Code: code1,
Run: run1,
Program: program1,
Weight: weight1
};
// Store the object in a flow variable
flow.set("dataObject", data);
// Debugging information
node.warn(`Data: ${JSON.stringify(data)}`);
// Return the message
return msg;
and this is my insert statement:
INSERT INTO checkweigher (date, time, code, run, program, weight, hardwareId) VALUES (@Date, @Time, @Code, @Run, @Program, @Weight, @HardwareId);
I'm very new to node-red. I had all these being done with python before and i'm currently changing them all over to node-red.
Any help would be great.
My object object looks like this:
{"Date":"2025-01-06","Time":"13:07:45.634","Code":"005","Run":"204250","Program":"001","Weight":"127.0"}
Just to add i have checked my sql for the type on the Time field and its set to time.
I have no issues doing the insert with python directly with the same data but not through node-red. I'm sure i'm just missing something stupid but I have been struggling for too many hours on this little thing so hense why i'm asking some pro's.
Also in the picture above i have the time field set to a string. That was just me trying things. it was flow.dataObject.Time before which gave me the same error.
First, I recommend you simply add your computed values to the msg object. This is using context unnecessarily (I won't go into why this matters here and now)
Next, if you add a debug node (set to show the complete msg object) to the output of the mssql node, you will be able to see in the various properties of the returned msg what values were used and the final SQL executed against the database (that you can copy and test in Azure SQL studio or your client of choice)
Thanks for the replys but I'm still having issues.
I have changed it so its being sent to the msg.objectTime. I have also changed my time format to something like "07:07:07" even though this is not what I want. I'm just trying to get it to do the insert first. The reason why that format will not work for me is because I'm trying to log products that are passing by a photo eye and they are going faster then 1 per second so I need the milli second portion but one thing at a time.
Here is my setup. I'm also wondering if I'm missing something because from what I see in the debug log for the SQL node, I'm not seeing the select statement that would of been executed agains the SQL server.
I will keep trying things. I feeling I'm making a stupid mistake somewhere since I'm very new to node-red, but like I said i have no issues using a python script for the same purpose. And it excepts the time format of 00:00:00.000 with no issues.
As you can see in the picture once I did the insert. I have been able to pass the sql statement from node-red but only with a null value. I can't seem to pass any format that is acceptable through node-red.
This seems very simple. This is a fresh install of node-red on a windows server 2019. I may try to spin up a docker on another box to see if something with the windows install. I have had no issues getting data from esp32's, mqtt, select statements from sql and such but this one little thing is ticking me off and i'm a person that can't leave it along until i get it working. Grrrr.
I have tried a bunch of different formats for time but non work for me.
This is my last bug look at my issues for tonight at least.
Has nobody else had issues with this.
I'm just trying to send a time stamp to and sql and it won't work at all. I have been able to get my sql to except data but it doesn't make any sense.
I have a simple injector that injects the timestamp.
I have a function that parts that time stamp with:
let rawdata = msg.payload;
let pattern = /(\d{2}:\d{2}:\d{2})/;
// Extract values using the regular expression patterns
let timematch = rawdata.match(pattern);
msg.objectTime = timematch
msg.timeTest = "090909"
return msg;
For my sql query I'm using:
INSERT INTO checkweigher_test (Date, Time, Code, Run, Program, Weight,hardwareId)
VALUES ('2025-01-06', @time, '005', '204250', '001', '127.0','1');
Which if I substitue the @time with "07:06:07" or 07:06:07.876 it has no issues doing the insert through node-red.
The issue is if i'm using a variable or and object it seems.
I have had no issues inserting any other data types other them time.
I did see that. I changed it in my function script but that did not make a difference.
I was able to get everything working by passing the query statement to the sql module via something like msg.query. I was always using the gui in the mssql plus node instead of passing it in. Not sure why the gui does not work as it should but I'm able to make it work which is sad because I would rather of made it work in there for ease of read ability for non code people.
let rawdata = msg.payload;
let pattern = /(\d{2}:\d{2}:\d{2})/;
// Extract values using the regular expression patterns
let timematch = rawdata.match(pattern);
msg.testTime = timematch ? timematch[0] : null; // Access the first element of the array
// Construct the SQL query
msg.query = `INSERT INTO checkweigher_test (Date, Time, Code, Run, Program, Weight, hardwareId)
VALUES ('2025-01-06', '${msg.testTime}', '005', '204250', '001', '127.0', '1');`;
return msg;
This was my test function which worked with no issues.
Like I said though using the Parameters section in the mssql plus node config with the editor would not work with the same data being as an input.
Cheers.