4
Data-Access JavaBeans and Tags

This chapter describes JavaBeans and tags provided with OC4J for use in accessing a database from servlets and JSP pages.

The chapter consists of the following sections:

• JavaBeans for Data Access

• SQL Tags for Data Access

JavaBeans for Data Access

The OC4J product includes a set of JavaBeans that you can use to access a database. The following sections describe the beans:

• Introduction to Data-Access JavaBeans

• Data-Access Support for Data Sources and Pooled Connections

• Data-Access JavaBean Descriptions

Note: The JavaBeans described here are used by the tags discussed in "SQL Tags for Data Access". Generally speaking, these beans and tags can be used with non-Oracle databases, assuming you have appropriate JDBC driver classes; however, numerous features described below, as noted, are Oracle-specific.

Introduction to Data-Access JavaBeans

OC4J supplies a set of custom JavaBeans for database access. The following beans are included in the oracle.jsp.dbutil package:

• ConnBean opens a database connection. This bean also supports data sources and connection pooling. See "Data-Access Support for Data Sources and Pooled Connections" for related information.

• ConnCacheBean uses the Oracle JDBC connection caching implementation for database connections. This requires JDBC 2.0.

• DBBean executes a database query. It also has its own connection mechanism, but does not support data sources.

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

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

To use the data-access JavaBeans, verify that the file ojsputil.jar is installed and in your classpath. This file is provided with the OC4J installation. For XML-related methods and functionality, you will also need the file xsu12.jar (for JDK 1.2.x) or xsu111.jar (for JDK 1.1.x), both of which are provided with Oracle Application Server.

You will also need appropriate JDBC driver classes installed and in your classpath, such as classes12.zip for Oracle Database and JDK 1.2 or higher.

Note: The Oracle data-access JavaBeans implement the Oracle JspScopeListener interface for event notification. Refer to "JSP Event-Handling with JspScopeListener" for information about this interface.

Data-Access Support for Data Sources and Pooled Connections

The data-access JavaBeans, as well as the data-access tag library, support the use of data sources to specify connection properties. This is also how support for connection pooling is implemented. This mechanism supports both Oracle connection objects and OC4J connection objects.

To use a data source in a JSP page, you must define the data source, its JNDI name, and its connection and pooling properties. In OC4J, do this in a <data-source> element in the data-sources.xml file. Here is an example:

<data-source
   class="oracle.jdbc.pool.OracleDataSource"
   name="jdbc/ejbpool/OracleDS"
   location="jdbc/ConnectionDS"
   ejb-location="jdbc/ejbpool/OracleDS"
   url="jdbc:oracle:thin:@myhost:1521:orcl"
   username="scott"
   password="tiger"
   min-connections="3"
   max-connections="50"
   wait-timeout="10"
   inactivity-timeout="30" />

It is advisable to use only the ejb-location JNDI name in the JNDI lookup for an emulated data source. See the Oracle Application Server Containers for J2EE Services Guide for more information about data sources.

Data-Access JavaBean Descriptions

The following sections describe attributes and methods of the data-access JavaBeans--ConnBean, ConnCacheBean, DBBean, and CursorBean--and concludes with an example that uses a data source:

• ConnBean for a Database Connection

• ConnCacheBean for Connection Caching

• DBBean for Queries Only

• CursorBean for DML and Stored Procedures

• Example: Using ConnBean and CursorBean with a Data Source

ConnBean for a Database Connection

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

Note: For queries only, if you do not require a data source, it is simpler to use DBBean, which has its own connection mechanism.

ConnBean has the following properties. The user, password, and URL properties are not required if you use a data source.

• dataSource: JNDI name for a data source location

  This is valid only for an environment that supports data sources. See "Data-Access Support for Data Sources and Pooled Connections" for information about how to set up a data source in OC4J.

• user: user ID for database schema

• password: user password

• URL: database connection string

• stmtCacheSize: cache size for Oracle JDBC statement caching

  Setting stmtCacheSize enables Oracle JDBC statement caching.

