Oracle JavaServer Pages Developer's Guide and Reference Release 8.1.7 Part Number A83726-01 |
Library | Service | Contents | Index

## Portable OracleJSP Programming Extensions

The Oracle extensions documented in this section are implemented either through the Oracle JSP Markup Language (JML) sample tag library or through custom JavaBeans. These extensions are portable to any standard JSP environment. This includes the following:

- JML extended datatypes

- XML and XSL support (including JML tags)

- database-access JavaBeans

- JML SQL tags

  Important: To use any of the JML functionality, see "Overview of the JSP Markup Language (JML) Sample Tag Library".

### JML Extended Datatypes

To work around shortcomings for JSP usage in the Java primitive datatypes and `java.lang` wrapper types (as discussed in "OracleJSP Extended Datatypes"), OracleJSP provides the following JavaBean classes in the `oracle.jsp.jml` package to act as wrappers for the most common Java datatypes:

- `JmlBoolean` to represent a `boolean` value

- `JmlNumber` to represent an `int` value

- `JmlFPNumber` to represent a `double` value

- `JmlString` to represent a `String` value

Each of these classes has a single attribute, `value`, and includes methods to get the value, set the value from input in various formats, test whether the value is equal to a value specified in any of several formats, and convert the value to a string.

Alternatively, instead of using the `getValue()` and `setValue()` methods, you can use the `jsp:getProperty` and `jsp:setProperty` tags, as with any other bean.

The following example creates a `JmlNumber` instance called `count` that has `application` scope.

    <jsp:useBean id="count" class="oracle.jsp.jml.JmlNumber" scope="application" />

Later, assuming that the value has been set elsewhere, you can access it as follows:

    <h3> The current count is <%=count.getValue() %> </h3>

The following example creates a `JmlNumber` instance called `maxSize` that has `request` scope, and sets it using `setProperty`:

    <jsp:useBean id="maxSize" class="oracle.jsp.jml.Number" scope="request" >
        <jsp:setProperty name="maxSize" property="value" value="<%= 25 %>" />
    </jsp:useBean>

The remainder of this section documents the public methods of the four extended datatype classes, followed by an example.

#### Type JmlBoolean

A `JmlBoolean` object represents a Java `boolean` value.

The `getValue()` and `setValue()` methods get or set the bean's `value` property as a Java `boolean` value.

- `boolean getValue()`

- `void setValue(boolean)`

The `setTypedValue()` method has several signatures and can set the bean's `value` property from a string (such as "true" or "false"), a `java.lang.Boolean` value, a Java `boolean` value, or a `JmlBoolean` value. For the string input, conversion of the string is performed according to the same rules as for the standard `java.lang.Boolean.valueOf()` method.

- `void setTypedValue(String)`

- `void setTypedValue(Boolean)`

- `void setTypedValue(boolean)`

- `void setTypedValue(JmlBoolean)`

The `equals()` method tests whether the bean's `value` property is equal to the specified Java `boolean` value.

- `boolean equals(boolean)`

The `typedEquals()` method has several signatures and tests whether the bean's `value` property has a value equivalent to a specified string (such as "true" or "false"), `java.lang.Boolean` value, or `JmlBoolean` value.

- `boolean typedEquals(String)`

- `boolean typedEquals(Boolean)`

- `boolean typedEquals(JmlBoolean)`

The `toString()` method returns the bean's `value` property as a `java.lang.String` value, either "true" or "false".

- `String toString()`

#### Type JmlNumber

A `JmlNumber` object represents a 32-bit number equivalent to a Java `int` value.

The `getValue()` and `setValue()` methods get or set the bean's `value` property as a Java `int` value.

- `int getValue()`

- `void setValue(int)`

The `setTypedValue()` method has several signatures and can set the bean's `value` property from a string, a `java.lang.Integer` value, a Java `int` value, or a `JmlNumber` value. For the string input, conversion of the string is performed according to the same rules as for the standard `java.lang.Integer.decode()` method.

- `void setTypedValue(String)`

- `void setTypedValue(Integer)`

- `void setTypedValue(int)`

- `void setTypedValue(JmlNumber)`

The `equals()` method tests whether the bean's value property is equal to the specified Java `int` value.

- `boolean equals(int)`

The `typedEquals()` method has several signatures and tests whether the bean's `value` property has a value equivalent to a specified string (such as "1234"), `java.lang.Number` value, or `JmlNumber` value.

- `boolean typedEquals(String)`

- `boolean typedEquals(Integer)`

- `boolean typedEquals(JmlNumber)`

The `toString()` method returns the bean's `value` property as an equivalent `java.lang.String` value (such as "1234"). This method has the same functionality as the standard `java.lang.Integer.toString()` method.

- `String toString()`

#### Type JmlFPNumber

A `JmlFPNumber` object represents a 64-bit floating point number equivalent to a Java `double` value.

The `getValue()` and `setValue()` methods get or set the bean's `value` property as a Java `double` value.

- `double getValue()`

- `void setValue(double)`

The `setTypedValue()` method has several signatures and can set the bean's `value` property from a string (such as "3.57"), a `java.lang.Integer` value, a Java `int` value, a `java.lang.Float` value, a Java `float` value, a `java.lang.Double` value, a Java `double` value, or a `JmlFPNumber` value. For the string input, conversion of the string is according to the same rules as for the standard `java.lang.Double.valueOf()` method.

