How to connect to SQL Server database from JavaScr

Question

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.

Answer 1

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.

Answer 2

(sorry, this was a more generic answer about SQL backends--I hadn't read the answer about SQL Server 2005's WebServices feature. Although, this feature is still run over HTTP rather than more directly via sockets, so essentially they've built a mini web server into the database server, so this answer is still another route you could take.)

You can also connect directly using sockets (google "javascript sockets") and by directly at this point I mean using a Flash file for this purpose, although HTML5 has Web Sockets as part of the spec which I believe let you do the same thing.

Some people cite security issues, but if you designed your database permissions correctly you should theoretically be able to access the database from any front end, including OSQL, and not have a security breach. The security issue, then, would be if you weren't connecting via SSL.

Finally, though, I'm pretty sure this is all theoretical because I don't believe any JavaScript libraries exist for handling the communications protocols for SSL or SQL Server, so unless you're willing to figure these things out yourself it'd be better to go the route of having a web server and server-side scripting language in between the browser and the database.

Answer 3

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

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

Answer 4

A perfect working code..

    <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>

Answer 5

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.

• http://msdn.microsoft.com/en-us/library/ms345123.aspx - SQL native WebServices
• http://www.google.com/search?q=javascript+soap - Google results for Javascript SOAP clients

Answer 6

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.

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