Using URL Queries

SQL Server 2000 provides tight integration with IIS via an ISAPI filter called sqlisapi.dll that IIS calls on behalf of SQL Server to execute the queries you specify in the text of a URL.

If you haven't already done so, please follow the instructions in the first section of this chapter to create the virtual directory and virtual names you will use to access the Northwind database.

The simplest way to execute T-SQL commands using a URL query is to specify a select statement in the URL. Open Internet Explorer and type the following, substituting your Web server name for < myserver > :

http://< myserver >/NorthwindVdir?sql=SELECT EmployeeID FROM Employees FOR XML AUTO&root=myEmployees

The syntax of this address is as follows :

http://<myserver>/ myVirtualDirectory ?sql=[ sql statements ]&root=[ rootElementName ]

myVirtualDirectory is the name of a virtual directory and the ? character specifies that what follows is a query string ”a list of name-value pairs of the form name=value&name=value. These pairs are the named parameters that are passed to the virtual directory or template file for processing.

In this example, the value of sql is one or more T-SQL statements that return XML using FOR XML . root tells the URL query processor to enclose the resulting document fragment in a root element named rootElementName. The results are shown in Figure 41.10.

Figure 41.10. Using a virtual directory to execute a URL query.

NOTE

Notice how IE automatically converts the spaces in the URL to the string %20 ? This is called URL-encoding and it makes the URL safe for transmission from server to server across the Internet. When you type queries by hand into Internet Explorer, you don't have to worry about manually encoding the URL because IE does it for you. You still have to in code.

Fortunately, when writing client or server-side script (the usual method for calling Web pages in code) the JScript language provides the escape and unescape functions that encode and unencode URLs. In addition, the Active Server Page (ASP) object model's Server object provides the URLEncode method. Therefore, you really don't need to memorize the list of special characters that need to be encoded.

You can also execute stored procedures that return XML by using the EXEC keyword in the sql parameter. To try this, open Query Analyzer and, using the Object Browser, navigate to and expand the Stored Procedures node under the Northwind database. Right-click dbo.CustOrderHist , navigate to Script Object to New Window As, and select Alter (see Figure 41.11).

Figure 41.11. Altering CustOrderHist stored procedure with FOR XML RAW .

Add a FOR XML RAW clause to the select statement (after GROUP BY ). Press F5 to save your changes. Then test this URL in Internet Explorer by typing the following address:

http://<myserver>/NorthwindVdir?sql=exec CustOrderHist @CustomerID='ANTON' &root=OrderHistory

The resulting well- formed XML document is found in Figure 41.12.

Figure 41.12. Executing a stored procedure from a URL query.

Allowing URL queries opens up a potential security hole in your system you might not want. Not to give you any bad ideas, but how easy would it be to run a query like sql=DELETE FROM Customers ? For better security, use XML templates.