joola.io.engine/lib/connectors/mssql.js

/**
 *  joola.io
 *
 *  Copyright Joola Smart Solutions, Ltd. <info@joo.la>
 *
 *  Licensed under GNU General Public License 3.0 or later.
 *  Some rights reserved. See LICENSE, AUTHORS.
 *
 *  @license GPL-3.0+ <http://spdx.org/licenses/GPL-3.0+>
 */

exports.open = function (query) {
  var config = {
    driver: 'SQL Server Native Client 11.0',
    userName: query.datasource.user,
    password: query.datasource.password,
    server: query.datasource.host,

    options: {
      database: query.datasource.dbname,
      encrypt: true,
      rowCollectionOnRequestCompletion: true
      /*debug: {
       packet: true,
       data: true,
       payload: true,
       token: true,
       log: true
       }*/
    }
  };

  var Connection = require('tedious').Connection;
  return new Connection(config);
};

exports.close = function (connection) {
  //connection.end();
};

exports.executeQuery = function (query, callback) {
  if (query.limit)
    query.sql = query.sql.replace('SELECT', 'SELECT TOP ' + query.limit + ' ');


  query.sql = query.sql.replace(/`/gi, '"');

  var connection = this.open(query);

  connection.on('errorMessage', function (err) {
    throw err;
  });

  connection.on('connect', function (err) {
    if (err)
      throw err;

    var Request = require('tedious').Request;
    var request = new Request(query.sql, function (err, rowCount, rows) {
      if (err)
        throw err;

      connection.close();

      var _fields = [];
      var _rows = [];

      if (rowCount > 0) {
        //console.log(rows[0]);
        _.each(rows[0], function (columns) {
          var meta = columns.metadata;
          var field = {};
          _.each(meta, function (value, key) {
            field[key] = value;
          });
          field.name = field.colName;
          _fields.push(field);
        })
      }

      _.each(rows, function (row) {
        var _row = {};
        _.each(row, function (columns) {
          var value = columns.value;
          var name = columns.metadata.colName;

          _row[name] = value;

        });
        _rows.push(_row);
      });

      var result = {
        fields: _fields,
        rows: _rows
      };

      return callback(err, result);
      //console.log(result);
    });

    connection.execSql(request);
  });
};