Release 2 (9.2)
Part Number A96621-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i XML Developer's Kits Guide - XDK Release 2 (9.2) Part Number A96621-01 |
|
This chapter contains the following sections:
The Oracle XSQL Pages publishing framework is an extensible platform for easily publishing XML information in any format you desire. It greatly simplifies combining the power of SQL, XML, and XSLT to publish dynamic web content based on database information.
Using the XSQL publishing framework, anyone familiar with SQL can create and use declarative templates called "XSQL pages" to:
Assembling and transforming information for publishing requires no programming. In fact, most of the common things you will want to do can be easily achieved in a declarative way. However, since the XSQL publishing framework is extensible, if one of the built-in features does not fit your needs, you can easily extend the framework using Java to integrate custom information sources or to perform custom server-side processing.
Using the XSQL Pages framework, the assembly of information to be published is cleanly separated from presentation. This simple architectural detail has profound productivity benefits. It allows you to:
Using server-side templates -- known as "XSQL pages" due to their .xsql extension -- you can publish any information in any format to any device. The XSQL page processor "engine" interprets, caches, and processes the contents of your XSQL page templates. Figure 9-1 illustrates that the core XSQL page processor engine can be "exercised" in four different ways:
<jsp:include> to include a templateXSQLRequest object, the engine's Java API
The same XSQL page templates can be used in any or all of these scenarios. Regardless of the means by which a template is processed, the same basic steps occur to produce a result. The XSQL page processor "engine":
During the transformation step in this process, you can use stylesheets that conform to the W3C XSLT 1.0 standard to transform the assembled "datagram" into document formats like:
XSQL Pages bring this functionality to you by automating the use of underlying Oracle XML components to solve many common cases without resorting to custom programming. However, when only custom programming will do -- as we'll see in the Advanced Topics section of this chapter -- you can augment the framework's built-in actions and serializers to assemble the XSQL "datagrams" from any custom source and serialize the datagrams into any desired format, without having to write an entire publishing framework from scratch.
See Also:
|
XSQL Servlet is provided with Oracle9i and is also available for download from the OTN site: http://otn.oracle.com/tech/xml.
Where indicated, the examples and demos described in this chapter are also available from OTN.
To run the Oracle XSQL Pages publishing framework from the command-line, all you need is a Java VM (1.1.8, 1.2.2, or 1.3). The XSQL Pages framework depends on two underlying components in the Oracle XML Developer's Kit:
Both of their Java archive files must be present in the CLASSPATH where the XSQL pages framework is running. Since most XSQL pages will connect to a database to query information for publishing, the framework also depends on a JDBC driver. Any JDBC driver is supported, but when connecting to Oracle, it's best to use the Oracle JDBC driver (classes12.jar) for maximum functionality and performance.
Lastly, the XSQL publishing engine expects to read its configuration file named XSQLConfig.xml as a Java resource, so you must include the directory where the XSQLConfig.xml file resides in the CLASSPATH as well.
To use the XSQL Pages framework for Web publishing, in addition to the preceding you need a web server that supports Java Servlets. The following is the list of web servers with Servlet capability on which the XSQL Servlet has been tested:
For details on installing, configuring your environment, and running XSQL Servlet and for additional examples and guidelines, see the XSQL Servlet "Release Notes" on OTN at http://otn.oracle.com/tech/xml
In this section, we'll get take a brief look at the most basic features you can exploit in your server-side XSQL page templates:
It is extremely easy to serve database information in XML format over the Web using XSQL pages. For example, let's see how simple it is to serve a real-time XML "datagram" from Oracle9i, of all available flights landing today at JFK airport. Using Oracle JDeveloper, or your favorite text editor, just build an XSQL page template like the one following, and save it in a file named, AvailableFlightsToday.xsql:
<?xml version="1.0"?> <xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql"> SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') AS Due FROM FlightSchedule WHERE TRUNC(ExpectedTime) = TRUNC(SYSDATE) AND Arrived = 'N' AND Destination = ? /* The ? is a bind variable being bound */ ORDER BY ExpectedTime /* to the value of the City parameter */ </xsql:query>
With XSQL Servlet properly installed on your web server, you just need to copy the AvailableFlightsToday.xsql file preceding to a directory under your web server's virtual directory hierarchy. Then you can access the template through a web browser by requesting the URL:
http://yourcompany.com/AvailableFlightsToday.xsql?City=JFK
The results of the query in your XSQL page are materialized automatically as XML and returned to the requestor. This XML-based "datagram" would typically be requested by another server program for processing, but if you are using a browser such as Internet Explorer 5.0, you can directly view the XML result as shown in Figure 9-2.
Let's take a closer look at the "anatomy" of the XSQL page template we used. Notice the XSQL page begins with:
<?xml version="1.0"?>
This is because the XSQL template is itself an XML file (with an *.xsql extension) that contains any mix of static XML content and XSQL "action elements". The AvailableFlightsToday.xsql example preceding contains no static XML elements, and just a single XSQL action element <xsql:query>. It represents the simplest useful XSQL page we can build, one that just contains a single query.
Notice that the first (and in this case, only!) element in the page <xsql:query> includes a special attribute that declares the xsql namespace prefix as a "synonym" for the Oracle XSQL namespace identifier urn:oracle-xsql.
<xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">
This first, outermost element -- known at the "document element" -- also contains a connection attribute whose value "demo" is the name of one of the pre-defined connections in the XSQLConfig.xml configuration file:
<xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">
The details concerning the username, password, database, and JDBC driver that will be used for the "demo" connection are centralized into the configuration file. Setting up these connection definitions is discussed in a later section of this chapter.
Lastly, the <xsql:query> element contains a bind-params attribute that associates the values of parameters in the request by name to bind parameters represented by question marks in the SQL statement contained inside the <xsql:query> tag.
Note that if we wanted to include more than one query on the page, we'll need to invent an XML element of our own creation to "wrap" the other elements like this:
<?xml version="1.0"?> <page connection="demo" xmlns:xsql="urn:oracle-xsql"> <xsql:query bind-params="City"> SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') AS Due FROM FlightSchedule WHERE TRUNC(ExpectedTime) = TRUNC(SYSDATE) AND Arrived = 'N' AND Destination = ? /* The ? is a bind variable being bound */ ORDER BY ExpectedTime /* to the value of the City parameter */ </xsql:query> <!-- Other xsql:query actions can go here inside <page> and </page> --> </page>
Notice in this example that the connection attribute and the xsql namespace declaration always go on the document element, while the bind-params is specific to the <xsql:query> action.
If the canonical <ROWSET> and <ROW> XML output from Figure 9-2 is not the XML format you need, then you can associate an XSLT stylesheet to your XSQL page template to transform this XML "datagram" in the server before returning the information in any alternative format desired.
When exchanging data with another program, typically you will agree in advance with the other party on a specific Document Type Descriptor (DTD) that describes the XML format you will be exchanging. A DTD is in effect, a "schema" definition. It formally defines what XML elements and attributes that a document of that type can have.
Let's assume you are given the flight-list.dtd definition and are told to produce your list of arriving flights in a format compliant with that DTD. You can use a visual tool such as Extensibility's "XML Authority" to browse the structure of the flight-list DTD as shown in Figure 9-3.
This shows that the standard XML formats for Flight Lists are:
<flight-list> element, containing one or more...<flight> elements, having attributes airline and number, each of which contains an...<arrives> element.By associating the following XSLT stylesheet, flight-list.xsl, with the XSQL page, you can change the default <ROWSET> and <ROW> format of your arriving flights into the "industry standard" DTD format.
<!-- XSLT Stylesheet to transform ROWSET/ROW results into flight-list format --> <flight-list xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xsl:version="1.0"> <xsl:for-each select="ROWSET/ROW"> <flight airline="{CARRIER}" number="{FLIGHTNUMBER}"> <arrives><xsl:value-of select="DUE"/></arrives> </flight> </xsl:for-each> </flight-list>
The stylesheet is a template that includes the literal elements that you want produced in the resulting document, such as, <flight-list>, <flight>, and <arrives>, interspersed with special XSLT "actions" that allow you to do the following:
<xsl:for-each><xsl:value-of>{something}Note two things have been added to the top-level <flight-list> element in the stylesheet:
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
This defines the XML Namespace (xmlns) named "xsl" and identifies the uniform resource locator string that uniquely identifies the XSLT specification. Although it looks just like a URL, think of the string http://www.w3.org/1999/XSL/Transform as the "global primary key" for the set of elements that are defined in the XSLT 1.0 specification. Once the namespace is defined, we can then make use of the <xsl:XXX> action elements in our stylesheet to loop and plug values in where necessary.
xsl:version="1.0"
This attribute identifies the document as an XSLT 1.0 stylesheet. A version attribute is required on all XSLT Stylesheets for them to be valid and recognized by an XSLT Processor.
Associate the stylesheet to your XSQL Page by adding an <?xml-stylesheet?> processing instruction to the top of the page as follows:
<?xml version="1.0"?> <?xml-stylesheet type="text/xsl" href="flight-list.xsl"?> <xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql"> SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') AS Due FROM FlightSchedule WHERE TRUNC(ExpectedTime) = TRUNC(SYSDATE) AND Arrived = 'N' AND Destination = ? /* The ? is a bind variable being bound */ ORDER BY ExpectedTime /* to the value of the City parameter */ </xsql:query>
This is the W3C Standard mechanism of associating stylesheets with XML documents (http://www.w3.org/TR/xml-stylesheet). Specifying an associated XSLT stylesheet to the XSQL page causes the requesting program or browser to see the XML in the "industry-standard" format as specified by flight-list.dtd you were given as shown in Figure 9-4.
Text description of the illustration xsql3.gif
To return the same XML information in HTML instead of an alternative XML format, simply use a different XSLT stylesheet. Rather than producing elements like <flight-list> and <flight>, your stylesheet produces HTML elements like <table>, <tr>, and <td> instead. The result of the dynamically queried information would then look like the HTML page shown in Figure 9-5. Instead of returning "raw" XML information, the XSQL Page leverages server-side XSLT transformation to format the information as HTML for delivery to the browser.
Similar to the syntax of the flight-list.xsl stylesheet, the flight-display.xsl stylesheet looks like a template HTML page, with <xsl:for-each>, <xsl:value-of> and attribute value templates like {DUE} to plug in the dynamic values from the underlying <ROWSET> and <ROW> structured XML query results.
<!-- XSLT Stylesheet to transform ROWSET/ROW results into HTML -->
<html xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xsl:version="1.0">
<head><link rel="stylesheet" type="text/css" href="flights.css" /></head>
<body>
<center><table border="0">
<tr><th>Flight</th><th>Arrives</th></tr>
<xsl:for-each select="ROWSET/ROW">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="4">
<tr>
<td><img align="absmiddle" src="images/{CARRIER}.gif"/></td>
<td width="180">
<xsl:value-of select="CARRIER"/>
<xsl:text> </xsl:text>
<xsl:value-of select="FLIGHTNUMBER"/>
</td>
</tr>
</table>
</td>
<td align="center"><xsl:value-of select="DUE"/></td>
</tr>
</xsl:for-each>
</table></center>
</body>
</html>
You can see that by combining the power of:
you can achieve very interesting and useful results quickly. You will see in later sections that what you have seen earlier is just scratching the surface of what you can do using XSQL pages.
You can develop and use XSQL pages in a variety of ways. We start by describing the easiest way to get started, using Oracle JDeveloper, then cover the details you'll need to understand to use XSQL pages in your production environment.
The easiest way to work with XSQL pages during development is to use Oracle JDeveloper. Versions 3.1 and higher of the JDeveloper IDE support color-coded syntax highlighting, XML syntax checking, and easy testing of your XSQL pages. In addition, the JDeveloper 3.2 release supports debugging XSQL pages and adds new wizards to help create XSQL actions.
To create an XSQL page in a JDeveloper project, you can:
To get assistance adding XSQL action elements like <xsql:query> to your XSQL page, place the cursor where you want the new element to go and either:
The XSQL Element wizard takes you through the steps of selecting which XSQL action you want to use, and which attributes you need to provide.
To syntax-check an XSQL page template, you can select Check XML Syntax... at any time from the right-mouse menu in the navigator after selecting the name of the XSQL page you'd like to check. If there are any XML syntax errors, they will appear in the message view and your cursor will be brought to the first one.
To test an XSQL page, simply select the page in the navigator and choose Run from the right-mouse menu. JDeveloper automatically starts up a local Web-to-go web server, properly configured to run XSQL pages, and tests your page by launching your default browser with the appropriate URL to request the page. Once you've run the XSQL page, you can continue to make modifications to it in the IDE -- as well as to any XSLT stylesheets with which it might be associated -- and after saving the files in the IDE you can immediately refresh the browser to observe the effect of the changes.
Using JDeveloper, the "XSQL Runtime" library should be added to your project's library list so that the CLASSPATH is properly setup. The IDE adds this entry automatically when you go through the New Object gallery to create a new XSQL page, but you can also add it manually to the project by selecting Project | Project Properties... and clicking on the "Libraries" tab.
Outside of the JDeveloper environment, you need to make sure that the XSQL page processor engine is properly configured to run. Oracle9i comes with the XSQL Servlet pre-installed to the Oracle HTTP Server that accompanies the database, but using XSQL in any other environment, you'll need to ensure that the Java CLASSPATH is setup correctly.
There are three "entry points" to the XSQL page processor:
oracle.xml.xsql.XSQLServlet, the servlet interfaceoracle.xml.xsql.XSQLCommandLine, the command-line interfaceoracle.xml.xsql.XSQLRequest, the programmatic interfaceSince all three of these interfaces, as well as the core XSQL engine itself, are written in Java, they are very portable and very simple to setup. The only setup requirements are to make sure the appropriate JAR files are in the CLASSPATH of the JavaVM that will be running processing the XSQL Pages. The JAR files include:
oraclexsql.jar, the XSQL page processorxmlparserv2.jar, the Oracle XML Parser for Java v2xsu12.jar, the Oracle XML SQL utilityclasses12.jar, the Oracle JDBC driverIn addition, the directory where XSQL Page Processor's configuration file XSQLConfig.xml resides must also be listed as a directory in the CLASSPATH.
Putting all this together, if you have installed the XSQL distribution in C:\xsql, then your CLASSPATH would appear as follows:
C:\xsql\lib\classes12.classes12.jar;C:\xsql\lib\xmlparserv2.jar;
C:\xsql\lib\xsu12.jar;C:\xsql\lib\oraclexsql.jar;directory_where_XSQLConfig.xml_resides
On Unix, if you extracted the XSQL distribution into your /web directory, the CLASSPATH would appear as follows:
/web/xsql/lib/classes12.jarclasses12.jar:/web/xsql/lib/xmlparserv2.jar:
/web/xsql/lib/xsu12.jar:/web/xsql/lib/oraclexsql.jar:
directory_where_XSQLConfig.xml_resides
To use the XSQL Servlet, one additional setup step is required. You must associate the .xsql file extension with the XSQL Servlet's java class oracle.xml.xsql.XSQLServlet. How you set the CLASSPATH of the web server's servlet environment and how you associate a Servlet with a file extension are done differently for each web server. The XSQL Servlet's Release Notes contain detailed setup information for specific web servers you might want to use with XSQL Pages.
XSQL pages refer to database connections by using a "nickname" for the connection defined in the XSQL configuration file. Connection names are defined in the <connectiondefs> section of XSQLConfig.xml file like this:
<connectiondefs> <connection name="demo"> <username>scott</username> <password>tiger</password> <dburl>jdbc:oracle:thin:@localhost:1521:testDB</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> </connection> </connectiondefs>
For each connection, you can specify five pieces of information:
<username><password><dburl>, the JDBC connection string<driver>, the fully-qualified class name of the JDBC driver to use<autocommit>, optionally forces the autocommit to true or falseIf the <autocommit> element is omitted, then the XSQL page processor will use the JDBC driver's default setting of the AutoCommit flag.
Any number of <connection> elements can be placed in this file to define the connections you need. An individual XSQL page refers to the connection it wants to use by putting a connection="xxx" attribute on the top-level element in the page (also called the "document element").
Often the content of a dynamic page will be based on data that is not frequently changing in your environment. To optimize performance of your web publishing, you can use operating system facilities to schedule offline processing of your XSQL pages, leaving the processed results to be served statically by your web server.
You can process any XSQL page from the command line using the XSQL command-line utility. The syntax is:
$ java oracle.xml.xsql.XSQLCommandLine xsqlpage [outfile] [param1=value1 ...]
If an outfile is specified, the result of processing xsqlpage is written to it, otherwise the result goes to standard out. Any number of parameters can be passed to the XSQL page processor and are available for reference by the XSQL page being processed as part of the request. However, the following parameter names are recognized by the command-line utility and have a pre-defined behavior:
xml-stylesheet=stylesheetURL
Provides the relative or absolute URL for a stylesheet to use for the request. Also can be set to the string none to suppress XSLT stylesheet processing for debugging purposes.
posted-xml=XMLDocumentURL
Provides the relative or absolute URL of an XML resource to treat as if it were posted as part of the request.
useragent=UserAgentString
Used to simulate a particular HTTP User-Agent string from the command line so that an appropriate stylesheet for that User-Agent type will be selected as part of command-line processing of the page.
The ?/xdk/java/xsql/bin directory contains a platform-specific command script to automate invoking the XSQL command-line utility. This script sets up the Java runtime to run oracle.xml.xsql.XSQLCommandLine class.
So far we've only seen a single XSQL action element, the <xsql:query> action. This is by far the most popular action, but it is not the only one that comes built-in to the XSQL Pages framework. We explore the full set of functionality that you can exploit in your XSQL pages in the following sections.
This section provides a list of the core built-in actions, including a brief description of what each action does, and a listing of all required and optional attributes that each supports.
The <xsql:query> action element executes a SQL select statement and includes a canonical XML representation of the query's result set in the data page. This action requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.
The syntax for the action is:
<xsql:query> SELECT Statement </xsql:query>
Any legal SQL select statement is allowed. If the select statement produces no rows, a "fallback" query can be provided by including a nested <xsql:no-rows-query> element like this:
<xsql:query> SELECT Statement <xsql:no-rows-query> SELECT Statement to use if outer query returns no rows </xsql:no-rows-query> </xsql:query>
An <xsql:no-rows-query> element can itself contain nested <xsql:no-rows-query> elements to any level of nesting. The options available on the <xsql:no-rows-query> are identical to those available on the <xsql:query> action element.
By default, the XML produced by a query will reflect the column structure of its resultset, with element names matching the names of the columns. Columns in the result with nested structure like:
produce nested elements that reflect this structure. The result of a typical query containing different types of columns and returning one row might look like this:
<ROWSET> <ROW id="1"> <VARCHARCOL>Value</VARCHARCOL> <NUMBERCOL>12345</NUMBERCOL> <DATECOL>12/10/2001 10:13:22</DATECOL> <OBJECTCOL> <ATTR1>Value</ATTR1> <ATTR2>Value</ATTR2> </OBJECTCOL> <COLLECTIONCOL> <COLLECTIONCOL_ITEM> <ATTR1>Value</ATTR1> <ATTR2>Value</ATTR2> </COLLECTIONCOL_ITEM> <COLLECTIONCOL_ITEM> <ATTR1>Value</ATTR1> <ATTR2>Value</ATTR2> </COLLECTIONCOL_ITEM> </COLLECTIONCOL> <CURSORCOL> <CURSORCOL_ROW> <COL1>Value1</COL1> <COL2>Value2</COL2> </CURSORCOR_ROW> </CURSORCOL> </ROW> </ROWSET>
A <ROW> element will repeat for each row in the result set. Your query can use standard SQL column aliasing to rename the columns in the result, and in doing so effectively rename the XML elements that are produced as well. Note that such column aliasing is required for columns whose names would otherwise be an illegal name for an XML element.
For example, an <xsql:query> action like this:
<xsql:query>SELECT TO_CHAR(hiredate,'DD-MON') FROM EMP</xsql:query>
would produce an error because the default column name for the calculated expression will be an illegal XML element name. You can fix the problem with column aliasing like this:
<xsql:query>SELECT TO_CHAR(hiredate,'DD-MON') as hiredate FROM EMP</xsql:query>
The optional attributes listed in Table 9-1 can be supplied to control various aspects of the data retrieved and the XML produced by the <xsql:query> action.
You can use the <xsql:dml> action to perform any DML or DDL operation, as well as any PL/SQL block. This action requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.
The syntax for the action is:
<xsql:dml> DML Statement or DDL Statement or PL/SQL Block </xsql:dml>
Table 9-2 lists the optional attributes that you can use on the <xsql:dml> action.
The <xsql:ref-cursor-function> action allows you to include the XML results produced by a query whose result set is determined by executing a PL/SQL stored function. This action requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.
By exploiting PL/SQL's dynamic SQL capabilities, the query can be dynamically and/or conditionally constructed by the function before a cursor handle to its result set is returned to the XSQL page processor. As its name implies, the return value of the function being invoked must be of type REF CURSOR.
<xsql:ref-cursor-function> [SCHEMA.][PACKAGE.]FUNCTION_NAME(args); </xsql:ref-cursor-function>
With the exception of the fetch-size attribute, the optional attributes available for the <xsql:ref-cursor-function> action are exactly the same as for the <xsql:query> action that are listed Table 9-1.
For example, consider the PL/SQL package:
CREATE OR REPLACE PACKAGE DynCursor IS TYPE ref_cursor IS REF CURSOR; FUNCTION DynamicQuery(id NUMBER) RETURN ref_cursor; END; CREATE OR REPLACE PACKAGE BODY DynCursor IS FUNCTION DynamicQuery(id NUMBER) RETURN ref_cursor IS the_cursor ref_cursor; BEGIN -- Conditionally return a dynamic query as a REF CURSOR IF id = 1 THEN OPEN the_cursor FOR 'SELECT empno, ename FROM EMP'; -- An EMP Query ELSE OPEN the_cursor FOR 'SELECT dname, deptno FROM DEPT'; -- A DEPT Query END IF; RETURN the_cursor; END; END;
An <xsql:ref-cursor-function> can include the dynamic results of the REF CURSOR returned by this function by doing:
<xsql:ref-cursor-function> DynCursor.DynamicQuery(1); </xsql:ref-cursor-function>
The <xsql:include-owa> action allows you to include XML content that has been generated by a database stored procedure. This action requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.
The stored procedure uses the standard Oracle Web Agent (OWA) packages (HTP and HTF) to "print" the XML tags into the server-side page buffer, then the XSQL page processor fetches, parses, and includes the dynamically-produced XML content in the data page. The stored procedure must generate a well-formed XML page or an appropriate error is displayed.
The syntax for the action is:
<xsql:include-owa> PL/SQL Block invoking a procedure that uses the HTP and/or HTF packages </xsql:include-owa>
Table 9-3 lists the optional attributes supported by this action.
To parameterize the results of any of the preceding actions, you can use SQL bind variables. This allows your XSQL page template to produce different results based on the value of parameters passed in the request. To use a bind variable, simply include a question mark anywhere in the statement where bind variables are allowed by SQL. For example, your <xsql:query> action might contain the select statement:
SELECT s.ticker as "Symbol", s.last_traded_price as "Price" FROM latest_stocks s, customer_portfolio p WHERE p.customer_id = ? AND s.ticker = p.ticker
Using a question mark to create a bind-variable for the customer id. Whenever the SQL statement is executed in the page, parameter values are bound to the bind variable by specifying the bind-params attribute on the action element. Using the example preceding, we could create an XSQL page that binds the indicated bind variables to the value of the custid parameter in the page request like this:
<!-- CustomerPortfolio.xsql --> <portfolio connnection="prod" xmlns:xsql="urn:oracle-xsql"> <xsql:query bind-params="custid"> SELECT s.ticker as "Symbol", s.last_traded_price as "Price" FROM latest_stocks s, customer_portfolio p WHERE p.customer_id = ? AND s.ticker = p.ticker </xsql:query> </portfolio>
The XML data for a particular customer's portfolio can then be requested by passing the customer id parameter in the request like this:
http://yourserver.com/fin/CustomerPortfolio.xsql?custid=1001
The value of the bind-params attribute is a space-delimited list of parameter names whose left-to-right order indicates the positional bind variable to which its value will be bound in the statement. So, if your SQL statement has five question marks, then your bind-params attribute needs a space-delimited list of five parameter names. If the same parameter value needs to be bound to several different occurrences of a question-mark-indicated bind variable, you simply repeat the name of the parameters in the value of the bind-params attribute at the appropriate position. Failure to include exactly as many parameter names in the bind-params attribute as there are question marks in the query, will results in an error when the page is executed.
Bind variables can be used in any action that expects a SQL statement. The following page gives additional examples:
<!-- CustomerPortfolio.xsql --> <portfolio connnection="prod" xmlns:xsql="urn:oracle-xsql"> <xsql:dml commit="yes" bind-params="useridCookie"> BEGIN log_user_hit(?); END; </xsql:dml> <current-prices> <xsql:query bind-params="custid"> SELECT s.ticker as "Symbol", s.last_traded_price as "Price" FROM latest_stocks s, customer_portfolio p WHERE p.customer_id = ? AND s.ticker = p.ticker </xsql:query> </current-prices> <analysis> <xsql:include-owa bind-params="custid userCookie"> BEGIN portfolio_analysis.historical_data(?,5 /* years */, ?); END; </xsql:include-owa> </analysis> </portfolio>
For any XSQL action element, you can substitute the value of any attribute, or the text of any contained SQL statement, by using a lexical substitution parameter. This allows you to parameterize how the actions behave as well as substitute parts of the SQL statements they perform. Lexical substitution parameters are referenced using the syntax {@ParameterName}.
The following example illustrates using two lexical substitution parameters, one which allows the maximum number of rows to be passed in as a parameter, and the other which controls the list of columns to ORDER BY.
<!-- DevOpenBugs.xsql --> <open-bugs connection="demo" xmlns:xsql="urn:oracle-xsql"> <xsql:query max-rows="{@max}" bind-params="dev prod"> SELECT bugno, abstract, status FROM bug_table WHERE programmer_assigned = UPPER(?) AND product_id = ? AND status < 80 ORDER BY {@orderby} </xsql:query> </open-bugs>
This example could then show the XML for a given developer's open bug list by requesting the URL:
http://yourserver.com/bug/DevOpenBugs.xsql?dev=smuench&prod=817
or using the XSQL Command-Line Utility to request:
$ xsql DevOpenBugs.xsql dev=smuench prod=817
We close by noting that lexical parameters can also be used to parameterize the XSQL page connection, as well as parameterize the stylesheet that is used to process the page like this:
<?xml version="1.0"?> <?xml-stylesheet type="text/xsl" href="{@sheet}.xsl"?> <!-- DevOpenBugs.xsql --> <open-bugs connection="{@conn}" xmlns:xsql="urn:oracle-xsql"> <xsql:query max-rows="{@max}" bind-params="dev prod"> SELECT bugno, abstract, status FROM bug_table WHERE programmer_assigned = UPPER(?) AND product_id = ? AND status < 80 ORDER BY {@orderby} </xsql:query> </open-bugs>
It is often convenient to provide a default value for a bind variable or a substitution parameter directly in the page. This allows the page to be parameterized without requiring the requester to explicitly pass in all the values in each request.
To include a default value for a parameter, simply add an XML attribute of the same name as the parameter to the action element, or to any ancestor element. If a value for a given parameter is not included in the request, the XSQL page processor looks for an attribute by the same name on the current action element. If it doesn't find one, it keeps looking for such an attribute on each ancestor element of the current action element until it gets to the document element of the page.
As a simple example, the following page defaults the value of the max parameter to 10 for both <xsql:query> actions in the page:
<example max="10" connection="demo" xmlns:xsql="urn:oracle-xsql"> <xsql:query max-rows="{@max}">SELECT * FROM TABLE1</xsql:query> <xsql:query max-rows="{@max}">SELECT * FROM TABLE2</xsql:query> </example>
This example defaults the first query to have a max of 5, the second query to have a max of 7 and the third query to have a max of 10.
<example max="10" connection="demo" xmlns:xsql="urn:oracle-xsql"> <xsql:query max="5" max-rows="{@max}">SELECT * FROM TABLE1</xsql:query> <xsql:query max="7" max-rows="{@max}">SELECT * FROM TABLE2</xsql:query> <xsql:query max-rows="{@max}">SELECT * FROM TABLE3</xsql:query> </example>
Of course, all of these defaults would be overridden if a value of max is supplied in the request like:
http://yourserver.com/example.xsql?max=3
Bind variables respect the same defaulting rules so a -- not-very-useful, yet educational -- page like this:
<example val="10" connection="demo" xmlns:xsql="urn:oracle-xsql"> <xsql:query tag-case="lower" bind-params="val val val"> SELECT ? as somevalue FROM DUAL WHERE ? = ? </xsql:query> </example>
Would return the XML datagram:
<example> <rowset> <row> <somevalue>10</somevalue> </row> </row> </example>
if the page were requested without any parameters, while a request like:
http://yourserver.com/example.xsql?val=3
Would return:
<example> <rowset> <row> <somevalue>3</somevalue> </row> </row> </example>
To illustrate an important point for bind variables, imagine removing the default value for the val parameter from the page by removing the val attribute like this:
<example connection="demo" xmlns:xsql="urn:oracle-xsql"> <xsql:query tag-case="lower" bind-params="val val val"> SELECT ? as somevalue FROM DUAL WHERE ? = ? </xsql:query> </example>
Now a request for the page without supplying any parameters would return:
<example> <rowset/> </example>
because a bind variable that is bound to a parameter with neither a default value nor a value supplied in the request will be bound to NULL, causing the WHERE clause in our example page preceding to return no rows.
XSQL pages can make use of parameters supplied in the request, as well as page-private parameters whose names and values are determined by actions in the page. If an action encounters a reference to a parameter named param in either a bind-params attribute or in a lexical parameter reference, the value of the param parameter is resolved by using:
For XSQL pages that are processed by the XSQL Servlet over HTTP, two additional HTTP-specific type of parameters are available to be set and referenced. These are HTTP-Session-level variables and HTTP Cookies. For XSQL pages processed through the XSQL Servlet, the parameter value resolution scheme is augmented as follows. The value of a parameter param is resolved by using:
The resolution order is arranged this way so that users cannot supply parameter values in a request to override parameters of the same name that have been set in the HTTP session -- whose lifetime is the duration of the HTTP session and controlled by your web server -- or set as cookies, which can bet set to "live" across browser sessions.
The <xsql:include-request-params> action allows you to include an XML representation of all parameters in the request in your datagram. This is useful if your associated XSLT stylesheet wants to refer to any of the request parameter values by using XPath expressions.
The syntax of the action is:
<xsql:include-request-params/>
The XML included will have the form:
<request> <parameters> <paramname>value1</paramname> <ParamName2>value2</ParamName2> : </parameters> </request>
or the form:
<request> <parameters> <paramname>value1</paramname> <ParamName2>value2</ParamName2> : </parameters> <session> <sessVarName>value1</sessVarName> : </session> <cookies> <cookieName>value1</cookieName> : </cookies> </request>
when processing pages through the XSQL Servlet.
This action has no required or optional attributes.
The <xsql:include-param> action allows you to include an XML representation of a single parameter in your datagram. This is useful if your associated XSLT stylesheet wants to refer to the parameter's value by using an XPath expression.
The syntax of the action is:
<xsql:include-param name="paramname" />
This name attribute is required, and supplies the name of the parameter whose value you would like to include. This action has no optional attributes.
The XML included will have the form:
<paramname>value1</paramname>
The <xsql:include-xml> action includes the XML contents of a local, remote, or database-driven XML resource into your datagram. The resource is specified either by URL or a SQL statement.
The syntax for this action is:
<xsql:include-xml href="URL"/>
or
<xsql:include-xml> SQL select statement selecting a single row containing a single
CLOB or VARCHAR2 column value </xsql:include-xml>
The URL can be an absolute, http-based URL to retrieve XML from another web site, or a relative URL. The href attribute and the SQL statement are mutually exclusive. If one is provided the other is not allowed.
Table 9-5 lists the attributes supported by this action. Attributes in bold are required.
The <xsql:include-posted-xml> action includes the XML document that has been posted in the request into the XSQL page. If an HTML form is posted instead of an XML document, the XML included will be similar to that included by the <xsql:include-request-params> action.
The <xsql:set-page-param> action sets a page-private parameter to a value. The value can be supplied by a combination of static text and other parameter values, or alternatively from the result of a SQL select statement.
The syntax for this action is:
<xsql:set-page-param name="paramname" value="value"/>
or
<xsql:set-page-param name="paramname"> SQL select statement </xsql:set-page-param>
or
<xsql:set-page-param name="paramname" xpath="XPathExpression"/>
If you use the SQL statement option, a single row is fetched from the result set and the parameter is assigned the value of the first column. This usage requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.
As an alternative to providing the value attribute, or a SQL statement, you can supply the xpath attribute to set the page-level parameter to the value of an XPath expression. The XPath expression is evaluated against an XML document or HTML form that has been posted to the XSQL Page Processor. The value of the xpath attribute can be any valid XPath expression, optionally built using XSQL parameters as part of the attribute value like any other XSQL action element.
Once a page-private parameter is set, subsequent action handlers can use this value as a lexical parameter, for example {@po_id}, or as a SQL bind parameter value by referencing its name in the bind-params attribute of any action handler that supports SQL operations.
If you need to set several session parameter values based on the results of a single SQL statement, instead of using the name attribute, you can use the names attribute and supply a space-or-comma-delimited list of one or more session parameter names. For example:
<xsql:set-page-param names="paramname1 paramname2 paramname3"> SELECT expression_or_column1, expression_or_column2, expression_or_column3 FROM table WHERE clause_identifying_a_single_row </xsql:set-page-param>
Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be.
Table 9-5 lists the attributes supported by this action. Attributes in bold are required.
The <xsql:set-session-param> action sets an HTTP session-level parameter to a value. The value of the session-level parameter remains for the lifetime of the current browser user's HTTP session, which is controlled by the web server. The value can be supplied by a combination of static text and other parameter values, or alternatively from the result of a SQL select statement.
Since this feature is specific to Java Servlets, this action is only effective if the XSQL page in which it appears is being processed by the XSQL Servlet. If this action is encountered in an XSQL page being processed by the XSQL command-line utility or the XSQLRequest programmatic API, this action is a no-op.
The syntax for this action is:
<xsql:set-session-param name="paramname" value="value"/>
or
<xsql:set-session-param name="paramname"> SQL select statement </xsql:set-session-param>
If you use the SQL statement option, a single row is fetched from the result set and the parameter is assigned the value of the first column. This use requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.
If you need to set several session parameter values based on the results of a single SQL statement, instead of using the name attribute, you can use the names attribute and supply a space-or-comma-delimited list of one or more session parameter names. For example:
<xsql:set-session-param names="paramname1 paramname2 paramname3"> SELECT expression_or_column1, expression_or_column2, expression_or_column3 FROM table WHERE clause_identifying_a_single_row </xsql:set-session-param>
Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be.
Table 9-6 lists the optional attributes supported by this action.
The <xsql:set-cookie> action sets an HTTP cookie to a value. By default, the value of the cookie remains for the lifetime of the current browser, but its lifetime can be changed by supplying the optional max-age attribute. The value to be assigned to the cookie can be supplied by a combination of static text and other parameter values, or alternatively from the result of a SQL select statement.
Since this feature is specific to the HTTP protocol, this action is only effective if the XSQL page in which it appears is being processed by the XSQL Servlet. If this action is encountered in an XSQL page being processed by the XSQL command-line utility or the XSQLRequest programmatic API, this action is a no-op.
The syntax for this action is:
<xsql:set-cookie name="paramname" value="value"/>
or
<xsql:set-cookie name="paramname"> SQL select statement </xsql:set-cookie>
If you use the SQL statement option, a single row is fetched from the result set and the parameter is assigned the value of the first column. This use requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.
If you need to set several cookie values based on the results of a single SQL statement, instead of using the name attribute, you can use the names attribute and supply a space-or-comma-delimited list of one or more cookie names. For example:
<xsql:set-cookie names="paramname1 paramname2 paramname3"> SELECT expression_or_column1, expression_or_column2, expression_or_column3 FROM table WHERE clause_identifying_a_single_row </xsql:set-cookie>
Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be. The number of columns in the select list must match the number of cookies being set or an error message will result.
Table 9-7 lists the optional attributes supported by this action.
The <xsql:set-stylesheet-param> action sets a top-level XSLT stylesheet parameter to a value. The value can be supplied by a combination of static text and other parameter values, or alternatively from the result of a SQL select statement. The stylesheet parameter will be set on any stylesheet used during the processing of the current page.
The syntax for this action is:
<xsql:set-stylesheet-param name="paramname" value="value"/>
or
<xsql:set-stylesheet-param name="paramname"> SQL select statement </xsql:set-stylesheet-param>
If you use the SQL statement option, a single row is fetched from the result set and the parameter is assigned the value of the first column. This use requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.
If you need to set several stylesheet parameter values based on the results of a single SQL statement, instead of using the name attribute, you can use the names attribute and supply a space-or-comma-delimited list of one or more cookie names. For example:
<xsql:set-stylesheet-param names="paramname1 paramname2 paramname3"> SELECT expression_or_column1, expression_or_column2, expression_or_column3 FROM table WHERE clause_identifying_a_single_row
</xsql:set-stylesheet-param>
Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be.
Table 9-8 lists the optional attributes supported by this action.
The <xsql:include-xsql> action makes it very easy to include the results of one XSQL page into another page. This allows you to easily aggregate content from a page that you've already built and repurpose it. The examples that follow illustrate two of the most common uses of <xsql:include-xsql>.
Assume you have an XSQL page that lists discussion forum categories:
<!-- Categories.xsql --> <xsql:query connection="forum" xmlns:xsql="urn:oracle-xsql"> SELECT name FROM categories ORDER BY name </xsql:query>
You can include the results of this page into a page that lists the ten most recent topics in the current forum like this:
<!-- TopTenTopics.xsql --> <top-ten-topics connection="forum" xmlns:xsql="urn:oracle-xsql"> <topics> <xsql:query max-rows="10"> SELECT subject FROM topics ORDER BY last_modified DESC </xsql:query> </topics> <categories> <xsql:include-xsql href="Categories.xsql"/> </categories> </top-ten-topics>
You can use <xsql:include-xsql> to include an existing page to apply an XSLT stylesheet to it as well. So, if we have two different XSLT stylesheets:
cats-as-html.xsl, which renders the topics in HTML, andcats-as-wml.xsl, which renders the topics in WMLThen one approach for catering to two different types of devices is to create different XSQL pages for each device. We can create:
<?xml version="1.0"?> <!-- HTMLCategories.xsql --> <?xml-stylesheet type="text/xsl" href="cats-as-html.xsl"?> <xsql:include-xsql href="Categories.xsql" xmlns:xsql="urn:oracle-xsql"/>
which aggregates Categories.xsql and applies the cats-as-html.xsl stylesheet, and another page:
<?xml version="1.0"?> <!-- WMLCategories.xsql --> <?xml-stylesheet type="text/xsl" href="cats-as-html.xsl"?> <xsql:include-xsql href="Categories.xsql" xmlns:xsql="urn:oracle-xsql"/>
which aggregates Categories.xsql and applies the cats-as-wml.xsl stylesheet for delivering to wireless devices. In t