16 Using the XSQL Pages Publishing Framework

Creating and Testing XSQL Pages with Oracle JDeveloper

The easiest way to use XSQL pages is with Oracle JDeveloper. The IDE supports these features:

• Color-coded syntax highlighting

• XML syntax checking

• In-context drop-down lists that help you pick valid XSQL tag names and auto-complete tag and attribute names

• XSQL page deployment and testing

• Debugging tools

• Wizards for creating XSQL actions

To create an XSQL page in an Oracle JDeveloper project:

1. Create or open a project.

2. Select File and then New.

3. In the New Gallery dialog box, select the General category and then XML.

4. In the Item window, select XSQL Page and click OK. Oracle JDeveloper loads a tab for the new XSQL page into the central window.

To add XSQL action elements such as <xsql:query> to your XSQL page, place the cursor where you want the new element to go and click an item in the Component Palette. A wizard opens that takes you through the steps of selecting which XSQL action you want to use and which attributes you must provide.

To check the syntax of an XSQL page, place the cursor in the page and right-click Check XML Syntax. If there are any XML syntax errors, Oracle JDeveloper displays them.

To test an XSQL page, select the page in the navigator and right-click Run. Oracle JDeveloper automatically starts a local web server, properly configured to run XSQL pages, and tests your page by starting your default browser with the appropriate URL to request the page. After you have run the XSQL page, you can continue to make modifications to it in the IDE. And, you can modify any XSLT style sheets with which it might be associated. After saving the files in the IDE, you can immediately refresh the browser to observe the effect of the changes.

You must add the XSQL runtime library to your project library list so that the CLASSPATH is properly set. The IDE adds this entry automatically when you go through the New Gallery dialog to create a new XSQL page, but you can also add it manually to the project as follows:

1. Right-click the project in the Applications Navigator.

2. Select Project Properties.

3. Select Profiles and then Libraries from the navigation tree.

4. Move XSQL Runtime from the Available Libraries pane to Selected Libraries.

Setting the CLASSPATH for XSQL Pages

Outside of the Oracle JDeveloper environment, ensure that the XSQL page processor engine is properly configured.

Ensure that the appropriate Java Archive (JAR) files are in the CLASSPATH of the Java Virtual Machine (JVM) that processes the XSQL Pages. The complete set of XDK JAR files is described in "Java Libraries for Oracle XML Developer's Kit for Java Components." The JAR files for the XSQL framework include:

• xml.jar, the XSQL page processor

• xmlparserv2.jar, the Oracle XML parser

• xsu12.jar, the Oracle XML SQL utility (XSU)

• ojdbc6.jar, the Oracle JDBC driver

If you have configured your CLASSPATH as instructed in "Setting Up the XDK for Java Environment," you need to add the directory only where the XSQL pages configuration file resides. In the database installation of XDK, the directory for XSQLConfig.xml is $ORACLE_HOME/xdk/admin.

On Windows your %CLASSPATH% variable contains these entries:

%ORACLE_HOME%\lib\ojdbc6.jar;%ORACLE_HOME%\lib\xmlparserv2.jar;
%ORACLE_HOME%\lib\xsu12.jar;C:\xsql\lib\xml.jar;%ORACLE_HOME%\xdk\admin

On UNIX the $CLASSPATH variable contains these entries:

$ORACLE_HOME/lib/ojdbc6.jar:$ORACLE_HOME/lib/xmlparserv2.jar:
$ORACLE_HOME/lib/xsu12.jar:$ORACLE_HOME/lib/xml.jar:$ORACLE_HOME\xdk\admin

Configuring the XSQL Servlet Container

You can install the XSQL servlet in a variety of different web servers. See the file $ORACLE_HOME/xdk/readme.html for servlet installation instructions.

Setting Up the Connection Definitions

XSQL pages specify database connections by using a short name for a connection that is defined in the XSQL configuration file, which by default is named $ORACLE_HOME/xdk/admin/XSQLConfig.xml.

The sample XSQL page shown in Example 16-1 contains this connection information:

<xsql:query connection="hr" xmlns:xsql="urn:oracle-xsql">

Connection names are defined in the <connectiondefs> section of the XSQL configuration file. Example 16-2 shows the relevant section of the sample configuration file included with the database, with the hr connection in bold.

<connectiondefs> 
  ...
  <connection name="hr">
    <username>hr</username>
    <password>hr_password</password>
    <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl>
    <driver>oracle.jdbc.driver.OracleDriver</driver>
    <autocommit>false</autocommit>
  </connection>
  ...
