| |
|
Microsoft's XML support in SQL Server 2000 used to fall a ways short of the mark. However, with the release of new add-on versions of XML support for SQL Server, Microsoft has provided a powerful and often overlooked set of tools that can add scalability and power to your applications. In this article, you'll look at the XML for SQL Server 2000 Web Release 2 beta 2 (WR2) package and discover what it brings to your development toolbox. You'll build a small Visual Basic example using the XML Web release and the XMLHTTP object provided with the MSXML parser to demonstrate how you can use this release to rev up your applications. SQL Server's enhanced XML support has evolved rapidly from its initial introduction. It offers three primary methods for accessing XML data: templates, updategrams, and URL-based queries. Templates are XML files containing one or more queries to be executed against a database. Queries can be generic in nature ("SELECT * FROM tbl_ Categories") or they can accept parameters to limit the scope of the resultset ("SELECT * FROM tbl_Products WHERE Category_ID=@Category_ID"). Templates are secure, because users can't change them unless they have write permissions in the virtual directory. Templates make it much simpler for you to access SQL Server data, because they don't require you to write (or even know) Transact-SQL (T-SQL) statements. You can use templates to return data in one of several defined modes, including a highly customizable format you can use to produce a structured document that's abstracted from its source's structure. You use updategrams for processing operations (INSERTs, UPDATEs, and DELETEs) against one or more tables in a database. Updategrams can perform multistep operations to update tables, and they provide limited transaction support using an "all or nothing" approach. Updategrams use optimistic concurrency when processing updates to data. In other words, if the record you're attempting to update doesn't match what you pass to the updategram, the record isn't updated. This helps ensure that you update only records that match what you tell the updategram they should look like. The third method for accessing XML data is URL-based queries. Queries run through your browser's URL are a great way to test retrieving data from a database, but they aren't advisable in a production environment. Enabling URL queries creates serious security implications, because it provides relatively unrestricted access to run any type of valid query. |
Determine Concurrency Level
One advantage to using updategrams is that you can determine the level of concurrency to use by specifying a certain number of fields in the updategram's "before" section (see the sidebar, "Be Optimistic About Concurrency"). If you're willing to overwrite changes made by other users since you first retrieved your records, you can simply specify the key fields of the record to be updated. On the other hand, if you want to preserve changes made by other users, you can specify all the fields in the "before" section for the record to be updated. If the updategram's "before" section doesn't exactly match the data in the tables to be updated, the change is rejected.
Now that you understand the basics, construct a sample database to work with before you download and install WR2. Create a product catalog database named "Demo," and add several tables to it (see Tables 1, 2, and 3).
You can download WR2 from MSDN . WR2 provides XML extensibility for SQL Server by requiring you to configure a specialized virtual directory on a Web server. The Web server doesn't need to be running on the same machine as SQL Server. Any machine running Internet Information Server (IIS) that has access to SQL Server can be the host for XML-enabled virtual directories. These directories aren't the same type you'd use for serving Web pages. Instead, they use a specialized ISAPI DLL named sqlis2.dll to serve data requests to and from SQL Server. This DLL is the core of the XML functionality. It acts as an intermediary between requests for data and the SQL Server. It parses the XML request, builds the proper SQL statement and sends it to the server, and finally builds the XML to be returned from the query's result.
After you install the release package, your first task is to create a virtual directory to use for serving SQL Server XML requests. I won't go into every detail of the virtual directory tool, but I'll discuss the ones relevant to this example. The installer creates a new program group named Microsoft SQL Server XML Tools. Within this group, you see an item named Configure IIS Support - Web Release 2. Click on it to launch the directory tool. When you launch it, you see a Microsoft Management Console (MMC) similar to the IIS MMC console, except this one manages virtual directories only for use with SQL Server.
You create a new virtual directory by right-clicking on the Default Web Site node in the list and selecting New | Virtual Directory. A new dialog box with six tabs pops up. Enter the name of the new virtual directory and its physical path on the General tab. For the purposes of this example, create a new directory named Demo at the root of your C drive. On the Security tab, select the authentication method you must use for connecting to your SQL Server database and the username and password. You're prompted to re-enter the password for verification before you leave this tab.
On the Data Source tab, you need to specify the name or instance of the SQL Server hosting your database, then select the appropriate database. Note: Each database you want to use the XML Web release for requires its own virtual and physical directory. Supporting more than one database with a single virtual directory isn't allowed.
Use the Crucial Settings Tab
The next tab, Settings, is perhaps the most important in the entire configuration process, because it determines what operations users can perform against your database (see Figure 1 ). Make sure to check all the items as shown in Figure 1. "Allow URL queries" allows users to write a SQL query in the URL of their browser directly. The "Allow template queries" option lets users use XML templates to query the database. "Allow XPath" permits the use of XPath queries against data. "Allow POST" entitles users to send a POST operation that contains valid SQL and/or XML statements to the database. Leave the remaining options on this tab alone for now, because they don't apply to this example.
| |
|
| Figure 1. Set Proper Access Levels
|
On the Virtual Names tab, define the directories where you want your templates, schemas, and URL to execute. The physical directory hosting your virtual directory should have two subdirectories named "templates" and "schemas." Click on the New button to create this structure. First, enter "template" for the name, select "template" as the type, and browse to your virtual directory's "template" subdirectory. Click on New again, enter "schema" for the name, and browse to the virtual directory's "schema" subdirectory. Then click on New one last time. Enter "Demo" as the name and select "dbobject" as the type. However, don't select a path, because the "dbobject" type is always the root of your virtual directory.
Finally, click on the last tab, Advanced, and ensure the bottom two options—"Disable caching of templates" and "Disable caching of mapping schemas"—are both unchecked, because you want them cached in memory for some time for performance reasons. The maximum cache period is 1,800 seconds, so they're reloaded automatically if you haven't used them within the last half hour. Once finished, you can click on OK, and you're done with configuring your virtual directory.
I've given you a high-level overview of how to use templates and updategrams. Now it's time for a more in-depth rundown, beginning with looking at an XML template. Create a simple XML template file named getcategories.xml, and save it in your virtual root's "template" subdirectory. Your template's purpose is to retrieve a list of product categories:
| <root xmlns:sql= 'urn:schemas-microsoft-com:xml-sql'> <Categories> <sql:query> SELECT Category_ID, Category FROM tbl_Categories FOR XML AUTO </sql:query> </Categories> </root> |
The first line of this file describes the namespace required by the ISAPI to process the template file, although you can replace the "root" with any tag name you choose to represent your resulting document's root.
The optional Categories element defines the parent node for all child nodes returned by the query. The <sql:query> tag pair encapsulates the query statement to be executed. The query statement itself shapes the resultset returned. Save this file in your virtual directory's "template" subdirectory, then browse to it in your browser. The output looks similar to this code:
<root xmlns:sql=
"urn:schemas-microsoft-com:xml-sql">
<Categories>
<tbl_Categories Category_ID="1"
Category="Books" />
<tbl_Categories Category_ID="2"
Category="Electronics" />
<tbl_Categories Category_ID="3"
Category="Stuff" />
<tbl_Categories Category_ID="4"
Category="Miscellaneous" />
</Categories>
</root> |
Templates Accept Parameters
However, an XML template can contain more than one query. So if you added another <sql:query> block to the file, the result would be a second hierarchy of results in the template's output. The query being executed doesn't have to be directly against a table, either. You can use a view or stored procedure to shape your query results.
Templates can also accept parameters to limit the scope of resultsets returned. For example, use this code in the template file to return a list of subcategories for a given category:
<root xmlns:sql=
'urn:schemas-microsoft-com:xml-sql'>
<sql:header>
<sql:param name="category_id">
</sql:param>
</sql:header>
<SubCategories>
<sql:query client-side-xml="1">
SELECT
SubCategory_ID,
SubCategory
FROM tbl_SubCategories
WHERE Category_ID = @category_id
FOR XML AUTO
</sql:query>
</SubCategories>
</root> |
You've added something new to the file: the <sql:param> tags. Here you list the parameters the query requires. In this case, your query requires only the category_id parameter. Assuming you've added four records—Fiction, Non-Fiction, Reference, and Technical—to your tbl_SubCategories table associated with your Books category, this template produces this code:
<root xmlns:sql=
"urn:schemas-microsoft-com:xml-sql">
<SubCategories>
<tbl_SubCategories
SubCategory_ID="2"
SubCategory="Fiction" />
<tbl_SubCategories
SubCategory_ID="1"
SubCategory="Non-Fiction" />
<tbl_SubCategories
SubCategory_ID="3"
SubCategory="Reference" />
<tbl_SubCategories
SubCategory_ID="4"
SubCategory="Technical" />
</SubCategories>
</root> |
Note that everything associated with SQL Server virtual directories is case-sensitive. So calling this template with anything in the improper case results in an error, including parameter names being passed. The client-side-xml attribute on the sql:query tag defines whether the client (1) or server (0) will process the XML. If you specify client-side processing, only the query itself is sent to SQL Server, and the client is responsible for applying the clause's FOR XML portion.
Now that you understand templates, it's time to move on to updategrams, which are one of the most powerful features of SQL Server's XML support. They allow you to perform complex operations on data using platform-neutral technology in a disconnected fashion. Look at a simple updategram to see how it works:
<ROOT xmlns:updg="urn:schemas-
microsoft-com:xml-updategram">
<updg:sync>
<updg:after>
<tbl_Categories>
<Category>Gardening
Equipment</Category>
</tbl_Categories>
</updg:after>
</updg:sync>
</ROOT> |
The rules for using updategrams are simple. A <updg:before> block without a corresponding <updg:after> block is a DELETE operation. A <updg:after> block without a corresponding <updg:before> block is an INSERT operation. An operation with both a <updg:before> block and a <updg:after> block is an UPDATE.
The preceding code is an example of a hard-coded updategram, in that it doesn't accept parameters. Here is a more sophisticated example of the same updategram, which not only accepts the name of the category to be added as a parameter, but also returns the new record's IDENTITY:
| <ROOT
xmlns:updg="urn:schemas- microsoft-com:xml-updategram"> <updg:header> <updg:param name="Category"/> </updg:header> <updg:sync > <updg:before> </updg:before> <updg:after updg: returnid="Category_ID"> <Authors updg: at-identity="Category_ID" Category="$Category" /> </updg:after> </updg:sync> </ROOT> |
Here's the output of this template in a browser:
<ROOT xmlns:updg="urn:schemas-
microsoft-com:xml-updategram">
<returned>
<Category_ID>5</Category_ID>
</returnid>
</root> |
As mentioned previously, updategrams can also perform more than one operation at a time. For instance, you can create a new parent/child record pair with an updategram that creates the parent, retrieves the IDENTITY, and uses it with the new child record to create the proper relation between the two.
From here, you'll put together the knowledge you've gained in this article by building a small example to show you can incorporate this technology into a VB6 application. The application you'll create demonstrates some basic concepts, and you can use it as a template for more advanced XML programming.
Build the Code
You've created your database for this example already; now you need to build the VB code. Start by creating a new VB application. Your demo app consists of several forms for manipulating the data in the database using XMLHTTP.
Select References from the Project menu, and add the Microsoft XML 3.0 reference to the project. Click on Projects | Components and add the MS Hierarchical FlexGrid grid to your project. Then construct your menu (see Figure 2).
Next, build the Categories form. You'll make your first use of the SQL Server technologies here. First, add a new form to the project and name it frmCategories. Then add a DataGrid control, a textbox control named txtCategory, and four button controls—cmdNew, cmdSave, cmdDelete, and cmdClose—to the form. Next, wire up the Form_Load event by calling a private subroutine called LoadCategoryGrid. The code for this subroutine calls your getcategories.xml template and loads the grid control with product category data (see Listing 1). When you run the demo, you'll see that this code is fast when loading the grid.
 |
