Total.js – Connection with SQL Server on Portal Azure

 

One of the best node.js modules for connection to the SQL Server database on Azure Portal is mssql (https://www.npmjs.com/package/mssql). The documentation states that to connection with the database, we use the following construction connection string

“mssql://username:password@localhost/database”.

screen-shot-2016-12-07-at-07-46-11

Problems begin in when we need to use the specific driver with which cooperates our database or we want to use the connection string, eg.


"Driver={SQL Server Native Client 11.0};Server={tcp:db1.database.windows.net,1433};Database=DatabaseDev;Uid=admin@db1.database.windows.net;Pwd=myPassword"

generated by applications Widnows e.g. SQL Server Management Studio, Visual Studio or other programs. Then the problems begin – during try to establish connection we receive connection error with our database on portal azure. How to solve this problem? Below explains step by step how to solve this issue.
First we have to install standard modules listed in the total.js documentation:

$ npm install totaljs
$ npm install mssql
$ npm install sqlagent

In addition to the standard modules which have been listed above also we have to install the important module msnodesqlv8

$ npm install msnodesqlv8

It remains properly convey structures connection string to our sql agent. Please note of two very important things: give the name of the driver and the connection string is placed in the object property defined as connectionString. A whole construction we pass through the defined object

sample of mssql.js in /definitions:

var config = {
 driver:'msnodesqlv8',
 connectionString: "Driver={SQL Server Native Client 11.0};Server=  {tcp:db1.database.windows.net,1433};Database=DatabaseDev;Uid=admin@db1.database.windows.net;Pwd=myPassword"
};

require('sqlagent/sqlserver').init(config,true);

 

Connection string, we can also put in the config file project

var config = {
 driver:'msnodesqlv8',
 connectionString: F.config.SQLAZURECONNSTR_AzureSQL 
};

require('sqlagent/sqlserver').init(config,true);

 

Sample of config in /:

// Mail settings
mail.smtp : smtp.maxprog.net.pl
mail.smtp.options : {"secure":true,"port":465,"user":"maxproga","password":"password15,"timeout":10000}
mail.address.from : info@maxprog.net.pl
mail.address.reply : info@maxprog.net.pl

SQLAZURECONNSTR_AzureSQL : Driver={SQL Server Native Client 11.0};Server={tcp:db1.database.windows.net,1433};Database=DatabaseDev;Uid=admin@db1.database.windows.net;Pwd=myPassword

 

Sample of controller.js in /controllers :


exports.install = function() {

F.route('/db', db);

};
function db() {
const self = this;

users(function(item){
self.json(item);

});

}
function users(callback) {

const sql = DATABASE();

sql.select('users', 'UserTable').make(function(filter) {

filter.where('UserID', '>',1000);
filter.like('Name', 'K%');
filter.fields('Name', 'Email');
});

sql.exec(function(err, response) {

// No users === end
if (!response.users.length)
return callback();
callback({success: true, data: response.users});

});
}

 

December 7th, 2016

  • I actually wanted to send a simple word so as to thank you for these splendid tips you are placing at this site. My time-consuming internet investigation has now been recognized with good quality information to go over with my neighbours. I ‘d admit that many of us site visitors are undoubtedly blessed to be in a fantastic place with so many awesome professionals with interesting hints. I feel quite blessed to have come across your entire webpage and look forward to plenty of more enjoyable minutes reading here. Thanks a lot again for everything..

  • I must voice my passion for your generosity giving support to those people who require help on your concept. Your real commitment to passing the solution all through came to be astonishingly powerful and have in most cases allowed others like me to achieve their ambitions. Your amazing useful guide implies a great deal a person like me and still more to my peers. Thanks a lot; from all of us..

  • Thanks a lot for providing individuals with a very wonderful chance to discover important secrets from this blog. It is always so fantastic and jam-packed with a good time for me personally and my office friends to search your site at the very least 3 times weekly to find out the newest secrets you have got. And lastly, I am at all times impressed with all the exceptional creative ideas served by you. Selected two tips in this article are ultimately the most suitable I’ve ever had..

  • Youre so cool! I dont suppose Ive learn something like this before. So good to seek out any person with some original ideas on this subject. realy thank you for starting this up. this website is something that’s wanted on the net, somebody with just a little originality. helpful job for bringing something new to the internet!.

  • An impressive share, I simply given this onto a colleague who was doing a bit analysis on this. And he in actual fact bought me breakfast as a result of I discovered it for him.. smile. So let me reword that: Thnx for the treat! But yeah Thnkx for spending the time to debate this, I feel strongly about it and love reading more on this topic. If attainable, as you grow to be expertise, would you mind updating your blog with extra particulars? It’s extremely helpful for me. Huge thumb up for this blog post!.