var request = new tedious.Request(sql.GetBookDetails, function (err, rc, rows) {
if (err) {
console.log(err);
res.status(500).send("database error");
connection.close();
return;
}
if (!rc) {
res.status(404).send("book not found");
connection.close();
return;
}
var book = {
id: rows[0].ID.value,
title: rows[0].Title.value,
author: rows[0].Author.value,
authorId: rows[0].AuthorID.value,
nominations: []
};
book.nominations = rows.slice(1).map(function (row) {
return {
awardId: row.AwardID.value,
yearNominated: row.YearNominated.value,
isWinner: !!row.IsWinner.value
};
});
res.json(book);
connection.close();
});
router.post('/', function (req, res) {
var managers = new Managers.Managers();
var userId = req.body.userId;
var apiKey = req.body.apiKey;
var username = req.body.username;
var password = req.body.password;
var depot = req.body.depot;
var createUsers = req.body.createUsers;
console.log('LOGIN PARAMS: %s, %s, %s, %s', username, password, depot);
if (username == "")
res.status(500).send();
if (password == "")
res.status(500).send();
if (depot == "")
res.status(500).send();
var connection = new Connection(managers.dbManager.generateTediousConfig());
connection.on('connect', function (err) {
// If no error, then good to proceed.
if (err) {
console.log('ERROR: %s', err);
res.status(500).send("Error");
}
else {
console.log("Connected");
executeStatement(res);
}
});
function executeStatement(res) {
managers.dbManager.allowRequest(userId, apiKey, function (authorised) {
if (!authorised) {
res.status(500).send();
}
else {
bcrypt.hash(password, 8, function (err, hash) {
if (!err) {
var request = new Request('[dbo].[CreateNewUser]', function (err, rowCount) {
console.log('RC: ' + rowCount);
if (err) {
console.log(err);
res.status(500).send();
}
else {
connection.close();
res.status(200).send();
}
});
request.addParameter('UserId', TYPES.NVarChar, username);
request.addParameter('Password', TYPES.NVarChar, hash);
request.addParameter('Depot', TYPES.NVarChar, depot);
request.addParameter('CreatedOn', TYPES.DateTime, new Date());
request.addParameter('ApiKey', TYPES.NVarChar, Guid.raw());
request.addParameter('ServerKey', TYPES.NVarChar, Guid.raw());
request.addParameter('CreateUsers', TYPES.Bit, createUsers);
connection.callProcedure(request);
}
});
}
});
}
});
router.get('/', function(req, res, next) {
var Connection = require("tedious").Connection;
var config = {
userName:"******",
password:"******",
server:"10.4.30.40",
options:{encrypt:false,database:"st_rvc7",}
}
var cnn = new Connection(config);
cnn.on("connect",function(err){
if (err){
console.log(err);
return;
}
var Request = require("tedious").Request;
var request = new Request("select * from dbo.service",function(err,rowCount){
});
request.on('row',function(columns){
columns.forEach(function(column){
console.log(column.value);
});
//res.json(columns);
});
cnn.execSql(request);
})
res.render('index', { title: 'Express' });
});
AzureSqlClient.prototype.retrieve = function (partitionKey, rowKey, callback) {
var _this = this;
var client = new tedious_1.Connection(this.options);
client.on('connect', function (error) {
if (error) {
callback(AzureSqlClient.getError(error), null, null);
}
else {
var request = new tedious_1.Request("SELECT TOP 1 * FROM " + _this.options.options.table + " WHERE id=@id", function (err, rowCount, rows) {
if (err) {
client.close();
callback(AzureSqlClient.getError(err), null, null);
}
else if (!rowCount) {
client.close();
callback(null, null, null);
}
else {
client.close();
var row = rows[0];
callback(null, row, rows[0]);
}
});
var id = partitionKey + ',' + rowKey;
AzureSqlClient.addParameters(request, id);
client.execSql(request);
}
});
};
it('Name Collision', function () {
assert(!Connection.prototype.release);
var con = new Connection({});
assert(!con.pool);
con.close();
});
var server = http.createServer(function(request, response) {
//client.trackRequest(req, res);
response.writeHead(200, {"Content-Type": "text/plain"});
var url_parts = url.parse(request.url, true);
var query = url_parts.query;
config.password = query.pass;
rcv_msg = query.message;
connection = new Connection(config);
self = this;
self._response = response;
// Attempt to connect and execute queries if connection goes through
connection.on('connect', function(err) {
if (err) {
console.log(err)
}
else{
insertIntoDatabase(rcv_msg);
}
});
});
fs.readFile('time.txt', function (err, data) {
if (err) throw err;
console.log(data.toString());
last_read_time = new Date(Date.parse(data.toString()));
if(last_read_time < device.record_time)
{ // время предыдущего чтения меньше - значит есть новая архивная запись
last_read_time = device.record_time;
fs.writeFile('time.txt', device.record_time.toString(), function (err) {
if (err) throw err;
console.log('It\'s saved!');
});
// сокет закрыли данные есть - можно вносить в базу
var connection = new Connection(config);
connection.on('connect', function(err) {
// If no error, then good to go...
executeStatement(connection,device);
}
);
connection.on('debug', function(text) {
console.log(text);
}
);
};
});
var request = new tedious.Request(sql.GetAuthorDetails, function (err, rc, rows) {
if (err) {
console.log(err);
res.status(500).send("database error");
connection.close();
return;
}
if (!rc) {
res.status(404).send("author not found");
connection.close();
return;
}
var author = {
id: rows[0].ID.value,
name: rows[0].Name.value,
books: []
};
author.books = rows.slice(1).map(function (row) {
return {
id: row.ID.value,
title: row.Title.value
};
});
res.json(author);
connection.close();
});
var connect = function(done, config) {
config = config || this.config
var connection = new tedious.Connection({
server: config.host,
userName: config.username,
password: config.password,
options: {
port: config.port,
database: config.database
}
});
var log = function(msg){ if (config.log) {config.log(msg)}};
//connection.on('debug', log)
connection.on('infoMessage', log)
connection.on('errorMessage', console.error)
connection.on('connect', function(err){
this.isConnecting = false
if (config.pool.handleDisconnects) {
handleDisconnect(this.pool, connection)
}
done(err, connection);
});
}
openSqlServer: function(config, sqlQuery, callback){
var connection = new Connection(config);
connection.on('connect', function(err) {
if(err){
console.log(err);
callback(err);
return;
}
runDbQuery();
}
);
function runDbQuery() {
var results = [];
var request = new Request(sqlQuery, function(err, rowCount) {
if (err) {
console.log(err);
} else {
callback(null, results);
}
});
request.on('row', function(columns) {
var item = {};
columns.forEach(function(column) {
item[column.metadata.colName] = column.value + '';
});
results.push(item);
});
connection.execSql(request);
}
}
//---------------------------------------------------------- DATABASE--------------------------------------------------------------------
function connectToDatabase(callback) {
if (client) {
client.close();
}
// Prepare config
var config = {
userName: username,
password: pass,
server: dbAddress,
options: {encrypt: true, database: dbName, rowCollectionOnDone: true}
};
// Create connection
client = new Connection(config);
client.on('connect', function(err) {
if (err) {
setStatus(statusEnum.error);
node.error('Error: ' +JSON.stringify(err));
node.log('Error: ' +JSON.stringify(err));
} else {
node.log("Connected");
setStatus(statusEnum.connect);
callback();
}
});
}
executeQuery : function(stmt, callback){
console.log(now(), 'SQL Started');
var connection = new Connection(config);
var results = [];
connection.on("connect", function(){
var request = new Request(stmt, function(err, rowCount){
if(err){ console.log('SQL Error:', err); }
callback(results, err, rowCount);
console.log(now(), 'SQL Ended');
});
request.on("row", function(columns){
var row = {};
columns.forEach(function(column){
if (column.isNull){
row[column.metadata.colName] = null;
}else{
row[column.metadata.colName] = column.value;
}
});
//agrega al results cada valor obtenido
results.push(row);
});
this.execSql(request);
});
}
router.post('/deactivate', function(req, res) {
var id = req.body.id;
// connect database
var connection = new Connection(config);
connection.on('connect', function(err) {
// If no error, then good to proceed.
console.log("Connected");
executeStatement();
});
// execute a query
var Request = require('tedious').Request;
function executeStatement() {
request = new Request(`UPDATE [LVS].[User] SET [active] = 0 WHERE id = '${id}';`, function(err) {
if (err) {
console.log("err: " + err);
res.json({error: err});
} else {
res.json({success: "User deactivated successfully!"});
console.log("User deactivated successfully!");
}
});
request.on('done', function(rowCount, more) {
console.log(rowCount + ' rows returned');
});
connection.execSql(request);
}
});
function executeQuery(query, callback) {
var connection = new Connection(config);
var result = null;
connection.on('connect', function(err) {
if (err) {
callback(err, null);
return;
}
var request = new Request(query, function(err, rowCount, rows) {
if (err) {
callback(err, null);
} else {
// sanitize rows to result
result = [];
rows.forEach(function(row) {
var resultItem = {}
row.forEach(function(column) {
resultItem[column.metadata.colName] = column.value
});
result.push(resultItem);
});
callback(null, result);
}
connection.close();
});
connection.execSql(request);
});
}
module.exports = function (req, res) {
var config = {
userName: '******',
password: '******',
server: 'xxxx',
connectionTimeout: 300000,
requestTimeout: 300000,
options: {
port: 'xxxx',
database: 'xxxx.MDF',
},
pool: {
idleTimeoutMillis: 300000,
max: 100
}
}
var connection = new Connection(config);
connection.on('connect', function (err) {
if (err) {
console.log('error : ' + err);
return;
}
console.log("Connected");
executeStatement(connection, req, res);
});
}
AzureSqlClient.prototype.initialize = function (callback) {
var _this = this;
var client = new tedious_1.Connection(this.options);
client.on('connect', function (error) {
if (error) {
callback(AzureSqlClient.getError(error));
}
else {
var checkTableRequest = new tedious_1.Request("IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME = N'" + _this.options.options.table + "') BEGIN SELECT TOP 1 * FROM " + _this.options.options.table + " END", function (error, rowCount, rows) {
if (error) {
client.close();
callback(AzureSqlClient.getError(error));
}
else if (!rowCount) {
var createTableRequest = new tedious_1.Request("CREATE TABLE " + _this.options.options.table + " (id NVARCHAR(200), data NVARCHAR(1000), isCompressed BIT)", function (error, rowCount, rows) {
client.close();
callback(AzureSqlClient.getError(error));
});
client.execSql(createTableRequest);
}
else {
client.close();
callback(null);
}
});
client.execSql(checkTableRequest);
}
});
};
app.get("/api/authors/:author", function (req, res) {
var authorId = req.params.author;
var connection = new tedious.Connection(config);
connection.on('connect', function (err) {
var request = new tedious.Request(sql.GetAuthorDetails, function (err, rc, rows) {
if (err) {
console.log(err);
res.status(500).send("database error");
connection.close();
return;
}
if (!rc) {
res.status(404).send("author not found");
connection.close();
return;
}
var author = {
id: rows[0].ID.value,
name: rows[0].Name.value,
books: []
};
author.books = rows.slice(1).map(function (row) {
return {
id: row.ID.value,
title: row.Title.value
};
});
res.json(author);
connection.close();
});
request.addParameter("authorId", tedious.TYPES.Int, authorId);
connection.execSql(request);
});
});
router.get('/', function (req, res, next) {
var userId = req.get('userId');
var apiKey = req.get('apiKey');
var refresh = req.get('refresh');
console.log('PARAMS: %s, %s, %s', userId, apiKey, refresh);
var managers = new Managers.Managers();
var connection = new Connection(managers.dbManager.generateTediousConfig());
connection.on('connect', function (err) {
// If no error, then good to proceed.
if (err) {
console.log('ERROR: %s', err);
res.status(500).send();
}
else {
console.log("Connected");
executeStatement(res);
}
});
connection.on('end', function () { console.log('Db Disconnected'); });
function executeStatement(res) {
managers.dbManager.allowRequest(userId, apiKey, function (authorised) {
if (!authorised) {
res.status(500).send();
}
else {
var result = [];
var request = new Request("\n SELECT CD.Id, CD.DeviceId, CD.UserId, CD.RouteId, CD.LastConnected, PD.IsPilot\n FROM dbo.ConnectedDevices CD\n LEFT JOIN [dbo].[PilotDevices] PD ON CD.DeviceId = PD.DeviceId\n WHERE CD.LastConnected > CAST(GETDATE() AS DATE);\n ", function (err, rowCount) {
if (err) {
console.log(err);
res.status(500).send("Error");
}
else {
console.log('Complete: %s row(s) returned', rowCount);
connection.close();
res.setHeader('content-type', 'application/x-www-form-urlencoded');
res.setHeader('Access-Control-Allow-Origin', '*');
res.status(200).send(result);
}
});
request.on('row', function (columns) {
var row = {};
columns.forEach(function (column) {
if (column.value === null) {
console.log('NULL');
}
else {
row[column.metadata.colName] = column.value;
}
});
result.push(row);
});
request.on('done', function (rowCount, more) {
console.log(rowCount + ' rows returned');
});
connection.execSql(request);
}
});
}
});
function(callback) {
log.debug('Connecting to SQL server %s%s and database %s', credential.sqlServerName, serverSuffix, credential.sqldbName);
connection = new Connection(config);
connection.on('connect', function(err) {
var message = 'The SQL Database can %sbe connected.';
nextStep(err, message, callback);
});
},
function createConnection(finished) {
var connection = new tedious.Connection(config);
connection.on('debug', function(text) {
//console.log("connection debug:",text);
});
connection.on('connect',function(err) {
finished(err,connection)
});
}
var insertRequest = new tedious_1.Request("INSERT INTO " + _this.options.options.table + " (id, data, isCompressed) VALUES (@id, @data, @isCompressed)", function (error, rowCount, rows) {
if (error) {
client.close();
callback(AzureSqlClient.getError(error), null, null);
}
else {
client.close();
callback(null, rows[0], rows[0]);
}
});
function createConnection(config) {
SqlConnection = new Connection(config);
SqlConnection.on('connect', function(err) {
console.log("Connected to Azure Database");
});
SqlConnection.on('end', function() {
console.log('Failure in connection to database: ' + config.server);
});
}
var updateRequest = new tedious_1.Request("UPDATE " + _this.options.options.table + " SET data=@data, isCompressed=@isCompressed WHERE id=@id", function (error, rowCount, rows) {
if (error) {
client.close();
callback(AzureSqlClient.getError(error), null, null);
}
else {
client.close();
callback(null, rows[0], rows[0]);
}
});
var executeQueries = function(host, port, user, password, db, queries, options, cb) {
var isAzure = (options && options.isAzure) ? true : false;
var config = {
server: host,
userName: user,
password: password,
options: {
port: port || 1433,
database: db,
readOnlyIntent: true,
useColumnNames: true,
encrypt: (options && options.isAzure) ? true : false,
requestTimeout: 0
}
};
var connection = new Connection(config);
connection.on('connect', function(err) {
if(err) return cb(err);
var result = [];
async.eachSeries(queries, function(query, callback) {
var queryResult = {
columns: [],
rows: []
};
request = new Request(query, function(err){
if(err)
result.push(new Error(err));
else
result.push(queryResult);
return callback();
});
request.on('columnMetadata', function (columns) {
queryResult.columns = Object.keys(columns);
});
request.on('row', function (columns) {
var row = [];
for(var column in columns) row.push(columns[column].value);
queryResult.rows.push(row);
});
connection.execSql(request);
}, function() {
connection.close();
return cb(null, result);
});
});
};
.addSource('msDb', function(options){
var stream = streamler.through2.call(this),
connection = new Connection({
userName: '******',
password: '******',
server: 'qa-auto-ui-db01.devid.local',
connectTimeout: 3000
});
connection.on('connect', function(err) {
if (err) {
console.error('Received error', err);
if(options.defaults && options.defaults.length){
options.defaults.forEach(function(item){
stream.emit("data", item);
})
}else if( options.defaults ){
stream.emit("data", options.defaults);
}
stream.emit("end");
} else {
var request = new Request(options.query, function(err, rowCount) {
if (err) {
console.log(err + 'for query: '+ options.query);
process.exit();
} else {
console.log(rowCount + ' rows for query: '+ options.query);
}
});
request.on('row', function(columns) {
var emitme;
switch(options.columns){
case "singleValue":
emitme = columns[0].value;
break;
case "columnValues":
emitme = [];
columns.forEach(function(item){
emitme.push(item.value);
})
break;
default:
emitme = columns;
break;
}
stream.emit("data", emitme );
});
request.on('doneProc', function(){
stream.emit("end");
process.nextTick( connection.close.bind(connection) );
});
connection.execSql(request);
};
});
return stream;
});
router.get('/:id', function(req, res) {
var id = req.params.id;
// connect database
var connection = new Connection(config);
connection.on('connect', function(err) {
// If no error, then good to proceed.
console.log("Connected");
executeStatement();
});
// execute a query
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
function executeStatement() {
request = new Request(`SELECT * FROM [LVS].[Announcement] AS announcementInfo WHERE announcementInfo.id = ${id}`, function(err) {
if (err) {
console.log("err: " + err);}
});
var resultTotal = [];
var promise = new Promise(function(resolve, reject) {
request.on('row', function(columns) {
var group = {};
columns
.map(function(row) {
announcement[row.metadata.colName] = row.value;
if (announcement.id === row.value) {
resultTotal.push(announcement);
}
});
resolve(resultTotal);
});
});
promise.then(function(result) {
res.json(result);
})
request.on('done', function(rowCount, more) {
console.log(rowCount + ' rows returned');
});
connection.execSql(request);
}
});
router.get('/', function(req, res) {
// connect database
var connection = new Connection(config);
connection.on('connect', function(err) {
// If no error, then good to proceed.
console.log("Connected");
executeStatement();
});
// execute a query
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
function executeStatement() {
request = new Request(`SELECT [LVS].[Announcement].*, [LVS].[User].first_name, [LVS].[User].last_name
FROM [LVS].[Announcement]
LEFT JOIN [LVS].[User] ON [LVS].[Announcement].author = [LVS].[User].id
`, function(err) {
if (err) {
console.log("err: " + err);}
});
var resultTotal = [];
var promise = new Promise(function(resolve, reject) {
request.on('row', function(columns) {
var announcement = {};
columns
.map(function(row) {
announcement[row.metadata.colName] = row.value;
if (announcement.id === row.value) {
resultTotal.push(announcement);
}
});
resolve(resultTotal);
});
});
promise.then(function(result) {
res.json(result);
})
request.on('done', function(rowCount, more) {
console.log(rowCount + ' rows returned');
});
connection.execSql(request);
}
});
connect: function (callback) {
conn = new tedious.Connection(config.db);
conn.on('connect', function (err) {
if (err) return callback(err);
callback(null);
});
// conn.on('debug', function(msg) {
// console.log('[DEBUG]', msg)
// });
},
function initSQLEXPRESS(callback) {
// Initialize SQLEXPRESS
try {
var connConfig = config.sqlConnection;
var conn = new tedious.Connection(connConfig)
conn.on('connect', function(err) {
callback(err, conn);
})
} catch (e) {
err = new Error('Failed to connect SQLEXPRESS.\n' + e);
callback(err);
}
}
TediousPromise.prototype._createConnection = function() {
// TODO: Transaction support - if transaction, resolve with transaction's connection
var deferred = q.defer();
if(this._connection) { // existing connection found
deferred.resolve(this._connection);
} else {
if(this._mode === 'mock') {
return q(new MockTediousConnection(this));
} else if(this._mode === 'pool') {
// get from pool
this._connectionPool.acquire(function (err, connection) {
try {
if (err) {
connection.release();
deferred.reject(err);
} else {
enableDebugLogging(connection);
deferred.resolve(connection);
}
} catch(e) {
deferred.reject(e);
}
});
} else if (this._mode === 'single') {
// create new connection
var connection = new Connection(this._connectionConfig);
connection.on('connect', function(err) {
try {
if (err) {
connection.close();
deferred.reject(err);
} else {
enableDebugLogging(connection);
deferred.resolve(connection);
}
} catch(e) {
deferred.reject(e);
}
});
} else {
throw new Error('Create connection not implemented for mode: ' + this._mode + '.');
}
}
return deferred.promise;
};