Using XML to Return Data from Web Pages
In this section, we walk through a sample Web project that starts with a simple Web page used to capture the necessary query parameters. We'll use the Web page just discussed and walk through an ASP that returns the data in XML, based on the passed parameters. The second application we discuss (in the "Managing Recordsets Using XML Stream Objects" section of the chapter) uses a Visual Basic client/server-like application to launch ASP pages, update rows, and process the errors.
In the preceding example, we discussed how the Submit button launches an ASP that runs a query based on the input parameters. This ASP code returns an XML Recordset that's formatted and converted to HTML with an XSL template. This draws and populates a table filled with the Recordset data on the user's browser. In this case, the "table" generated is a series of boxes, headings, and content—not a database table.
The Server-side Active Server Page
The ASP code does most of the work in our application. It opens an SQL Server connection, runs a query, returns a Recordset, and constructs the XML to be sent back to the browser. It also formats the XML using an XLS script that formats the XML into an HTML table. The XSL style sheet is designed to morph to any XML structure passed, so it would be handy to use it for other generic XML applications.
The ASP code itself is stored as a file on the IIS server with its permissions and properties set up, so IIS recognizes this as an executable page. Without these settings, IIS won't do much with the page, but it will complain that you don't have sufficient permission to execute the ASP. One quick way to get these settings right is to put your ASP pages in a directory with ASP pages created by your Visual InterDev system. Yes, I used Visual InterDev to develop these samples—so sue me.
| Tip | When working with Visual InterDev, you'll see many method arguments being autoinserted for you as you type. That's cool. However, if you want to come up with nonenumerated options, you have to make sure that the application can recognize the ADO constants by adding the following line to the top of your ASP:
<!— #INCLUDE FILE="../include/ADOVBS.INC" —> |
| Note | Without this line, I kept getting an ADO error (0x800A0BB9) without further explanation. I suspect the Open method's adCmdText option setting threw the interpreter. However, once I inserted Option Explicit into the ASP code, the interpreter told me it did not understand adCmdText. Just make sure your project contains the Include file. |
Let's walk through the ASP server-side code. The first section sets up the variables used in the ASP code. We don't have the option to declare the datatypes here—everything's a Variant. The On Error Resume Next statement helps prevent the VBScript interpreter from simply passing an error page back to the client (browser), but I usually comment this out while developing, to help catch the syntax errors. Once you enable On Error Resume Next, all errors are ignored and you'll have to insert code to test for errors after each operation that is likely to cause an error. After that, the code should look very familiar. We build a perfectly ordinary ConnectionString to pass to the Recordset Open later in the ASP.
Note that the ConnectionString (sConn) hard-codes the user name, password, server name, and database into the ASP. Yes, we could have passed these in from the HTM page that launches the ASP and inserted them into the Visual Basic code using the Request.Form("variable name") technique, but this disables connection pooling—unless everybody types in exactly the same thing. But you don't need to worry about hard coding these values because changing them later is easy, and once the page is changed and resaved, all subsequent references use the new settings—instantly.
However, in secure production systems, you probably don't want to leave this username and password hard coded—especially because lots of other people are likely to work on the page and everyone who does will be able to see the password. A workable solution, as I have mentioned before, is to use a UDL file to contain the connect string. You can give read/write access for the UDL to the administrators and read-only access to users under which context this file will be used (usually the System, and IIS's IUSR_machinename and IWAM_machinename local users).
Let's walk through the ASP code. The first thing we do is tell IIS that the page uses VBScript as the script language. After that notation, the ASP code looks pretty much like any other Visual Basic application.
<%@ Language=VBScript%> <% Option Explicit Dim sConn, sSql, rs, Query, MaxRows, stm On error resume next sConn = "Provider=SQLOLEDB.1;Password=xyzzy;User ID=ADOASP;" _ & "Initial Catalog=biblio;Data Source=(local)"
The following code extracts the parameters by name from the Request.Form properties collection. These named properties correspond to the named INPUT controls tagged in the invoking client-side Web page. Each input argument is validated and forced to a default value if it is incorrect. Input validation could have been done on the client, but if business rules change, you have to keep the client-side code in sync, too. But this is not nearly as hard as it is when working with Win32 client/server applications. All you have to do is change the Web page HTML to prevalidate the INPUT values.
MaxRows=Request.Form("txtMaxRows") if MaxRows > "99" or MaxRows < "1" then MaxRows = "50" Query = Request.Form("txtName") if Query ="" then Query = "A%" The following code constructs a SELECT statement to return the desired rowset. No, I don't recommend use of "&" concatenation to build the string, but we have to use it here so my editors don't try to chop it up (it would stretch off the printed page). Next, we create an ADODB Recordset object the hard way—by using the CreateObject method. We open the Recordset using the SQL string and the ConnectionString constructed earlier. Note that we don't create a separate Connection object in this case—but ADO does, behind the scenes.
| Tip | The ASP Connection.Open method will be executed many, many times in quick succession—once by each client that references the ASP. That's why it's important to make sure connection pooling (as we discussed in Chapter 4) is working. |
Actually, to help manage connection errors, you might consider creating a separate Connection object and using it to open the database connection. This way you can trap errors independently of opening the Recordset. Once the Connection is open, you can pass the opened Connection object to the Recordset Open method.
sSQL="Select Top " & MaxRows & " Author, Year_Born " _ & " from authors where author like '" & query & "' " set rs = CreateObject("ADODB.Recordset") rs.Open sSQL, sConn,,,adCmdText | IMHO | Many (okay, almost all) example code samples I found did not set the CommandType option when using the Open method. This is simply lazy coding—it can make a big difference in how quickly a query is executed. |
The next line tests whether the Open method worked by testing the Visual Basic Err object for 0. If there are errors, we simply pass a message back to the browser using the Response.Write method. This logic also bypasses any attempt to return the XML.
if err then Response.Write "Error occurred:" & err.description & "<BR>" Err.Clear else if rs.EOF then Response.Write "No authors found using " & Query & " filter.<BR>" Else
| Tip | When sending messages back to the browser, be sure to add the <BR/> tag at the end of each line. Unlike Visual Basic's Print function, no CRLF sequence is automatically added to the end of your string. |
Now it's time to construct the XML to return to the client. In this case, we persist the Recordset to an in-memory structure that was implemented for the first time in ADO 2.5—the Stream object. In this case, the Stream is persisted as an XML structure based on the Recordset. We then "write" the stream (containing the Recordset in XML) to the client browser as a Response. We follow this with an XSL-formatting style page. Both the Stream and the XSL (file) are sent back using the Response. Write technique, but notice that we set the Response.ContentType to "text/XML" so the browser knows how to deal with the data being passed back by the ASP. The ContentType attribute is formatted as type/subtype where type is the general content category and subtype is the specific content type. For example, "text/HTML," "image/GIF," "image/JPEG," "text/plain," and (for our example) "text/XML" are typical ContentType settings. Let's look at the code to do all of this magic.
| Warning | If you set the ContentType to "text/XML" and you decide to write plain text (or HTML) later, the browser won't show it. |
Set stm = Server.CreateObject("ADODB.Stream") rs.Save stm,adPersistXML Response.ContentType = "text/xml" Response.Write "<?xml:stylesheet type=""text/xsl"" href=_""recordsetxml.xsl""?>" _ & vbCrLf Response.Write stm.ReadText stm.close Set stm = Nothing end if end if %> | Note | The final step (as shown above) concatenates a small XML Style file (XSL) to format the XML data. It's included on the CD, so you can look at it at your leisure. Developed by Andrew Brust[5], this illustrates another OSFA approach. That is, this template script knows how to break down the XML and create a browser-based table from the structure—as long as it's not hierarchical. |
The non-XML Response.Write messages are only sent back if something goes wrong during the Open (Connection or Recordset) and we don't switch to XML ContentType. If you do mix in human-readable messages in a XML document, you'll get strange errors about your Headers not matching. What's really happening is the extra Response.Write strings corrupt the carefully formatted XML generated in the next step.
| IMHO | As far as I'm concerned, error handling should be a top priority in your Web page development.We've all seen "professional" sites throw up meaningless messages when things go wrong. As your application does more and more things, the chances that an ASP page will fail go up dramatically.When the server can't support any more connections, your RAM is exhausted, or your system gets locked out by other operations, you need to provide more than a moronic (default) message—as would be generated by the code shown above. |
The next few lines of code close the VBScript tag and tell the VBScript interpreter to include the ADOVBS.INC file. I expect that some performance might be gained if only the specific constants contained in this (rather large) file were included—especially if you consider that the ASP is interpreted from source code each time it's run. More lines to compile means poorer performance.
<!— #INCLUDE FILE="../include/ADOVBS.INC" —>
| IMHO | I used Visual InterDev to write and test these samples.When compared to Visual Basic and its interactive debugging, statement-by-statement execution, and support windows (such as the Locals window),Visual InterDev comes up really lacking. I found it very hard to figure out what was going on with my code and why it did or did not work. The error messages returned by ADO and IIS were cryptic at best. |
[5]I used a number of references to find examples of ASP coding. These included the book Professional Active Server Pages published by Wrox Press (Homer, Enfield, et al.) and several articles published in Visual Basic Programmer Journal, including a great article "ADO Enhances the Web" by Andrew Brust and William Wen (Progressive Consulting). I also got a lot of help from my friends and coworkers at Microsoft, including John Thorson and Carmen Sarro (and many others), and from my technical editor, Eduardo.
Team-Fly |
EAN: 2147483647
Pages: 106