Oracle JavaServer Pages Developer's Guide and Reference
Release 8.1.7
Part Number A83726-01

Library
Service
Contents
Index

Go to previous page Go to beginning of chapter Go to next page

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

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:

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.

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.

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

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.

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

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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

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.

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

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.

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:

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:

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:

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:

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

Use the following methods to open and close a connection:

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

or:

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:

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

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:

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:

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

Use the following methods to open and close a connection:

Use either of the following methods to execute a query.

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:

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

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:

Or you can combine the process into a single step:

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

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():

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:

Or you can combine the process into a single step:

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

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

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:

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:

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

Note the following requirements for using SQL tags:

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:

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>

Go to previous page
 Go to beginning of chapter
 Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.
All Rights Reserved.

Library
Service
Contents
Index