How to connect to SQL Server database from JavaScript in the browser?

How to connect to SQL Server database from JavaScript in the browser?

Can anybody give me some sample source code showing how to connect to a SQL Server 2005 database from JavaScript locally? I am learning web programming on my desktop.
Or do I need to use any other scripting language? Suggest some alternatives if you have them, but I am now trying to do it with JavaScript. My SQL Server is locally installed on my desktop — SQL Server Management Studio 2005 and IE7 browser.

Solutions/Answers:

Solution 1:

You shouldn´t use client javascript to access databases for several reasons (bad practice, security issues, etc) but if you really want to do this, here is an example:

var connection = new ActiveXObject("ADODB.Connection") ;

var connectionstring="Data Source=<server>;Initial Catalog=<catalog>;User ID=<user>;Password=<password>;Provider=SQLOLEDB";

connection.Open(connectionstring);
var rs = new ActiveXObject("ADODB.Recordset");

rs.Open("SELECT * FROM table", connection);
rs.MoveFirst
while(!rs.eof)
{
   document.write(rs.fields(1));
   rs.movenext;
}

rs.close;
connection.close; 

A better way to connect to a sql server would be to use some server side language like PHP, Java, .NET, among others. Client javascript should be used only for the interfaces.

Related:  Array to Comma separated string and for last tag use the 'and' instead of comma in jquery

And there are rumors of an ancient legend about the existence of server javascript, but this is another story. 😉

Solution 2:

This would be really bad to do because sharing your connection string opens up your website to so many vulnerabilities that you can’t simply patch up, you have to use a different method if you want it to be secure. Otherwise you are opening up to a huge audience to take advantage of your site.

Solution 3:

Web services

SQL 2005+ supports native WebServices that you could almost use although I wouldn’t suggest it, because of security risks you may face. Why did I say almost. Well Javascript is not SOAP native, so it would be a bit more complicated to actually make it. You’d have to send and receive SOAP via XmlHttpRequest. Check google for Javascript SOAP clients.

Solution 4:

A perfect working code..

Related:  What are the differences between Deferred, Promise and Future in JavaScript?
    <script>
    var objConnection = new ActiveXObject("adodb.connection");
    var strConn = "driver={sql server};server=QITBLRQIPL030;database=adventureworks;uid=sa;password=12345";
    objConnection.Open(strConn);
    var rs = new ActiveXObject("ADODB.Recordset");
    var strQuery = "SELECT * FROM  Person.Address";
    rs.Open(strQuery, objConnection);
    rs.MoveFirst();
    while (!rs.EOF) {
        document.write(rs.fields(0) + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
        document.write(rs.fields(1) + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
        document.write(rs.fields(2) + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;    ");
        document.write(rs.fields(3) + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;    ");
        document.write(rs.fields(4) + "<br/>");
        rs.movenext();
    }
</script>

Solution 5:

Playing with JavaScript in an HTA I had no luck with a driver={SQL Server};... connection string, but a named DSN was OK :
I set up TestDSN and it tested OK, and then var strConn= "DSN=TestDSN"; worked, so I carried on experimenting for my in-house testing and learning purposes.

Our server has several instances running, e.g. server1\dev and server1\Test which made things slightly more tricky as I managed to waste some time forgetting to escape the \ as \\ 🙂
After some dead-ends with server=server1;instanceName=dev in the connection strings, I eventually got this one to work :
var strConn= "Provider=SQLOLEDB;Data Source=server1\\dev;Trusted_Connection=Yes;Initial Catalog=MyDatabase;"

Using Windows credentials rather than supplying a user/pwd, I found an interesting diversion was discovering the subtleties of Integrated Security = true v Integrated Security = SSPI v Trusted_Connection=Yes – see Difference between Integrated Security = True and Integrated Security = SSPI

Related:  JSLint is suddenly reporting: Use the function form of “use strict”

Beware that RecordCount will come back as -1 if using the default adOpenForwardOnly type. If you’re working with small result sets and/or don’t mind the whole lot in memory at once, use rs.Open(strQuery, objConnection, 3); (3=adOpenStatic) and this gives a valid rs.RecordCount

Solution 6:

As stated before it shouldn’t be done using client side Javascript but there’s a framework for implementing what you want more securely.

Nodejs is a framework that allows you to code server connections in javascript so have a look into Nodejs and you’ll probably learn a bit more about communicating with databases and grabbing data you need.