</connectiondefs>

For each database connection, you can specify these elements:

• <username>, the database user name

• <password>, the database password

• <dburl>, the JDBC connection string

• <driver>, the fully qualified class name of the JDBC driver to use

• <autocommit>, which optionally forces AUTOCOMMIT to TRUE or FALSE

Specify an <autocommit> child element to control the setting of the JDBC autocommit for any connection. If no <autocommit> child element is set for a <connection>, then the autocommit setting is not set by the XSQL connection manager. In this case, the setting is the default autocommit setting for the JDBC driver.

You can place an arbitrary number of <connection> elements in the XSQL configuration file to define your database connections. 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").

Setting Up the XSQL Demos

To set up the XSQL demos perform these steps:

1. Change into the $ORACLE_HOME/xdk/demo/java/xsql directory (UNIX) or %ORACLE_HOME%\xdk\demo\java\xsql directory (Windows).

2. Start SQL*Plus and connect to your database as ctxsys—the schema owner for the Oracle Text packages—and issue this statement:

   GRANT EXECUTE ON ctx_ddl TO scott;
   

3. Connect to your database as a user with DBA privileges and issue this statement:

   GRANT QUERY REWRITE TO scott;
   

   The preceding query enables scott to create a function-based index that one of the demos requires to perform case-insensitive queries on descriptions of airports.

4. Connect to your database as scott. You are prompted for the password.

5. Run the SQL script install.sql in the current directory. This script runs all SQL scripts for all the demos:

   @install.sql
   

6. Change to the ./doyouxml subdirectory, and run this command to import sample data for the "Do You XML?" demo (you are prompted for the password):

   imp scott file=doyouxml.dmp
   

7. To run the Scalable Vector Graphics (SVG) demonstration, install an SVG plug-in such as Adobe SVG plug-in into your browser.

Running the XSQL Demos

The XSQL demos are designed to be accessed through a web browser. If you have set up the XSQL servlet in a web server as described in "Configuring the XSQL Servlet Container," then you can access the demos through this URL, substituting appropriate values for yourserver and port:

http://yourserver:port/xsql/index.html

Figure 16-3 shows a section of the XSQL home page in Internet Explorer. (You must use browser version 5 or later.)

Figure 16-3 XSQL Home Page

Description of "Figure 16-3 XSQL Home Page"

The demos are designed to be self-explanatory. Click the demo titles—Hello World Page, Employee Page, and so forth—and follow the online instructions.

Using Bind Parameters

The <xsql:query> element shown in Example 16-3 contains a bind-params attribute that associates the values of parameters in the request to bind variables in the SQL statement included in the <xsql:query> tag. The bind parameters in the SQL statement are represented by question marks.

You can use SQL bind variables to parameterize the results of any of the actions in Table 32-1, "Built-In XSQL Elements and Action Handler Classes" that allow SQL statements. Bind variables enable your XSQL page template to produce results based on the values of parameters passed in the request.

To use a bind variable, include a question mark anywhere in a statement where bind variables are allowed by SQL. Whenever a SQL statement is executed in the page, the XSQL engine binds the parameter values to the variable by specifying the bind-params attribute on the action element.

Example 16-5 shows an XSQL page that binds the bind variables to the value of the custid parameter in the page request.

<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 customer with ID of 101 can then be requested by passing the customer id parameter in the request:

http://yourserver.com/fin/CustomerPortfolio.xsql?custid=1001

The value of the bind-params attribute is a space-delimited list of parameter names. The left-to-right order indicates the positional bind variable to which its value is bound in the statement. Thus, if your SQL statement contains five question marks, then the bind-params attribute needs a space-delimited list of five parameter names. If the same parameter value must be bound to several different occurrences of a bind variable, then repeat the name of the parameters in the value of the bind-params attribute at the appropriate position. Failure to include the same number of parameter names in the bind-params attribute as in the query causes an error when the page is executed.

You can use variables in any action that expects a SQL statement or PL/SQL block. The page shown in Example 16-6 shows this technique. The XSQL page contains three action elements:

• <xsql:dml> binds useridCookie to an argument in the log_user_hit procedure.

• <xsql:query> binds parameter custid to a variable in a WHERE clause.

• <xsql:include-owa> binds parameters custid and userCookie to two arguments in the historical_data procedure.

<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>

Using Lexical Substitution Parameters

