How to return mysql data from custom js funtion in node.js

Question

here is my function in egallery.js

var conn = require('./conn.js');

function getAllUrls () {
    console.log('sdfsadfsaf');
    var result;
    conn.connect();

    conn.query('Select * from ray_url', function(err, rows, fields) {

        if (err) throw err;
        result = rows;
        //console.log(err);
        //console.log(rows);
        return rows;
    });

    conn.end();


}

module.exports = {
    getAllUrls
};

in app.js, the console output is undefined.

let app = require('express')();
var egallery = require('./models/egallery.js');
rows = egallery.getAllUrls();
console.log(rows); //undefined
app.set('view engine', 'ejs');
app.get('/e', function (req, res) {
    //res.send("Hello, welcome to our app")
    console.log(rows);
    res.render('pages/index', {rows : rows});
})

// about page
app.get('/e/about', function(req, res) {
    res.render('pages/about');
});
app.listen(3000,'0.0.0.0')

-------------------update changed to use async & promise---------------
egallery.js

var conn = require('./conn.js');
conn.connect();

function getAllUrls () {

    return new Promise((resolve, reject) => {
        conn.query(
            "Select * from ray_url",
            (err, result) => {
                return err ? reject(err) : resolve(result)
            }
        )
    })
}
conn.end();
module.exports = {
    getAllUrls
};

app.js

let app = require('express')();
var egallery = require('./models/egallery.js');

app.set('view engine', 'ejs');
app.get('/e', async  (req, res) => {

    rows = await egallery.getAllUrls();
    console.log(rows);

    res.render('pages/index', {rows : rows});
})

// about page
app.get('/e/about', function(req, res) {
    res.render('pages/about');
});
app.listen(3000,'0.0.0.0')

------------error------------

    const err = new Error(
                ^

Error: Can't add new command when connection is in closed state
    at Connection._addCommandClosedState (/home/opc/docker/src/html/e/node_modules/mysql2/lib/connection.js:148:17)
    at Connection.query (/home/opc/docker/src/html/e/node_modules/mysql2/lib/connection.js:546:17)
    at /home/opc/docker/src/html/e/models/egallery.js:7:14
    at new Promise (<anonymous>)
    at Object.getAllUrls (/home/opc/docker/src/html/e/models/egallery.js:6:12)
    at /home/opc/docker/src/html/e/app.js:7:27
    at Layer.handle [as handle_request] (/home/opc/docker/src/html/e/node_modules/express/lib/router/layer.js:95:5)
    at next (/home/opc/docker/src/html/e/node_modules/express/lib/router/route.js:144:13)
    at Route.dispatch (/home/opc/docker/src/html/e/node_modules/express/lib/router/route.js:114:3)
    at Layer.handle [as handle_request] (/home/opc/docker/src/html/e/node_modules/express/lib/router/layer.js:95:5) {
  fatal: true
}

Node.js v18.9.1

i guess you are new to javascript based on the question, try learning about asynchronous code in javascript, and Promises it will help you a lot. — Amit Wagner, Dec 07 '22 at 13:30
Does this answer your question? [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — Dave Newton, Dec 07 '22 at 13:31
@AmitWagner i removed conn.end(); work fine now, but how to handle close connection in my case? — hkguile, Dec 07 '22 at 14:04
Usually, you keep the connection in nodejs apps alive and reuse it — Amit Wagner, Dec 07 '22 at 16:18

How to return mysql data from custom js funtion in node.js

0 Answers0