Skip to content

Instantly share code, notes, and snippets.

@ahallora
Last active August 29, 2015 14:17
Show Gist options
  • Save ahallora/591845a9d16ebb215355 to your computer and use it in GitHub Desktop.
Save ahallora/591845a9d16ebb215355 to your computer and use it in GitHub Desktop.
PDO with MS-SQL (Azure) and Node.js
// -------------------------------------------------------------------------
// USING MSSQL NPM AND PREPARED STATEMENTS IN NODE.JS
// ENABLING ? AS PLACEHOLDERS IN SQL AND AUTOMATIC TYPECASTING OF PARAMETERS
// THIS IS WORKING ON AZURE WITH NODE.JS AND MSSQL NODE MODULE - YAII :)
// -------------------------------------------------------------------------
// MIT LICENSE 2015 @ Anders Holm-Jensen
// -------------------------------------------------------------------------
var sql = require('mssql');
var config = {
user: 'INSERT-USERNAME',
password: 'INSERT-PASSWORD',
server: 'INSERT-SERVER',
port: 1433,
database: 'INSERT-DATABASE-NAME',
stream: false,
options: {
useUTC: true,
encrypt: true
}
}
function defineSQLType(input) {
returnVal = null;
switch(typeof(input)) {
case 'string': returnVal = sql.NVarChar; break;
case 'number': returnVal = sql.Int; break;
case 'boolean': returnVal = sql.Bit; break;
case 'buffer': returnVal = sql.VarBinary; break;
case 'object':
returnVal = sql.NVarChar;
if (input.getMonth) returnVal = sql.DateTime;
break;
default: returnVal = sql.NVarChar; break;
}
return returnVal;
}
function prepareQueryAndParams(sqlQuery, sqlParams) {
// Prepare query and params (based in ? and typeof of params)
var sqlSplit = sqlQuery.split('?');
var valueObject = {};
var paramObject = [];
if (sqlSplit.length - 1 != sqlParams.length) {
returnVal = {
'error': 'Params and query does not match.'
};
} else {
for (sqlLoop = 0; sqlLoop < sqlParams.length; sqlLoop++) {
var value = sqlParams[sqlLoop];
// set param object array
paramArray = ['param' + sqlLoop, defineSQLType(value)];
paramObject.push(paramArray);
// append paramValue array
valueObject['param' + sqlLoop] = value;
// update query with param name
sqlSplit[sqlLoop] = sqlSplit[sqlLoop] + '@param' + sqlLoop;
}
returnVal = {
'error': null,
'query': sqlSplit.join(''),
'params': paramObject,
'values': valueObject
};
}
return returnVal;
}
function executeSQL(query, params, callback) {
if(sql == null || config == null || query == null) {
error = 'Error in executeSQL - sql/config/query is null.';
console.log(error);
callback(error, null);
return false;
}
// perform sql and return data
sql.connect(config, function (err) {
if (err != null) {
console.log('Error with sql.connect:');
console.log(err);
callback(err, null);
return false;
}
var ps = new sql.PreparedStatement();
sqlPrepared = prepareQueryAndParams(query, params);
if (sqlPrepared.error != null) {
console.log('Error with prepared sql:');
console.log(sqlPrepared.error);
callback(sqlPrepared.error, null);
return false;
}
// set any params
if (sqlPrepared.params.length > 0) {
for (sqlLoop = 0; sqlLoop < sqlPrepared.params.length; sqlLoop++) {
ps.input(sqlPrepared.params[sqlLoop][0], sqlPrepared.params[sqlLoop][1]);
}
}
// prepare (SQL) the prepared (DIY-fix) statement
ps.prepare(sqlPrepared.query, function (err) {
if (err != null) {
console.log('Error performing query:');
console.log(sqlPrepared.query);
callback('Error performing query', null);
return false;
}
// execute the SQL (with params)
ps.execute(sqlPrepared.values, function (err, recordset) {
// important to unprepare (release the connection)
ps.unprepare(function (err) {
if (err != null) {
console.log('Error unpreparing statement:');
console.log(err);
}
});
callback(err, recordset);
return false;
});
});
});
}
module.exports.executeSQL = executeSQL;
var http = require('http');
var execSQL = require('./executesql');
var sqlQuery = 'SELECT foo FROM dbo.table WHERE firstname = ? AND age > ? AND lastlogin < ?';
var sqlParams = ['Anders',25, new Date()];
execSQL.executeSQL(sqlQuery, sqlParams, function (error, result) {
if (error != null) {
console.log(error);
} else {
console.log(result);
}
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment