Multi-line SQL query in a NodeRed function

[JR01]

Hi all, I have a multi-line SQL query that I want to host in a function, to pass into a MySQL DB node, using msg.topic, how does one do a multi-line query in NodeRed Javascript?

[Ben Hardill]

Line breaks are just another white space char in SQL so there isn't really such a thing as a multi line sql querey.

You can assemble the msg.topic variable over multiple lines to make it easier to read if that helps.

var query = "select * from foo " +
                  "where x equals 'bar'";

msg.topic = query;

Just remember to include the extra space at the end of the lines

[Dave C-J]

To clarify, do you mean a single query that is spread over multiple lines, as per Ben's answer, or multiple SQL statements that you want to pass in as one blob ?

[JR01]

Thank you Ben and Dave for your responses. Dave, something like this - a multi SQL statement:

--------------------

CREATE TEMPORARY TABLE IF NOT exists RHZA.temp1 ENGINE=MEMORY AS

(SELECT idSystem, idTask, DATE_FORMAT(tmstamp,'%Y-%m-%d %H:00') AS tmstamp, sum(val) AS val 

FROM RHZA.SysTaskVal2day WHERE idTask = 8 GROUP BY DATE_FORMAT(tmstamp,'%Y-%m-%d %H:00')

    );

UPDATE RHZA.temp1 SET idTask = 4 WHERE idTask = 8 AND idSystem = 3;

INSERT INTO RHZA.SysTaskVal (SELECT idSystem, idTask, tmstamp, val FROM RHZA.temp1);

DROP Table RHZA.temp1;

DELETE FROM RHZA.SysTaskVal2day WHERE idTask = 8; 

INSERT INTO RHZA.SysTaskVal (SELECT idSystem, idTask, tmstamp, val FROM RHZA.SysTaskVal2day);

TRUNCATE RHZA.SysTaskVal2day;

--------------------

[Dave C-J]

So given lots of ; in there - yes multiple statements in one big request.

Currently our node can't support that - as we don't set the correct flag on the connection - (eg see this thread on SO - http://stackoverflow.com/questions/23266854/node-mysql-multiple-statement-in-one )

I have no problem adding it - just need to get to a laptop.

[Dave C-J]

pushed as version 0.0.9

[JR01]

Wow, thank you guys. I have upgraded to NodeRed 14.4 - but I am not sure how to ups the nodes - like this one for MySQL ?

just a sudo apt-get update ? Does not sound if that will work? 

On Wednesday, 20 July 2016 21:13:39 UTC+2, Dave C-J wrote:

pushed as version 0.0.9

[Dave C-J]

any extra nodes need to be updated individually

  cd ~/.node-red

  npm update node-red-node-mysql

or

  npm i node-red-node-mysql

(to just re-install it - shouldn't matter either way)

while in  ~/.node-red  you can also run

  npm outdated   to see any others that are back-level if you wish... 

[JR01]

Wow, than you very much guys, you are awesome !

I am running a blog on what I play with, part of choosing NodeRed are you guys helping out the community so much

See a post on my blog.

http://iotplay.blogspot.co.za/2016/05/the-components-i-use-for-my-projects.html


Thank you again ! And for 14.4, its awesome !