[SOLVED] Form to mysql error

General
1

Hi,

New to node-red and have been fighting with this for a while now and I seem to be quite stuck...

Using mariaDB with SQL node. Sending data from form to database

The fist part is working fine

This is my whole function block:

msg.quantity = msg.payload.bt1;
msg.topic = "INSERT INTO `ORDERS` (`OrderID`, `CreatedAt`, `FinishedAt`, `Status`) VALUES (NULL, current_timestamp(), '0000-00-00 00:00:00.000000', '1'); INSERT INTO `QUANTITIES` (`QuantityID`, `ProductType`, `Quantity`, `OrderID`) VALUES (NULL, '1', "+ msg.quantity +",(SELECT OrderID FROM ORDERS ORDER BY OrderID DESC LIMIT 1)); DROP PROCEDURE IF EXISTS dowhile;DELIMITER $$ CREATE  PROCEDURE dowhile() BEGIN DECLARE num_rows INT DEFAULT (SELECT Quantity FROM QUANTITIES ORDER BY OrderID DESC LIMIT 1); WHILE num_rows > 0 DO INSERT INTO `BARCODES`(`BarCode`, `OrderID`, `ProductType`) VALUES (NULL, (SELECT OrderID FROM ORDERS ORDER BY OrderID DESC LIMIT 1), '1'); SET num_rows = num_rows - 1; END WHILE; END$$ call dowhile();";
return msg;

This is the part giving trouble, but it doesn't give any errors when trying to query it with phpMyadmin and creates the correct rows.:

DELIMITER $$ CREATE PROCEDURE dowhile() BEGIN DECLARE num_rows INT DEFAULT (SELECT Quantity FROM QUANTITIES ORDER BY OrderID DESC LIMIT 1); WHILE num_rows > 0 DO INSERT INTOBARCODES(BarCode,OrderID,ProductType) VALUES (NULL, (SELECT OrderID FROM ORDERS ORDER BY OrderID DESC LIMIT 1), '1'); SET num_rows = num_rows - 1; END WHILE; END$$ call dowhile();"

nodered-screen1
nodered-screen1.jpeg1920Ă—1080 300 KB

2

Why are you dropping & recreating the procedure each time? The purpose of a procedure is to enable the db engine to handle some incoming data more efficiently by compiling the procedure, if you keep recreating it, it can't do that.

3 Likes
3

Also I suggest splitting it into separate messages, one for each SQL command. Then you will be able more clearly to see whether the problem is the end of one statement or the start of the next.

1 Like
4

Thanks for replies.

@TotallyInformation just to make sure the while loop isn't already running when initiating it again. Is that silly?

@Colin how exactly can I do this. As per documentation of node-red-node-mysql the msg.topic should hold the query.

5

You can send multiple messages from a function node, so something like

msg1 = {topic:  "INSERT INTO `ORDERS` (`OrderID`, `CreatedAt`, `FinishedAt`, `Status`) VALUES (NULL, current_timestamp(), '0000-00-00 00:00:00.000000', '1')"}
msg2 = {topic: "INSERT INTO `QUANTITIES` (`QuantityID`, `ProductType`, `Quantity`, `OrderID`) VALUES (NULL, '1', "+ msg.quantity +",(SELECT OrderID FROM ORDERS ORDER BY OrderID DESC LIMIT 1))"}
msg3 = {topic: "...."};
return( [[msg1, msg2, msg3]])

See https://nodered.org/docs/user-guide/writing-functions

1 Like
6

:slight_smile: Yup!

All the stored procedure does is to create a compiled function internal to the db engine that lets you slot data in more efficiently. If you are sending lots of updates of the same structure, procedures are a good bet. But you only create the procedure once - the overheads for creating it are relatively high.

Deleting and re-creating it does nothing for you.

1 Like
7

Thanks alot @TotallyInformation and @Colin - your help made me fix all of the issues!

I changed topic to [SOLVED]

1 Like