• executeBatch: batch size for Oracle JDBC update batching

  Setting executeBatch enables Oracle JDBC update batching.

• preFetch: number of statements to prefetch in Oracle JDBC row prefetching

  Setting preFetch enables Oracle JDBC row prefetching.

• commitOnClose: "true" or "false" for whether to execute commit when the connection is closed

  The value of commitOnClose indicates whether an automatic commit should be executed when the connection is closed. A "true" setting results in a commit; a "false" setting results in a rollback. Prior to Oracle9iAS Release 2, an automatic commit was always executed, but in current releases the default is an automatic rollback. The commitOnClose property allows for backward compatibility to ease migration.

  Be aware that there can be an application-wide commit-on-close setting in the application web.xml file, but the setting of the ConnBean property is not automatically dependent on that setting. If a JSP pages uses ConnBean instead of a dbOpen tag, the value of the commit-on-close context parameter should be retrieved and then explicitly set as the commitOnClose value in the ConnBean instance. For reference, here is a sample web.xml entry that sets the commit-on-close context parameter:

  <context-param>
     <param-name>commit-on-close</param-name>
     <param-value>true</param-value>
  </context-param>
  
  

Note: See the Oracle9i JDBC Developer's Guide and Reference for information about statement caching, update batching, and row prefetching.

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

• void setDataSource(String)

• String getDataSource()

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

• void setCommitOnClose(String)

• String getCommitOnClose()

Note: 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.

Use the following methods to open and close a connection or to verify its status:

• void connect()

  Establish a database connection using ConnBean property settings.

• void close()

  Close the connection and any open cursors.

• boolean isConnectionClosed()

  Determine if the connection is closed.

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, you can specify the SQL operation 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. Refer to the Oracle9i JDBC Developer's Guide and Reference for information about connection caching.

Notes: To use data sources or simple connection objects, use ConnBean instead. ConnCacheBean extends OracleConnectionCacheImpl, which extends OracleDataSource (both in Oracle JDBC package oracle.jdbc.pool).

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 use fewer than this number, 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. Refer to the Oracle9i JDBC Developer's Guide and Reference for information about Oracle JDBC statement caching features and limitations.

• cacheScheme: type of cache

  This is indicated by one of the following int constants.

  • 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, returning null, 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 Oracle9i JDBC Developer's Guide and Reference.

Note: 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.

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.) Refer to the Oracle9i JDBC Developer's Guide and Reference for information about these features.

Notes: ConnCacheBean has the same functionality as the OracleConnectionCacheImpl class. See the Oracle9i JDBC Developer's Guide and Reference for more information. When you use ConnCacheBean, use normal Connection object functionality to create and execute statement objects (unlike the case with ConnBean).

DBBean for Queries Only

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

Notes: DBBean has its own connection mechanism but does not support data sources. If you require a data source, use ConnBean instead. Use CursorBean for any other DML operations (UPDATE, INSERT, DELETE, or stored procedure calls).

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

Note: 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.

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 that contains 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 Oracle9i JDBC Developer's Guide and Reference.

Note: To use connection caching, use ConnCacheBean and normal Connection object functionality. Do not use CursorBean.

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 the result set

  This is indicated by one of the following int constants.

  • TYPE_FORWARD_ONLY (default): Use this for a result set that can scroll only forward (using the next() method) and cannot be positioned.

  • TYPE_SCROLL_INSENSITIVE: Use this for a result set that can scroll forward or backward and can be positioned, but is not sensitive to underlying data changes.

  • TYPE_SCROLL_SENSITIVE: Use this for a result set that can scroll forward or backward, can be positioned, and is sensitive to underlying data changes.

• resultSetConcurrency: updatability of the result set

  This is indicated by one of the following int constants.

  • CONCUR_READ_ONLY (default): Use this for a result set that is read-only (cannot be updated).

  • CONCUR_UPDATABLE: Use this for a result set that is updatable.

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.

