This post demonstrates:
 – how to write a callback function so that results from your database are returned by the time you are ready to render;
 – how to use multi-field LIKE syntax in a SELECT statement.

The most important thing to understand in Node.js is a callback mechanism and its purpose.  It is all fine and dandy, when you just type something for the training session, or copying code from an Internet example, but things are different, when you need to write you own function that must rely on a callback function.  Here I will show no-blocking code that would process POST request, call for MySQL to execute a SELECT query and will not render until result is returned from the database.

The story goes like this.  First, I’ve spent several hours trying to understand, why my function that calls MySQL query doesn’t return anything. Then I saw that it does work, but it returns results too late, when render request was already sent. And finally, I realized that I do need to understand the callback mechanism,  and I forced myself to find a simple explanation.  That took another couple of hours. And then, three of four attempts later it finally started to work.  Here is my environment and the code.

My configuration is very minimalistic: latest node.js on Win 8, https, express, mysql and jade. 

My startup file index.js off the root.

Initially user is presented with a simple search box and a search button====>  

The task is to match a search string against multiple text fields in the table and return all matching records in a structured format.

The code for MySQL functions is located in a separate file tucked away in a DB folder, so I need to load that in the beginning of index.js: 

var myMySql = require(‘./DB/myMySql’);

Some lines later I am ready to process a POST request using express app.post:

app.post('/searchMaster', function(req, res){
    // 01 Get the search text from the form
    var searchText = req.body.txtSearch;
    // 02 Call external function to get data from D/B and 
    // render results only when results are ready
    var rows = []; 
    myMySql.searchMaster(searchText, res, function(searchText, res, rows) {
        var count = rows.length;
        // 21:20 2016-02-11 My callback function is working!
        res.render('searchMaster', {pageTitle: 'After POST',
                                    title:     'Search Master',
                                    txtSearch: searchText,
                                    rows:      rows,
                                    count:     count,
                                    post:      1
                                    }
                   )
    } )

The thing to notice here is that  res.render located not after searchMaster, but inside searchMaster as a callback function.

Here is function searchMaster that is located in ./DB/myMySql.js:

var mysql = require('mysql');

// All functions inside this block are exposed to outside world
var myMySql = {
    searchMaster: function (sTx, res, callWhenDone) {
        // Output search results
        var db = getDb()
        var masterRows = new Array;
        var txtQuery = "SELECT * FROM tMaster WHERE    msLastName LIKE ? OR \
                        msFirstName LIKE ? OR           msCompany LIKE ? OR \
                       msSearchType LIKE ? OR        msAccountNum LIKE ? OR \
                         msKeyWords LIKE ? OR      msMasterRemark LIKE ?";
        var temp = Array(7).fill("%" + sTx + "%");
        db.query(txtQuery, temp , function (err, rows) {
            db.end();
            if (err) {
                console.error('searchMaster query: ' + err.stack);
                return;
            } else {
                masterRows = rows;

                if (typeof callWhenDone === "function") {
                    callWhenDone(sTx, res, masterRows);
                }
            }
        });
    }
}

 

There are several interesting things to note in the listing above.

First, is that I needed to populate search text sTx from search form in an array of 7 elements, and I found the latest ESMA way to do that with: 

var temp = Array(7).fill("%" + sTx + "%");

 

Second interesting thing is LIKE syntax.    As you can see, statement contains seven “?” that are substituted with search text wrapped in a pair of %…% that come from temp array:

 db.query(txtQuery, temp . .

 And finally, to the heart of the matter – callback function.  At the top, you need to place your function in the parameter list

searchMaster: function (sTx, res, callWhenDone)

 . . . and then you have to write where exactly you want your callback function to be called.  In this case we would need to call it right after rows are returned from the database: 

       masterRows = rows;
       callWhenDone(sTx, res, masterRows) 

 For those, who just coping the code, here is less interesting continuation of ./DB/myMySql.js file: 

    var db = mysql.createConnection({
        host:     'xxxx',
        user:     'xxxx',
        password: 'xxxx',
        database: 'xxxx'
    });

    db.connect(function (err) {
        if (err) {
            console.error('error connecting: ' + err.stack);
            return;
        }
        console.log('connected as id ' + db.threadId);
    });
    return db;
}

 

Just to give you a complete picture, here are the result of a search in a browser . . .

 

 . . . and a Jade.js template that displays these:

doctype html
html(lang="en")
  head
    title #{pageTitle}
  body
    // TH 17:23 2016-02-11 Almost working, but zero results
    a(href='/') Home
    h1 #{title}
    form(method="POST", action="/searchMaster")
      input(type="hidden", name="_method", value="PUT")
      p   
        input(type="text", name="txtSearch", value=txtSearch, autofocus, onfocus="select(this);")
        span        
        input(type="submit", value="Search")
     if post && count
      h2 Search Results
      table
       tr
         th Last Name
         th First Name
         th Company
         th Search Type
         th Account
         th Key Words
         th Remark
       each row in rows
         tr
           td= row.msLastName
           td= row.msFirstName
           td= row.msCompany
           td= row.msSearchType
           td= row.AccountNum
           td= row.msKeyWords
           td= row.msMasterRemark
     else if post
       h3  No records match search criteria

I have one question for the readers:
Is it normal that I open and close the D/B connection on every inquiry.  Is there a better way?

RE: written 2016-02-12 FR