For any XSQL action element, you can substitute the value of any attribute or the text of any contained SQL statement with a lexical substitution parameter. Thus, you can parameterize how actions behave and substitute parts of the SQL statements that they perform. Lexical substitution parameters are referenced with this syntax: {@ParameterName}.

Example 16-7 shows how you can use two lexical substitution parameters. One parameter in the <xsql:query> element sets the maximum number of rows to be passed in, whereas the other controls the list of columns to be ordered.

<!-- 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>

Example 16-7 also contains two bind parameters: dev and prod. For example, you might want to get the open bugs for developer yxsmith against product 817. And, you want to retrieve only 10 rows and order them by bug number. You can fetch the XML for the bug list by specifying parameter values:

http://server.com/bug/DevOpenBugs.xsql?dev=yxsmith&prod=817&max=10&orderby=bugno

You can also use the XSQL command-line utility to make the request:

xsql DevOpenBugs.xsql dev=yxsmith prod=817 max=10 orderby=bugno

Lexical parameters also enable you to specify parameters for the XSQL pages connection and the style sheet used to process the page. Example 16-8 shows this technique. You can switch between style sheets test.xsql and prod.xsl by specifying the name/value pairs sheet=test and sheet=prod.

<?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>

Providing Default Values for Bind and Substitution Parameters

You may want to provide a default value for a bind variable or a substitution parameter directly in the page. In this way, the page is parameterized without requiring the requester to explicitly pass in all values in every request.

To include a default value for a parameter, 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, then the XSQL page processor searches for an attribute by the same name on the current action element. If it does not 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.

The page in Example 16-9 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 page in Example 16-10 defaults the first query to a max of 5, the second query to a max of 7, and the third query to 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>

All defaults are overridden if a value of max is supplied in the request, as shown in this example:

http://yourserver.com/example.xsql?max=3

Bind variables respect the same defaulting rules. Example 16-11 shows how you can set the val parameter to 10 by default.

<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>

If the page in Example 16-11 is requested without any parameters, it returns this XML datagram:

<example>
  <rowset>
    <row>
      <somevalue>10</somevalue>
    </row>
  </row>
</example>

Alternatively, assume that the page is requested with this URL:

http://yourserver.com/example.xsql?val=3

The preceding URL returns this datagram:

<example>
  <rowset>
    <row>
      <somevalue>3</somevalue>
    </row>
  </row>
</example>

You can remove the default value for the val parameter from the page by removing the val attribute. Example 16-12 shows this technique.

<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>

A URL request for the page that does not supply a name/value pair returns this datagram:

<example>
  <rowset/>
</example>

A bind variable that is bound to a parameter with neither a default value nor a value supplied in the request is bound to NULL, which causes the WHERE clause in Example 16-12 to return no rows.

How the XSQL Page Processor Handles Different Types of Parameters

XSQL pages can make use of parameters supplied in the request and also of page-private parameters. The names and values of page-private parameters 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, then the value of the param parameter is resolved in this order:

1. The value of the page-private parameter named param, if set

2. The value of the request parameter named param, if supplied

3. The default value provided by an attribute named param on the current action element or one of its ancestor elements

4. The value NULL for bind variables and the empty string for lexical parameters

For XSQL pages that are processed by the XSQL servlet over HTTP, you can also set and reference the HTTP-Session-level variables and HTTP Cookies parameters.

For XSQL pages processed through the XSQL servlet, the value of a parameter param is resolved in this order:

1. The value of the page-private parameter param, if set

2. The value of the cookie named param, if set

3. The value of the session variable named param, if set

4. The value of the request parameter named param, if supplied

5. The default value provided by an attribute named param on the current action element or one of its ancestor elements

6. The value NULL for bind variables and the empty string for lexical parameters

The resolution order means that users cannot supply parameter values in a request to override parameters of the same name set in the HTTP session. Also, users cannot set them as cookies that persist across browser sessions.

Specifying a DTD While Transforming XSQL Output to a WML Document

You can specify a DTD while transforming XSQL output to a Wireless Markup Language (WML) document for a wireless application. The technique is to use a built-in facility of the XSLT style sheet called <xsl:output>. The following example shows this technique:

<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>

The preceding style sheet produces an XML result that includes this code, where "your.dtd" can be any valid absolute or relative URL:

<!DOCTYPE xxxx SYSTEM "your.dtd">

Testing Conditions in XSQL Pages