Note: 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.

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. Use the following methods to create the cursor and supply a connection in separate steps:

• void create()

• void setConnBean(ConnBean)

Alternatively, use the following method to combine the process into a single step:

• void create(ConnBean)

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

Use the following method to specify and execute a query (using a JDBC plain Statement object behind the scenes):

• ResultSet executeQuery(String)

  Input a string that contains 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. Use the following methods to create the cursor, specifying a statement type as an integer and specifying a SQL statement as a string, and supply a connection:

• void create(int, String)

• void setConnBean(ConnBean)

Alternatively, use the following method to 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 takes 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.

Use the following method to execute the INSERT, UPDATE, or DELETE statement. You can ignore the boolean return value.

• boolean execute()

Alternatively, for update batching, use the following method, which returns the number of rows affected.

• int executeUpdate()

Note: Specify the SQL operation either during statement creation or during statement execution, but not both. The execute() and executeUpdate() methods can optionally take a string to specify a SQL operation. This is also true of the create() method, as well as the getCursorBean() method in ConnBean.

Additionally, CursorBean supports Oracle JDBC functionality such as registerOutParameter() for callable statements, setXXX() methods for prepared statements and callable statements, and getXXX() methods for result sets and callable statements.

Use the following method to close the database cursor:

• void close()

Example: Using ConnBean and CursorBean with a Data Source

This following is a sample JSP page that uses ConnBean with a data source to open a connection, then uses CursorBean to execute a query.

<%@ page import="java.sql.*, oracle.jsp.dbutil.*" %>
<jsp:useBean id="cbean" class="oracle.jsp.dbutil.ConnBean" scope="session">
   <jsp:setProperty name="cbean" property="dataSource"
                    value="<%=request.getParameter("datasource")%>"/>
</jsp:useBean>
<% try {
      cbean.connect();
      String sql="SELECT ename, sal FROM scott.emp ORDER BY ename";
      CursorBean cb = cbean.getCursorBean (CursorBean.PREP_STMT, sql);
      out.println(cb.getResultAsHTMLTable());
      cb.close();
      cbean.close();
    } catch (SQLException e) {
      out.println("<P>" + "There was an error doing the query:");
      out.println("<PRE>" + e + "</PRE>\n<P>"); }
%>

SQL Tags for Data Access

OC4J includes a set of tags you can use in JSP pages to execute SQL commands to access a database. The following sections describe the tags:

• Introduction to Data-Access Tags

• Data-Access Tag Descriptions

Note: The custom SQL tag library provided with OC4J pre-dates the JavaServer Pages Standard Tag Library (JSTL) and has areas of duplicate functionality. For standards compliance, it is now generally advisable to use JSTL instead. See "Support for the JavaServer Pages Standard Tag Library". Oracle is not desupporting the existing library, however. For features in the custom library that are not yet available in JSTL, where there seems to be general usefulness, Oracle will try to have the features adopted into the JSTL standard as appropriate.

Introduction to Data-Access Tags

OC4J supplies a custom tag library for SQL functionality, consisting of the following tags:

• dbOpen: Open a database connection. This tag also supports data sources and connection pooling. See "Data-Access Support for Data Sources and Pooled Connections" for related information.

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

• dbSetParam: Set a parameter to bind into a dbQuery or dbExecute tag.

• dbSetCookie: Set a cookie.

Note the following requirements for using SQL tags:

• You will need the appropriate JDBC driver file, such as classes12.zip for JDK 1.2 or higher, installed and in your classpath.

• Verify that the file ojsputil.jar is installed and in your classpath. This file is provided with the OC4J installation, in the "well-known" tag library directory.

• The tag library descriptor, sqltaglib.tld, must be available to the application, and any JSP page using the library must have an appropriate taglib directive. In an Oracle Application Server installation, the TLD is in ojsputil.jar. The uri value for sqltaglib.tld is the following:

  http://xmlns.oracle.com/j2ee/jsp/tld/ojsp/sqltaglib.tld
  
  

