URL Accessing in MS SQL Server- II

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

In the first part we saw how to create and query basic queries and also how to execute a simple stored procedure. In this part we will see how to access Database Objects, execute Template Files and XPath Queries.

               First let us see how we can execute Template Files. A Template is a well-formed XML document containing one or more SQL statements and/or XPath queries. Templates provide security, as the templates cannot be edited. For that, create a template file named MyTemplate.xml and type in the code below.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <sql:query>

      select * from emp Emp,dept Dept where Emp.deptno=Dept.deptno and      Emp.deptno=10 For XML AUTO

  </sql:query>

</ROOT>

               Now copy the MyTemplate.xml to \Inetpub\wwwroot\Master\Template directory of your web server and test it by typing the following in the address bar of your web browser.

http://localhost/masterVirDir/Template/MyTemplate.xml

              

                    The element <query> is defined in sql namespace (i.e , xmlns:sql) thus the namespace declaration is required. “sql” is only an alias, we can name it anything. The other elements available are <header>, <param>, <xpath-query>. The screenshot shows the result.

               You can also specify the templates directly   in the URL as

http://localhost/masterVirDir?Template=<ROOT+xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>select+*+from+emp+Emp,dept+Dept+where+Emp.deptno=Dept.deptno+and+Emp.deptno=10+For+XML+AUTO</sql:query></ROOT>

               XML Path Language (XPath) is a graph navigation language. It is used to select a set of nodes from an XML document. XPath language is defined by the W3C as a standard navigation language. XPath Queries are executed against annotated XML-Data Reduced (XDR) schema, which is an XML view of the relational data. A detailed explanation of the language is out of the scope of this article. To query, create a file named MySchema.xml and type in the code below.

<?xml version="1.0" ?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

        xmlns:dt="urn:schemas-microsoft-com:datatypes"

        xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <ElementType name="Emp" sql:relation="emp" >

    <AttributeType name="empno" />

    <AttributeType name="ename" />

    <AttributeType name="deptno" />

    <attribute type="empno" />

    <attribute type="ename" />

    <attribute type="deptno" >

   

    </attribute>

  </ElementType>

  <ElementType name="Dept" sql:relation="dept" sql:key-fields="deptno" >

    <AttributeType name="deptno" />

    <AttributeType name="dname" />

    <attribute type="deptno" />

    <attribute type="dname" />

 

    <element type="Emp" >

             <sql:relationship

                         key-relation="dept"

                         key="deptno"

                         foreign-relation="emp"

                         foreign-key="deptno" />

    </element>

  </ElementType>

</Schema>

               Now copy the MySchema.xml to \Inetpub\wwwroot\Master\Schema directory of your web server and test it by typing the following in the address bar of your web browser. The screenshot shows the result.

http://localhost/masterVirDir/Schema/MySchema.xml/Dept[Emp[@deptno=10]]?root=EmployeeDetails

              

               We can directly access Database Objects as well as shown. This is primarily used for retrieving binary data. More over the XPath query must identify a single row and a single column.

http://localhost/MasterVirDir/dbobject/emp[@deptno=30]/@ename

               We can even write Distributed Queries in the URL as shown. For more on Distributed Queries refer PCQuest April 2004 issue.

http://localhost/MasterVirDir?sql=select +*+from+openquery(mysqlDB,’select+*+from+mysql.title’)+as+title+for+xml+auto&root=BookDetails

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.

Distributed Queries in MS SQL

With a single query from My SQL Server 2000, you can query data from multiple databases. These
are called distributed queries. In this article, we see how to create one

What makes MS SQL Server 2000 differ from other databases? One of
the answers is distributed queries. With distributed queries you can query
data in multiple databases with a single query from MS SQL Server.For instance, imagine a situation where you want data from a table named EMP in one of the databases (say Oracle), a table named DEPT in another database (say SQL Server) and a table named ACCOUNT in yet another database (say MySQL). In such situations we use distributed queries.


What is the need to query different databases? Think you are in a clientserver environment and each computer maintains its own database, in fact a different database.Now,if the client needs to be given data from all the sources but need not know what the underlying databases are, then the system administrator can create a view by querying different databases and give the client permission to access the view. Now, the client can query all the sources as if it’s a single source. Further, they can be used in migration from one or more legacy databases (see screenshot below).

 
Let us see how we can create one. First,you need to create a linked server.A linked server is one, which once created, is used by SQL Server 2000 to implicitly connect to and retrieve data from it. To create a linked server for Oracle, follow the steps.

EXEC sp_addlinkedserver ‘OrclDB’,’Oracle’,’MSDAORA’,’ORACLEDB’

EXEC sp_addlinkedsrvlogin ‘OrclDB’,false,’sa’,’SCOTT’,’TIGER’

In the first step, we created a linked server where first argument refers to the server name, second to server product, third to the server provider name and the fourth to the server data source. The server data source name is the name of the oracle instance or an SQL * Net alias name that points to an Oracle data instance.

In the second step,we created the login credentials that are used by SQL Server to log to a particular database.The second argument specifies whether SQL Server login credentials be used or not. The default value is TRUE, which specifies that SQL Server uses its own username and password to connect with the remote server with remote_userid and remote_pwd arguments ignored .The third argument refers to the SQL Server user account name. The last two arguments refers to the remote_userid and remote_pwd.Now you can query Oracle from MS SQL as

SELECT * FROM OrclDB..SCOTT.EMP

SQL Server uses a four-part name to access a linked server.The syntax for this is

LinkedServerName.Catalog.Schema.ObjectName

For Oracle the syntax slightly differs as:

OracleLinkedServerName..OwnerUserName.ObjectName

For MySQL, the code slightly differs. Since MySQL provider does not have a friendly name (such as MSDAORA, MSDASQL etc) and instance name, we give MSDASQL as the provider name and Null as the data source.

sp_addlinkedserver ‘mysqlDB’, ’MySQL’, ’MSDASQL’, Null, Null, ‘Driver= {MySQL ODBC3.51 Driver}’

That’s all for MySQL and no need to create a login, as they are not needed for MySQL. But observe that we have used two more arguments.The fifth argument here refers to the location,which is again a Null and sixth refers to the provider string. Now to query it we use a rowset function named ‘OpenQuery ()’. This is because the MySQL provider does not support a four-part name as yet.We query it as:

SELECT * FROM OPENQUERY (mysqlDB,’SELECT * FROM MYSQL.ACCOUNT’)

Now to list all the linked servers we have just created along with their details, use the following stored procedure.

EXEC sp_linkedservers

To drop a linked remote server we need to drop all the existing logins to it and then drop the server. To drop all logins to a linked server we use the keyword ‘droplogins’along with sp_dropserver as:

Exec sp_dropserver Linked_ServerName, droplogins

Now that we have created the linked servers, we can query the 3 tables in 3 databases in Query Analyzer as:

SELECT * FROM OrclDB..SCOTT.EMP O, DEPT S,
OPENQUERY (mysqlDB,’SELECT * FROM MYSQL.ACCOUNT’) M
WHERE O.DEPTNO=S.DEPTNO AND S.DNAME=M.DNAME

For those who use MSDE (MS SQL Server Desktop Engine) there is a small (192 kb) but powerful free GUI utility called DBTray that is very simple and easy to use and can be downloaded from www.sulaco.co.za or http://home.global.co. za/~jhorn.

MySQL users can use any GUI utility given on the February 2004 PCQEssential CD, of which SQLyog is a free utility and is more convenient.