Perl Cd Bookshelf [Electronic resources] نسخه متنی

Introducing the XSQL Page Publishing Framework

Building upon the functionality of the XML SQL Utility and the XML parser is the XSQL Servlet. Written in Java, this servlet provides a high-level declarative interface to developers and webmasters to render data across the Internet dynamically and in custom formats. Able to run with the servlet engines of most web servers, the XSQL Servlet delivers the capability to transform a single data source automatically in terms of the client browser and the format best suited to its capabilities and those of the platform.

The XSQL Pages

The heart of the XSQL Servlet is the XSQL page. This page is simply an XML file that contains specific elements to direct the action of the servlet. The following booklist.xsql file is a simple XSQL page:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
select * from Booklist
</xsql:query>

xsql extension with the server. The servlet then hands the page to the XML parser to retrieve its instructions. In this case, it is asked to open a JDBC connection with the alias of demo and submit the query Select * from Booklist. It does this by passing this data to the XML SQL Utility, which performs the query as described previously and returns the result as an XML DOM object. Finally, the servlet passes the stylesheet reference along with the DOM object to the XML parser’s XSLT processor to apply the transformation to HTML for display in the client’s browser.

Figure 7-2: XSQL page process

The essential elements of the file are the <xsql:query> element, which includes the database connection information within its connection attribute, and the SQL query within its body. The connection value is an alias contained within the <connectiondefs> section of the XMLConfig.xml file:

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

This section from the default XMLConfig.xml file shows the declaration of the database connection string and the JDBC driver that will be used by the XML SQL Utility. Because you can have this file reside on the server in a directory that is not accessible to the client, this information remains secure.

Installing the XSQL Servlet

The XSQL Servlet is designed to be quite flexible in its installation and setup. It may be used in any Java 1.2 or greater JVM and with any JDBC-enabled database. Specific testing has been done with JDK 1.2.2, 1.3.1, and 1.4.2 on Windows and several Unix platforms, including Solaris, Linux, and HP-UX. Even though it has previously supported 1.1.8, the 10g libraries no longer support this JDK version, nor does Sun.

Submitting Queries to the XSQL Servlet

The XSQL Servlet is designed to create dynamic web pages from database queries. The XSQL pages can be linked to any web site and can contain one or more queries whose results will replace the respective <xsql:query> section in the page. These results can be further customized through the use of attributes within the <xsql:query> tag. Table 7-1 shows the various options that are available.

Parameters can also be passed into the query from the HTTP request line. By prefixing an @ to the parameter name, the XSQL Servlet will search the HTTP request parameters and then the <xsql:query> attributes to find a match. Once a match is found, a straight lexical substitution is performed. The following is an example of an XSQL page using this function when the HTTP request line is http://localhost/xsql/demo/booksearch.xsql?year=2001:

<?xml version="1.0"?>
<xsql:query xmlns:xsql="urn:oracle-xsql" connection="demo"
SELECT TITLE, AUTHOR, DESCRIPTION FROM BOOKLIST
WHERE YEAR = {@year}
</xsql:query>

Queries that return no rows can also be handled by adding an optional <xsql:no-rows-query> element within the <xsql:query> tags. This allows the user to see a formatted page instead of the raw error. The following is an example that initially tries to retrieve the listings corresponding to the author’s name; failing that, it attempts to do a fuzzy match on the submitted name:

<?xml version="1.0"?>
<xsql:query xmlns:xsql="urn:oracle-xsql" connection="demo"
SELECT TITLE, AUTHOR, DESCRIPTION FROM BOOKLIST
WHERE AUTHOR = UPPER ('{@author}')
<xsql:no-rows-query>
SELECT TITLE, AUTHOR, DESCRIPTION FROM BOOKLIST
WHERE AUTHOR LIKE UPPER ('%{@author}%')
ORDER BY AUTHOR
</xsql:no-rows-query>
</xsql:query>

Transforming the XSQL Output with Stylesheets