For general information about JSP tag library usage, including tag library descriptor files, taglib directives, the well-known tag library directory, and the meaning of uri values, refer to the Oracle Application Server Containers for J2EE Support for JavaServer Pages Developer's Guide.

Notes: The data-access tags use the beans described in "JavaBeans for Data Access". Generally speaking, these beans and tags can be used with non-Oracle databases, assuming you have appropriate JDBC driver classes; however, numerous features described below, as noted, are Oracle-specific. For applications using the data-access tags, consider using the dbSetParam tag to supply only parameter values rather than textual completion of the SQL statement itself. This avoids the possibility of what is referred to as "SQL poisoning", where users might enter additional SQL in addition to the expected value.

Data-Access Tag Descriptions

The following sections provide detailed syntax for the data-access tags and an example using dbOpen and dbQuery tags with a data source:

• SQL dbOpen Tag

• SQL dbClose Tag

• SQL dbQuery Tag

• SQL dbCloseQuery Tag

• SQL dbNextRow Tag

• SQL dbExecute Tag

• SQL dbSetParam Tag

• SQL dbSetCookie Tag

• Example: Using dbOpen and dbQuery with a Data Source

Notes: The prefix "sql:" is used in the tag syntax here. This is by convention but is not required. You can specify any desired prefix in your taglib directive. See "Tag Syntax Symbology and Notes" for general information about tag syntax conventions in this manual.

SQL dbOpen Tag

Use the dbOpen tag to open a database connection for subsequent SQL operations through such tags as dbQuery and dbExecute. Do this by specifying a data source location, in which case connection caches are supported, or by specifying the user, password, and URL individually. See "Data-Access Support for Data Sources and Pooled Connections" for information about how to set up a data source in OC4J.

The implementation uses oracle.jsp.dbutil.ConnBean instances. For simple connections, but not connection caches, you can optionally set ConnBean properties such as stmtCacheSize, preFetch, and batchSize to enable those Oracle JDBC features. See "ConnBean for a Database Connection" for more information.

The ConnBean object for the connection is created in an instance of the tag-extra-info class of the dbOpen tag. Refer to the Oracle Application Server Containers for J2EE Support for JavaServer Pages Developer's Guide for information about the standard JSP tag library framework and tag-extra-info classes.

Syntax

<sql:dbOpen
   [ connId = "connection_id" ]
   [ scope = "page" | "request" | "scope" | "application" ]
   [ dataSource = "JNDI_name" ]
   [ user = "username" 
     password = "password" 
     URL = "databaseURL" ]
   [ commitOnClose = "true" | "false" ] > 

   ...

</sql:dbOpen>

Nested code that you want to execute through this connection can go into the tag body, between the dbOpen start-tag and end-tag.

Note: You must set either the dataSource attribute or the user, password, and URL attributes. Optionally, you can use a data source to specify a URL, then use the dbOpen tag user and password attributes separately. When a data source is used, and is for a cache of connections, the first use of the cache initializes it. If you specify the user and password through the dbOpen tag user and password attributes, that will initialize the cache for that user and password. Subsequent uses of the cache are for the same user and password.

Attributes

• connId: Optionally use this to specify an ID name for the connection. You can then reference this ID in subsequent tags such as dbQuery or dbExecute. Alternatively, you can nest dbQuery and dbExecute tags inside the dbOpen tag. You can also reference the connection ID in a dbClose tag when you want to close the connection.

  You can still specify a connection ID if you nest dbQuery or dbExecute tags inside the dbOpen tag. In this case, the connection will be found through the connection ID. With the scope attribute, it is possible to have multiple connections using the same connection ID but different scopes.

  If you specify a connection ID, then the connection is not closed until you close it explicitly with a dbClose tag. Without a connection ID, the connection is closed automatically when the dbOpen end-tag is encountered.

