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

ID : 10039

viewed : 34

Tags : javascriptsql-serverdatabase-connectionjavascript

Top 5 Answer for How to connect to SQL Server database from JavaScript in the browser?

vote vote

93

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. ;)

vote vote

85

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.

vote vote

72

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

64

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.

vote vote

54

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

Top 3 video Explaining How to connect to SQL Server database from JavaScript in the browser?

Related QUESTION?