- `void setTypedValue(String)`

- `void setTypedValue(Integer)`

- `void setTypedValue(int)`

- `void setTypedValue(Float)`

- `void setTypedValue(float)`

- `void setTypedValue(Double)`

- `void setTypedValue(double)`

- `void setTypedValue(JmlFPNumber)`

The `equals()` method tests whether the bean's `value` property is equal to the specified Java `double` value.

- `boolean equals(double)`

The `typedEquals()` method has several signatures and tests whether the bean's `value` property has a value equivalent to a specified string (such as "3.57"), `java.lang.Integer` value, Java `int` value, `java.lang.Float` value, Java `float` value, `java.lang.Double` value, Java `double` value, or `JmlFPNumber` value.

- `boolean typedEquals(String)`

- `boolean typedEquals(Integer)`

- `boolean typedEquals(int)`

- `boolean typedEquals(Float)`

- `boolean typedEquals(float)`

- `boolean typedEquals(Double)`

- `boolean typedEquals(JmlFPNumber)`

The `toString()` method returns the bean's value property as a `java.lang.String` value (such as "3.57"). This method has the same functionality as the standard `java.lang.Double.toString()` method.

- `String toString()`

#### Type JmlString

A `JmlString` object represents a `java.lang.String` value.

The `getValue()` and `setValue()` methods get or set the bean's `value` property as a `java.lang.String` value. If the input in a `setValue()` call is null, then the value property is set to an empty (zero-length) string.

- `String getValue()`

- `void setValue(String)`

The `toString()` method is functionally equivalent to the `getValue()` method.

- `String toString()`

The `setTypedValue()` method sets the bean's `value` property according to the specified `JmlString` value. If the `JmlString` value is null, then the value property is set to an empty (zero-length) string.

- `void setTypedValue(JmlString)`

The `isEmpty()` method tests whether the bean's `value` property is an empty (zero-length) string: ""

- `boolean isEmpty()`

The `equals()` method has two signatures and tests whether the bean's `value` property is equal to a specified `java.lang.String` value or `JmlString` value.

- `boolean equals(String)`

- `boolean equals(JmlString)`

#### JML Datatypes Example

This example illustrates use of JML datatype JavaBeans for management of simple datatypes at scope. The page declares four session objects--one for each JML type. The page presents a form that allows you to enter values for each of these types. Once new values are submitted, the form displays both the new values and the previously set values. In the process of generating this output, the page updates the session objects with the new form values.

    <jsp:useBean id = "submitCount" class = "oracle.jsp.jml.JmlNumber" scope = "session" />

    <jsp:useBean id = "bool" class = "oracle.jsp.jml.JmlBoolean" scope = "session" >
            <jsp:setProperty name = "bool" property = "value" param = "fBoolean" />
    </jsp:useBean>

    <jsp:useBean id = "num" class = "oracle.jsp.jml.JmlNumber" scope = "session" >
            <jsp:setProperty name = "num" property = "value" param = "fNumber" />
    </jsp:useBean>

    <jsp:useBean id = "fpnum" class = "oracle.jsp.jml.JmlFPNumber" scope = "session" >
            <jsp:setProperty name = "fpnum" property = "value" param = "fFPNumber" />
    </jsp:useBean>

    <jsp:useBean id = "str" class = "oracle.jsp.jml.JmlString" scope = "session" >
            <jsp:setProperty name = "str" property = "value" param = "fString" />
    </jsp:useBean>

    <HTML>

    <HEAD>
            <META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1">
            <META NAME="GENERATOR" Content="Visual Page 1.1 for Windows">
            <TITLE>OracleJSP Extended Datatypes Sample</TITLE>
    </HEAD>

    <BODY BACKGROUND="images/bg.gif" BGCOLOR="#FFFFFF">

    <% if (submitCount.getValue() > 1) { %>
            <h3> Last submitted values </h3>
            <ul>
                    <li> bool: <%= bool.getValue() %>
                    <li> num: <%= num.getValue() %>
                    <li> fpnum: <%= fpnum.getValue() %>
                    <li> string: <%= str.getValue() %>
            </ul>
    <% }

       if (submitCount.getValue() > 0) { %>

            <jsp:setProperty name = "bool" property = "value" param = "fBoolean" />
            <jsp:setProperty name = "num" property = "value" param = "fNumber" />
            <jsp:setProperty name = "fpnum" property = "value" param = "fFPNumber" />
            <jsp:setProperty name = "str" property = "value" param = "fString" />

            <h3> New submitted values </h3>
            <ul>
                    <li> bool: <jsp:getProperty name="bool" property="value" />
                    <li> num: <jsp:getProperty name="num" property="value" />
                    <li> fpnum: <jsp:getProperty name="fpnum" property="value" />
                    <li> string: <jsp:getProperty name="str" property="value" />
            </ul>
    <% } %>

    <jsp:setProperty name = "submitCount" property = "value" value = "<%= submitCount.getValue() + 1
    %>" />

    <FORM ACTION="index.jsp" METHOD="POST" ENCTYPE="application/x-www-form-urlencoded">
    <P> <pre>
     boolean test: <INPUT TYPE="text" NAME="fBoolean" VALUE="<%= bool.getValue() %>" >
      number test: <INPUT TYPE="text" NAME="fNumber" VALUE="<%= num.getValue() %>" >
    fpnumber test: <INPUT TYPE="text" NAME="fFPNumber" VALUE="<%= fpnum.getValue() %>" >
      string test: <INPUT TYPE="text" NAME="fString" VALUE= "<%= str.getValue() %>" >
    </pre>

    <P> <INPUT TYPE="submit">

    </FORM>

    </BODY>

    </HTML>

