Connecting MSSQL Database to Node-RED.

[Liam Broughton]

Hi there,

I am fully aware there are tonnes of these types of posts. However I have been searching for a few days now and still no luck.

May be an easy fix, I dunno haha.

So I am trying to connect Node-RED to my database and I am getting the error code:

SequelizeDatabaseError: Could not find stored procedure 'Timestamp'.

The value is Timestamp as that is what I have entered in the inject node (because one of my table values is Timestamp).



I'm obviously doing something wrong but at least I have established the connection aha!

Thanks guys!

[Garry Hayne]

Liam, can you show us the mssql node, this is an SQL problem.

[Liam Broughton]

Here is a print screen of the settings I have entered for my MSSQL node.

Thanks,

-Liam.

[Liam Broughton]

However, if I get rid of the "Payload" in the inject node, then it displays this message in the debug tab, and still displays no data in my MSSQL Server database.

Thanks,

-Liam.

On Wednesday, October 18, 2017 at 12:14:04 PM UTC+1, Garry Hayne wrote:

[Garry Hayne]

Liam, do you know the SQL language and have you created a database on the server?

[Liam Broughton]

I do not know the SQL Language, I am very new to all of this stuff. And yes, I have created a database on the server. I have entered the exact details of the server into the SQL node.. Just don't see why it isn't connecting to the SQL Database..

Do I need to set up another procedure? Or should it just auto connect?

Thanks,

-Liam.

[Garry Hayne]

Does the MsSql node show "Connected" when you deploy?

Also you are just passing "Timestamp" as a topic in the inject mode. The topic should probably be a valid SQL statement that Mssql server understands, I don't know this node, have a look at the info.

[Liam Broughton]

Yes, the MSSQL node is connecting to the server. As in the Node-Red log it says:

Connection Successful to database Node-Red-Test with user sa

So are you saying that I need to input a code which the MSSQL server will recognise? How would I find out how to do this aha?

Thanks,

-Liam.

[Garry Hayne]

I would put a function node between the inject node and the mssql node:

msg.topic = "INSERT INTO YourTableNameHere (Timestamp) VALUES (" + msg.payload + ")";

return msg ;

This should insert the Timestamp you are generating in the inject node into the Timestamp field in your database table.

SQL is easy to learn for basic database access, google a bit.

[Liam Broughton]

Thanks for the advice Garry,

I tried what you suggested.. and now another problem haha:

SequelizeDatabaseError: Arithmetic overflow error converting expression to data type datetime.

I really do have no luck in these types of things.. However, I will keep on trying to get it to work!

I have tried another node also and some coding I found online, getting another error on that one which says:

RequestError: Cannot insert explicit value for identity column in table 'MQTTData' when IDENTITY_INSERT is set to OFF.

Anything you can help with?

Thanks,

-Liam.

[Julian Knight]

While not an MS SQL expert by any means. I can say this. Your first error will be due to the fact that you have created the Timestamp field in your db table with the wrong data type or you need to transform the JavaScript timestamp into a datatype that the server understands correctly. If you've created the table with a datetime field type, I'd guess that a JavaScript timestamp (which is a large integer number) cannot be correctly interpreted.

For the second error, you are trying to insert data into a field that is an auto-id field. Make sure you don't have that field listed in your INSERT statement, the database will add an appropriate ID automatically.

[Liam Broughton]

Hi Julian,

Thanks for the support!

I have been at it all day haha and I have finally sorted the input into the database!

The timestamp function didn't work but if I use the command "current_timestamp" then it works perfectly.

Just figuring out how to retrieve data from text boxes and insert them into the function now. Have either of you two dealt with this before?

So basically I want to have an input text box on the ui (which I have created) but I want the text that is typed into this text box to be sent to the database.

I just need to know the command for retrieving data from that text box form..

Any ideas?

Thanks,

-Liam.

[Julian Knight]

You don't "retrieve" the data, it is sent to you :-)

Try connecting the output port of a text node to a debug node and see what output you get as you type things in.

One of the things you are REALLY going to want to know is about data types. Databases, especially SQL ones, are very picky about data types and if you don't understand at least a little about them, you will constantly get stuck. In particular, you are using MS SQL I believe which is, after all, an enterprise level tool used by professionals and that scales to millions or even billions of records. So data has to be robust, well documented and completely predictable.

[Liam Broughton]

Hi Julian,

Thanks for the advice! I am on the road today so I will have a look at all this tomorrow.

So, i'll try and see what I get from just connecting a form to a debug node. And then in the function I should enter the values into that???

Would it not all have to string together in order to get the connection or would it just pick it up if in the same flow?

At the moment I have some code in my function node:

msg.topic = "INSERT INTO dbo.MQTTData (JobType, Timestamp, JobStatus) VALUES ('Maintenance', current_timestamp, 'Not Completed')";

return msg ;

So what I would want from this form is the JobType to be entered in place of the value 'Maintenance'. I'll have a little play and if I get stuck then I can always refer to the forum for some advice :)

Thanks a lot guys! Much appreciated!

-Liam.

[Julian Knight]

Something like:

msg.topic = "INSERT INTO dbo.MQTTData (JobType, Timestamp, JobStatus) VALUES ('" + msg.payload + "', current_timestamp, 'Not Completed')";
return msg ;

[Liam Broughton]

Hi Julian,

Thanks a lot for that line of code. I had a play around with Node-RED and the forms that are on the Node-RED dashboard. And I have successfully got it working.

Now, when the form is entered, the data from the form is collected by the function node and sent over to my MS SQL database.

All i want to do now is look into getting a button, that updates the "Not Completed" to being "Completed" once a job has been completed.

I will look into this today, and as I keep on saying, if I get stuck i'll come running for some advice!

Thanks a lot guys, much appreciated.

-Liam.

[Liam Broughton]

So, end of the day and I have finally got all that I need working!

I have set up the MSSQL Server to an Excel Spreadsheet to allow easy access for anyone wanting to see the data from the server.

Excel also allows filters to the headings in the table also, very useful.

I have also got the UPDATE query to work so I can update the status of the job via a form on the node red dashboard ui.

Same with the DELETE query, if you enter the id of the job then it will delete it from the database.

Thanks everyone for the help and I am glad that I had to use some of my own knowledge and playing around with Node-RED in order to get it working.

Feeling accomplished! :)

Thanks,

-Liam.

[Julian Knight]

Great, glad you got it working. You know that you can document example flows on the flows site? If you think someone might benefit from the info in the future, it is nice to put something up if you have the time.

[Liam Broughton]

Good thinking!

I'll add the flow on Monday when I am back at work! :)

Thanks again!

- Liam.