Return value from inner function of a function node.js / MySQL

Question

I'm working on a very simple web app that doesn't do much, however it does connect to a database for INSERT and SELECT operations on one table. I have a function that I utilized while browsing through several great tutorials, however I'm having trouble returning the rows from the SELECT query. Keeping in mind I'm learning Node.JS -- how would I display the data returned from the query (SELECT) to this block?

app.post("/getcsv", function(req,res){

var sqlselall = "SELECT * FROM office";
var rows = handle_database(sqlselall);
res.json(rows);
res.end();

The function for handling the database connections (using pooling):

function handle_database(sqlstmt){

pool.getConnection(function(err,connection){

    if(err) {
        res.json({"code" : 100, "status" : "Error in connection to database."});
        return;
    }

    console.log('connected as id ' + connection.threadId);

    connection.query(sqlstmt, function(err,rows){
        connection.release();
        if(!err){
            console.log("Number of rows affected: " + rows.affectedRows);
        }

    });

    connection.on('error', function(err) {
        res.json({"code": 100, "status" : "Error in connection to database."});
        return;
    });

I realize that the rows in the inner function contains the data I need, however I'm at a loss as to how to return it when I call the function.

Possible duplicate of [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) — Paul, Jun 09 '17 at 03:19
@Paul - unfortunately I'm having trouble fully grasping the concept of callbacks in JS. — Smitty, Jun 09 '17 at 03:25
it's just something you'll have to run through some tutorials on. Callbacks are just functions, but the meat you want (e.g. the rows) are only scoped *inside* that function. So you need to make sure everything you need to work with is available in the same scope. — Paul, Jun 09 '17 at 03:29
@Paul ok - that's what I'm working on now - reading up on callbacks. I figured scope had a lot to do with it. — Smitty, Jun 09 '17 at 03:30
Yeah, it's not hard it's just a cognitive leap you can only make after banging your head against it once or twice. :) *After* you grok it, have a look at asyncjs library for easier flow control and Promises (especially if you're willing to use the latest release of Node which brings async/await to the table) — Paul, Jun 09 '17 at 03:31

score 2 · Answer 1 · answered Jun 09 '17 at 17:39

So if I could have commented on you answer I would have. This is something that I would like to supplement to your answer, because it looks like your answer should work to me, although I have not tested it personally.

From experience of trying to learn the callback style I think this might help you. It does help keep the code a little more modular.

app.post("/getcsv", function(req, res) {
   var sqlselall = "SELECT * FROM office"
   select_query(sqlselall, function(results){
      res.send(results)
   })
})

function select_query(sqlstmt, callback) {
  pool.query(sqlstmt, function(err, results, fields) {
  //I suppose if this is how you want to handle errors
  //for debugging purposes I like returning them as well
  //returning it helps both you and others who might be working on
  //the front end to know whats happening
  if (err) throw err
  callback(JSON.stringify(results))
  })
}

This way your select_query function doesn't require the res to get passed in, and doesn't rely on a parameter that has a function in order to work. Some times that cant be helped, but when it can I find its easier for maintenance to take that into account.

In the hypothetical situation that you might have another end point that needs to query as well, but needs to append modify the information before you send it, you would still be able to use your select_query function, and just modify your callback that you pass into it. So you would end up with something like this: (I also changed the error handling a little)

app.post("/getcsv", function(req, res) {
   var sqlselall = "SELECT * FROM office"
   select_query(sqlselall, function(err, results){
      if(err){
         res.send(err)
         //throw an error if you would like to here 
      }
      res.send(results)
   })
})
app.post("/modifyCSV", function(req, res){
  var sql = "{sql statement}"
   select_query(sql, function(err, results){
     if(err){
         res.send("Aww an error: "+err)
         //throw an error if you would like to here 
     }res.send(reuslts + "\nHello to you too")
   })
})

function select_query(sqlstmt, callback) {
  pool.query(sqlstmt, function(err, results, fields) {
  if (err) 
     callback(JSON.stringify(err), null)
  callback(null, JSON.stringify(results))
  })
}

Like I said, I am not saying your way is wrong, it works, and perhaps it will work better for you. I have just found that this helped me get a handle on callbacks, and actually start to enjoy using them.

Thanks for that! Your first code example actually helped bring back the concept of a callback home a little bit more. So you can pass an anonymous function as a parameter that corresponds to the callback function within the named function you are calling. From a logic perspective, not an easy concept to grasp quickly - especially coming from C#. — Smitty, Jun 09 '17 at 17:45
Right, its kind of like a different paradigm, but I really enjoy it. I'm glad that it helped out. — Jeff, Jun 09 '17 at 17:57

score 0 · Answer 2 · answered Jun 09 '17 at 17:00

Answering my own question, especially considering some may consider it a duplicate (which due to my lack of knowledge in regards to Node.JS and JS in general is likely), seems inappropriate, however I discovered that once I did some research (thanks @Paul - and callback hell) and gained clarification on some fundamentals regarding functions, callbacks, anonymous functions, and the nature of a function's scope in Javascript I was able to come up with a solution to my problem. So for my connection to the DB I created a new simplified function which is passed the parameter 'res' from the callback parameter (which I now somewhat understand) from app.post:

app.post("/getcsv", function(req, res) {

  var sqlselall = "SELECT * FROM office"
  var thisData = select_query(sqlselall, res)
})

function select_query(sqlstmt, res) {

  pool.query(sqlstmt, function(err, results, fields) {
    if (err) throw err
    res.send(JSON.stringify(results))
  })
}

Return value from inner function of a function node.js / MySQL

2 Answers2