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 <
, > , " , '
and & 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.