### OracleJSP Support for XML and XSL

JSP technology can be used to produce dynamic XML pages as well as dynamic HTML pages. OracleJSP supports the use of XML and XSL technology with JSP pages in two ways:

- The OracleJSP translator includes logic to recognize standard XML-alternative JSP syntax.

- OracleJSP provides JML tags to apply an XSL stylesheet to the JSP output stream.

Additionally, the `oracle.xml.sql.query.OracleXMLQuery` class is provided with Oracle8i as part of the XML-SQL utility for XML functionality in database queries. This class requires file `xsu12.jar` (for JDK 1.2.x) or `xsu111.jar` (for JDK 1.1.x), which is also required for XML functionality in the OracleJSP database-access JavaBeans, and which is provided with Oracle8i release 8.1.7.

For a JSP sample using `OracleXMLQuery`, see "XML Query--XMLQuery.jsp".

For information about the `OracleXMLQuery` class and other XML-SQL utility features, refer to the Oracle8i Application Developer's Guide - XML.

#### XML-Alternative Syntax

JSP tags, such as `<%...%>` for scriptlets, `<%!...%>` for declarations, and`<%=...%>` for expressions, are not syntactically valid within an XML document. Sun Microsystems addressed this in the JavaServer Pages Specification, Version 1.1 by defining equivalent JSP tags using syntax that is XML-compatible. This is implemented through a standard DTD that you can specify within a `jsp:root` start tag at the beginning of an XML document.

This functionality allows you, for example, to write XML-based JSP pages in an XML authoring tool.

OracleJSP does not use this DTD directly or require you to use a `jsp:root` tag, but the OracleJSP translator includes logic to recognize the alternative syntax specified in the standard DTD. Table 5-1 documents this syntax.

##### Table 5-1 XML-Alternative Syntax

Standard JSP Syntax | XML-Alternative JSP Syntax
--- | ---
`<%@` `directive``...%>` Such as: `<%@ page ... %> <%@ include ... %>` | `<jsp:directive.``directive``... />` Such as: `<jsp:directive.page ... /> <jsp:directive.include ... />`
`<%! ... %>` (declaration) | `<jsp:declaration>` `...declarations go here...` `</jsp:declaration>`
`<%= ... %>` (expression) | `<jsp:expression>` `...expression goes here...` `</jsp:expression>`
`<% ... %>` (scriptlet) | `<jsp:scriptlet>` `...code fragment goes here...` `</jsp:scriptlet>`

JSP action tags, such as `jsp:useBean`, for the most part already use syntax that complies with XML. Changes due to quoting conventions or for request-time attribute expressions may be necessary, however.

#### JML Tags for XSL Stylesheets

Many uses of XML and XSL for dynamic pages require an XSL transformation to occur in the server before results are returned to the client.

OracleJSP provides two synonymous JML tags to simplify this process. Use either the JML `transform` tag or the JML `styleSheet` tag (their effects are identical), as in the following example:

    <jml:transform href="xslRef" >

       ...Tag body contains regular JSP commands and static text that
       produce the XML code that the stylesheet is to be applies to...

    </jml:transform >

(The `jml:` prefix is used by convention, but you can specify any prefix in your `taglib` directive.)

Important: If you will use any JML tags, refer to "Overview of the JSP Markup Language (JML) Sample Tag Library".

Note the following regarding the `href` parameter:

- It can refer to either a static XSL stylesheet or a dynamically generated one. For example, it can refer to a JSP page or servlet that generates the stylesheet.

- It can be a fully qualified URL (`http://`host[:port]`/yourpath`), an application-relative JSP reference (starting with "/"), or a page-relative JSP reference (not starting with "/"). See "Indirectly Requesting a JSP Page" for information about application-relative and page-relative paths.

- It can be dynamically specified. By default, the value of `href` is a static Java string. However, you can use standard JSP expression syntax to provide a dynamically computed value.

Typically, you would use the `transform` or `styleSheet` tag to transform an entire page. However, the tag applies only to what is in its body, between its start and end tags. Therefore, you can have distinct XSL blocks within a page, each block bounded by its own `transform` or `styleSheet` tag set, specifying its own `href` pointer to the appropriate stylesheet.

#### XSL Example using jml:transform

This section provides a sample XSL stylesheet and a sample JSP page that uses the `jml:transform` tag to filter its output through the stylesheet. (This is a simplistic example--the XML in the page is static. A more realistic example might use the JSP page to dynamically generate all or part of the XML before performing the transformation.)

##### Sample Stylesheet: hello.xsl

    <?xml version="1.0"?>

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

      <xsl:template match="page">
       <html>
        <head>
         <title>
          <xsl:value-of select="title"/>
         </title>
        </head>
        <body bgcolor="#ffffff">
         <xsl:apply-templates/>
        </body>
       </html>
      </xsl:template>

      <xsl:template match="title">
       <h1 align="center">
        <xsl:apply-templates/>
       </h1>
      </xsl:template>

      <xsl:template match="paragraph">
       <p align="center">
        <i>
         <xsl:apply-templates/>
        </i>
       </p>
      </xsl:template>

    </xsl:stylesheet>