• scope (used only with a connId): Use this to specify the desired scope of the connection instance. The default is page scope.

  If you specify a scope setting in a dbOpen tag, then you must specify the same scope setting in any other tag--dbQuery, dbExecute, or dbClose--that uses the same connection ID.

• dataSource (required if you do not set the user, password, and URL attributes): Optionally use this to specify the JNDI name of a data source for database connections. First set up the data source in the OC4J data-sources.xml file. (See "Data-Access Support for Data Sources and Pooled Connections".) The dataSource setting should correspond to the location name, ejb-location name, or pooled-location name in a <data-source> element in data-sources.xml.

  A data source must specify a URL setting, but does not have to specify a user/password pair. You can use the dbOpen tag user and password attributes instead.

  This attribute is supported only in OC4J environments.

Note: It is advisable to use only the ejb-location JNDI name in the JNDI lookup for an emulated data source. See the Oracle Application Server Containers for J2EE Services Guide for more information about data sources.

• user (required if no user/password pair is specified through a data source): This is the user name for a database connection.

  If a user name is specified through both a data source and the user attribute, the user attribute takes precedence. It is advisable to avoid such duplication, because conflicts could arise if the data source is a pooled connection with existing logical connections using a different user name.

• password (required if no user/password pair is specified through a data source): This is the user password for a database connection.

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

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

  As with the user attribute, if a password is specified through both a data source and the password attribute, the password attribute takes precedence.

• URL (required if no data source is specified): This is the URL for a database connection. If a URL is supplied through a data source, the dbOpen tag URL attribute is ignored.

• commitOnClose: Set this to "true" for an automatic SQL commit when the connection is closed or goes out of scope. The default "false" setting results in an automatic SQL rollback.

  As a convenience, if you want to specify application-wide automatic commit or rollback behavior, set the parameter name commit-on-close in the application web.xml file, as in the following example:

  <context-param>
     <param-name>commit-on-close</param-name>
     <param-value>true</param-value>
  </context-param>
  
  

  The commitOnClose setting in a dbOpen tag takes precedence over the commit-on-close setting in web.xml.

Note: In previous releases, the behavior is always to commit automatically when the connection is closed. The commitOnClose attribute offers backward compatibility to simplify migration.

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; a dbClose tag is not required.

Note that by using the JspScopeListener utility provided with OC4J, you can have the connection closed automatically with session-based event-handling. Refer to "JSP Event-Handling with JspScopeListener" for information.

Syntax

<sql:dbClose connId = "connection_id" 
           [ scope = "page" | "request" | "scope" | "application" ] />

Attributes

• connId (required): This is the ID for the connection being closed, specified in the dbOpen tag that opened the connection.

• scope: This is the scope of the connection instance. The default is "page", but if the dbOpen tag specified a scope other than page, you must specify that same scope in the dbClose tag.

SQL dbQuery Tag

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

This tag uses an oracle.jsp.dbutil.CursorBean object for the cursor, so you can set properties such as the result set type, result set concurrency, batch size, and prefetch size, if desired. See "CursorBean for DML and Stored Procedures" for information about CursorBean functionality.

For XML usage, this tag acts as an XML producer. See "XML Producers and XML Consumers" for more information. Also see "Example Using the transform and dbQuery Tags".

Syntax

<sql:dbQuery 
           [ queryId = "query_id" ]
           [ connId = "connection_id" ]
           [ scope = "page" | "request" | "scope" | "application" ]
           [ output = "HTML" | "XML" | "JDBC" ] 
           [ maxRows = "number" ]
           [ skipRows = "number" ]
           [ bindParams = "value" ]
           [ toXMLObjName = "objectname" ] >

    ...SELECT statement (one only)...

 </sql:dbQuery>

Important: Do not terminate the SELECT statement with a semicolon. This currently results in a syntax error. The dbQuery tag does not currently support LOB columns.

Attributes

