6.6 Web Access to the Database

   

---

XSQL is a Java servlet for visualizing SQL query results. XSQL combines the power of SQL, XML, and XSLT to publish dynamic Web content based on database information. The servlet is similar to JSP (Java Server Pages) and follows the same mechanisms. Moreover, XSQL is interoperable with JSP. XSQL can be integrated in many popular Web servers like Apache 1.3.9 and higher, Oracle9iAS, or WebLogic 5.1. Thanks to Java, the servlet is easily extensible to one's needs.

6.6.1 The Principle of XSQL

The overall principle of XSQL is similar to JSP: A GET request with a URL of the form http://mycompany.com/myQuery.xsql references a file myQuery.xsql with a specific extension .xsql, denoting an XSQL servlet. The .xsql file mixes XML and SQL. The Web server recognizes the suffix .xsql, which has to be registered at the Web server before, and passes the file content to the XSQL servlet. The servlet processes the file and executes all the embedded SQL statements in the sense of server-side templates. The result, an XML document, is passed back to the caller.

In the easiest case, the XSQL file myQuery.xsql could have the content shown in Listing 6.29.

Listing 6.29 XSQL Example

<?xml version="1.0"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
  SELECT  . . .  FROM  . . .  WHERE  . . .
</xsql:query>

Principally, the file is an ordinary XML document. Queries are embedded by the special tag <xsql:query . . . >, the result of which is inserted at this place instead. xsql is the nickname for the namespace "urn:oracle-xsql" that defines all the XSQL tags.

The attribute connection="demo" fixes a database connection to be used for the query. The name "demo" refers to an entry in an XML configuration file named XSQLConfig.xml, which contains the details about the JDBC connection, as shown in Listing 6.30.

Listing 6.30 JDBC Connection

<connectiondefs>
   <connection name="demo">
      <username>scott</username>
      <password>tiger</password>
      <dburl> jdbc:oracle:thin:@myhost:1521:mydb </dburl>
      <driver> oracle.jdbc.driver.OracleDriver </driver>
      <autocommit> true </autocommit>
   </connection>
   <connection name="lite">
      <username>system</username>
      <password>manager</password>
      <dburl> jdbc:Polite:POlite </dburl>
      <driver> oracle.lite.poljdbc.POLJDBCDriver </driver>
      <autocommit> true </autocommit>
   </connection>
</connectiondefs>