##### Sample JSP Page: hello.jsp

    <%@ page session = "false" %>
    <%@ taglib uri="/WEB-INF/jmltaglib.tld" prefix="jml" %>

    <jml:transform href="style/hello.xsl" >

    <page>
     <title>Hello</title>
     <content>
      <paragraph>This is my first XML/XSL file!</paragraph>
     </content>
    </page>

    </jml:transform>

This example results in the following output:

### Oracle Database-Access JavaBeans

OracleJSP supplies a set of custom JavaBeans for accessing an Oracle database. The following beans are included in the `oracle.jsp.dbutil` package:

- `ConnBean` opens a simple database connection.

- `ConnCacheBean` uses Oracle's connection caching implementation for database connections. (This requires JDBC 2.0.)

- `DBBean` executes a database query.

- `CursorBean` provides general DML support for queries; `UPDATE`, `INSERT`, and `DELETE` statements; and stored procedure calls.

For examples using these beans, see "Database-Access JavaBean Samples".

All four beans implement the OracleJSP `JspScopeListener` interface for event notification. See "OracleJSP Event Handling--JspScopeListener".

This section presumes a working knowledge of Oracle JDBC. Consult the Oracle8i JDBC Developer's Guide and Reference as necessary.

Important: To use the Oracle database-access JavaBeans, install the file `ojsputil.jar` and include it in your classpath. This file is provided with the OracleJSP installation. For XML-related methods and functionality, you will also need file `xsu12.jar` (for JDK 1.2.x) or `xsu111.jar` (for JDK 1.1.x), which is provided with Oracle8i release 8.1.7.

#### ConnBean for a Database Connection

Use `oracle.jsp.dbutil.ConnBean` to establish a simple database connection (one that uses no connection pooling or caching).

Notes: For queries only, it is simpler to use `DBBean`, which has its own connection mechanism. To use connection caching, use `ConnCacheBean` instead. As with any JavaBean you use in a JSP page, you can set any of the `ConnBean` properties with a `jsp:setProperty` action instead of using the setter method directly.

`ConnBean` has the following properties:

- `user` (user ID for database schema)

- `password` (user password)

- `URL` (database connection string)

- `stmtCacheSize` (cache size for Oracle JDBC statement caching)

  Setting `stmtCacheSize` enables the Oracle JDBC statement caching feature. See "JDBC Statement Caching" for a brief overview of statement caching features and limitations.

- `executeBatch` (batch size for Oracle JDBC update batching)

  Setting `executeBatch` enables Oracle JDBC update batching. See "Update Batching" for a brief overview of this feature.

- `preFetch` (number of statements to prefetch in Oracle JDBC row prefetching)

  Setting `preFetch` enables Oracle JDBC row prefetching. Refer to "Row Prefetching" for a brief overview of this feature.

`ConnBean` provides the following setter and getter methods for these properties:

- `void setUser(String)`

- `String getUser()`

- `void setPassword(String)`

- `String getPassword()`

- `void setURL(String)`

- `String getURL()`

- `void setStmtCacheSize(int)`

- `int getStmtCacheSize()`

- `void setExecuteBatch(int)`

- `int getExecuteBatch()`

- `void setPreFetch(int)`

- `int getPreFetch()`

Use the following methods to open and close a connection:

- `void connect()`--Establish a database connection using `ConnBean` property settings.

- `void close()`--Close the connection and any open cursors.

Use the following method to open a cursor and return a `CursorBean` object:

- `CursorBean getCursorBean(int, String)`

or:

- `CursorBean getCursorBean(int)`

  Input the following:

  - one of the following `int` constants to specify the type of JDBC statement you want: `CursorBean.PLAIN_STMT` (for a `Statement` object), `CursorBean.PREP_STMT` (for a `PreparedStatement` object), or `CursorBean.CALL_STMT` (for a `CallableStatement` object)

  - a string specifying the SQL operation to execute (optional; alternatively, the SQL operation can be specified in the `CursorBean` method call that executes the statement)

See "CursorBean for DML and Stored Procedures" for information about `CursorBean` functionality.

#### ConnCacheBean for Connection Caching

Use `oracle.jsp.dbutil.ConnCacheBean` to use the Oracle JDBC connection caching mechanism (using JDBC 2.0 connection pooling) for your database connections. For a brief overview of connection caching, see "Database Connection Caching".

Notes: To use simple connection objects (no pooling or caching), use `ConnBean` instead. `ConnCacheBean` extends `OracleConnectionCacheImpl`, which extends `OracleDataSource` (both in Oracle JDBC package `oracle.jdbc.pool`). See the Oracle8i JDBC Developer's Guide and Reference

(release 8.1.6 or later) for more information about these Oracle JDBC classes.

- As with any JavaBean you use in a JSP page, you can set any of the `ConnCacheBean` properties with a `jsp:setProperty` action instead of using the setter method directly.

- Unlike `ConnBean`, when you use `ConnCacheBean`, you use normal `Connection` object functionality to create and execute statement objects.

`ConnCacheBean` has the following properties:

- `user` (user ID for database schema)

- `password` (user password)

