title: Node JS DataTables And mysql
body: <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>