Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
Library | Service | Contents | Index

Using XSQL Servlet, 7 of 24

## Setting Up the Connection Definitions

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>
        </connection>
        <connection name="prod">
          <username>prodapp</username>
          <password>sumpti0us</password>
    <dburl>jdbc:oracle:thin:@localhost:1521:prodDB</dburl>
          <driver>oracle.jdbc.driver.OracleDriver</driver>
        </connection>
    </connectiondefs>
    :

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").

#### Single Query Page

For a page with a single query this looks like:

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

#### Multiple Query Page

For a page with more than one query, it looks like the following:

    <?xml version="1.0"?>
    <page connection="demo" xmlns:xsql="urn:oracle-xsql">
      <xsql:query>SELECT 'X' as X FROM DUAL</xsql:query>
      <xsql:query>SELECT 'Y' as Y FROM DUAL</xsql:query>
    </page>

You can use these techniques to publish an XML-based product catalog.

### XSQL Page Common Tags

Table 19-1 lists three useful and common XSQL Servlet tags. For a full list of tags see Oracle8i XML Reference.

##### Table 19-1 XSQL Common Tags

XSQL Tag Examples | Description
--- | ---
<xsql:query> | To include XML-based SQL query result in the template wherever you want, use <xsql:query> tags.
<?xml-stylesheet?> | To associate an XSL stylesheet to the page, add <?xml-stylesheet?> instructions line at the top of the file.
<xsql:action> | To extend the set of actions that can be performed to assemble the "datapage", use the <xsql:action> element.

Save the file and request it through your browser to get immediate results.

### Additional XSQL Page Example

Here is another simple example of an XSQL Page. This could be in response to a URL request such as:

    http://yourcompany.com/AvailableFlightsToday.xsql?City=NYC

An example XSQL Page:

    <?xml version="1.0"?>
      <xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
         SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') Due
            FROM FlightSchedule
            WHERE TRUNC(ArrivalTime) = TRUNC(SYSDATE)
            AND Destination = '{@City}'
            ORDER BY ExpectedTime
      </xsql:query>

To return the information in HTML or other XML format that complies with a DTD, add a <?xml-stylesheet....?> tag such as:

    <?xml version="1.0"?>
      <?xml-stylesheet type="text/xsl" href="FlightList.xsl"?>
       <xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
          SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') Due
            FROM FlightSchedule
            WHERE TRUNC(ArrivalTime) = TRUNC(SYSDATE)
            AND Destination = '{@City}'
            ORDER BY ExpectedTime
      </xsql:query>

Note: {@City} is a parameter based on City=NYC. The parameter is made up of actions comprised of handles. The actions make it easy to assemble and transform information. The handles allow stylesheets to be transformed.

### Built-in XSQL Action Elements

Table 19-2 lists several supplied built-in XSQL Action Elements

.

##### Table 19-2 Built-in XSQL Action Elements

Action Element | Description
--- | ---
<xsql:ref-cursor-function> | Includes the canonical XML representation of the result set of a cursor returned by a PL/SQL stored function. Use the new dynamic SQL features that are built-in to PL/SQL in Oracle8i, with <xsql:set-page-param>.
<xsql:set-page-param> | Set a page-level (local) parameter that can be referred to in subsequent SQL statements in the page. The value can be set using a static value, the value of another parameter, or the results of a SQL statement.
<xsql:include-param> | Include a parameter and its value as an element in your XSQL page.
<xsql:set-session-param> | Set an HTTP-Session level parameter. The value can be set using a static value, the value of another parameter, or the results of a SQL statement.
<xsql:set-cookie> | Set an HTTP Cookie. The value can be set using a static value, the value of another parameter, or the results of a SQL statement.
<xsql:insert-param> | Inserts the value of a single parameter containing XML. Can optionally supply a transform to get it in canonical format.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index