- `URL` (database connection string)

- `maxLimit` (maximum number of connections allowed by this cache)

- `minLimit` (minimum number of connections existing for this cache; if you are using fewer than this number, then there will also be connections in the "idle pool" of the cache)

- `stmtCacheSize` (cache size for Oracle JDBC statement caching)

  Setting `stmtCacheSize` enables the Oracle JDBC statement caching feature. See "JDBC Statement Caching" for a brief overview of Oracle JDBC statement caching features and limitations.

- `cacheScheme` (type of cache):

  - `DYNAMIC_SCHEME`--New pooled connections can be created above and beyond the maximum limit, but each one is automatically closed and freed as soon as the logical connection instance that it provided is no longer in use.

  - `FIXED_WAIT_SCHEME`--When the maximum limit is reached, any new connection waits for an existing connection object to be released.

  - `FIXED_RETURN_NULL_SCHEME`--When the maximum limit is reached, any new connection fails (`null` is returned) until connection objects have been released.

The `ConnCacheBean` class supports methods defined in the Oracle JDBC `OracleConnectionCacheImpl` class, including the following getter and setter methods for its properties:

- `void setUser(String)`

- `String getUser()`

- `void setPassword(String)`

- `String getPassword()`

- `void setURL(String)`

- `String getURL()`

- `void setMaxLimit(int)`

- `int getMaxLimit()`

- `void setMinLimit(int)`

- `int getMinLimit()`

- `void setStmtCacheSize(int)`

- `int getStmtCacheSize()`

- `void setCacheScheme(int)`

  Specify `ConnCacheBean.DYNAMIC_SCHEME`, `ConnCacheBean.FIXED_WAIT_SCHEME`, or `ConnCacheBean.FIXED_RETURN_NULL_SCHEME`.

- `int getCacheScheme()`

  Returns `ConnCacheBean.DYNAMIC_SCHEME`, `ConnCacheBean.FIXED_WAIT_SCHEME`, or `ConnCacheBean.FIXED_RETURN_NULL_SCHEME`.

The `ConnCacheBean` class also inherits properties and related getter and setter methods from the `oracle.jdbc.pool.OracleDataSource` class. This provides getter and setter methods for the following properties: `databaseName`, `dataSourceName`, `description`, `networkProtocol`, `portNumber`, `serverName`, and `driverType`. For information about these properties and their getter and setter methods, see the Oracle8i JDBC Developer's Guide and Reference.

Use the following methods to open and close a connection:

- `Connection getConnection()`--Get a connection from the connection cache using `ConnCacheBean` property settings.

- `void close()`--Close all connections and any open cursors.

Although the `ConnCacheBean` class does not support Oracle JDBC update batching and row prefetching directly, you can enable these features by calling the `setDefaultExecuteBatch(int)` and `setDefaultRowPrefetch(int)` methods of the `Connection` object that you retrieve from the `getConnection()` method. Alternatively, you can use the `setExecuteBatch(int)` and `setRowPrefetch(int)` methods of JDBC statement objects that you create from the `Connection` object (update batching is supported only in prepared statements). See "Update Batching" and "Row Prefetching" for brief overviews of these features.

#### DBBean for Queries Only

Use `oracle.jsp.dbutil.DBBean` to execute queries only.

Notes: `DBBean` has its own connection mechanism; do not use `ConnBean`. Use `CursorBean` for any other DML operations (`UPDATE`, `INSERT`, `DELETE`, or stored procedure calls). As with any JavaBean you use in a JSP page, you can set any of the `DBBean` properties with a `jsp:setProperty` statement instead of using the setter method directly.

`DBBean` has the following properties:

- `user` (user ID for database schema)

- `password` (user password)

- `URL` (database connection string)

`DBBean` provides the following setter and getter methods for these properties:

- `void setUser(String)`

- `String getUser()`

- `void setPassword(String)`

- `String getPassword()`

- `void setURL(String)`

- `String getURL()`

Use the following methods to open and close a connection:

- `void connect()`--Establish a database connection using `DBBean` property settings.

- `void close()`--Close the connection and any open cursors.

Use either of the following methods to execute a query.

- `String getResultAsHTMLTable(String)`--Input a string with the `SELECT` statement.

  This method returns a string with the HTML commands necessary to output the result set as an HTML table. SQL column names (or aliases) are used for the table column headers.

- `String getResultAsXMLString(String)`--Input a string with the `SELECT` statement.

  This method returns the result set as an XML string, using SQL names (or aliases) for the XML tags.

#### CursorBean for DML and Stored Procedures

Use `oracle.jsp.dbutil.CursorBean` for `SELECT`, `UPDATE`, `INSERT`, or `DELETE` operations or stored procedure calls on a simple connection. It uses a previously defined `ConnBean` object for the connection.

You can specify a SQL operation in a `ConnBean` object `getCursorBean()` call, or through a call to one of the `create()`, `execute()`, or `executeQuery()` methods of a `CursorBean` object as described below.

`CursorBean` supports scrollable and updatable cursors, update batching, row prefetching, and query timeout limits. For information about these Oracle JDBC features, see the Oracle8i JDBC Developer's Guide and Reference, release 8.1.6 or later.

Notes: To use connection caching, use `ConnCacheBean` and normal `Connection` object functionality. Do not use `CursorBean`. As with any JavaBean you use in a JSP page, you can set any of the `CursorBean` properties with a `jsp:setProperty` action instead of using the setter method directly.