The real power of the XSQL Servlet lies in its capability to dynamically transform query results by applying XSL stylesheets. The stylesheet declaration is included in the XSQL file and is applied once the XML output from the query is received. It most commonly transforms query results into HTML, as can be seen in the following example; however, the stylesheet can perform any text-based transformation. The following XSQL page and its associated stylesheet will return the results to the requesting browser as an HTML table:

<?xml version="1.0"?>
<xsql-stylesheet type="text/xsl" href="?>
<xsql:query xmlns:xsql="urn:oracle-xsql" connection="demo"
SELECT Title, Author, Description FROM Booklist
WHERE Author = UPPER ('{@author}')
<xsql:no-rows-query>
SELECT Title, Author, Description FROM Booklist
WHERE Author LIKE UPPER ('%{@author}%')
ORDER BY Author
</xsql:no-rows-query>
</xsql:query>

This is the Totable.xsl stylesheet:

<html xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<head>
<title>Book Listing</title>
</head>
<body>
<table border="1" cellspacing="0">
<tr>
<th><b>Author</b></th>
<th><b>Title</b></th>
<th><b>Description</b></th>
</tr>
<xsl:for-each select="ROWSET/ROW">
<tr>
<td><xsl:value-of select="TITLE"/></td>
<td><xsl:value-of select="AUTHOR"/></td>
<td><xsl:value-of select="DESCRIPTION"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>

Figure 7-3 shows the query result and subsequent transformation.

Figure 7-3: Formatted output from the SearchAuthor.xsql page

Multiple stylesheet declarations are also supported. In such instances, the XSQL Servlet chooses the transformation method by matching the user-agent string in the HTTP header to an optional media attribute in the <xml-stylesheet> element. The match is case-insensitive, and the first match in file order is the one applied. The following example shows how multiple browsers are supported.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="lynx" href="?>
<?xml-stylesheet type="text/xsl" media="msie" href="?>
<?xml-stylesheet type="text/xsl" href="?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
select * from BOOKLIST
</xsql:query>

Note that the last stylesheet declaration has no media attribute. It will be applied to any HTTP requests that do not match the others and thus acts as the default stylesheet. Table 7-2 shows the allowable attributes that can be added to the <xml-stylesheet> element and their functions.

The final way to apply a stylesheet is to pass its URL as an HTTP parameter as follows:

http://localhost/yourdatapage.xsql?param1=
value&xml-stylesheet=yourstyle.xsl

This techniqvue is especially useful for prototyping and development. By replacing the stylesheet URL with none, you ensure that the raw XML document is sent without any stylesheet processing.

Inserting XML Documents with the XSQL Servlet

By leveraging the full capability of the XML SQL Utility, you can set up an XSQL page to insert XML documents into a database. An XML document can be submitted to the OracleXMLSave class of the XML SQL Utility by employing the Action Element, <xsql:insert-request>. As discussed previously, the schema must already exist in the database to save a document. While at first this may be considered a limitation, the XSQL Servlet’s capability to apply a stylesheet to the XML document on submission provides the necessary functionality to filter or transform documents as needed.

Returning to the book listing example presented earlier in the chapter, an XSQL page can be set up to accept book listings not only in the prescribed format of the database but also from virtually any text-based format. For example, consider the case in which a local bookseller would like to list his books on the pages, but his book listings use a different set of tags from the database schema. By creating an XSLT stylesheet and applying it on receipt of his listings, his selections could be accommodated. The following XSQL page could accept the book feed from “Joe’s Books” via HTTP and transform it into the Booklist database schema by applying the joesbooks.xsl stylesheet and then submitting the resulting XML to the OracleXMLSave class for insertion:

<?xml version="1.0">
<xsql:insert-request xmlns:xsql="urn:oracle-xsql"
connection = "demo" table = "BOOKLIST"
transform = "joesbooks.xsl"/>

However, one more item must be set up to make this example function properly. The database generates the BookID column; therefore, this column’s entry must be created for each new insertion. This can be done by setting up a trigger on the Booklist table that generates this ID whenever an insertion is made. The following SQL script will create a new BookID when each new listing is added, assuming you have already created a sequence named bookid_seq:

