Package Exports

node-sql

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (node-sql) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

node-sql

A simple node-style callback wrapper for the wonderful Tedious driver.

Just call the exec function with a query, or sproc with a stored procedure, and get back an Array of JSON objects. Query in, JSON out.

exec(query, config, callback) Execute arbitrary SQL

query: String - standard SQL query e.g. 'Select * From tbl'.

config: Object - standard tedious config object.

callback: Function - standard node callback, returns (err, result). Where err = Error, and result = the query results.

var nodeSQL = require('node-sql')
//standard tedious config object : http://tediousjs.github.io/tedious/api-connection.html#function_newConnection
var config = {
  userName: process.env.USERNAME,
  password: process.env.PASSWORD,
  server: 'MyServer',
  domain: 'DOMAIN'
}
//result has data as an Array of JSON objects with column name => column value
app.get('/', function (req, res) {
  nodeSQL.exec(`Select FirstName, LastName From tbl Where FirstName='Moshe'`, config, function(err, result){
    if(err) return res.sendStatus(500);
    res.status(200).json(result);// [{FirstName: 'Moshe', LastName: 'Karmel'}]
  });
})

sproc(name, params, config, callback) Call a stored procedure

name: String - stored procedure name e.g. MyDB.dbo.GetDataById.

params: Object - Key Value pairs of parameter name to parameter value, e.g. { ID : 4 } (the type is inferred).

config: Object - standard tedious config object.

callback: Function - standard node callback, returns (err, result). Where err = Error, and result = the query results.

var nodeSQL = require('node-sql')
//standard tedious config object : http://tediousjs.github.io/tedious/api-connection.html#function_newConnection
var config = {
  userName: process.env.USERNAME,
  password: process.env.PASSWORD,
  server: 'MyServer',
  domain: 'DOMAIN'
}
//result has data as an Array of JSON objects with column name => column value
app.get('/:Id', function (req, res) {
  var params = { ID: req.params.Id };
  nodeSQL.sproc(`MyDB.dbo.GetDataById`, params, config, function(err, result){
    if(err) return res.sendStatus(500);
    res.status(200).json(result);// [{FirstName: 'Moshe', LastName: 'Karmel'}]
  });
})

tvp(name, params, config, callback) Call a table-value parameter sproc

name: String - stored procedure name e.g. MyDB.dbo.GetDataById.

paramName: String - Name of the Table-Value Parameter, e.g. Todos.

table: Array of Objects - Follow the pattern here tedious tvp object.

config: Object - standard tedious config object.

callback: Function - standard node callback, returns (err, result). Where err = Error, and result = the query results.

var nodeSQL = require('node-sql')
//standard tedious config object : http://tediousjs.github.io/tedious/api-connection.html#function_newConnection
var config = {
  userName: process.env.USERNAME,
  password: process.env.PASSWORD,
  server: 'MyServer',
  domain: 'DOMAIN'
}
//result has data as an Array of JSON objects with column name => column value
app.post('/:Id', function (req, res) {
  var body = req.body;
  var rows = [];
  // use the index to match the metadata in the table variable
  rows.push(body.Id, body.Text, etc.. );

  var table = {
    columns: [
      {name: 'Id', type: nodeSQL.TYPES.BigInt},
      {name: 'Text', type: nodeSQL.TYPES.VarChar, length: 13},
      ...
    ],
    rows: rows
  };

  nodeSQL.sproc(`MyDB.dbo.SaveTVPRows`, 'Stuff', table, config, function(err, result){
    if(err) return res.sendStatus(500);
    res.status(200).json(result);// [{Id: 1, Text: 'Hello'}, {Id: 2, Text: 'World'}]
  });
})

getColumnNames(query, config, callback) Get SQL column names

query: String - standard SQL query e.g. 'Select * From tbl'.

config: Object - standard tedious config object.

callback: Function - standard node callback, returns (err, result). Where err = Error, and result = the query results.

var nodeSQL = require('node-sql')
//standard tedious config object : http://tediousjs.github.io/tedious/api-connection.html#function_newConnection
var config = {
  userName: process.env.USERNAME,
  password: process.env.PASSWORD,
  server: 'MyServer',
  domain: 'DOMAIN'
}
//result has data as an Array of column names
app.get('/', function (req, res) {
  nodeSQL.exec(`Select TOP 1 * From tbl Where FirstName='Moshe'`, config, function(err, result){
    if(err) return res.sendStatus(500);
    res.status(200).json(result);// ['FirstName', 'LastName', etc...]
  });
})

Installation

$ npm install node-sql --save

License

MIT