The entry "demo" for <connection name> specifies a JDBC connection to the schema scott/tiger, using the database URL (<dburl>) (i.e., the database mydb on myhost using Oracle's "thin" JDBC driver); the database server is listening on port 1521 for incoming JDBC requests. Several connections can be specified that way. Here, a second connection "lite" is defined.

The result of <xsql:query> is processed in the manner of XSU. That is, the result is embedded in <ROWSET>, whereby each tuple becomes a <ROW> element. Again, object-relational concepts such as object-, reference-, and collection-valued attributes can be used in order to give the document a nested structure, according to the canonical XSU mapping. Using aliases helps to rename attributes to tags and to avoid invalid XML names.

The file content is an arbitrary XML document following the XML rules. It can embed xsql queries in any number, combine different query results, nest them, and so on (see Listing 6.31).

Listing 6.31 XSQL Query Examples

<Tag1>
   <xsql:query  . . . > Query1 </xsql:query>
   <Tag2>
      <xsql:query  . . . > Query2 </xsql:query>
   </Tag2>
   <xsql:query  . . . > Query3 </xsql:query>
</Tag1>

Several queries can be executed and aggregated to a valid XML document that way.

Being a valid XML document, an XSLT style sheet can be registered for the .xsql file, thus further processing the resulting document:

<?xml-stylesheet type="text/xsl" href="myXsl.xsl?>

myXsl.xsl is the style sheet that is applied to the resulting document. Integrating XSLT has several well-known advantages:

• Style sheets are useful if the browser is unable to display XML. Then XSLT can convert XML into HTML, for instance.

• Moreover, it is possible to present the same information in different ways, tailored to the capabilities of a specific client (PDA, browser, etc.). Common formats are HTML, Tiny HTML, HDML (Handheld Device Markup Language) for hand-held devices, WML (Wireless Markup Language) for wireless devices, and SVG (Scalable Vector Graphics).

The <xsql:query> element possesses various attributes such as fetch-size, skip-rows, max-rows, id-attribute (renaming the NUM-attribute), row-element (<ROW> tag), rowset-element (<ROWSET> tag), id-attribute-column (determining the column for setting the NUM attribute), and tag-case, which all can be used to control the naming of tags.

By default, an empty query result returns an XML document <ROWSET/>. However, putting <xsql:no-rows-query> SELECT . . . </xsql:no-rows-query> within an <xsql:query . . . > element, a query is registered as a substitute: If the original query yields an empty result, then <xsql:no-rows-query> is executed instead.

6.6.2 Posting XML Data into the Database

The XSQL approach also allows for storing XML documents in a fine-grained manner according to inverse canonical mapping. That is, the documents are "posted" into the database. The target is a table or a view. The principle is analogous to XSU; however, the table/view, the key, and the columns to be modified are specified by xsql attributes. The same holds for the batch size (commit-batch-size) and the registration of XSLT style sheets (transform). The XSQL tags are <xsql:insert-request>, <xsql:update-request>, and <xsql:delete-request> to insert, update, and delete documents, respectively (see Listing 6.32).

Listing 6.32 XSQL Update Example

<xsql:update-request table="MyCustomer" columns="Name"
key-columns="CNo">
   . . .  XML document  . . .
</xsql:update-request>

As an alternative, XSQL also can directly execute DML (Data Manipulation Language) statements using <xsql:dml> . . . </xsql:dml>. Consequently, data can be inserted, modified, and deleted in the database in an SQL-like manner via the Web by means of XSQL. The attribute commit="yes" sets an autocommit mode:

<xsql:dml commit="yes"> DELETE FROM MyCustomer WHERE . . .
<xsql:dml>

Analogously, an anonymous PL/SQL block can be executed in the database server.

6.6.3 Parameterization

Queries can be parameterized in a simple way. First of all, parameter names must be declared by using the bind-params attribute of <xsql:query> (see Listing 6.33).

Listing 6.33 XSQL bind-params Example

<?xml version="1.0"?>
<xsql:query connection="demo" bind-params="x y"
xmlns:xsql="urn:oracle-xsql">
  SQL-query
</xsql:query>

The value of the bind-params attribute is a space-separated list of parameter names whose left-to-right order indicates the positional bind variable to which its value will be bound in the statement. Here, two parameters x and y are declared. Hence, the SQL query possesses two parameters, which are denoted as "?". The first occurrence of "?" corresponds to the first parameter (i.e., x), and the second one to y. A GET request specifies values for the parameters, thus replacing "?" in the query:

http://mycompany.com/myQuery.xsql?x=1&y=2

Furthermore, parameters can be initialized with default values. In Listing 6.34, the parameter values are x=10 and y=20, unless the GET request provides different values.

Listing 6.34 XSQL Default Values for bind-params Example

<?xml version="1.0"?>
<example x="10" y="20">
 <xsql:query connection="demo" bind-params="x y"
 xmlns:xsql="urn:oracle-xsql">
    SELECT . . .
 </xsql:query>
</example>

The full power of XML is available in XSQL. For example, for any XSQL action element we can substitute the value of any attribute or the text of any contained SQL statement, by using a lexical substitution parameter. This allows one to dynamically set style sheets as href={ @name} .xsl, to parameterize configuration files as connection="{ @name}", or to control the result size or commit behavior by max-rows="{ @name} ". Listing 6.35 shows the principle.

Listing 6.35 XSQL max-rows Example

<example max="10" connection="demo" xlmns:xsql="urn:oracle-xsql">
   <xsql:query max-rows="{@max}"> SELECT . . . </xsql:query>
</example>

max="10" defines a default value for the whole <example> element. The attribute can be referenced by { @max}. The value of max is here used to restrict the query result to a certain number of tuples by means of max-rows. If the xsql-file is invoked without any further parameters, then max=10 is taken; otherwise, the default value is overridden by the actual parameter. Those substitutions { @var} can also be used to replace text in SQL queries: ORDER BY { @var}.

<xsql:include-request-params/> and <xsql:include-request-param name="P"/> are special forms to create an XML representation for all parameters or a certain parameter P, respectively. <xsql:include-request-params/> generates something like that shown in Listing 6.36.

Listing 6.36 XSQL include-request-params Example

<request>
   <parameters>
      <Param1> value </Param1>
      <Param2> value </Param2>
       . . .
   </parameters>
   <session>
      <SessionVariable1> value <SessionVariable1>
       . . .
   </session>
   <cookies>
      <CookieName1> value <CookieName1>
       . . .
   </cookies>
</request>

Param1 and Param2 are parameter names. <xsql:include-request-param name="P"/> returns the value of only one parameter P as value </P>. This is useful to give XSLT style sheets an opportunity to access the parameters?for example, to control XSL processing in a more powerful way. Even session variables and cookies are displayed by tags <session> and <cookies>, respectively. Additional advanced features assign constants or results of queries to session variable and cookies. Other concepts are known from JSP. <xsql:include-xml href="url"/> includes the XML contents of a local or remote resource referenced by the URL: The URL can be absolute, HTTP-based to retrieve XML from another Web site, or relative to include XML from a file on the file system, thus interpreting the URL relative to the XSQL page being processed. <xsql:include-xsql href="url"/> includes the results of one XSQL page into another page. This allows one to easily aggregate content from a page that has already been built.

6.5.9 Servlet Invocations

Besides a GET request via HTTP, .xsql files can be executed in the following ways:

• Invoking a Java program from the command line:

  java oracle.xml.xsql.XSQLCommandLine myQuery.xsql output-file parameter5value . . .
  

  The Java program helps in learning the XSQL servlet by experience. Furthermore, the program is useful in generating XML for static data in order to store it in the file system. Successive accesses refer to the pregenerated data to avoid unnecessary generations. An output file is passed to the command, just as additional parameter values are. Using the special parameter xml-stylesheet, a URL to a style sheet can be registered. The parameter posted-xml provides a URL of an XML resource to be treated as if it were posted as part of the request. user-agent can be used to simulate a particular HTTP User-Agent string so that an appropriate style sheet for that User-Agent type will be selected when processing the page.

• A shell command behaves similarly:

  xsql myQuery.xsql output-file parameter=value . . .
  

• Java programs can use an oracle.xml.xsql.XSQLRequest object in the manner demonstrated in Listing 6.37.

• Using the JSP mechanisms <jsp:include> and <jsp:forward>, XSQL files become part of a JSP application. This lets XSQL and JSP cooperate.

Listing 6.37 XSQLRequest Example

URL url = new URL ("myQuery.xsql");
XSQLRequest req = new XSQLRequest(url);
Hashtable params = new Hashtable(2);
params.put("x", "value1");
params.put("y", "value2");
req.process(params,new PrintWriter(System.out),
                   new PrintWriter(System.err));

The first three types of invocations do not require a servlet installation in the Web server.

---

Top Title TOC Preface P.1 What Is XML? P.2 XML Concepts P.3 XML-Related Technologies P.4 XML Data Management P.5 How This Book Is Organized P.6 Who Should Read This Book P.7 Resources Acknowledgments Chapter 8 Chapter 12 Chapter 13 Chapter 14 Chapter 19 Part I: What Is XML? Chapter 1. Information Modeling with XML 1.1 Introduction 1.2 XML as an Information Domain 1.3 How XML Expresses Information 1.4 Patterns in XML 1.5 Common XML Information-Modeling Pitfalls 1.6 A Very Simple Way to Design XML 1.7 Conclusion Part II: Native XML Databases Chapter 2. Tamino-Software AG's Native XML Server 2.1 Introduction 2.2 Tamino Architecture and APIs 2.3 XML Storage 2.4 Querying XML 2.5 Tools 2.6 Full Database Functionality 2.7 Conclusion Chapter 3. eXist Native XML Database 3.1 Introduction 3.2 Features 3.3 System Architecture Overview 3.4 Getting Started 3.5 Query Language Extensions 3.6 Application Development 3.7 Technical Background 3.8 Conclusion Chapter 4. Embedded XML Databases 4.1 Introduction 4.2 A Primer on Embedded Databases 4.3 Embedded XML Databases 4.4 Building Applications for Embedded XML Databases 4.5 Conclusion Part III: XML and Relational Databases Chapter 5. IBM XML-Enabled Data Management Product Architecture & Technology 5.1 Introduction 5.2 Product and Technology Offering Summaries 5.3 Current Architecture and Technology 5.4 Future Architecture and Technology 5.5 Conclusion Notices Chapter 6. Supporting XML in Oracle9i 6.1 Introduction 6.2 Storing XML as CLOB 6.3 XMLType 6.4 Using XSU for Fine-Grained Storage 6.5 Building XML Documents from Relational Data 6.6 Web Access to the Database 6.7 Special Oracle Features 6.8 Conclusion Chapter 7. XML Support in Microsoft SQL Server 2000 7.1 Introduction 7.2 XML and Relational Data 7.3 XML Access to SQL Server 7.4 Serializing SQL Query Results into XML 7.5 Providing Relational Views over XML 7.6 SQLXML Templates 7.7 Providing XML Views over Relational Data 7.8 Conclusion Chapter 8. A Generic Architecture for Storing XML Documents in a Relational Database 8.1 Introduction 8.2 System Architecture 8.3 The Data Model 8.4 Creating the Database 8.5 Connecting to the Repository 8.6 Uploading XML Documents 8.7 Querying the Repository 8.8 Further Enhancements 8.9 Conclusion Chapter 9. An Object-Relational Approach to Building a High-Performance XML Repository 9.1 Introduction 9.2 Overview of XML Use-Case Scenario 9.3 High-Level System Architecture 9.4 Detailed Design Descriptions 9.5 Conclusion Part IV: Applications of XML Chapter 10. Knowledge Management in Bioinformatics 10.1 Introduction 10.2 A Brief Molecular Biology Background 10.3 Life Sciences Are Turning to XML to Model Their Information 10.4 A Genetic Information Model 10.5 NeoCore XMS* 10.6 Integration of BLAST into NeoCore XMS Conclusion Chapter 11. Case Studies of XML Used with IBM DB2 Universal Database 11.1 Introduction 11.2 Case Study 1: "Our Most Valued Customers Come First" 11.3 Case Study 2: "Improve Cash Flow" 11.4 Conclusion Notices Chapter 12. The Design and Implementation of an Engineering Data Management System Using XML and J2EE 12.1 Introduction 12.2 Background and Requirements 12.3 Overview 12.4 Design Choices 12.5 Future Directions 12.6 Conclusion Chapter 13. Geographical Data Interchange Using XML-Enabled Technology within the GIDB System 13.1 Introduction 13.2 GIDB METOC Data Integration 13.3 GIDB Web Map Service Implementation 13.4 GIDB GML Import and Export 13.5 Conclusion Chapter 14. Space Wide Web by Adapters in Distributed Systems Configuration from Reusable Components 14.1 Introduction 14.2 Advanced Concept Description: The Research Problem 14.3 Integration of Components with Architecture 14.4 Example 14.5 Future Generation NASA Institute for Advanced Concepts, Space Wide Web Research, and Boundaries 14.6 Advanced Concept Development 14.7 Conclusion Chapter 15. XML as a Unifying Framework for Inductive Databases 15.1 Introduction 15.2 Past Work 15.3 The Proposed Data Model: XDM 15.4 Benefits of XDM 15.5 Toward Flexible and Open Systems 15.6 Related Work 15.7 Conclusion Chapter 16. Designing and Managing an XML Warehouse 16.1 Introduction 16.2 Architecture 16.3 Data Warehouse Specification 16.4 Managing the Metadata 16.5 Storage and Management of the Data Warehouse 16.6 DAWAX: A Graphic Tool for the Specification and Management of a Data Warehouse 16.7 Related Work 16.8 Conclusion Part V: Performance and Benchmarks Chapter 17. XML Management System Benchmarks 17.1 Introduction 17.2 Benchmark Specification 17.3 Benchmark Data Set 17.4 Existing Benchmarks for XML 17.5 Conclusion Chapter 18. The Michigan Benchmark: A Micro-Benchmark for XML Query Performance Diagnostics 18.1 Introduction 18.2 Related Work 18.3 Benchmark Data Set 18.4 Benchmark Queries 18.5 Using the Benchmark 18.6 Conclusion Chapter 19. A Comparison of Database Approaches for Storing XML Documents 19.1 Introduction 19.2 Data Models for XML Documents 19.3 Databases for Storing XML Documents 19.4 Benchmarking Specification 19.5 Test Results 19.6 Related Work 19.7 Summary Chapter 20. Performance Analysis between an XML-Enabled Database and a Native XML Database 20.1 Introduction 20.2 Related Work 20.3 Methodology 20.4 Database Design 20.5 Discussion 20.6 Experiment Result 20.7 Conclusion Chapter 21. Conclusion References Contributors Editors Chapter 1: Information Modeling with XML Chapter 2: Tamino-Software AG's Native XML Server Chapter 3: eXist Native XML Database Chapter 4: Embedded XML Databases Chapter 5: IBM XML-Enabled Data Management Product Architecture and Technology Chapter 6: Supporting XML in Oracle9i Chapter 7: XML Support in Microsoft SQL Server 2000 Chapter 8: A Generic Architecture for Storing XML Documents in a Relational Database Chapter 9: An Object-Relational Approach to Building a High-Performance XML Repository Chapter 10: Knowledge Management in Bioinformatics Chapter 11: Case Studies of XML Used with IBM DB2 Universal Database Chapter 12: The Design and Implementation of an Engineering Data Management System Using XML and J2EE Chapter 13: Geographical Data Interchange Using XML-Enabled Technology within the GIDB System Chapter 14: Space Wide Web by Adapters in Distributed Systems Configuration from Reusable Components Chapter 15: XML as a Unifying Framework for Inductive Databases Chapter 16: Designing and Managing an XML Warehouse Chapter 17: XML Management System Benchmarks Chapter 18: The Michigan Benchmark: A Micro-Benchmark for XML Query Performance Diagnostics Chapter 19: A Comparison of Database Approaches for Storing XML Documents Chapter 20: Performance Analysis between an XML-Enabled Database and a Native XML Database Remember the name: eTutorials.org Copyright eTutorials.org 2008-2023. All rights reserved.