How do I return SQL results in the scope of the parent function in NodeJS?

Question

I'm currently working on a brackets text-editor extension that keeps your work time in a database and lets you view your time entries. I'm using NodeJS to interact with the SQL server. I've been able to enter time as I want and I've successfully called the entries with a select statement. However, I am trying to display the results on a panel at the bottom of brackets.

I can't figure out how to get the result from my select statement to the wider scope so I can pass it to main.js. I'm new to Node and have read a lot about callbacks and promises but nothing seems to help. I tried using a callback but I can't get the value from the callback to equal a variable that can be returned.

UPDATE: The viewTime() function is returned to main.js as a promise. If I could name con.connect() as a regular function then I could call it in main.js through registerCommand (command handler function).

Here's the code:`

(function () {
"use strict";
var mysql = require('mysql');
/**
* @private
*
* @return {array}
*/

function viewTime() {
    var con = mysql.createConnection({
        host: "localhost",
        user: "root",
        password: "root",
        database: "brackets_timer_data"
    });

    con.connect(function(err) {
      if (err) {
          console.log(err);
      } else {
        console.log("Connected!");
      }
        con.query("SELECT * FROM work_sessions", function (err, result, fields) {
            if (err) {
                console.log(err);
            }
            //I need to get the result from here
         });
        });
      //And return it here so I can pass the variable to main.js
}

 /**
 * Initializes the test domain with several test commands.
 * @param {DomainManager} domainManager The DomainManager for the server
*/
    function init(domainManager) {
        if (!domainManager.hasDomain("view")) {
            domainManager.registerDomain("view", {major: 0, minor: 1});
        }
        domainManager.registerCommand(
            "view",       // domain name
            "viewTime",    // command name
            viewTime,   // command handler function
            false,          // this command is synchronous in Node
            "Returns time entry results.",
            [{}],
            [{name: "results", // return values
                type: "array",
              description: "Time entry results"}]
        );
    }
exports.init = init;

}());

`Any thoughts?

You can wrap `con.query` in Promise. Like `return new Promise((resolve,reject)=>{ con.query() });` — Sridhar, May 23 '18 at 06:09
I've tried that. I'm able to get the results now using .then() but it only works inside of .then(). When I set it to a variable and try to log the result, it comes back as undefined. — Isaac Dew, May 23 '18 at 17:23

Sridhar · Answer 1 · 2018-05-24T14:25:23.600

You can use Promise and async/await to return the session details. First, you need to chain Promise and then return the session details. The calling function in main.js should be async to read the return values from the promised function.

Making those changes to your code,

"use strict";

let mysql = require('mysql');

function viewTime() {
  let con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "root",
    database: "brackets_timer_data"
  });

  return new Promise((resolve, reject) => {
    con.connect(function (err) {
      if (err) {
        console.log('err while establishing connection ', err);
        reject(err);
      }
      console.log('Connected!');
      resolve();
    });
  }).then(() => {
    con.query("SELECT * FROM work_sessions", function (err, result, fields) {
      if (err) {
        console.log('err in while fetching session details ', err);
        return Promise.reject(err);
      }
      return Promise.resolve(result);
    });
  });
}

function init(domainManager) {

  // your other code

  viewTime().then(sessions => {
    domainManager.registerCommand(
      "view",       // domain name
      "viewTime",    // command name
      sessions,   // command handler function
      false,          // this command is synchronous in Node
      "Returns time entry results.",
      [{}],
      [{
        name: "results", // return values
        type: "array",
        description: "Time entry results"
      }]
    );
  }).catch(err => {
    console.log('err in init()', err);
  });

}

exports.init = init;

I am getting an unexpected token function error. As far as calling the function in main.js with an async function, main.js isn't interpreted by node. Is this the problem? — Isaac Dew, May 24 '18 at 12:49
@IsaacDew Could be. I've updated the code to use only promises — Sridhar, May 24 '18 at 14:26
With this code I'm getting an "Cannot read property 'apply' of undefined". — Isaac Dew, May 24 '18 at 15:54
May be some other code causes it? Could you replace ()=>{} with function(){} and check — Sridhar, May 25 '18 at 04:11

How do I return SQL results in the scope of the parent function in NodeJS?

1 Answers1