Node JS DataTables And mysql
<p>I im working with NodeJS and datatables with mysql. I can't get json data response from my server and this is error i get from console.log:</p>

<pre><code>/root/project/node_modules/mysql/lib/protocol/Parser.js:78
    throw err; // Rethrow non-MySQL errors
          ^
Error: ER_EMPTY_QUERY: Query was empty
</code></pre>

<p>Here is my server.js:</p>

<pre><code> var express      = require('express'),
 var mysql        = require('mysql');
 var async        = require('async'),
     QueryBuilder = require('datatable');
     var tableDefinition = {
     sTableName: 'users'
 };

var queryBuilder = new QueryBuilder(tableDefinition);

var requestQuery = {
    iDisplayStart: 0,
    iDisplayLength: 5
};
 var connection = mysql.createConnection({          // CONNECT - MySQL
    host : 'localhost',
    user : 'root',
    password : 'mydatabasepassword',
    database : 'mydatabasename',
 });

connection.connect();

/* RETURN - all users */
apiRoutes.get('/users', function(req, res) {
/* QUERY - user */
connection.query(queries.changeDatabaseOrSchema,function(err){
    if (err) throw err;

        async.parallel({
            recordsFiltered: function(cb) {
                connection.query(queries.recordsFiltered, cb);
            },
            recordsTotal: function(cb) {
                connection.query(queries.recordsTotal, cb);
            },
            select: function(cb) {
                connection.query(queries.select, cb);
            }
        }, function(err, results) {
            if (err) { 
                res.error(err);
            } else {
                res.json(queryBuilder.parseResponse(results));
            }
        });
    });
});
</code></pre>

<p>And users.html file:</p>

<pre><code>&lt;script&gt;
    $(document).ready(function() {
        var oTable = $('#users').dataTable({
            'aoColumnDefs': [
                { 'sTitle': 'ID', 'aTargets': [ 0 ], 'sWidth': '1%' },
                { 'sTitle': 'Description', 'aTargets': [ 1 ], 'sWidth': '3%' }
            ],
            'bAutoWidth': false,
            'bServerSide': true,
            'sPaginationType': 'full_numbers',
            'aaSorting': [[ 0, 'desc' ]],
            'sAjaxDataProp': 'data',
            'sAjaxSource': '/api/users?id=1',
            'oLanguage': {
                'sEmptyTable': 'No users available',
                'sLengthMenu': '_MENU_ users',
                'sInfo': 'Showing _START_ to _END_ of _TOTAL_ users',
                'sInfoFiltered': '(filtered from _MAX_ total users)',
                'sInfoEmpty': 'Showing 0 to 0 of 0 users',
                'sZeroRecords': 'No matching users found',
                'sSearch': ''
            }
        });
        /* DATATABLES - placeholder */
        $('div.dataTables_filter input').attr('placeholder', 'Search').addClass('form-control');
    });
&lt;/script&gt;
</code></pre>

<p>I im using:
       "express": "~4.14.0",
       "mysql": "2.12.0",
       "async": "~2.1.4",
       "underscore": "~1.8.3"</p>

<p>UPDATE:</p>

<p>I try with this code on server.js:</p>

<pre><code>/* RETURN - all users */
apiRoutes.get('/users', function(req, res) {
/* QUERY - user */
pool.getConnection(function(err,connection){
    if (err) {
       connection.release();
       res.json({"code" : 100, "status" : "Error in connection database"});
       return;
     }
        console.log(queries);
    connection.query(queries.changeDatabaseOrSchema,function(err, row){
        connection.release();
        res.json(queryBuilder.parseResponse(row));

    });
});
});
</code></pre>

<p>And in console.log in server.js i got this:</p>

<pre><code>{ recordsTotal: 'SELECT COUNT(id) FROM clients',
  select: 'SELECT * FROM dealers' }
</code></pre>

<p>And in Chrome JSON return from server.js i got this:</p>

<pre><code>{"recordsFiltered":0,"recordsTotal":0,"draw":0}
</code></pre>

<p>Anyone have idea how to get valid JSON from server.js so that datatables on chrome works?</p>

<p>Thanks.</p>