• queryId: You can use this to specify an ID name for the cursor. This is required if you want to process the results using a dbNextRow tag.

  If the queryId parameter is present, then the cursor is not closed until you close it explicitly with a dbCloseQuery tag. Without a query ID, the cursor is closed automatically when the dbQuery end-tag is encountered. This is not a request-time attribute, meaning it cannot take a JSP expression value.

• connId: This is the ID for a database connection, according to the connId setting in the dbOpen tag that opened the connection. If you do not specify connId in a dbQuery tag, then the tag must be nested within the body of a dbOpen tag and will use the connection opened in the dbOpen tag. This is not a request-time attribute.

• scope: This is the scope of the connection instance. The default is "page", but if the associated dbOpen tag specified a scope other than page, you must specify that same scope in the dbQuery tag. This is not a request-time attribute.

• output: This is the desired output format, one of the following.
  • HTML specifies that the result set is to be output as an HTML table (default).

  • XML specifies that the result set is to be output as an XML string, or an XML DOM object if an object name is specified in the toXMLObjName attribute.

  • JDBC specifies that the result set is to be output as a JDBC ResultSet object that can be processed using the dbNextRow tag to iterate through the rows.

• maxRows: This is the maximum number of rows of data to display. The default is all rows.

• skipRows: This is the number of data rows to skip in the query results before displaying results. The default is 0.

• bindParams: Use this to bind a parameter into the query. The following example is from an application that prompts the user to enter an employee number, using bindParams to bind the specified value into the empno field of the query:

  <sql:dbQuery connId="con1" bindParams="empno">
       select * from EMP where empno=?
  </sql:dbQuery>
  
  

  Alternatively, you can set a parameter value with the dbSetParam tag to bind it in through the bindParams attribute. See "SQL dbSetParam Tag".

• toXMLObjName: Specify an XML object name if you want to output the results as an XML DOM object. To use this, you must also set output to "XML".

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 specified in the dbQuery tag, then the cursor is closed automatically when the dbQuery end-tag is reached; a dbCloseQuery tag is not required.

Syntax

<sql:dbCloseQuery queryId = "query_id" />

Attributes

• queryId (required): The ID for the cursor to be closed, specified in the dbQuery tag that opened the cursor.

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-tag and end-tag. The body is executed for each row of the result set.

To use the dbNextRow tag, the dbQuery tag must set output to "JDBC" and specify a queryId for the dbNextRow tag to reference.

The result set object is created in an instance of the tag-extra-info class of the dbQuery tag. Refer to the Oracle Application Server Containers for J2EE Support for JavaServer Pages Developer's Guide for information about the standard JSP tag library framework and tag-extra-info classes.

Syntax

<sql:dbNextRow queryId = "query_id" >
...Row processing...
</sql:dbNextRow >

Attributes

• queryId (required): This is the ID of the cursor containing the results to be processed, specified in the dbQuery tag that opened the cursor.

Example

The following example shows the combined use of a dbOpen, dbQuery, and dbNextRow tag.

<sql:dbOpen connId="con1" URL="jdbc:oracle:thin:@myhost: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" />

SQL dbExecute Tag

Use the dbExecute tag to execute a single DML or DDL statement. Place the statement in the tag body, between the dbExecute start-tag and end-tag.

This tag uses an oracle.jsp.dbutil.CursorBean object for the cursor. See "CursorBean for DML and Stored Procedures" for information about CursorBean functionality.

Syntax

<sql:dbExecute 
           [ connId = "connection_id" ]
           [ scope = "page" | "request" | "scope" | "application" ]
           [ output = "yes" | "no" ]
           [ bindParams = "value" ] >

   ...DML or DDL statement (one only)...

</sql:dbExecute > 

Important: Do not terminate the DML or DDL statement with a semicolon. This currently results in a syntax error. The dbExecute tag does not currently support LOB columns.

Attributes

• connId: This is the ID of a database connection, according to the connId setting in the dbOpen tag that opened the connection. If you do not specify connId in a dbExecute tag, then the tag must be nested within the body of a dbOpen tag and will use the connection opened in the dbOpen tag.

