Access SQL Azure Data Using Java Script

  • Article

I regularly access SQL Azure from my .Net code and once a while from Java or Ruby code. But I was just thinking since it’s so easy and not at all different from on premise code can I use a scripting language to access SQL Azure. Is it even possible? To my amusement I was able to get the data from SQL Azure and will detail the steps and code below.

I will start with a caveat though in case you are not writing a fun application for your friends or kids or may be some personal application you want to run on your PC read no further and go to this link instead. Javascript was not developed for data access purpose. The main use of JavaScript is for client side form validation, for AJAX(Asynchronous Java Script and XML) implementation. But since IE has the ability to load ActiveX objects we can still use Java Script to connect to SQL Azure.

So let’s have the ball rolling. I assume there is already a database called Cloud that has a table Employee. This has columns called FirstName and LastName …yeah yeah the same ubiquitous table we all learnt in programming 101 class… whatever.

We will get it done in two steps.

Step 1

Set up a DSN to connect to SQL Azure. Depending on the environment use odbcad32.exe either from (C:\Windows\SysWOW64) for 32bit or from (C:\Windows\System32) for the 64bit. No I am not incorrect the folder with 64 in it’s name has all 32bit binaries and the folder with 32 in it’s name has all 64bit binaries. Create a new system DSN, use the SQL Server Native Client 11 driver, then give the FQDN of the SQL Azure database as below.


 
  Use the Database Name of the database you want to connect to
 
 
  
  

Check that the test connection succeeds. 


 

Step 2

So now we come to the script part of it. Add a new HTML file in your existing project and replace the code in there with the following.

<!DOCTYPE html PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="https://www.w3.org/1999/xhtml">

<head>

  <title>Untitled Page</title>  

  <script type="text/javascript" language="javascript" >

      function connectDb() {

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

         Conn.ConnectionString = "dsn=sqlazure;uid=annayak;pwd=P@$$word;";

         Conn.Open();

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

          var strEmpName = " " ;

          rs.Open("select * from dbo.Employees", ConnDB, 1, 3);

         while (!rs.EOF) {

                strEmpName = rs("FirstName") + " " + rs("LastName") + "<br>";              
                rs.MoveNext;

           }

         test.innerHTML = strEmpName;

      }

  </script>

 </head>

<body> 

<p><div id="test"> </div> </p>

<p><input id="button1" type="button" value="GetData" name="button1" onclick="connectDb();"/></p>  

</body>

</html>

Modify the highlighted part to use the DSN name, user id, password, table name, column name as per the actual values applicable in your scenario. Now when the HTML page is called and the GetData button clicked it will get the data and display in IE (please note ActiveX works only with IE). 

Since you are loading a driver in IE use this setting to suppress warning messages.

Open Internet Explorer -> Tools -> Internet Options -> Security -> Trusted sites -> Sites -> Add this site to the web zone

Author : Angshuman Nayak , Azure Escalation Services, Microsoft Corporation