URL Accessing in MS SQL Server- I

With URL Accessing you can query your MS SQL Server database from the address bar of your web browser

               Ever dreamt of querying a database from your web browser? Yes, it’s now a reality with SQL Server. This feature in SQL Server is called URL Accessing. This is made possible through XML. SQL Server is an XML –enabled database.

                   

                    The URL can be specified to directly access the Database Objects such as tables, views etc, to execute Template files or to execute XPath Queries. In all the above three types we create a virtual name of concerned type in IIS. Virtual names serves as an optional path relative to the physical path where actual files will be stored. Besides the above three we can directly query the Virtual Directory also (not to be confused with virtual names, a virtual directory contains virtual names of concerned type).A Virtual Directory is one which is mapped to a physical directory and allows us to access the SQL Server database. In this two part series, first we see how to create a basic query (directly query the Virtual Directory) and how to execute a stored procedure. In the second part, we see how to access Database Objects, how to execute Template Files and XPath Queries.

               To create a virtual directory open Configure XML Support in IIS utility from Microsoft SQL Server program group. Right click Default web Site and select New then select Virtual Directory. In New Virtual Directory Properties dialog box, give a name for the virtual directory and for local path create a directory (named Master) in \Inetpub\wwwroot directory of your web server and give path to that directory. Also create two new directories named Template and Schema in Master directory. In Security tab, give the login credentials. In Data Source tab, select your server’s name for SQL Server and for Database, select the database for which you want to enable URL Accessing (in this case master). In Settings tab, check all the 4 check boxes. In Virtual Names tab, create a virtual name of type template by clicking New button and give path to the Template subdirectory of Master directory. Also create virtual names of type schema and dbobject specifying the Schema subdirectory as path for schema and for dbobject path is not required.

               Now test it by typing the following in the address bar of your favorite browser (assuming that you have created the Virtual Directory Name as masterVirDir).

http://localhost/masterVirDir?sql=select+*+from+emp+for+xml+auto&root=EmployeeRecords                      

               Here we are directly querying the virtual directory. The screenshot shows the result of the above query which is returned in XML format.

In the query localhost is the computer name, masterVirDir is the virtual directory we have created, for xml clause is used to tell the database retrieve data as XML document and not as a standard recordset, auto is the XML mode to use which says that the query results must be returned as nested XML elements. Other modes available are raw and explicit. The raw mode is used to say that the results be returned as XML elements with generic identifier row and explicit is used to manually control the shape of the XML document. And root is used to give a name to the document root. This root is not necessary if only one row is returned. The root can also be specified using Select clause. Using the Select clause the above query can be modified as

http://localhost/masterVirDir?sql=select+’<EmployeeRecords>’+select+*+from+emp+for+xml+auto+select+’</EmployeeRecords>’

 Finally, a space is replaced by a +, as URL encodes a space as %20.

When characters like < , > , “, ‘ and &  are specified in SQL queries or in XPath Queries , they must be properly encoded as &lt; , &gt; , &quot; , &apos; and &amp; respectively. This is also referred to as Entity Encoding.

When queries are directly specified in URL then even entity encoded characters needs further encoding. This is referred to as URL Encoding. The characters +, / ,?, % ,# and & are URL encoded as  %2B, %2F, %3F, %25, %23 and %26 respectively.

Stored Procedures can be executed at the URL , using either Execute MyProc or using ODBC call syntax {call+MyProc}. Assuming that we have created a stored procedure(addxml) that adds two numbers and returns the result, we can execute the procedure in URL as follws. The screenshot shows the result

http://localhost/masterVirDir?sql=declare+@result+int+execute+addxml+50,70,@sum=@result+output&root=Sum

or

http://localhost/masterVirDir?sql=declare+@result+int+{call+addxml}+50,70,@sum=@result+output&root=Sum

Notice that the record has identifier as row this is because in the stored procedure we have used FOR XML RAW. If AUTO mode is to be used, then we have to specify the table name in the FROM clause. As we are doing a simple addition we have used RAW.

            In the Next Part we will see how to access Database Objects, how to execute Template Files and how to execute XPath Queries.