• scope: This is the scope of the connection instance. The default is "page", but if the dbOpen tag specified a scope other than page, you must specify that same scope in the dbExecute tag.

• output: 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 is "no".

• bindParams: Use this to bind a parameter into the SQL statement. The following example is from an application that prompts the user to enter an employee number, using bindParams to bind the specified value into the empno field of the DELETE statement:

  <sql:dbExecute connId="con1" bindParams="empno">
       delete from EMP where empno=?
  </sql:dbExecute>
  
  

  Alternatively, you can set a parameter value with the dbSetParam tag to bind it in through the bindParams attribute. See the next section, "SQL dbSetParam Tag".

SQL dbSetParam Tag

You can use this tag to set a parameter value to bind into a query, through the dbQuery tag, or to bind into any other SQL operation, through the dbExecute tag.

Note: For applications using the data-access tags, consider using the dbSetParam tag to supply only parameter values rather than textual completion of the SQL statement itself. This avoids the possibility of what is referred to as "SQL poisoning", where users might enter more SQL code in addition to the expected value.

Syntax

<sql:dbSetParam name = "param_name"
                value = "param_value" 
              [ scope = "page" | "request" | "scope" | "application" ] />

Attributes

• name (required): This is the name of the parameter to set.

• value (required): This is the desired value of the parameter.

• scope: This is the scope of the bind parameter. The default is page scope.

Example

The following example uses a dbSetParam tag to set the value of a parameter named id2. This value is then bound into the SQL statement in the dbExecute tag.

<sql:dbSetParam name="id2" value='<%=request.getParameter("id")%>'
                scope="session" />
Result:
   <HR>
   <sql:dbOpen dataSource="<%= dataSrcStr %>" >
        <sql:dbExecute output="yes" bindParams="id2 name job sal">
             insert into emp(empno, ename, deptno, job, sal) 
                    values (?, ?, 20, ?, ?)
        </sql:dbExecute>
    </sql:dbOpen>
    <HR>

SQL dbSetCookie Tag

You can use this tag to set a cookie. The dbSetCookie tag wraps functionality of the standard javax.servlet.http.Cookie class.

Syntax

<sql:dbSetCookie name = "cookie_name"
               [ value = "cookie_value" ]
               [ domain = "domain_name" ]
               [ comment = "comment" ]
               [ maxAge = "age" ]
               [ version = "protocol_version" ]
               [ secure = "true" | "false" ]
               [ path = "path" ] />

Attributes

• name (required): This is the name of the cookie.

• value: This is the desired value of the cookie. Because it is permissible to have a null-value cookie, this attribute is not required.

• domain: This is the domain name for the cookie. The form of the domain name is according to the RFC 2019 specification.

• comment: This is for a comment describing the purpose of the cookie.

• maxAge: This is the maximum allowable age of the cookie, in seconds. Use a setting of "-1" for the cookie to persist until the browser is shut down.

• version: This is the version of the HTTP protocol that the cookie complies with.

• secure: This informs the browser whether the cookie should be sent using a secure protocol, such as HTTPS.

• path: This specifies a file system path for the cookie, the location to which the client should return the cookie.

Example

<sql:dbSetCookie name="cId" value='<%=request.getParameter("id")%>'
                 maxAge='800000' />

Example: Using dbOpen and dbQuery with a Data Source

This section provides a sample JSP page that uses a dbOpen tag with a data source to open a connection, then uses a dbQuery tag to execute a query.

<%@ taglib uri="http://xmlns.oracle.com/j2ee/jsp/tld/ojsp/sqltaglib.tld" 
prefix="sql" %>
<HTML>
<BODY>
   <sql:dbOpen dataSource='<%=request.getParameter("datasource") %>'
               connId="con1">
   </sql:dbOpen>
   <sql:dbQuery connId="con1">
      SELECT * FROM emp ORDER BY ename
   </sql:dbQuery>
   <sql:dbClose connId="con1" />
</BODY>
</HTML>