This users guide will go through some of the basic steps that one might take in order send an HTTP request to a SQL Server. The request will instruct the SQL server to return XML, which we will parse and display neatly in a grid.RequirementsTo do this, we'll need SQLXML installed on top of SQL Server 2000. We'll also make use of the WebForm and XMLp components included in the IP*Works! .Net Edition. Click here to download ...This users guide will go through some of the basic steps that one might take in order send an HTTP request to a SQL Server. The request will instruct the SQL server to return XML, which we will parse and display neatly in a grid. Requirements To do this, we'll need SQLXML installed on top of SQL Server 2000. We'll also make use of the WebForm and XMLp components included in the IP*Works! .Net Edition. Click here to download a fully functional free trial of this very useful toolkit. For the purposes of this document, we will assume that IP*Works! .Net Edition has already been installed on the readers computer. This toolkit includes sample code for both VB.Net and C#, but here we will be coding in VB.Net.The first thing we need to do is understand how to make the http request to the SQL server. The best resource for this information is of course the help documentation for SQLXML itself. Using this documentation, we learn that we must run a SQLXML configuration application to configure SQLXML with IIS, creating a virtual directory. After that we'll see that the request is sent just like any other HTTP request, on port 80, with a query string containing the SQL query, ie:
"SELECT CustomerId, CompanyName FROM Customer"
http://localhost/sql?sql=select%20CustomerId,%20CompanyName%20from%20Customers%20FOR%20XML%20AUTO
The resulting response is basic unvalidated XML which includes the results of the query. Try typing this in your browser to see the output for yourself (replace http://localhost/sql with your own sql virtual directory).Lets go ahead and get some GUI out of the way. We'll provide only a very few basic inputs for this demo. Textboxes for the URL to use to access the server, a query string, a user name, and password.I have changed the security settings of the IIS virtual directory to use basic authentication rather than windows integrated. NTLM authentication will be supported in the very soon to be released (entering Beta in Spr 2002) IP*Works! .Net SSL Edition.I am using the user name "sa" (system administrator) and the password "" (blank). The "Query" button will simply submit the HTTP request to the server using the WebForm component, receive the information back and parse it to the listview box on the form.[bold]WebForm[/bold]We'll use the WebForm component (rather than HTTP) because it can easily and automatically URL encode the query string information.In order to perform Basic Authentication, set the User and Password properties of the component, ie:
Webform1
.User = txtUser.Text
Webform1.Password = txtPassword.Text
Next, I set the encoding property of the WebForm component. This property allows me to select between query string (get), multipart/form data (post), or url encoded (post). Then we'll add the variables required in the query string using the AddFormVar method (syntax: AddFormVar(varname, varvalue)). As we see from the SQLXML documentation, the name of the variable is "sql" and the value of that variable is the query that we actually want to send. Then we'll add "FOR XML AUTO;" to instruct the server how to format the data (for more info on data formatting see the SQLXML documentation).
Webform1
.Encoding = nsoftware.IPWorks.WebformEncodings.encQueryString Webform1.AddFormVar("sql", txtQuery.Text & " FOR XML AUTO;")
Webform1.SubmitTo(txtServer.Text)
Now the request has been received by the server and the server sends back the XML response. In order to catch this response, we can use either the Webform Transfer event, or the TransferredData property. Right now, we will use the TransferredData property. This property is ready and available as soon as the SubmitTo method returns.[bold]Parsing[/bold]Ok, so we have the XML data - now we will use whatever method to parse this xml and display it in a grid or listview. I am going to demonstrate using the XMLp component to parse this data and split it into a listview. We will dynamically create as many listview columns as there are search terms. For example, in my example we are searching for CustomerId and CompanyName - so there will be two columns in the resulting listview - CustomerId and CompanyName.The XML data that comes back from this request will not be valid, but raw XML with no parent nodes. We'll just add our own parent node quite simply by prepending and appending a root element around the data.
'input the xml data received to the xml parser component
'(add "<root>" element: SQLServer does not provide validated XML)
Xmlp1.InputText = "<root>" & Webform1.TransferredData & "</root>"
lvwOutput.Items.Clear()
lvwOutput.Columns.Clear()
lvwOutput.Columns.Add("Number", 50, HorizontalAlignment.Left)
'go to the first element and see what type of data we are getting,
'set up the appropriate column headers
Dim i As Integer
Dim j As Integer
Xmlp1.XPath = "/root/[1]" '[1] is the first child
For i = 1 To Xmlp1.AttrCount
'For every attribute of this first child, we create a matching listview column
lvwOutput.Columns.Add(Xmlp1.AttrName(i), (lvwOutput.Width - 50) / Xmlp1.AttrCount, HorizontalAlignment.Left)
lvwOutput.Columns(lvwOutput.Columns.Count - 1).Text = Xmlp1.AttrName(i)
Next i
'Now, go back to the root
Xmlp1.XPath = "/root"
'And for every single child of the root, extract the info and add it to the listview
For i = 1 To Xmlp1.XChildren
Xmlp1.XPath = "/root/[" & CStr(i) & "]"
lvwOutput.Items.Add(CStr(i))
For j = 1 To Xmlp1.AttrCount
lvwOutput.Items(lvwOutput.Items.Count - 1).SubItems.Add(Xmlp1.AttrValue(j))
Next j
Next i
There are of course many, many ways to parse the data - and many forms that your data might arrive in depending on the query you use. Use any query you like, parse the data any way you prefer. But this simple demonstration shows one easy way to perform SQL queries over HTTP using XML - after you get past the first steps the possibilities are many.[bold]More Information[/bold]For information about the author, please contact lancer@nsoftware.com.For more information about IP*Works! or the WebForm or XMLp components, please visit /n software.Copyright © 2002, Lance Robinson - All Rights Reserved. For publishing permissions, contact lancer@nsoftware.com. | DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP Articles
More By Lati M  developerWorks - FREE Tools!
|
Discover how Rational tools and best practices for testing can make your job easier. The new Rational Testing eKits provide you with valuable resources – including demos, webcasts, tutorials, and articles – that help you address your specific testing needs across the software lifecycle. Five new eKits are available covering the topics of Requirements and Test Management, Functional Testing, Performance Testing, Code Quality and Embedded Systems, and SOA and Web Services Testing.
FREE! Go There Now!
| | | |
David Barnes, Lead Evangelist for IBM Emerging Internet Technologies will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
| | | |
As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change.
FREE! Go There Now!
| | | |
Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
FREE! Go There Now!
| | | |
Download a free trial version of IBM DB2 9.5 for Linux, UNIX, and Windows. DB2 9 is the result of a five-year development project that transformed traditional (static) database technology into an interactive data server that merges the high performance and ease of use of DB2 with the self-describing benefits of XML.
FREE! Go There Now!
| | | |
Poor Requirements Management capabilities in an Enterprise have been linked to excessive project failures, escalating IT costs, and failure to deliver competitive advantage into the marketplace. Join Brianna M Smith from IBM Rational and learn about how successful organizations align IT and Business stakeholders through collaborative processes and tools for effective requirements management, and how an integrated approach across the IT lifecycle can provide unparalleled visibility and traceability to ensure that project teams are delivering on the business vision by "doing the right things" and "doing things right."
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
FREE! Go There Now!
| | | |
IBM Enterprise Modernization solutions help organizations evolve core IT systems towards modern architectures and technologies—reducing the burden of maintenance and freeing up resources to develop new business requirements and capabilities. With the IBM Enterprise Modernization Sandbox for System z you can evaluate IBM Enterprise Modernization solutions focused on five key areas: Assets, Architectures, Skills, Processes and Infrastructures, and Investment. Each solution is based upon real customer experiences and offers a proven path to get you started with your modernization projects.
FREE! Go There Now!
| | | |
Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads.
FREE! Go There Now!
| | | |
XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
