Returning asyc function (mysql select query)

General
1

Hello and TIA for anyone that could lead me to a solution..

I'm trying to make a SELECT query to a mysql database using mysql2 but the result when calling the function is always a promisse.
I do manage to get data from the database but only inside the function...

Any help is appreciated. Thanks!

I've set node-red settinhs.js as:


    functionGlobalContext: {
        os:require('os'),
        fs:require('fs'),
        mysql2:require('mysql2'),
        // jfive:require("johnny-five"),
        // j5board:require("johnny-five").Board({repl:false})
    },

    // Allow the Function node to load additional npm modules
    functionExternalModules: true,

I do have the console showing data:
mysql async

Flow:

[{"id":"e31e66e2.5af808","type":"function","z":"c4f601fe.4aba7","name":"getDevices","func":"\ncontext.global.getDevices = async function getData(){\n \n //Function checkin\n node.warn(\"DEBUG 1\")\n \n const mysql = global.get(\"mysql2\")\n \n //create the pool\n const connection = await mysql.createConnection({host:'--YOURSERVER--', user: '--YOURUSERNAME--', password:'--YOURPASS--', database: 'mika'});\n const [rows, fields] = await connection.promise().execute('SELECT 1');\n \n\n node.warn(\"DEBUG 2\" + JSON.stringify(rows))\n //Data is printed in console log.\n \n return rows\n \n}\n\n//Calls function and display the results\nnode.warn(\"RESULT is \" + context.global.getDevices());\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"mysql","module":"mysql2"}],"x":1030,"y":240,"wires":[[]]},{"id":"77d1c752.3dabd8","type":"inject","z":"c4f601fe.4aba7","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"mysql","payload":"","payloadType":"date","x":1040,"y":160,"wires":[["e31e66e2.5af808"]]}]
2

Hi @Ricardo-Miguel-Calde out of curiosity why are you coding mySQL in a function instead of using the mySQL node-red node?

As for your question, you should call .then() and .catch() on the promise. Inside the then handler, you would call node.send() to pass the data out of the function node.

3

Reusing mostly. I need to get data along the flow. This function will simplify the entire project.

I did try that also and I get the same result.

    //create the pool
    const connection = mysql.createConnection({host:'--HOST--', user: '--YOURUSER--', password:'--YOURPASSWORD--', database: '--DBNAME--'});
    const [rows, fields] = await connection.promise().execute('SELECT 1').then((rows)=>{
        return rows
    });

I do think it's related to the call itself.

To use this library I should require("mysql2/promise") but when I do the "translated" statement for nodered as global.get("mysql2/promise") the result is the same except in the console log I can't see "Debug 2" printed.

Nothing is printed in the node-red log also...

https://www.npmjs.com/package/mysql2#using-promise-wrapper

4

You would probably already be up and running already if you install/use node-red-node-mysql. It handles connections, pools, reconnections, safely encrypts connection details, handles errors by directing them to the appropriate logs, etc etc etc etc.

You do realise a function node is fully executed every time it is called (meaning you will be continuously connecting and destroying connections unless you use context memory or some other mechanism to maintain connections?

As the code is asynchronous, you cannot return. You must call node.send as I said earlier. E.g...

node.send({payload: rows});
5

I see...

I was really looking for a solution to return data from the function... Can't see how node.send will help me (unless I set a global and wait for the data change)...

Thanks anyway! Cheers!

6

Node send sends it out of the function. I.e. allows you to pass to the next node (or wherever you wish for the data to go)

If you want to process the data inside the function, do it inside the then handler.

I'm not quite understanding your issue (note I haven't loaded your flow so I am working from what you write)

7

Just need this function to return the data so it can be used inside other functions... The objective is not to pass it to other nodes at all..

8

Ah. Well. That it pretty much the opposite of what node-red is about tbh.

What is your aversion to passing the data? I'm guessing some design direction?

If you need to, you can store the data in flow or global context for later use - which you can access in another function using flow.get or global.get.

10

Solution for my own case is:

https://flows.nodered.org/flow/1d626aac6273d16d5eefd74aafdaee59

11

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.