`CursorBean` has the following properties:

- `executeBatch` (batch size for Oracle JDBC update batching)

  Setting this property enables Oracle JDBC update batching.

- `preFetch` (number of statements to prefetch in Oracle JDBC row prefetching)

  Setting this property enables Oracle JDBC row prefetching.

- `queryTimeout` (number of seconds for the driver to wait for a statement to execute before issuing a timeout)

- `resultSetType` (scrollability of result set):

  - `TYPE_FORWARD_ONLY` (default)--A result set that can scroll only forward (using the `next()` method) and is not positionable.

  - `TYPE_SCROLL_INSENSITIVE`--A result set that can scroll forward or backward and is positionable, but is not sensitive to underlying database changes.

  - `TYPE_SCROLL_SENSITIVE`--A result set that can scroll forward or backward, is positionable, and is sensitive to underlying database changes.

  See the Oracle8i JDBC Developer's Guide and Reference for information about result set scrollability types.

- `resultSetConcurrency` (updatability of result set):

  - `CONCUR_READ_ONLY` (default)--A result set that is read-only (cannot be updated).

  - `CONCUR_UPDATABLE`--A result set that is updatable.

  See the Oracle8i JDBC Developer's Guide and Reference for information about updatable result sets.

You can set these properties with the following methods to enable Oracle JDBC features, as desired:

- `void setExecuteBatch(int)`

- `int getExecuteBatch()`

- `void setPreFetch(int)`

- `int getPreFetch()`

- `void setQueryTimeout(int)`

- `int getQueryTimeout()`

- `void setResultSetConcurrency(int)`

  Specify `CursorBean.CONCUR_READ_ONLY` or `CursorBean.CONCUR_UPDATABLE`.

- `int getResultSetConcurrency()`

  Returns `CursorBean.CONCUR_READ_ONLY` or `CursorBean.CONCUR_UPDATABLE`.

- `void setResultSetType(int)`

  Specify `CursorBean.TYPE_FORWARD_ONLY`, `CursorBean.TYPE_SCROLL_INSENSITIVE`, or `CursorBean.TYPE_SCROLL_SENSITIVE`.

- `int getResultSetType()`

  Returns `CursorBean.TYPE_FORWARD_ONLY`, `CursorBean.TYPE_SCROLL_INSENSITIVE`, or `CursorBean.TYPE_SCROLL_SENSITIVE`.

To execute a query once a `CursorBean` instance has been defined in a `jsp:useBean` statement, you can use `CursorBean` methods to create a cursor in one of two ways. You can use the following methods to create the cursor and supply a connection in separate steps:

- `void create()`

- `void setConnBean(ConnBean)`

Or you can combine the process into a single step:

- `void create(ConnBean)`

(Set up the `ConnBean` object as described in "ConnBean for a Database Connection".)

Then use the following method to specify and execute a query. (This uses a JDBC plain `Statement` object behind the scenes.)

- `ResultSet executeQuery(String)`

  Specify a string with the `SELECT` statement.

Alternatively, if you want to format the result set as an HTML table or XML string, use either of the following methods instead of `executeQuery()`:

- `String getResultAsHTMLTable(String)`

  Returns a string with HTML statements to create an HTML table for the result set. Specify a string with the `SELECT` statement.

- `String getResultAsXMLString(String)`

  Returns the result set data in an XML string. Specify a string with the `SELECT` statement.

To execute an `UPDATE`, `INSERT`, or `DELETE` statement once a `CursorBean` instance has been defined in a `jsp:useBean` action, you can use `CursorBean` methods to create a cursor in one of two ways. You can use the following methods to create the cursor (specifying a statement type as an integer and SQL statement as a string) and supply a connection:

- `void create(int, String)`

- `void setConnBean(ConnBean)`

Or you can combine the process into a single step:

- `void create(ConnBean, int, String)`

(Set up the `ConnBean` object as described in "ConnBean for a Database Connection".)

The `int` input is to specify one of the following constants to specify the type of JDBC statement you want: `CursorBean.PLAIN_STMT` (for a `Statement` object), `CursorBean.PREP_STMT` (for a `PreparedStatement` object), or `CursorBean.CALL_STMT` (for a `CallableStatement` object).

The `String` input is to specify the SQL statement.

Then use the following method to execute the `INSERT`, `UPDATE`, or `DELETE` statement. (You can ignore the `boolean` return value.)

- `boolean execute()`

Or for update batching, use the following method, which returns the number of rows affected. (See below for how to enable update batching.)

- `int executeUpdate()`

  Note: The `execute()` and `executeUpdate()` methods can optionally take a `String` to specify a SQL operation. The corresponding `create()` call, as well as the `getCursorBean()` call in `ConnBean`, optionally does not take a `String` to specify the SQL operation. Specify an operation either on statement creation or execution, but not both.

Additionally, `CursorBean` supports Oracle JDBC statement and result set functionality such as the `registerOutParameter()` method, `setXXX()` methods, and `getXXX()` methods.

Use the following method to close the database cursor:

- `void close()`

### OracleJSP Tag Library for SQL

With release 8.1.7, OracleJSP supplies a custom tag library for SQL functionality (separate from the JML custom tag library).

The following tags are provided:

- `dbOpen`--Open a database connection.

- `dbClose`--Close a database connection.

- `dbQuery`--Execute a query.

- `dbCloseQuery`--Close the cursor for a query.

- `dbNextRow`--Process the rows of a result set.

- `dbExecute`--Execute any SQL statement (DML or DDL).

These tags are described in the following subsections. For examples, see "SQL Tag Examples".

Note the following requirements for using SQL tags:

- Install the file `ojsputil.jar` and include it in your classpath. This file is provided with the OracleJSP installation.

- Make sure the tag library description file, `sqltaglib.tld`, is deployed with the application and is in the location specified in the `taglib` directives of your JSP pages, such as in the following example:

      <%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %>

For general information about JSP 1.1 tag library usage, including tag library description files and `taglib` directives, see "Standard Tag Library Framework".

#### SQL dbOpen Tag

Use the `dbOpen` tag to open a database connection.

    <sql:dbOpen
       [ connId="connection-id" ]
         user="username"
         password="password"
         URL="databaseURL" >
    ...
    </sql:dbOpen>

Nested code that you want to execute through this connection can go into the tag body, between the `dbOpen` start and end tags. (See "SQL Tag Examples".) If you use the optional `connId` parameter to set a connection identifier, then code to execute through this connection can reference the connection identifier and does not have to be between the `dbOpen` start and end tags. (The connection identifier can be any arbitrary string.)

Note that you do not have to hardcode a password into the JSP page (which would be a security concern). Instead, you can get it and other parameters from the `request` object, as follows:

    <sql:dbOpen connId="conn1" user=<%=request.getParameter("user")%>
                password=<%=request.getParameter("password")%> URL="url" />

(In this example you do not need a tag body for code that will use this connection; statements using the connection can reference it through the `conn1` value of `connId`.)

If you set a connection identifier, then the connection is not closed until you close it explicitly with a `dbClose` tag. Without a connection identifier, the connection is closed automatically when the `</sql:dbOpen>` end tag is encountered.

This tag uses a `ConnBean` object for the connection. You can optionally set the additional `ConnBean` properties `stmtCacheSize`, `preFetch`, and `batchSize` to enable those Oracle JDBC features. See "ConnBean for a Database Connection" for more information.

#### SQL dbClose Tag

Use the `dbClose` tag to close a connection associated with the optional `connId` parameter specified in a `dbOpen` tag. If `connId` is not used in the `dbOpen` tag, then the connection is closed automatically when the `dbOpen` end tag is reached; no `dbClose` tag is required.

    <sql:dbClose connId="connection-id" />

Note: In an OracleJSP environment, you can have the connection closed automatically with session-based event handling through the Oracle `JspScopeListener` mechanism. See "OracleJSP Event Handling--JspScopeListener".

#### SQL dbQuery Tag

Use the `dbQuery` tag to execute a query, outputting the result either as a JDBC result set, HTML table, or XML string. Place the `SELECT` statement (one only) in the tag body, between the `dbQuery` start and end tags.

    <sql:dbQuery
       [ queryId="query-id" ]
       [ connId="connection-id" ]
       [ output="HTML|XML|JDBC"] >
        ...SELECT statement (one only)...
     </sql:dbQuery>

Important: In release 8.1.7 (OracleJSP 1.1.0.0.0), do not terminate the `SELECT` statement with a semi-colon. This would result in a syntax error.

All parameters of this tag are optional, depending on your intended uses as described below.

You must use the `queryId` parameter to set a query identifier if you want to process the result set using a `dbNextRow` tag. The `queryId` can be any arbitrary string.

Additionally, if the `queryId` parameter is present, then the cursor is not closed until you close it explicitly with a `dbCloseQuery` tag. Without a query identifier, the cursor is closed automatically when the `</sql:dbQuery>` end tag is encountered.

If `connId` is not specified, then `dbQuery` must be nested within the body of a `dbOpen` tag and will use the connection opened in the `dbOpen` tag.

For the output type:

- `HTML` puts the result set into an HTML table (default).

- `XML` puts the result set into an XML string.

- `JDBC` puts the result set into a JDBC `ResultSet` object that can be processed using the `dbNextRow` tag to iterate through the rows.

This tag uses a `CursorBean` object for the cursor. See "CursorBean for DML and Stored Procedures" for information about `CursorBean` functionality.

#### SQL dbCloseQuery Tag

Use the `dbCloseQuery` tag to close a cursor associated with the optional `queryId` parameter specified in a `dbQuery` tag. If `queryId` is not used in the `dbQuery` tag, then the cursor is closed automatically when the `dbQuery` end tag is reached; no `dbCloseQuery` tag is required.

    <sql:dbCloseQuery queryId="query-id" />

Note: In an OracleJSP environment, you can have the cursor closed automatically with session-based event handling through the Oracle `JspScopeListener` mechanism. See "OracleJSP Event Handling--JspScopeListener".

#### SQL dbNextRow Tag

Use the `dbNextRow` tag to process each row of a result set obtained in a `dbQuery` tag and associated with the specified `queryId`. Place the processing code in the tag body, between the `dbNextRow` start and end tags. The body is executed for each row of the result set.

For you to use the `dbNextRow` tag, the `dbQuery` tag must specify `output=JDBC`, and specify a `queryId` for the `dbNextRow` tag to reference.

    <sql:dbNextRow queryId="query-id" >

    ...Row processing...

    </sql:dbNextRow >