|
| Figure 2. Use Updategrams with
VB |
The next task is adding or updating data using an updategram. Add the code for the Save button (see Listing 2). You call a routine named FormatString, located in the basMain module, which converts "illegal" XML characters to their valid Universal Character Set (UCS) format. Note that everything, including the parameter names, is case-sensitive. The main difference between the Load and Save processes is that in the Save process, you pass parameter data in the body of the POST message rather than as one large URL. This is because there's a limit to the amount of data a URL can contain, and because it makes it a bit simpler when separating the call to the updategram from the data being passed to it.
As you can see, two separate updategrams are used, based on whether you're adding a new category or updating an existing one. This isn't entirely necessary, because you could simply pass in empty values for parameters and use only one updategram. Your example displays a message box with the returned XML from the call to the updategram to show what gets returned. If an error occurs in processing the updategram, a specific error message is returned. You can enhance this article's code by adding a routine to manage this error information.
You've seen the concepts behind XML templates and updategrams, as well as one method for implementing them from VB using XMLHTTP. The growing need for applications running on disparate platforms to communicate makes technologies such as these more and more essential. If you don't want to depend on a proprietary data provider, these technologies in combination can be a powerful alternative to using ADO, and at the same time provide a multipurpose means of accessing and managing SQL Server data.
About the
Authors
Daniel Anderson is a
consultant specializing in Microsoft technologies. He makes his home in Golden,
Colo. Most recently he has focused his interest on .NET, and is the author of
two books on Web Services. E-mail him at dcanderson@uswest.net.
Mary V.
Hooke is a contributing editor for VSM. In addition, she is the
president of Distinctive Development, a firm that develops custom Internet and
intranet applications in the New York City area. Reach her at mhooke@distinctivedevelopment.com.