You can include if-then logic in your XSQL pages. Example 16-17 shows a technique for executing a query based on a test of a parameter value.

<xsql:if-param name="security" equals="admin">
  <xsql:query>
      SELECT ....
  </xsql:query>
</xsq:when>
<xsql:if-param name="security" equals="user">
  <xsql:query>
      SELECT ....
  </xsql:query>
</xsql:if-param>

Passing a Query Result to the WHERE Clause of Another Query

If you have two queries in an XSQL page, then you can use the value of a select list item of the first query in the second query by using page parameters. Example 16-18 shows this technique.

<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>

Handling Multivalued HTML Form Parameters

In some situations, you might have to process multivalued HTML <form> parameters that are needed for <input name="choices" type="checkbox">. Use the parameter array notation on your parameter name (for example, choices[]) to refer to the array of values from the selected check boxes.

Assume that you have a multivalued parameter named guy. You can use the array parameter notation in an XSQL page as shown in Example 16-19.

<page xmlns:xsql="urn:oracle-xsql">
  <xsql:set-page-param name="guy-list" value="{@guy[]}"
                       treat-list-as-array="yes"/>
  <xsql:set-page-param name="quoted-guys" value="{@guy[]}"
                       treat-list-as-array="yes" quote-array-values="yes"/>
  <xsql:include-param name="guy-list"/>
  <xsql:include-param name="quoted-guys"/>
  <xsql:include-param name="guy[]"/>
</page>

Assume that you request this page is requested with this URL, which contains multiple parameters of the same name to produce a multivalued attribute:

http://yourserver.com/page.xsql?guy=Curly&guy=Larry&guy=Moe

The page returned looks like this:

<page>
  <guy-list>Curly,Larry,Moe</guy-list>
  <quoted-guys>'Curly','Larry','Moe'</quoted-guys>
  <guy>
    <value>Curly</value>
    <value>Larry</value>
    <value>Moe</value>
  </guy>
</page>

You can also use the value of a multivalued page parameter in a SQL statement WHERE clause by using the code shown in Example 16-20.

<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:set-page-param name="quoted-guys" value="{@guy[]}"
                       treat-list-as-array="yes" 
                       quote-array-values="yes"/>
  <xsql:query>
    SELECT *
    FROM   sometable
    WHERE  name IN ({@quoted-guys})
  </xsql:query>
</page>

Invoking PL/SQL Wrapper Procedures to Generate XML Datagrams

You cannot set parameter values by binding them in the position of OUT variables with <xsql:dml>. Only IN parameters are supported for binding. You can create a wrapper procedure, however, that constructs XML elements with the HTTP package. Your XSQL page can then invoke the wrapper procedure with <xsql:include-owa>.

Example 16-21 shows a PL/SQL procedure that accepts two IN parameters, multiplies them and puts the value in one OUT parameter, then adds them and puts the result in a second OUT parameter.

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 can write the PL/SQL procedure in Example 16-22 to wrap the procedure in Example 16-21. The addmultwrapper procedure accepts the IN arguments that the addmult procedure preceding expects, and then encodes the OUT values as an XML datagram that you print to the Open Web Analytics (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;

The XSQL page shown in Example 16-23 constructs an XML document by including a call to the PL/SQL wrapper procedure.

<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:include-owa bind-params="arg1 arg2">
    BEGIN addmultwrapper(?,?); END;
  </xsql:include-owa>
</page>

You can invoke addmult.xsql by entering a URL in a browser:

http://yourserver.com/addmult.xsql?arg1=30&arg2=45

The XML datagram returned by the servlet reflects the OUT values:

<page>
  <addmult><sum>75</sum><product>1350</product></addmult>
</page>

Accessing Contents of Posted XML

The XSQL page processor can access the contents of posted XML. Any XML document can be posted and handled by the feature that XSQL supports.

For example, an XSQL page can access the contents of an inbound SOAP message by using the xpath="XpathExpression" attribute in the <xsql:set-page-param> action. Alternatively, custom action handlers can gain direct access to the SOAP message body by invoking getPageRequest().getPostedDocument(). To create the SOAP response body to return to the client, use an XSLT style sheet or a custom serializer implementation to write the XML response in an appropriate SOAP-encoded format.

Changing Database Connections Dynamically

You can choose database connections dynamically when invoking an XSQL page. For example, you might want to switch between a test database and a production database.You can achieve this goal by including an XSQL parameter in the connection attribute of the XSQL page. Define an attribute of the same name to serve as the default value for the connection name.

Assume that in your XSQL configuration file you define connections for database testdb and proddb. You then write an XSQL page with this <xsql:query> element:

<xsql:query conn="testdb" connection="{@conn}" xmlns:xsql="urn:oracle-xsql">
  ...
</xsql:query> 

If you request this page without any parameters, then the value of the conn parameter is testdb, so the page uses the connection named testdb defined in the XSQL configuration file. If you request the page with conn=proddb, then the page uses the connection named proddb instead.

Retrieving the Name of the Current XSQL Page

An XSQL page can access its own name in a generic way at run time to construct links to the current page. You can use a helper method like the one shown in Example 16-24 to retrieve the name of the page inside a custom action handler.

private String curPageName(XSQLPageRequest req) {
  String thisPage = req.getSourceDocumentURI();;
  int pos = thisPage.lastIndexOf('/');
  if (pos >=0) thisPage = thisPage.substring(pos+1);
  pos = thisPage.indexOf('?');
  if (pos >=0) thisPage = thisPage.substring(0,pos-1);
  return thisPage;
}

Receiving "Unable to Connect" Errors

Suppose you are unable to connect to a database and you see errors similar to these when running the helloworld.xsql sample program:

Oracle XSQL Servlet Page Processor
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))))