The result set object is created in an instance of the tag-extra-info class of the `dbQuery` tag (see "Tag Library Description Files" for information about tag-extra-info classes).

#### SQL dbExecute Tag

Use the `dbExecute` tag to execute any DML or DDL statement (one only). Place the statement in the tag body, between the `dbExecute` start and end tags.

    <sql:dbExecute
       [connId="connection-id"]
       [output="yes|no"] >
       ...DML or DDL statement (one only)...
    </sql:dbExecute >

Important: In release 8.1.7, do not terminate the DML or DDL statement with a semi-colon. This would result in a syntax error.

If you do not specify `connId`, then you must nest `dbExecute` within the body of a `dbOpen` tag and use the connection opened in the `dbOpen` tag.

If `output=yes`, then for DML statements, the HTML string "number row[s] affected" will be output to the browser to notify the user how many database rows were affected by the operation; for DDL statements, the statement execution status will be printed. The default setting is `no`.

This tag uses a `CursorBean` object for the cursor. See "CursorBean for DML and Stored Procedures" for information about `CursorBean` functionality.

#### SQL Tag Examples

The following examples show how to use the OracleJSP SQL tags. (To run them yourself, you will need to set the URL, user name, and password appropriately.)

##### Example 1: Query with Connection ID

    <%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %>
       <HTML>
           <HEAD>
               <TITLE>A simple example with open, query, and close tags</TITLE>
           </HEAD>
           <BODY BGCOLOR="#FFFFFF">
                <HR>
                <sql:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816"
                            user="scott" password="tiger" connId="con1">
                </sql:dbOpen>
                <sql:dbQuery connId="con1">
                   select * from EMP
                </sql:dbQuery>
                <sql:dbClose connId="con1" />
                <HR>
           </BODY>
      </HTML>

##### Example 2: Query Nested in dbOpen Tag

    <%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %>
       <HTML>
           <HEAD>
               <TITLE>Nested Tag with Query inside Open  </TITLE>
           </HEAD>
           <BODY BGCOLOR="#FFFFFF">
                <HR>
                <sql:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816"
                            user="scott" password="tiger">
                   <sql:dbQuery>
                      select * from EMP
                   </sql:dbQuery>
                </sql:dbOpen>
                <HR>
           </BODY>
      </HTML>

##### Example 3: Query with XML Output

    <%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %>
       <HTML>
           <HEAD>
               <TITLE>A simple tagLib with XML output</TITLE>
           </HEAD>
           <BODY BGCOLOR="#FFFFFF">
                <HR>
                <sql:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816"
                            user="scott" password="tiger">
                   <sql:dbQuery output="xml">
                     select * from EMP
                   </sql:dbQuery>
                </sql:dbOpen>
                <HR>
           </BODY>
      </HTML>

##### Example 4: Result Set Iteration

    <%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %>
       <HTML>
           <HEAD>
               <TITLE>Result Set Iteration Sample </TITLE>
           </HEAD>
           <BODY BGCOLOR="#FFFFFF">
                <HR>
                <sql:dbOpen connId="con1" URL="jdbc:oracle:thin:@dlsun991:1521:816"
                           user="scott" password="tiger">
                </sql:dbOpen>
                <sql:dbQuery connId="con1" output="jdbc" queryId="myquery">
                   select * from EMP
                </sql:dbQuery>
                <sql:dbNextRow queryId="myquery">
                    <%= myquery.getString(1) %>
                </sql:dbNextRow>
                <sql:dbCloseQuery queryId="myquery" />
                <sql:dbClose connId="con1" />
                <HR>
           </BODY>
      </HTML>

##### Example 5: DDL and DML Statements

This example uses an HTML form to let the user specify what kind of DML or DDL statement to execute.

    <%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %>
    <HTML>
    <HEAD><TITLE>DML Sample</TITLE></HEAD>
    <FORM METHOD=get>
    <INPUT TYPE="submit" name="drop" VALUE="drop table test_table"><br>
    <INPUT TYPE="submit" name="create"
                         VALUE="create table test_table (col1 NUMBER)"><br>
    <INPUT TYPE="submit" name="insert"
                         VALUE="insert into test_table values (1234)"><br>
    <INPUT TYPE="submit" name="select" VALUE="select * from test_table"><br>
    </FORM>
    <BODY BGCOLOR="#FFFFFF">
    Result:
                <HR>
                <sql:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816"
                 user="scott" password="tiger">
                  <% if (request.getParameter("drop")!=null) { %>
                  <sql:dbExecute output="yes">
                     drop table test_table
                  </sql:dbExecute>
                  <% } %>
                  <% if (request.getParameter("create")!=null) { %>
                  <sql:dbExecute output="yes">
                     create table test_table (col1 NUMBER)
                  </sql:dbExecute>
                  <% } %>
                  <% if (request.getParameter("insert")!=null) { %>
                  <sql:dbExecute output="yes">
                    insert into test_table values (1234)
                  </sql:dbExecute>
                  <% } %>
                  <% if (request.getParameter("select")!=null) { %>
                  <sql:dbQuery>
                     select * from test_table
                  </sql:dbQuery>
                  <% } %>
                </sql:dbOpen>
                <HR>
    </BODY>
    </HTML>

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