Release 1 (9.0.1)
Part Number A88894-01
 Home |
 Solution Area |
 Contents |
 Index |
| Oracle9i Application Developer's Guide - XML Release 1 (9.0.1) Part Number A88894-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 10-1 illustrates that the core XSQL page processor engine can be "exercised" in four different ways:
<jsp:include> to include a template
XSQLRequest 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 and comes bundled with 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.zip) 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 above you'll 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 below, 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 above 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 10-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 above 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 "nickname" 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 10-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 10-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 "morph" 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 10-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 10-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 above 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 interface
oracle.xml.xsql.XSQLCommandLine, the command line interface
oracle.xml.xsql.XSQLRequest, the programmatic interface
Since 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 processor
xmlparserv2.jar, the Oracle XML Parser for Java v2
xsu12.jar, the Oracle XML SQL utility
classes12.zip, the Oracle JDBC driver
In 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.zip;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.zip:/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 false
If 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 10-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 10-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 10-1.
For example, consider the PL/SQL package below:
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 10-3 lists the optional attributes supported by this action.
To parameterize the results of any of the above 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, pameter values are bound to the bind variable by specifying the bind-params attribute on the action element. Using the example above, 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-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. So, if your SQL statement has five question marks, then your bind-params attribute needs a space-separated 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 above 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 or remote resource into your datagram. The resource is specified by URL.
The syntax for this action is:
<xsql:include-xml href="URL"/>
The URL can be an absolute, http-based URL to retrieve XML from another web site, or a relative URL to include XML from a file on the file system. The href attribute is required, and this action has no other optional attributes.
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>
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.
The name attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be.
Table 10-4 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.
The name attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be.
Table 10-5 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 it's lifetime can be changed by supplying the optional max-age attribute. 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 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.
The name attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be.
Table 10-6 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.
The name attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be.
Table 10-7 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 below 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, and
cats-as-wml.xsl, which renders the topics in WML
Then 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 aggegrates 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 this way, we've repurposed the reusable Categories.xsql page content in two different ways.
If the page being aggregated contains an <?xml-stylesheet?> processing instruction, then that stylesheet is applied before the result is aggregated, so using <xsql:include-xsql> you can also easily chain the application of XSLT stylesheets together.
When one XSQL page aggregates another page's content using <xsql:include-xsql> all of the request-level parameters are visible to the "nested" page. For pages processed by the XSQL Servlet, this also includes session-level parameters and cookies, too. As you would expect, none of the aggregating page's page-private parameters are visible to the nested page.
Table 10-8 lists the attributes supported by this action. Required attributes are in bold.
In addition to simplifying the assembly and transformation of XML content, the XSQL Pages framework makes it easy to handle posted XML content as well. Built-in actions simplify the handling of posted information from both XML document and HTML forms, and allow that information to be posted directly into a database table using the underlying facilities of the Oracle XML SQL Utility.
The XML SQL Utility provides the ability to data database inserts, updates, and deletes based on the content of an XML document in "canonical" form with respect to a target table or view. For a given database table, the "canonical" XML form of its data is given by one row of XML output from a SELECT * FROM tablename query against it. Given an XML document in this canonical form, the XML SQL Utility can automate the insert, update, and/or delete for you. By combining the XML SQL Utility with an XSLT transformation, you can transform XML in any format into the canonical format expected by a given table, and then ask the XML SQL Utility to insert, update, delete the resulting "canonical" XML for you.
The following built-in XSQL actions make exploiting this capability easy from within your XSQL pages:
<xsql:insert-request>
Insert the optionally transformed XML document that was posted in the request into a table.Table 10-9 lists the required and optional attributes supported by this action.
<xsql:update-request>
Update the optionally transformed XML document that was posted in the request into a table or view. Table 10-10 lists the required and optional attributes supported by this action.
<xsql:delete-request>
Delete the optionally transformed XML document that was posted in the request from a table or view. Table 10-11 lists the required and optional attributes supported by this action.
<xsql:insert-param>
Insert the optionally transformed XML document that was posted as the value of a request parameter into a table or view. Table 10-12 lists the required and optional attributes supported by this action.
If you target a database view with your insert, then you can create INSTEAD OF INSERT triggers on the view to further automate the handling of the posted information. For example, an INSTEAD OF INSERT trigger on a view could use PL/SQL to check for the existence of a record and intelligently choose whether to do an INSERT or an UPDATE depending on the result of this check.
There are three different ways that the XSQL pages framework can handle posted information.
text/xml".
In this case, you can use the <xsql:insert-request>, <xsql:update-request>, or the <xsql:delete-request> action and the content of the posted XML will be insert, updated, or deleted in the target table as indicated. If you transform the posted XML document using an XSLT transformation, the posted XML document is the source document for this transformation.
In this case, you can use the <xsql:insert-param> action and the content of the posted XML parameter value will be inserted in the target table as indicated. If you transform the posted XML document using an XSLT transformation, the XML document in the parameter value is the source document for this transformation.
application/x-www-form-urlencoded"
In this case, there request does not contain an XML document, but instead an encoded version of the form parameters. However, to make all three of these cases uniform, the XSQL page processor will (on demand) materialize an XML document from the set of form parameters, session variables, and cookies contained in the request. Your XSLT transformation then transforms this dynamically-materialized XML document into canonical form for insert, update, or delete using <xsql:insert>, <xsql:update-request>, or <xsql:delete-request> respectively.
When working with posted HTML forms, the dynamically materialized XML document will have the following form:
<request>
<parameters>
<firstparamname>firstparamvalue</firstparamname>
:
<lastparamname>lastparamvalue</lastparamname>
</parameters>
<session>
<firstparamname>firstsessionparamvalue</firstparamname>
:
<lastparamname>lastsessionparamvalue</lastparamname>
</session>
<cookies>
<firstcookie>firstcookievalue</firstcookiename>
:
<lastcookie>firstcookievalue</lastcookiename>
</cookies>
</request>
If multiple parameters are posted with the same name, then they will automatically be "row-ified" to make subsequent processing easier. This means, for example, that a request which posts or includes the following parameters:
Will create a "row-ified" set of parameters like:
<request>
<parameters>
<row>
<id>101</id>
<name>Steve</name>
</row>
<row>
<id>102</id>
<name>Sita</name>
</row>
<operation>update</operation>
</parameters>
:
</request>
Since you will need to provide an XSLT stylesheet that transforms this materialized XML document containing the request parameters into canonical format for your target table, it might be useful to build yourself an XSQL page like this:
<!--
| ShowRequestDocument.xsql
| Show Materialized XML Document for an HTML Form
+-->
<xsql:include-request-params xmlns:xsql="urn:oracle-xsql"/>
With this page in place, you can temporarily modify your HTML form to post to the ShowRequestDocument.xsql page, and in the browser you will see the "raw" XML for the materialized XML request document which you can save out and use to develop the XSLT transformation.
When you need to perform tasks that are not handled by the built-in action handlers, the XSQL Pages framework allows custom actions to be invoked to do virtually any kind of job you need done as part of page processing. Custom actions can supply arbitrary XML content to the data page and perform arbitrary processing. See Writing Custom XSQL Action Handlers later in this chapter for more details on writing custom action handlers in Java. Here we explore how to make use of a custom action handler, once it's already created.
To invoke a custom action handler, use the built-in <xsql:action> action element. It has a single, required attribute named handler whose value is the fully-qualified Java class name of the action you want to invoke. The class must implement the oracle.xml.xsql.XSQLActionHandler interface. For example:
<xsql:action handler="yourpackage.YourCustomHandler"/>
Any number of additional attribute can be supplied to the handler in the normal way. For example, if the yourpackage.YourCustomHandler is expecting a attributes named param1 and param2, you use the syntax:
<xsql:action handler="yourpackage.YourCustomHandler" param1="xxx" param2="yyy"/>
Some action handlers, perhaps in addition to attributes, may expect text content or element content to appear inside the <xsql:action> element. If this is the case, simply use the expected syntax like:
<xsql:action handler="yourpackage.YourCustomHandler" param1="xxx" param2="yyy">
Some Text Goes Here
</xsql:action>
or this:
<xsql:action handler="yourpackage.YourCustomHandler" param1="xxx" param2="yyy">
<some> <other/> <elements/>
<here/>
</some>
</xsql:action>
Figure 10-13 lists the XSQL Servlet example applications supplied with the software in the ./demo directory.
To set up the demo data do the following:
GRANT EXECUTE ON CTX_DDL TO SCOTT;
GRANT QUERY REWRITE TO SCOTT;
This allows SCOTT to create a functional index that one of the demos uses to perform case-insensitive queries on descriptions of airports.
install.sql in the ./demo directory. This script runs all SQL scripts for all the demos.
install.sql @@insclaim/insclaim.sql @@document/docdemo.sql @@classerr/invalidclasses.sql @@airport/airport.sql @@insertxml/newsstory.sql @@empdept/empdeptobjs.sql
imp scott/tiger file=doyouxml.dmp
to import sample data for the "Do You XML? Site" demo.
If the current XSQL page being requested allows it, you can supply an XSLT stylesheet URL in the request to override the default stylesheet that would have been used -- or to apply a stylesheet where none would have been applied by default. The client-initiated stylesheet URL is provided by supplying the xml-stylesheet parameter as part of the request. The valid values for this parameter are:
This last value, xml-stylesheet=none, is particularly useful during development to temporarily "short-circuit" the XSLT stylesheet processing to see what XML datagram your stylesheet is actually seeing. This can help understand why a stylesheet might not be producing the expected results.
Client-override of stylesheets for an XSQL page can be disallowed either by:
If client-override of stylesheets has been globally disabled by default in the XSQLConfig.xml configuration file, any page can still enable client-override explicitly by including an allow-client-style="yes" attribute on the document element of that page.
Setting the content type of the information you serve is very important. It allows the requesting client to correctly interpret the information that you send back.If your stylesheet uses an <xsl:output> element, the XSQL Page Processor infers the media type and encoding of the returned document from the media-type and encoding attributes of <xsl:output>.
For example, the following stylesheet uses the media-type="application/vnd.ms-excel" attribute on <xsl:output> to transform the results of an XSQL page containing a standard query over the emp table into Microsoft Excel spreadsheet format.
<?xml version="1.0"?>
<!-- empToExcel.xsl -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" media-type="application/vnd.ms-excel"/>
<xsl:template match="/">
<html>
<table>
<tr><th>EMPNO</th><th>ENAME</th><th>SAL</th></tr>
<xsl:for-each select="ROWSET/ROW">
<tr>
<td><xsl:value-of select="EMPNO"/></td>
<td><xsl:value-of select="ENAME"/></td>
<td><xsl:value-of select="SAL"/></td>
</tr>
</xsl:for-each>
</table>
</html>
</xsl:template>
</xsl:stylesheet>
An XSQL page that makes use of this stylesheet looks like this:
<?xml version="1.0"?>
<?xml-stylesheet href="empToExcel.xsl" type="text/xsl"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
select * from emp order by sal desc
</xsql:query>
As we've seen, if you include an <?xml-stylesheet?> processing instruction at the top of your .xsql file, it will be considered by the XSQL page processor for use in transforming the resulting XML datagram. For example:
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="emp.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
<xsql:query>
SELECT * FROM emp ORDER BY sal DESC
</xsql:query>
</page>
would use the emp.xsl stylesheet to transform the results of the EMP query in the server tier, before returning the response to the requestor. The stylesheet is accessed by the relative or absolute URL provided in the href pseudo-attribute on the <?xml-stylesheet?> processing instruction.
By including one or more parameter references in the value of the href pseudo-attribute, you can dynamically determine the name of the stylesheet. For example, this page selects the name of the stylesheet to use from a table by assigning the value of a page-private parameter using a query.
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="{@sheet}.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
<xsql:set-page-param bind-params="UserCookie" name="sheet">
SELECT stylesheet_name
FROM user_prefs
WHERE username = ?
</xsql:set-page-param>
<xsql:query>
SELECT * FROM emp ORDER BY sal DESC
</xsql:query>
</page>
Some browsers like Microsoft's Internet Explorer 5.0 and higher support processing XSLT stylesheets in the client. These browsers recognize the stylesheet to be processed for an XML document in the same way that a server-side XSQL page does, using an <?xml-stylesheet?> processing instruction. This is not a coincidence. The use of <?xml-stylesheet?> for this purpose is part of the W3C Recommendation from June 29, 1999 entitled "Associating Stylesheets with XML Documents, Version 1.0"
By default, the XSQL page processor performs XSLT transformations in the server, however by adding on additional pseudo-attribute to your <?xml-stylesheet?> processing instruction in your XSQL page -- client="yes" -- the page processor will defer the XSLT processing to the client by serving the XML datagram "raw", with the current <?xml-stylesheet?> at the top of the document.
One important point to note is that Internet Explorer 5.0 shipped in late 1998, containing an implementation of the XSL stylesheet language that conformed to a December 1998 Working Draft of the standard. The XSLT 1.0 Recommendation that finally emerged in November of 1999 had significant changes from the earlier working draft version on which IE5 is based. This means that IE5 browsers understand a different "dialect" of XSLT than all other XSLT processors -- like the Oracle XSLT processor -- which implement the XSLT 1.0 Recommendation syntax.
Toward the end of 2000, Microsoft released version 3.0 of their MSXML components as a Web-downloadable release. This latest version does implement the XSLT 1.0 standard, however in order for it to be used as the XSLT processor inside the IE5 browser, the user must go through additional installation steps. Unfortunately there is no way for a server to detect that the IE5 browser has installed the latest XSLT components, so until the Internet Explorer 6.0 release emerges -- which will contain the latest components by default and which will send a detectably different User-Agent string containing the 6.0 version number -- stylesheets delivered for client processing to IE5 browsers should use the earlier IE5-"flavor" of XSL.
What we need is a way to request that an XSQL page use different stylesheets depending on the User-Agent making the request. Luckily, the XSQL Pages framework makes this easy and we learn how in the next section.
You can include multiple <?xml-stylesheet?> processing instructions at the top of an XSQL page and any of them can contain an optional media pseudo-attribute. If specified, the media pseudo-attribute's value is compared case-insensitively with the value of the HTTP header's User-Agent string. If the value of the media pseudo-attribute matches a part of the User-Agent string, then the processor selects the current <?xml-stylesheet?> processing instruction for use, otherwise it ignores it and continues looking. The first matching processing instruction in document order will be used. A processing instruction without a media pseudo-attribute matches all user agents so it can be used as the fallback/default.
For example, the following processing instructions at the top of an .xsql file...
<?xml version="1.0"?> <?xml-stylesheet type="text/xsl" media="lynx" href="doyouxml-lynx.xsl" ?> <?xml-stylesheet type="text/xsl" media="msie 5" href="doyouxml-ie.xsl" ?> <?xml-stylesheet type="text/xsl" href="doyouxml.xsl" ?> <page xmlns:xsql="urn:oracle-xsql" connection="demo"> :
will use doyouxml-lynx.xsl for Lynx browsers, doyouxml-ie.xsl for Internet Explorer 5.0 or 5.5 browsers, and doyouxml.xsl for all others.
Table 10-14 summarizes all of the supported pseudo-attributes allowed on the <?xml-stylesheet?> processing instruction.
Use the XSQLConfig.xml File to tune your XSQL pages environment. Table 10-15 defines all of the parameters that can be set.
Using the XSQL Pages framework's support for custom serializers, the oracle.xml.xsql.serializers.XSQLFOPSerializer is provided for integrating with the Apache FOP processor (http://xml.apache.org/fop). The FOP processor renders a PDF document from an XML document containing XSL Formatting Objects (http://www.w3.org/TR/xsl).
For example, given the following XSLT stylesheet, EmpTableFO.xsl:
<!-- EmpTableFO.xsl -->
<fo:root xmlns:fo="http://www.w3.org/1999/XSL/Format" xsl:version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<!-- defines the layout master -->
<fo:layout-master-set>
<fo:simple-page-master master-name="first"
page-height="29.7cm"
page-width="21cm"
margin-top="1cm"
margin-bottom="2cm"
margin-left="2.5cm"
margin-right="2.5cm">
<fo:region-body margin-top="3cm"/>
</fo:simple-page-master>
</fo:layout-master-set>
<!-- starts actual layout -->
<fo:page-sequence master-name="first">
<fo:flow flow-name="xsl-region-body">
<fo:block font-size="24pt" font-family="Garamond" line-height="24pt"
space-after.optimum="3pt" font-weight="bold"
start-indent="15pt">
Total of All Salaries is $<xsl:value-of select="sum(/ROWSET/ROW/SAL)"/>
</fo:block>
<!-- Here starts the table -->
<fo:block border-width="2pt">
<fo:table>
<fo:table-column column-width="4cm"/>
<fo:table-column column-width="4cm"/>
<fo:table-body font-size="10pt" font-family="sans-serif">
<xsl:for-each select="ROWSET/ROW">
<fo:table-row line-height="12pt">
<fo:table-cell>
<fo:block><xsl:value-of select="ENAME"/></fo:block>
</fo:table-cell>
<fo:table-cell>
<fo:block><xsl:value-of select="SAL"/></fo:block>
</fo:table-cell>
</fo:table-row>
</xsl:for-each>
</fo:table-body>
</fo:table>
</fo:block>
</fo:flow>
</fo:page-sequence>
</fo:root>
you can format the results of a query against the EMP table using the supplied FOP serializer (pre-defined in XSQLConfig.xml) with an XSQL page like:
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="emptablefo.xsl" serializer="FOP"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
SELECT ENAME, SAL FROM EMP
ORDER BY SAL asc
</xsql:query>
The XSQLRequest class, allows you to utilize the XSQL page processor "engine" from within your own custom Java programs. Using the API is simple. You construct an instance of XSQLRequest, passing the XSQL page to be processed into the constructor as one of the following:
Then you invoke one of the following methods to process the page:
If you want to use the built-in XSQL Connection Manager -- which implements JDBC connection pooling based on XSQLConfig.xml-based connection definitions -- then the XSQL page is all you need to pass to the constructor. Optionally, you can pass in a custom implementation for the XSQLConnectionManagerFactory interface as well, if you want to use your own connection manager implementation.
Note that the ability to pass the XSQL page to be processed as an in-memory XML Document object means that you can dynamically generate any valid XSQL page for processing using any means necessary, then pass the page to the XSQL engine for evaluation.
When processing a page, there are two additional things you may want to do as part of the request:
You accomplish this by passing any object that implements the Dictionary interface, to the process() or processToXML() methods. Passing a HashTable containing the parameters is one popular approach.
You can do this using the setPostedDocument() method on the XSQLRequest object.
Here is a simple example of processing a page using XSQLRequest:
import oracle.xml.xsql.XSQLRequest;
import java.util.Hashtable;
import java.io.PrintWriter;
import java.net.URL;
public class XSQLRequestSample {
public static void main( String[] args) throws Exception {
// Construct the URL of the XSQL Page
URL pageUrl = new URL("file:///C:/foo/bar.xsql");
// Construct a new XSQL Page request
XSQLRequest req = new XSQLRequest(pageUrl);
// Setup a Hashtable of named parameters to pass to the request
Hashtable params = new Hashtable(3);
params.put("param1","value1");
params.put("param2","value2");
/* If needed, treat an existing, in-memory XMLDocument as if
** it were posted to the XSQL Page as part of the request
req.setPostedDocument(myXMLDocument);
**
*/
// Process the page, passing the parameters and writing the output
// to standard out.
req.process(params,new PrintWriter(System.out)
,new PrintWriter(System.err));
}
}
When the task at hand requires custom processing, and none of the built-in actions does exactly what you need, you can augment your repertoire by writing your own actions that any of your XSQL pages can use.
The XSQL page processor at its very core is an engine that processes XML documents containing "action elements". The page processor engine is written to support any action that implements the XSQLActionHandler interface. All of the built-in actions implement this interface.
The XSQL Page Processor processes the actions in a page in the following way. For each action in the page, the engine:
init(Element actionElt,XSQLPageRequest context)
handleAction (Node result)
For built-in actions, the engine knows the mapping of XSQL action element name to the Java class that implements the action's handler. Table 10-16 lists that mapping explicitly for your reference. For user-defined actions, you use the built-in:
<xsql:action handler="fully.qualified.Classname" ... />
action whose handler attribute provides the fully-qualified name of the Java class that implements the custom action handler.
To create a custom Action Handler, you need to provide a class that implements the oracle.xml.xsql.XSQLActionHandler interface. Most custom action handlers should extend oracle.xml.xsql.XSQLActionHandlerImpl that provides a default implementation of the init() method and offers a set of useful helper methods that will prove very useful.
When an action handler's handleAction method is invoked by the XSQL page processor, the action implementation gets passed the root node of a DOM Document Fragment to which the action handler should append any dynamically created XML content that should be returned to the page.
The XSQL Page Processor conceptually replaces the action element in the XSQL page template with the content of this Document Fragment. It is completely legal for an Action Handler to append nothing to this document fragment, if it has no XML content to add to the page.
While writing you custom action handlers, several methods on the XSQLActionHandlerImpl class are worth noting because they make your life a lot easier. Table 10-17 lists the methods that will likely come in handy for you.
The following example shows a custom action handler MyIncludeXSQLHandler that leverages one of the built-in action handlers and then uses arbitrary Java code to modify the resulting XML fragment returned by that handler before appending its result to the XSQL page:
import oracle.xml.xsql.*; import oracle.xml.xsql.actions.XSQLIncludeXSQLHandler; import org.w3c.dom.*; import java.sql.SQLException; public class MyIncludeXSQLHandler extends XSQLActionHandlerImpl { XSQLActionHandler nestedHandler = null; public void init(XSQLPageRequest req, Element action) { super.init(req, action); // Create an instance of an XSQLIncludeXSQLHandler // and init() the handler by passing the current request/action // This assumes the XSQLIncludeXSQLHandler will pick up its // href="xxx.xsql" attribute from the current action element. nestedHandler = new XSQLIncludeXSQLHandler(); nestedHandler.init(req,action); } public void handleAction(Node result) throws SQLException { DocumentFragment df=result.getOwnerDocument().createDocumentFragment(); nestedHandler.handleAction(df); // Custom Java code here can work on the returned document fragment // before appending the final, modified document to the result node. // For example, add an attribute to the first child Element e = (Element)df.getFirstChild(); if (e != null) { e.setAttribute("ExtraAttribute","SomeValue"); } result.appendChild(df); } }
If you create custom action handlers that need to work differently based on whether the page is being requested through the XSQL Servlet, the XSQL Command Line Utility, or programmatically through the XSQLRequest class, then in your Action Handler implementation you can call getPageRequest() to get a reference to the XSQLPageRequest interface for the current page request. By calling getRequestType() on the XSQLPageRequest object, you can see if the request is coming from the "Servlet", "Command Line", or "Programmatic" routes respectively. If the return value is "Servlet", then you can get access to the HTTP Servlet's request, response, and servlet context objects by doing:
XSQLServletPageRequest xspr = (XSQLServletPageRequest)getPageRequest();
if (xspr.getRequestType().equals("Servlet")) {
HttpServletRequest req = xspr.getHttpServletRequest();
HttpServletResponse resp = xspr.getHttpServletResponse();
ServletContext cont = xspr.getServletContext();
// do something fun here with req, resp, or cont however
// writing to the response directly from a handler will
// produce unexpected results. Allow the XSQL Servlet
// or your custom Serializer to write to the servlet's
// response output stream at the write moment later when all
// action elements have been processed.
}
You can provide a user-defined serializer class to programmatically control how the final XSQL datapage's XML document should be serialized to a text or binary stream. A user-defined serializer must implement the oracle.xml.xsql.XSQLDocumentSerializer interface which comprises the single method:
void serialize(org.w3c.dom.Document doc, XSQLPageRequest env) throws Throwable;
In this release, DOM-based serializers are supported. A future release may support SAX2-based serializers as well. A custom serializer class is expected to perform the following tasks in the correct order:
PrintWriter (or OutputStream).
You set the type by calling setContentType() on the XSQLPageRequest that is passed to your serializer. When setting the content type, you can either set just a MIME type like this:
env.setContentType("text/html");
or a MIME type with an explicit output encoding character set like this:
env.setContentType("text/html;charset=Shift_JIS");
getWriter() or getOutputStream() -- but not both! -- on the XSQLPageRequest to get the appropriate PrintWriter or OutputStream respectively to use for serializing the content.
For example, the following custom serializer illustrates a simple implementation which simply serializes an HTML document containing the name of the document element of the current XSQL data page:
package oracle.xml.xsql.serializers;
import org.w3c.dom.Document;
import java.io.PrintWriter;
import oracle.xml.xsql.*;
public class XSQLSampleSerializer implements XSQLDocumentSerializer {
public void serialize(Document doc, XSQLPageRequest env) throws Throwable {
String encoding = env.getPageEncoding(); // Use same encoding as XSQL page
// template. Set to specific
// encoding if necessary
String mimeType = "text/html"; // Set this to the appropriate content type
// (1) Set content type using the setContentType on the XSQLPageRequest
if (encoding != null && !encoding.equals("")) {
env.setContentType(mimeType+";charset="+encoding);
}
else {
env.setContentType(mimeType);
}
// (2) Get the output writer from the XSQLPageRequest
PrintWriter e = env.getWriter();
// (3) Serialize the document to the writer
e.println("<html>Document element is <b>"+
doc.getDocumentElement().getNodeName()+
"</b></html>");
}
}
There are two ways to use a custom serializer, depending on whether you need to first perform an XSLT transformation before serializing or not. To perform an XSLT transformation before using a custom serializer, simply add the serializer="java:fully.qualified.ClassName" in the <?xml-stylesheet?> processing instruction at the top of your page like this:
<?xml version="1.0?>
<?xml-stylesheet type="text/xsl" href="mystyle.xsl"
serializer="java:my.pkg.MySerializer"?>
If you only need the custom serializer, simply leave out the type and href attributes like this:
<?xml version="1.0?>
<?xml-stylesheet serializer="java:my.pkg.MySerializer"?>
You can also assign a short nickname to your custom serializers in the <serializerdefs> section of the XSQLConfig.xml file and then use the nickname (case-sensitive) in the serializer attribute instead to save typing. For example, if you have the following in XSQLConfig.xml:
<XSQLConfig>
<!-- etc. -->
<serializerdefs>
<serializer>
<name>Sample</name>
<class>oracle.xml.xsql.serializers.XSQLSampleSerializer</class>
</serializer>
<serializer>
<name>FOP</name>
<class>oracle.xml.xsql.serializers.XSQLFOPSerializer</class>
</serializer>
</serializerdefs>
</XSQLConfig>
then you can use the nicknames "Sample" and/or "FOP" as shown in the following examples:
<?xml-stylesheet type="text/xsl" href="emp-to-xslfo.xsl" serializer="FOP"?>
or
<?xml-stylesheet serializer="Sample"?>
The XSQLPageRequest interface supports both a getWriter() and a getOutputStream() method. Custom serializers can call getOutputStream() to return an OutputStream instance into which binary data (like a dynamically produced GIF image, for example) can be serialized. Using the XSQL Servlet, writing to this output stream results in writing the binary information to the servlet's output stream.
For example, the following serializer illustrates an example of writing out a dynamic GIF image. In this example the GIF image is a static little "ok" icon, but it shows the basic technique that a more sophisticated image serializer would need to use:
package oracle.xml.xsql.serializers;
import org.w3c.dom.Document;
import java.io.*;
import oracle.xml.xsql.*;
public class XSQLSampleImageSerializer implements XSQLDocumentSerializer {
// Byte array representing a small "ok" GIF image
private static byte[] okGif =
{(byte)0x47,(byte)0x49,(byte)0x46,(byte)0x38,
(byte)0x39,(byte)0x61,(byte)0xB,(byte)0x0,
(byte)0x9,(byte)0x0,(byte)0xFFFFFF80,(byte)0x0,
(byte)0x0,(byte)0x0,(byte)0x0,(byte)0x0,
(byte)0xFFFFFFFF,(byte)0xFFFFFFFF,(byte)0xFFFFFFFF,(byte)0x2C,
(byte)0x0,(byte)0x0,(byte)0x0,(byte)0x0,
(byte)0xB,(byte)0x0,(byte)0x9,(byte)0x0,
(byte)0x0,(byte)0x2,(byte)0x14,(byte)0xFFFFFF8C,
(byte)0xF,(byte)0xFFFFFFA7,(byte)0xFFFFFFB8,(byte)0xFFFFFF9B,
(byte)0xA,(byte)0xFFFFFFA2,(byte)0x79,(byte)0xFFFFFFE9,
(byte)0xFFFFFF85,(byte)0x7A,(byte)0x27,(byte)0xFFFFFF93,
(byte)0x5A,(byte)0xFFFFFFE3,(byte)0xFFFFFFEC,(byte)0x75,
(byte)0x11,(byte)0xFFFFFF85,(byte)0x14,(byte)0x0,
(byte)0x3B};
public void serialize(Document doc, XSQLPageRequest env) throws Throwable {
env.setContentType("image/gif");
OutputStream os = env.getOutputStream();
os.write(okGif,0,okGif.length);
os.flush();
}
}
Using the XSQL Command Line utility, the binary information is written to the target output file. Using the XSQLRequest programmatic API, two constructors exist that allow the caller to supply the target OutputStream to use for the results of page processing.
Note that your serializer must either call getWriter() (for textual output) or getOutputStream() (for binary output) but not both. Calling both in the same request will raise an error.
You can provide a custom connection manager to replace the built-in connection management mechanism. To provide a custom connection manager implementation, you must provide:
oracle.xml.xsql.XSQLConnectionManagerFactory interface.
oracle.xml.xsql.XSQLConnectionManager interface.
Your custom connection manager factory can be set to be used as the default connection manager factory by providing the classname in the XSQLConfig.xml file in the section:
<!--
| Set the name of the XSQL Connection Manager Factory
| implementation. The class must implement the
| oracle.xml.xsql.XSQLConnectionManagerFactory interface.
| If unset, the default is to use the built-in connection
| manager implementation in
| oracle.xml.xsql.XSQLConnectionManagerFactoryImpl
+-->
<connection-manager>
<factory>oracle.xml.xsql.XSQLConnectionManagerFactoryImpl</factory>
</connection-manager>
In addition to specifying the default connection manager factory, a custom connection factory can be associated with any individual XSQLRequest object using API's provided.
The responsibility of the XSQLConnectionManagerFactory is to return an instance of an XSQLConnectionManager for use by the current request. In a multithreaded environment like a servlet engine, it is the responsibility of the XSQLConnectionManager object to insure that a single XSQLConnection instance is not used by two different threads. This can be assured by marking the connection as "in use" for the span of time between the invocation of the getConnection() method and the releaseConnection() method. The default XSQL connection manager implementation automatically pools named connections, and adheres to this threadsafe policy.
Errors raised by the processing of any XSQL Action Elements are reported as XML elements in a uniform way so that XSL Stylesheets can detect their presence and optionally format them for presentation.
The action element in error will be replaced in the page by:
<xsql-error action="xxx">
Depending on the error the <xsql-error> element contains:
Here is an example of an XSLT stylesheet that uses this information to display error information on the screen:
<xsl:if test="//xsql-error"> <table style="background:yellow"> <xsl:for-each select="//xsql-error"> <tr> <td><b>Action</b></td> <td><xsl:value-of select="@action"/></td> </tr> <tr valign="top"> <td><b>Message</b></td> <td><xsl:value-of select="message"/></td> </tr> </xsl:for-each> </table> </xsl:if>
XSQL Servlet has the following limitations:
HTTP parameters with multibyte names, for example, a parameter whose name is in Kanji, are properly handled when they are inserted into your XSQL page using <xsql:include-request-params>. An attempt to refer to a parameter with a multibyte name inside the query statement of an <xsql:query> tag will return an empty string for the parameter's value.
As a workaround use a non-multibyte parameter name. The parameter can still have a multibyte value which can be handled correctly.
If you use the CURSOR() function in SQL statements you may get an "Exhausted ResultSet" error if the CURSOR() statements are nested and if the first row of the query returns an empty result set for its CURSOR() function.
I am trying to write my own stylesheet for transforming XSQL output to WML and VML format. These programs (mobile phone simulators) need a WML document with a specific DTD assigned.
Is there any way, I can specify a particular DTD while transforming XSQL's output to a WML document.
Sure. The way you do it is using a built-in facility of the XSLT stylesheet called <xsl:output>.
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output type="xml" doctype-system="your.dtd"/> <xsl:template match="/"> </xsl:template> : : </xsl:stylesheet>
This will produce an XML result with a:
<!DOCTYPE xxxx SYSTEM "your.dtd">
in the result. "your.dtd" can be any valid absolute or relative URL.
Is it possible to write conditional statements in a .xsql file, and if yes what is the syntax to do that?
For example:
<xsql:choose> <xsql:when test="@security='admin'"> <xsql:query> SELECT .... </xsql:query> </xsq:when> <xsql:when test="@security='user'"> <xsql:query> SELECT .... </xsql:query> </xsql:when> </xsql:if>
Use <xsql:ref-cursor-function> to call a PL/SQL procedure that would conditionally return a REF CURSOR to the appropriate query.
I have two queries in an .xsql file.
<xsql:query> select col1,col2 from table1 </xsql:query> <xsql:query> select col3,col4 from table2 where col3 = {@col1} => the value of col1 in the previous query </xsql:query>
How can I use, in the second query, the value of a select list item of the first query?
You do this with page parameters.
<page xmlns:xsql="urn:oracle-xsql" connection="demo"> <!-- Value of page param "xxx" will be first column of first row --> <xsql:set-page-param name="xxx"> select one from table1 where ... </xsl:set-param-param> <xsql:query bind-params="xxx"> select col3,col4 from table2 where col3 = ? </xsql:query> </page>
Can the XSQL Servlet connect to any DB that has JDBC support?
Yes. Just indicate the appropriate JDBC driver class and connection URL in the XSQLConfig.xml file's connection definition. Of course, object/relational functionality only works when using Oracle with the Oracle JDBC driver.
I am running the demo helloworld.xsql. Initially I was getting the following error:
XSQL-007 cannot aquire a database connection to process page
Now my request times out and I see the following message in the jserv/log/jserv.log file:
Connections from Localhost/127.0.0.1 are not allowed
Is this a security issue? Do we have to give explicit permission to process an .xsql page. If so, how do we do that? I am using Apache web server and Apache jserver and Oracle9i as database. I have Oracle client installed and Tnsnames.ora file configured to get database connection. My XSQconnections.xml file is configured correctly.
This looks like a generic JServ problem. You have to make sure that your security.allowedAddresses=property in jserv.properties allows your current host access to the JServ process where Java runs. Can you successfully run *any* JServ Servlet?
I am trying to use XSQL with Oracle8i Lite (Win 98) and Apache/JServ Webserver. I am getting error message "no oljdbc40 in java.library.path" even though I have set the olite40.jar in my classpath (which contains the POLJDBC driver). Is there anything extra I need to do to run XSQL for Oracle8i Lite.
You must include the following instruction in your jserv.properties file:
wrapper.path=C:\orant\bin
where C:\orant\bin is the directory where (by default) the OLJDBC40.DLL lives.
Note that this is not wrapper.classpath,it's wrapper.path.
Is there any way to handle multi-valued HTML <form> parameters which is needed for <input type="checkbox">?
There is no built-in way, but you could use a custom Action Handler like this:
// MultiValuedParam: XSQL Action Handler that takes the value of // ---------------- a multi-valued HTTP request parameter and // sets the value of a user-defined page-parameter // equal to the concatenation of the multiple values // with optional control over the separator used // between values and delimiter used around values. // Subsequent actions in the page can then reference // the value of the user-defined page-parameter. import oracle.xml.xsql.*; import javax.servlet.http.*; import org.w3c.dom.*; public class MultiValuedParam extends XSQLActionHandlerImpl { public void handleAction(Node root) { XSQLPageRequest req = getPageRequest(); // Only bother to do this if we're in a Servlet environment if (req.getRequestType().equals("Servlet")) { Element actElt = getActionElement(); // Get name of multi-valued parameter to read from attribute String paramName = getAttributeAllowingParam("name",actElt); // Get name of page-param to set with resulting value String pageParam = getAttributeAllowingParam("page-param",actElt); // Get separator string String separator = getAttributeAllowingParam("separator",actElt); // Get delimiter string String delimiter = getAttributeAllowingParam("delimiter",actElt); // If the separator is not specified or is blank, use comma if (separator == null || separator.equals("")) { separator = ","; } // We're in a Servlet environment, so we can cast XSQLServletPageRequest spReq = (XSQLServletPageRequest)req; // Get hold of the HTTP Request HttpServletRequest httpReq = spReq.getHttpServletRequest(); // Get the String array of parameter values String[] values = httpReq.getParameterValues(paramName); StringBuffer str = new StringBuffer(); // If some values have been returned if (values != null) { int items = values.length; // Append each value to the string buffer for (int z = 0; z < items; z++) { // Add a separator before all but the first if (z != 0) str.append(separator); // Add a delimiter around the value if non-null if (delimiter != null) str.append(delimiter); str.append(values[z]); if (delimiter != null) str.append(delimiter); } // If page-param attribute not provided, default page param name if (pageParam == null) { pageParam = paramName+"-values"; } // Set the page-param to the concatenated value req.setPageParam(pageParam,str.toString()); } } } }
Then you can use this custom action in a page like this:
<page xmlns:xsql="urn:oracle-xsql"> <xsql:action handler="MultiValuedParam" name="guy" page-param="p1" />
<xsql:action handler="MultiValuedParam" name="guy" page-param="p2"
delimiter="'" />
<xsql:action handler="MultiValuedParam" name="guy" page-param="p3"
delimiter=""" separator=" " />
<xsql:include-param name="p1"/>
<xsql:include-param name="p2"/>
<xsql:include-param name="p3"/>
</page>
If this page is requested with the URL below, containing multiple parameters of the same name to produce a multi-valued attribute:
http://yourserver.com/page.xsql?guy=Curly&guy=Larry&guy=Moe
Then the page returned will be:
<page> <p1>Curly,Larry,Moe</p1> <p2>'Curly','Larry','Moe'</p2> <p3>"Curly" "Larry" "Moe"</p3> </page>
Of course you could also use the value of the multi-valued page parameter above in a SQL statement by doing:
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
<xsql:action handler="MultiValuedParam" name="guy" page-param="list"
delimiter="'" />
<!-- Above custom action sets the value of page param named 'list' -->
<xsql:query>
SELECT * FROM sometable WHERE name IN ({@list})
</xsql:query>
</page>
Is there anything that prevents me from running the XSQL Servlet with Oracle 7.3? I know the XML SQL Utility can be used with Oracle 7.3 as long as I use it as a client-side utility.
No. Just make sure you're using the Oracle9i JDBC driver, which can connect to an Oracle 7.3 database with no problems.
I using <xsql:dml> to call a stored procedure which has one OUT parameter, but I was not able to see any results. The executed code results in the following statement:
<xsql-status action="xsql:dml" rows="0"/>
You cannot set parameter values by binding them in the position of OUT variables in this release using <xsql:dml>. Only IN parameters are supported for binding. You can create a wrapper procedure that constructs XML elements using the HTP package and then your XSQL page can invoke the wrapper procedure using <xsql:include-owa> instead.
For an example, suppose you had the following procedure:
CREATE OR REPLACE PROCEDURE addmult(arg1 NUMBER,
arg2 NUMBER,
sumval OUT NUMBER,
prodval OUT NUMBER) IS
BEGIN
sumval := arg1 + arg2;
prodval := arg1 * arg2;
END;
You could write the following procedure to "wrap" it, taking all of the IN arguments that the procedure above expects, and then "encoding" the OUT values as a little XML datagram that you print to the OWA page buffer:
CREATE OR REPLACE PROCEDURE addmultwrapper(arg1 NUMBER, arg2 NUMBER) IS
sumval NUMBER;
prodval NUMBER;
xml VARCHAR2(2000);
BEGIN
-- Call the procedure with OUT values
addmult(arg1,arg2,sumval,prodval);
-- Then produce XML that encodes the OUT values
xml := '<addmult>'||
'<sum>'||sumval||'</sum>'||
'<product>'||prodval||'</product>'||
'</addmult>';
-- Print the XML result to the OWA page buffer for return
HTP.P(xml);
END;
This way, you can build an XSQL page like this that calls the wrapper procedure:
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
<xsql:include-owa bind-params="arg1 arg2">
BEGIN addmultwrapper(?,?); END;
</xsql:include-owa>
</page>
This allows a request like:
http://yourserver.com/addmult.xsql?arg1=30&arg2=45
to return an XML datagram that reflects the OUT values like this:
<page>
<addmult><sum>75</sum><product>1350</product></addmult>
</page>
Trying to play with XSQL I'm unable to connect to a database I get errors like this running the helloworld.xsql example:
Oracle XSQL Servlet Page Processor 9.0.0.0.0 (Beta)
XSQL-007: Cannot acquire a database connection to process page.
Connection refused(DESCRIPTION=(TMP=)(VSNNUM=135286784)(ERR=12505)
(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
Does this mean that it has actually found the config file? I have a user with scott/tiger setup.
Yes. If you get this far, it's actually attempting the JDBC connection based on the <connectiondef> info for the connection named "demo", assuming you didn't modify the helloworld.xsql demo page.
By default the XSQLConfig.xml file comes with the entry for the "demo"connection that looks like this:
<connection name="demo">
<username>scott</username>
<password>tiger</password>
<dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl>
<driver>oracle.jdbc.driver.OracleDriver</driver>
</connection>
So the error you're getting is likely because:
Make sure those values are appropriate for your database and you should be in business.
I want users to think they are accessing HTML files or XML files with extensions .html and .xml respectively, however I'd like to use XSQL so serve the HTML and XML to them. Is it possible to have the XSQL Servlet recognize files with an extension of .html and/or .xml in addition to the default .xsql extension?
Sure. There is nothing sacred about the *.xsql extension, it is just the default extension used to recognize XSQL pages. You can modify your servlet engine's configuration settings to associate any extension you like with the oracle.xml.xsql.XSQLServlet servlet class using the same technique that was used to associate the *.xsql extension with it.
I have a page like:
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
SELECT id, REPLACE(company,'&','and') company, balance
FROM vendors
WHERE outstanding_balance < 3000
</xsql:query>
but when I try to request the page I get an error:
XSQL-005: XSQL page is not well-formed.
XML parse error at line 4, char 16
Expected name instead of '
What's wrong?
The problem is that the ampersand character (&) and the less-than sign (<) are reserved characters in XML because:
To include a literal ampersand character or less-than character you need to either encode each one as a entity reference like this:
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
SELECT id, REPLACE(company,'&','and') company, balance
FROM vendors
WHERE outstanding_balance < 3000
</xsql:query>
Alternatively, you can surround an entire block of text with a so-called CDATA section that begins with the sequence <![CDATA[ and ends with a corresponding ]]> sequence. All text contained in the CDATA section is treated as literal.
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
<![CDATA[
SELECT id, REPLACE(company,'&','and') company, balance
FROM vendors
WHERE outstanding_balance < 3000
]]>
</xsql:query>
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|