The preceding errors indicate that the XSQL servlet is attempting the JDBC connection based on the <connectiondef> information for the connection named demo, assuming you did not modify the helloworld.xsql demo page.

By default the XSQLConfig.xml file comes with the entry for the demo connection that looks like this (use the correct password):

<connection name="demo">
  <username>scott</username>
  <password>password</password>
  <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl>
  <driver>oracle.jdbc.driver.OracleDriver</driver>
</connection>

The error is probably due to one of these reasons:

• Your database is not on the localhost machine.

• Your database SID is not ORCL.

• Your TNS Listener Port is not 1521.

Receiving "No Posted Document to Process" When Using HTTP POST

When trying to post XML information to an XSQL page for processing, it must be sent by the HTTP POST method. This transfer can be effected by an HTML form or an XML document sent by HTTP POST. If you try to use HTTP GET instead, then there is no posted document, and hence you get the "No posted document to process" error. Use HTTP POST instead to cause the correct behavior.

Installing Your XSQL Configuration File in a Safe Directory

The XSQLConfig.xml configuration file contains sensitive database user name and password information. This file must not reside in any directory that maps to a virtual path of your web server, nor in any of its subdirectories. The only required permissions for the configuration file are read permission granted to the UNIX account that owns the servlet engine. Failure to follow this recommendation could mean that a user of your site could browse the contents of your configuration file, thereby getting the passwords to database accounts.

Disabling Default Client Style Sheet Overrides

By default, the XSQL page processor enables the user to supply a style sheet in the page request by passing a value for the special xml-stylesheet parameter. If you want the style sheet referenced by the server-side XSQL page to be the only legal style sheet, then include the allow-client-style="no" attribute on the document element of your page. You can also globally change the default setting in the XSQLConfig.xml file to disallow client style sheet overrides. If you take either approach, then the only pages that allow client style sheet overrides are those that include the allow-client-style="yes" attribute on their document element.

Protecting Against the Misuse of Substitution Parameters

Any product that supports the use of lexical substitution variables in a SQL query can cause a developer problems. Any time you deploy an XSQL page that allows part of all of a SQL statement to be substituted by a lexical parameter, you must ensure that you have taken appropriate precautions against misuse.

For example, one of the demonstrations that comes with XSQL Pages is the Adhoc Query Demo. It shows how you can supply the entire SQL statement of an <xsql:query> action handler as a parameter. This technique is a powerful and beneficial tool when in the right hands, but if you deploy a similar page to your production system, then the user can execute any query that the database security privileges for the connection associated with the page allows. For example, the Adhoc Query Demo is set up to use a connection that maps to the scott account, so a user can query any data that scott would be allowed to query from SQL*Plus.

You can use these techniques to ensure that your pages are not abused:

• Ensure the database user account associated with the page has only the privileges for reading the tables and views you want your users to see.

• Use true bind variables instead of lexical bind variables when substituting single values in a SELECT statement. If you must parameterize syntactic parts of your SQL statement, then lexical parameters are the only way to proceed. Otherwise, use true bind variables so that any attempt to pass an invalid value generates an error instead of producing an unexpected result.