Connecting to MS SQL DB with node.js + functions()

Question

I am trying to connect to a MS SQL Server DB using node.js. I installed the msnodesql module to use for this task. I am able to connect to a DB with the following code:

var sql = require('msnodesql');
var util = require('util');
//
var connStr = "Driver={SQL Server Native Client 11.0};Server=myySqlDb,1433;Database=DB;UID=Henry;PWD=cat;";
var query = "SELECT * FROM GAData WHERE TestID = 17";

sql.open(connStr, function(err,conn){
    if(err){
        return console.error("Could not connect to sql: ", err);
    }

    conn.query(query,function(err,results){

        if (err){
            return console.error("Error running query: ", err);
        }
        console.log(results);
        console.log(results.length);
        for (var i = 0; i <= results.length; i++){
            util.inspect(results[i]);
        }
    });
});

My goal however is to connect to the DB from various events, such as button submits, from a HTML page. From the button click I want to call a node.js function to query the DB for a particular attribute, such as the following:

From the HTML:

<br /><button type="submit" id="continue" onClick="verifyEmail($('#email').val())">Continue</button>

From the script file:

function verifyEmail(email){

    var mQuery = "'EXEC WebUserRecovery '" +  email + "'";

    sql.open(conn_str, function (err, conn) {
    if (err) {
        console.log("Error opening the connection!\r\n" + err);
        return;
    }

    connection.query(mQuery,function(err,results){

        if (err){
            return console.error("Error running query: ", err);
        }

        alert(results);
    });
});

}

The code when put inside the function does not work, a DB connection is unsuccessful. Can anyone advise how to fix this issue? There is little good documentation on msnodesql out there...

You seem to be trying to issue a sql connection and sql query from the browser, which AFAIK is not possible. verifyEmail should issue an ajax request, handled by node to perform the sql query and return some (json) result. — jbl
– jbl, Commented Sep 26, 2013 at 13:11
Can you tell us verifyEmail() executes at client or at node.js server? — Amol M Kulkarni
– Amol M Kulkarni, Commented Sep 26, 2013 at 13:18
I think I figured out how to add the ajax call in to make the operation work. However, currently my SQL query is stored on the server side in a var. I'd like to be able to query the DB with different SQL queries. How can I pass into the ajax call different values to do different queries on the server side? — gjw80
– gjw80, Commented Sep 26, 2013 at 16:23
@gjw80 Hope you got the solution, how to pass MS SQL result-set to client through $.ajax() — Amol M Kulkarni
– Amol M Kulkarni, Commented Sep 27, 2013 at 6:47

Amol M Kulkarni · Accepted Answer · 2013-09-26 13:34:36Z

Server side .js file (Node.js):

var sql = require('msnodesql');
var util = require('util');
var connStr = "Driver={SQL Server Native Client 11.0};Server=myySqlDb,1433;Database=DB;UID=Henry;PWD=cat;";
var query = "SELECT * FROM GAData WHERE TestID = 17"; 

// Load the http module to create an http server.
var http = require('http');

// Configure our HTTP server to respond with Hello World to all requests.
var server = http.createServer(function (request, response) {

    sql.open(connStr, function(err,conn){
        if(err){
            return console.error("Could not connect to sql: ", err);
        }

        conn.query(query,function(err,results){

            if (err){
                return console.error("Error running query: ", err);
            } 
            response.writeHead(200, {"Content-Length": results.length});
            response.writeHead(200, {"Content-Type": "application/json"});
            response.end(results); 
        });
    });

});

// Listen on port 8000, IP defaults to 127.0.0.1
server.listen(8000);

// Put a friendly message on the terminal
console.log("Server running at http://127.0.0.1:8000/");

At Client Side .js file or in-line script should be something like following using jQuery ajax call:

var request = $.ajax({
  url: "http://127.0.0.1:8000/",
  type: "GET",
  dataType: "json"
});
request.done(function(dataRcvd) {
  alert(JSON.stringify(dataRcvd));
});

Collectives™ on Stack Overflow

Connecting to MS SQL DB with node.js + functions()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related