Connecting to an MS SQL Instance using NodeJS (Fixing ConnectionError: Port for SQLServer n...

Datetime:2016-08-23 05:18:51          Topic: SQL Server  Node.js  SQL           Share

After few debates and discussions on new technologies in the market and how to adapt to them, during the weekend I thought of exploring NodeJS and it applications. Since I spent most of my time designing data-centric applications at office, as the first step I thought of connecting to MS SQL Server using NodeJS.

As a newbie to NodeJS, I went through the official documentation and managed to achieve it. However, during the course, I faced many difficulties and by referring to many of the articles, I was managed to resolve all these hurdles.

So I thought of including these problems which I faced and how to overcome them. So it would be a great help to anyone who’s exploring or trying to achieve this more easily.

For this, I will be using SQL Server 2014 on an Instance (.\SQL2K14).

1. First you need to download and install NodeJS. ( https://nodejs.org/en/ )

2. Install MSSQL package for Node, using the following syntax: (Use windows command prompt)

npm install mssql

3. Create a file named ‘connecttosql.js’ and include the following code:

//We require mssql package for this sample

function GetSQLData(queryCallback){        //A callback function is taken as an argument. Once the operation is completed we will be calling this


//SQL Configuration

var config = {

user:'###'            //SQL User Id. Please provide a valid user

,password:'######'    //SQL Password. Please provide a valid password

,server:'localhost\\SQL2K14'   

/*

Since my SQL is an instance I am using 'localhost\\Instance'.

If you have SQL installed on the default instance, it should be server:'localhost'

*/

,database: 'master'        //You can use any database here

var connection = new sqlcon.Connection(config,function(err){

//In case of an error print the error to the console. You can have your customer error handling

if (err) console.log(err);

//Query Database

var dbQuery = new sqlcon.Request(connection);

//Purposely we are delaying the results

dbQuery.query("WAITFOR DELAY '00:00:05';SELECT * FROM INFORMATION_SCHEMA.TABLES",function(err,resultset){

//In case of an error print the error to the console. You can have your customer error handling

if (err) console.log(err);

//Passing the resultset to the callback function

queryCallback(resultset);

})

});

function callback (resultset){

console.dir('Results returned and printed from the call back function');

console.dir(resultset);


//Exit the application

console.dir('Exiting the Application');

process.exit(0);

//Calling the function

console.dir('Calling GetSQLData');

GetSQLData(callback);

/*

Once we call this function even there's a delay to return the results

you will see the next line printing 'Waiting for callback function to get invoked...'

*/

console.dir('Waiting for callback function to get invoked...');

I have provided the relevant information as comments.

Before running the program please make sure the following configurations on the SQL server is already done:

1. Enable TCP/IP Protocols in SQL Server Configuration Manager for both server and client.

Or else when running it will result an error shown below:

{ [ConnectionError: Port for SQL2K14 not found in ServerName;[YourPCName];InstanceName;SQL2K14;IsClustered;No;Version;12.0.2000.8;np;\\[YourPCName]\pipe\M

SSQL$SQL2K14\sql\query;;]

name: 'ConnectionError',

message: 'Port for SQL2K14 not found in ServerName;[YourPCName];InstanceName;SQL2K14;IsClustered;No;Version;12.0.2000.8;np;\\\\[YourPCName]\\pipe\\MSSQL

$SQL2K14\\sql\\query;;',

code: 'EINSTLOOKUP' }

{ [ConnectionError: Connection is closed.]

name: 'ConnectionError',

message: 'Connection is closed.',

code: 'ECONNCLOSED' }

2. In SQL Server Configuration Manager under SQL Server Services make sure that ‘SQL Server Browser’ service is running.

Or else when running the script it will result an error shown below:

{ [ConnectionError: Failed to connect to localhost:undefined in 15000ms]

name: 'ConnectionError',

message: 'Failed to connect to localhost:undefined in 15000ms',

code: 'ETIMEOUT' }

{ [ConnectionError: Connection is closed.]

name: 'ConnectionError',

message: 'Connection is closed.',

code: 'ECONNCLOSED' }

if the aforementioned issues are already addressed execute the above file using the following syntax in a Windows Command Window:

node connecttosql.js

You should get a similar result which is shown below:

'Calling GetSQLData'

'Waiting for callback function to get invoked...'

'Results returned and printed from the call back function'

[ { TABLE_CATALOG: 'master',

TABLE_SCHEMA: 'dbo',

TABLE_NAME: 'spt_fallback_db',

TABLE_TYPE: 'BASE TABLE' },

{ TABLE_CATALOG: 'master',

TABLE_SCHEMA: 'dbo',

TABLE_NAME: 'spt_fallback_dev',

TABLE_TYPE: 'BASE TABLE' },

{ TABLE_CATALOG: 'master',

TABLE_SCHEMA: 'dbo',

TABLE_NAME: 'spt_fallback_usg',

TABLE_TYPE: 'BASE TABLE' },

{ TABLE_CATALOG: 'master',

TABLE_SCHEMA: 'dbo',

TABLE_NAME: 'spt_values',

TABLE_TYPE: 'VIEW' },

{ TABLE_CATALOG: 'master',

TABLE_SCHEMA: 'dbo',

TABLE_NAME: 'spt_monitor',

TABLE_TYPE: 'BASE TABLE' },

{ TABLE_CATALOG: 'master',

TABLE_SCHEMA: 'dbo',

TABLE_NAME: 'MSreplication_options',

TABLE_TYPE: 'BASE TABLE' } ]

'Exiting the Application'

I hope this will help anyone who’s using node to connect to SQL and facing the aforementioned issues.





About List