tedious

Executing SQL Queries

This feature allows for the execution of SQL queries against a SQL Server database. The code sample demonstrates how to connect to a database and execute a simple SELECT query.

const Connection = require('tedious').Connection;
const Request = require('tedious').Request;

const config = {
  server: 'your_server.database.windows.net',
  authentication: {
    type: 'default',
    options: {
      userName: 'your_username',
      password: 'your_password',
    }
  },
  options: {
    database: 'your_database',
    encrypt: true
  }
};

const connection = new Connection(config);

connection.on('connect', function(err) {
  if (err) {
    console.log('Error:', err);
  } else {
    console.log('Connected!');
    executeStatement();
  }
});

function executeStatement() {
  const request = new Request(
    "SELECT * FROM your_table;",
    function(err, rowCount, rows) {
      console.log(rowCount + ' row(s) returned');
    }
  );

  request.on('row', function(columns) {
    columns.forEach(function(column) {
      console.log('%s	%s', column.metadata.colName, column.value);
    });
  });

  connection.execSql(request);
}

Parameterized Statements

This feature supports the execution of parameterized statements, enhancing security by preventing SQL injection. The code sample shows how to execute a query with a parameter.

const Request = require('tedious').Request;
const TYPES = require('tedious').TYPES;

function executeParameterizedStatement() {
  const request = new Request(
    "SELECT * FROM your_table WHERE your_column = @value;",
    function(err) {
      if (err) {
        console.log('Error:', err);
      }
    }
  );

  request.addParameter('value', TYPES.Int, 123);

  request.on('row', function(columns) {
    columns.forEach(function(column) {
      console.log('%s	%s', column.metadata.colName, column.value);
    });
  });

  connection.execSql(request);
}

Executing Stored Procedures

This feature allows for the execution of stored procedures within the database. The code sample demonstrates calling a stored procedure and retrieving an output parameter.

const Request = require('tedious').Request;

function executeStoredProcedure() {
  const request = new Request(
    'your_stored_procedure',
    function(err) {
      if (err) {
        console.log('Error:', err);
      }
    }
  );

  request.addOutputParameter('output_parameter', TYPES.VarChar);

  request.on('returnValue', function(parameterName, value, metadata) {
    console.log(parameterName + ' : ' + value);
  });

  connection.callProcedure(request);
}