Archive for ‘nodejs’

September 10, 2012

MS SQL Server driver for Node.js with JSON response

We covered installing and running the MS SQL driver for Node.js in the last post. The demonstration
was a simple web page that listed statically queried results from a database. For this post, we’ll
be demonstrating how to serve the results in JSON; which is a more practicle usage of the MS SQL
driver.

In the previous example, we utilized the built in http server to serve up the static web page and
the query was run once when the server was launched. This time we’ll create a RESTful service and
query the database each time there is a request.

Restify is a Node.js module for creating REST web services. To install it, open a command prompt in
the folder that you wish to create the demo and type the following:

npm install restify

Once restify is installed, you can create a basic server and run it to verify that the installation
was successful. Create a text file named server.js and copy the next block of code.

var restify = require('restify');
var server = restify.createServer();

server.listen(8080, function () {
    console.log('listening at %s', server.url);
});

Run the server with:

node server.js

If Restify was installed correctly, you should see ‘listening at http://0.0.0.0:8080’ in the console.
You can hit the page from your browser at this time, but you will get an error result similar to the
following:

{"code":"ResourceNotFound","message":"/ not found"}

This is expected behavior and it simply means that a route for the requested page does not exist. To
define a route, you will need to set up the “GET” callback.

server.get('/people', respond);

The first parameter is the route and the second parameter is the function that provides the response.
In this example, we have defined a route so that requests can be made to the url of the server followed
by /people. For example, you should now be able run the server and browse to it with
http://localhost:8080/people once the ‘respond’ function is defined.

To serve the JSON response, the respond function will query the database and put the results into an
array object with key / value pairs. Once populated, the array object is sent as the response.

function respond(request, response, next) {
	response.header("content-type: application/json");
	
	var data = [];
	sql.open(conn_str, function (err, conn) {
	    if (err) {
		data = "cannot open connection!";
		return;
	    }

	    conn.queryRaw("SELECT TOP 10 FirstName, LastName FROM Person.Contact", function (err, results) {
		if (err) {
		    console.log("query failed!");
		    return;
		}
		for (var i = 0; i < results.rows.length; i++) {
			data.push({
			    firstname: results.rows[i][0], 
			    lastname: results.rows[i][1]
			});
		}
		
		response.send(data);
	    });
	});
};

It is important that you set the content-type to json. This may seem obvious, but it is worth noting
in order to prevent a few moments of head scratching :).

We now have Node ready to serve JSON responses with data from a MS SQL Server. The complete script
should look similar to the following:

var restify = require('restify');
var server = restify.createServer();
var sql = require('node-sqlserver');
var connection_string = "Driver={SQL Server Native Client 10.0};Server=YOUR_SERVER;Database=YOUR_DB;uid=YOUR_USER;pwd=YOUR_PASSWORD";

function respond(request, response, next) {
	response.header("content-type: application/json");
	
	var data = [];
	sql.open(connection_string, function (err, conn) {
	    if (err) {
		data = "cannot open connection!";
		return;
	    }

	    conn.queryRaw("SELECT TOP 10 FirstName, LastName FROM Person.Contact", function (err, results) {
		if (err) {
		    console.log("query failed!");
		    return;
		}
		for (var i = 0; i < results.rows.length; i++) {
			data.push({
			    firstname: results.rows[i][0], 
			    lastname: results.rows[i][1]
			});
		}
		
		response.send(data);
	    });
	});
};

server.listen(8080, function () {
    console.log('listening at %s', server.url);
});

server.get('/people', respond);

If you have an AdventureWorks database installed, you will just need to update the connection string
for this demo. If not, be sure to update the connection string as well as the actual query.

To run the server, use the following command at the command prompt in the folder where your script
is located.

node server.js

To view the data, you can browse to http://localhost:8080/people. I recommend using either Chrome or Firefox
to do so. If you use Chrome, I highly recommend the JSONView extension as it will present the data in a more readable fashion.

Tags:
September 5, 2012

SQL Server driver for Node.js

Node.js is an intriguing technology that has garnered a lot of attention since it was created by
Ryan Dahl in 2009. Built around Google’s V8 JavaScript engine, it provides programmers with the
ability to write programs written in JavaScript using event driven, asynchronous I/O to minimize
overhead.

Support for Node.js on Windows platforms was initially non-existent unless you consider running it
in cygwin as an acceptable definition of support. Over the past several months, things have come a
long way on that front with a native installer, Windows Azure support and documentation, and even
a Microsoft SQL Server driver.

The Microsoft SQL Server driver was particullarly interesting, so I thought that I would write a
quick tutorial on how to quickly install it and get it runnning. If you do not have Node.js installed
yet, grab the installer from their official website.

nodejs.org/download

Once installed, create a folder for your project called nodesql. Inside the folder, create and empty
text file called server.js. To ensure everything is working, add the obligatory ‘Hello World’ web
server code.

var http = require('http');
http.createServer(function (request, response) {
  response.writeHead(200, {'Content-Type': 'text/plain'});
  response.end('Hello World\n');
}).listen(8080);
console.log('Server running on port 8080');

To run the server, navigate to ~/nodesql in the command shell (cmd.exe) and type the following:

node server.js

You should see “Server running on port 8080” in the console window. Once verified, you can stop the
server with ctrl-c, but keep the console open for the next step.

To install the SQL Server driver, navigate to the nodesql folder (if you are not still there) and
issue the following command:

npm install node-sqlserver

This will create a folder called node_modules, download the source for the module and compile it.
Once complete, copy sqlserver.node from the node_modules/node-sqlserver/build/Release folder to the
node_modules/node-sqlserver/lib folder. This is an important step that will ensure that the module
can be found when running the server.

For this test, I created a simple page that queries the database and lists the results in a simple
web page. Note that the query is run as soon as the server is started. This isn’t really practicle,
but serves the purpose of this demonstration.

var http = require('http');
var sql = require('node-sqlserver');
var connection_string = "Driver={SQL Server Native Client 10.0};Server=YOUR_SERVER;Database=YOUR_DB;uid=YOUR_USER;pwd=YOUR_PASSWORD";

var data = "";
sql.open(connection_string, function (err, connection) {
    if (err) {
	data = "cannot open connection!";
	return;
    }
    connection.queryRaw("SELECT TOP 10 Name, EmailAddress FROM Customer", function (err, results) {
	if (err) {
	    data = "query failed!";
	    return;
	}
	for (var i = 0; i < results.rows.length; i++) {
	    data += "Name: " + results.rows[i][0] + " Email: " + results.rows[i][1] + "<br />";
	}
    });
});

http.createServer(function (req, res) {
	req.on('end', function() {
		res.writeHead(200, { 
		 'Content-Type': 'text/html'
		});
		res.end(data);
	});
}).listen(8080);
console.log('Server running on port 8080');

You will of course want to update the connection string with your information and update the query.