CREATE TRIGGER booklist_autoid
BEFORE INSERT ON BOOKLIST FOR EACH ROW
BEGIN
SELECT bookid_seq.nextval
INTO :new.BookID
FROM dual;
END;

Other Action Elements that are supported by the XSQL Servlet and their functions are listed in Table 7-3.

Updating Data with the XSQL Servlet

Many applications require that data or documents be updated instead of wholly replaced. In a similar manner to the way the BookID was automatically generated, you can use a form of trigger to provide this functionality.

Oracle makes available an INSTEAD OF trigger that allows a stored procedure in PL/SQL or Java to be called whenever an INSERT of any kind is attempted. These triggers utilize Oracle’s Object Views to be associated with the INSERT.

For example, if you wanted to have the Booklist table be updatable, you could initially search for the unique combination of title and author and, if one is found, perform an UPDATE instead of doing an INSERT. To set this up, you must create an Object View corresponding to the Booklist table. This can be done using the following SQL:

CREATE VIEW Booklistview AS
SELECT * FROM Booklist;

Next, the trigger needs to be created and associated with this view. In this example, PL/SQL is being used, but the job could also be done with a Java stored procedure.

CREATE OR REPLACE TRIGGER insteadOfIns_booklistview
INSTEAD OF INSERT ON booklistview FOR EACH ROW
DECLARE
notThere BOOLEAN := TRUE;
tmp VARCHAR2(1);
CURSOR chk IS SELECT 'x' FROM BOOKLIST
WHERE TITLE = :new.title AND AUTHOR = :new.author;
BEGIN
OPEN chk;
FETCH chk INTO tmp;
notThere := chk%NOTFOUND;
CLOSE chk;
IF notThere THEN
UPDATE INTO Booklist(TITLE, AUTHOR, PUBLISHER, YEAR,
ISBN, DESCRIPTION)
VALUES (:new.title, :new.author, :new.Publisher,
:new.Year, :new.ISBN, :new.Description);
END IF;
END;

Finally, the XSQL file needs to be changed as follows to update the Booklistview instead of the Booklist table:

<?xml version="1.0">
<xsql:insert-request xmlns:xsql="urn:oracle-xsql"
connection = "demo"
table = "Booklistview"
transform = "joesbooks.xsl"/>

As a final note, since the uniqueness is being checked in terms of the combination of the title and author, a unique index can be created to speed up the check and improve performance. The following SQL statement will create the index:

CREATE UNIQUE INDEX booklist_index ON booklist(Title, Author);

While the previous example explained how to perform an update using the trigger functionality of an object view, you can also use the XML SQL Utility update capability from the XSQL Servlet using the following very simple .xsql file:

<?xml version="1.0"?>
<xsql:dml connection="demo" xmlns:xsql="urn:oracle-xsql">
update Booklist set status='S' where BookID = '1';
</xsql:dml>

This example illustrates the simplicity and power of the XSQL’s XML-based interface.

Using JSPs and XSQL Pages

While you can extend XSQL pages with custom action handlers to call Java classes, you can also use JSP pages to include XSQL pages using <jsp:include> or forward to them using <jsp:forward>. For example, this allows you to bind variables into a SQL query that would be run by an XSQL page. Consider the query used earlier with a simple booksearch.xsql page:

<?xml version="1.0"?>
<xsql:query xmlns:xsql="urn:oracle-xsql" connection="demo"
bind-param={@param}
SELECT TITLE, AUTHOR, DESCRIPTION FROM (@table)
</xsql:query>

This XSQL page can be called from the following JSP fragment to pass in the bound parameters:

<jsp:forward page="booksearch.xsql">
<jsp:param name="table" value="BOOKLIST WHERE YEAR = ?"/>
<jsp:param name="YEAR" value="2001"/>
<jsp:param name="param" value="YEAR"/>
</jsp:forward>

When you want to include the results of an XSQL page within a JSP, you can use <jsp:include> in the following manner:

<jsp:include page="booksearch.xsql">.