18
Advanced Topics

This chapter describes the following advanced JDBC topics:

• JDBC and Globalization Support
• JDBC Client-Side Security Features
• JDBC in Applets
• JDBC in the Server: the Server-Side Internal Driver

JDBC and Globalization Support

After a brief overview, this section covers the following topics:

• How JDBC Drivers Perform Globalization Support Conversions
• Globalization Support and Object Types
• SQL CHAR Data Size Restrictions with the Thin Driver

Oracle's JDBC drivers support Globalization Support (formerly NLS). Globalization Support allows you retrieve data or insert data into a database in any character set that Oracle supports. If the clients and the server use different character sets, then the driver provides the support to perform the conversions between the database character set and the client character set.

For more information on Globalization Support, Globalization Support environment variables, and the character sets that Oracle supports, see "Oracle Character Datatypes Support" and the Oracle9i Database Globalization Support Guide. See the Oracle9i Reference for more information on the database character set and how it is created.

Here are a few examples of commonly used Java methods for JDBC that rely heavily on character set conversion:

• The java.sql.ResultSet methods getString() and getUnicodeStream() return values from the database as Java strings and as a stream of Unicode characters, respectively.
• The oracle.sql.CLOB method getCharacterStream() returns the contents of a CLOB as a Unicode stream.
• The oracle.sql.CHAR methods getString(), toString(), and getStringWithReplacement() convert the following data to strings:
  • getString(): This converts the sequence of characters represented by the CHAR object to a string and returns a Java String object.
  • toString(): This is identical to getString(), but if the character set is not recognized, then toString() returns a hexadecimal representation of the CHAR data.
  • getStringWithReplacement(): This is identical to getString(), except characters that have no Unicode representation in the character set of this CHAR object are replaced by a default replacement character.

How JDBC Drivers Perform Globalization Support Conversions

The techniques that the Oracle JDBC drivers use to perform character set conversion for Java applications depend on the character set the database uses. The simplest case is where the database uses the US7ASCII or WE8ISO8859P1 character set. In this case, the driver converts the data directly from the database character set to UTF-16, which is used in Java applications, and vice versa.

If you are working with databases that employ a non-US7ASCII or non-WE8ISO8859P1 character set (for example, JA16SJIS or KO16KSC5601), then the driver converts the data first to UTF-8 (this step does not apply to the server-side internal driver), then to UTF-16. For example, the driver always converts CHAR and VARCHAR2 data in a non-US7ASCII, non-WE8ISO8859P1 character set. It does not convert RAW data.

JDBC OCI Driver and Globalization Support

For the JDBC OCI driver, the client character set is in the NLS_LANG environment variable, which is set at client-installation time. The language and territory settings, by default, are set to the Java VM locale settings.

Note that there are also server-side settings for these parameters, determined during database creation. So, when performing character set conversion, the JDBC OCI driver considers the following:

• database character set and language
• client character set and language
• Java application's character set

The JDBC OCI driver transfers the data from the server to the client in the character set of the database. Depending on the value of the NLS_LANG environment variable, the driver handles character set conversions in one of two ways:

• If NLS_LANG is not specified, or specifies the US7ASCII or WE8ISO8859P1 character set, then the JDBC OCI driver uses Java to convert the character set from US7ASCII or WE8ISO8859P1 directly to UTF-16, or the reverse.

or:

• If NLS_LANG specifies a character set other than US7ASCII or WE8ISO8859P1, the driver uses UTF-8 as the client character set. This happens automatically and does not require any user intervention. OCI converts the data from the database character set to UTF-8. The JDBC OCI driver then passes the UTF-8 data to the JDBC Class Library, where the UTF-8 data is converted to UTF-16.

  Notes: The driver uses UTF-8 as the character set to minimize the number of conversions it performs in Java. The change to UTF-8 is for the JDBC application process only.

JDBC Thin Driver and Globalization Support

If you are using the JDBC Thin driver, then there will presumably be no Oracle client installation. Globalization Support conversions must be handled differently.

Language and Territory

The Thin driver obtains language and territory settings (NLS_LANGUAGE and NLS_TERRITORY) from the Java locale in the JVM user.language property. The date format (NLS_DATE_FORMAT) is set according to the territory setting.

Character Set

If the database character set is US7ASCII or WE8ISO8859P1, then the data is transferred to the client without any conversion. The driver then converts the character set to UTF-16 in Java.

If the database character set is something other than US7ASCII or WE8ISO8859P1, then the server first translates the data to UTF-8 before transferring it to the client. On the client, the JDBC Thin driver converts the data to UTF-16 in Java.

Server-Side Internal Driver and Globalization Support

If your JDBC code running in the server accesses the database, then the JDBC server-side internal driver performs a character set conversion based on the database character set. The target character set of all Java programs is UTF-16.

Globalization Support and Object Types

The Oracle JDBC class files, classes12.zip and classes111.zip, provide Globalization Support for the Thin and OCI drivers. The files contain all the necessary classes to provide complete Globalization Support for all Oracle character sets for CHAR and NCHAR datatypes not retrieved or inserted as part of an Oracle object or collection type. See "Oracle Character Datatypes Support" for a description of CHAR and NCHAR datatypes.

However, in the case of the CHAR and VARCHAR data portion of Oracle objects and collections, the JDBC class files provide support for only the following commonly used character sets:

• US7ASCII
• WE8DEC
• ISO-LATIN-1
• UTF-8

To provide support for all character sets, the Oracle JDBC driver installation includes two additional files: nls_charset12.zip for JDK 1.2.x and nls_charset11.zip for JDK 1.1.x. The OCI and Thin drivers require these files to support all Oracle characters sets for CHAR and VARCHAR data in Oracle object types and collections. To obtain this support, you must add the appropriate nls_charset*.zip file to your CLASSPATH.

It is important to note that the nls_charset*.zip files are very large, because they must support a large number of character sets. To save space, you might want to keep only the classes you need from the nls_charset*.zip file. If you want to do this, follow these steps:

1. Unzip the appropriate nls_charset*.zip file.
2. Add only the needed character set classes to the CLASSPATH.
3. Remove the unneeded character set files from your system.

The character set extension class files are named in the following format:

CharacterConverter<OracleCharacterSetId>.class

where <OracleCharacterSetId> is the hexadecimal representation of the Oracle character set ID that corresponds to a character set name.

SQL CHAR Data Size Restrictions with the Thin Driver

If the database character set is neither ASCII (US7ASCII) nor ISO-LATIN-1 (WE8ISO8859P1), then the Thin driver must impose size restrictions for CHAR and VARCHAR2 bind parameters that are more restrictive than normal database size limitations. This is necessary to allow for data expansion during conversion.

The Thin driver checks CHAR bind sizes when the setXXX() method is called. If the data size exceeds the size restriction, then the driver throws a SQL exception ("Data size bigger than max size for this type") from the setXXX() call. This limitation is necessary to avoid the chance of data corruption whenever a conversion occurs and increases the length of the data. This limitation is enforced when you are doing all the following:

• using the Thin driver
• using binds (not defines)
• using CHAR, VARCHAR2, or LONG datatypes
• connecting to a database whose character set is neither ASCII (US7ASCII) nor ISO-Latin-1 (WE8ISO8859P1)

Role of the Expansion Factor

As previously discussed, when the database character set is neither US7ASCII nor WE8ISO8859P1, the Thin driver converts Java UTF-16 characters to UTF-8 encoding bytes for CHAR or VARCHAR2 binds. The UTF-8 encoding bytes are then transferred to the database, and the database converts the UTF-8 encoding bytes to the database character set encoding.

This conversion to the character set encoding can result in an increase in the number of bytes required to store the data. The expansion factor for a database character set indicates the maximum possible expansion in converting a character from UTF-8 to the character set: If the database character set is either UTF-8 or AL32UTF8, the expansion factor (exp_factor) is 1. Otherwise, the expansion factor is equal to the maximum character size in the database character set.

Size Restriction Formulas

Table 18-1 shows the database size limitations for CHAR data and the Thin driver size restriction formulas for CHAR binds. Database limits are in bytes. Formulas determine the maximum size of the UTF-8 encoding in bytes.

Table 18-1 Maximum CHAR and NCHAR Bind Sizes, Thin Driver 

The formulas guarantee that after the data is converted from UTF-8 to the database character set, the size will not exceed the database maximum size.

The number of UTF-16 characters that can be supported is determined by the number of bytes per character in the data. All ASCII characters are one byte long in UTF-8 encoding. Other character types can be two or three bytes long.

Expansion Factors and Calculated Size Restrictions for Common Character Sets

Table 18-2 lists the expansion factors of some common server character sets, then shows the Thin driver maximum bind sizes for SQL CHAR data for each character set, as determined by using the expansion factor in the appropriate formula.

Again, maximum bind sizes are for UTF-8 encoding, in bytes.

Table 18-2 Expansion Factors and Size Limits, Oracle8, Common Character Sets  

JDBC Client-Side Security Features

This section discusses support in the Oracle JDBC OCI and Thin drivers for login authentication, data encryption, and data integrity--particularly with respect to features of the Oracle Advanced Security option.

Oracle Advanced Security, previously known as the "Advanced Networking Option" (ANO) or "Advanced Security Option" (ASO), includes features to support data encryption, data integrity, third-party authentication, and authorizations. Oracle JDBC supports most of these features; however, the JDBC Thin driver must be considered separately from the JDBC OCI driver.

JDBC Support for Oracle Advanced Security

Both the JDBC OCI drivers and the JDBC Thin driver support at least some of the features of Oracle Advanced Security. If you are using one of the OCI drivers, you can set relevant parameters in the same way that you would in any thick-client setting. The Thin driver supports Advanced Security features through a set of Java classes included with the JDBC classes ZIP file, and supports security parameter settings through Java properties objects.

Included in your Oracle JDBC classes111.zip or classes12.zip file are a JAR file containing classes that incorporate features of Oracle Advance Security, and a JAR file containing classes whose function is to interface between the JDBC classes and the Advanced Security classes for use with the JDBC Thin driver.

OCI Driver Support for Oracle Advanced Security

If you are using one of the JDBC OCI drivers, which presumes you are running from a thick-client machine with an Oracle client installation, then support for Oracle Advanced Security and incorporated third-party features is, for the most part, no different from any Oracle thick-client situation. Your use of Advanced Security features is determined by related settings in the SQLNET.ORA file on the client machine, as discussed in the Oracle Advanced Security Administrator's Guide. Refer to that manual for information.

Thin Driver Support for Oracle Advanced Security

Because the Thin driver was designed to be downloadable with applets, one obviously cannot assume that there is an Oracle client installation and a SQLNET.ORA file where the Thin driver is used. This necessitated the design of a new, 100% Java approach to Oracle Advanced Security support.

Java classes that implement Oracle Advanced Security are included in your JDBC classes12.zip or classes111.zip file. Security parameters for encryption and integrity, normally set in SQLNET.ORA, are set in a Java properties file instead.

For information about parameter settings, see "Thin Driver Support for Encryption and Integrity".

JDBC Support for Login Authentication

Basic login authentication through JDBC consists of user names and passwords, as with any other means of logging in to an Oracle server. Specify the user name and password through a Java properties object or directly through the getConnection() method call, as discussed in "Open a Connection to a Database".

This applies regardless of which client-side Oracle JDBC driver you are using, but is irrelevant if you are using the server-side internal driver, which uses a special direct connection and does not require a user name or password.

The Oracle JDBC Thin driver implements Oracle O3LOGON challenge-response protocol to authenticate the user.

JDBC Support for Data Encryption and Integrity

You can use Oracle Advanced Security data encryption and integrity features in your Java database applications, depending on related settings in the server.

When using an OCI driver in a thick-client setting, set parameters as you would in any Oracle client situation. When using the Thin driver, set parameters through a Java properties file.

Encryption is enabled or disabled based on a combination of the client-side encryption-level setting and the server-side encryption-level setting.

Similarly, integrity is enabled or disabled based on a combination of the client-side integrity-level setting and the server-side integrity-level setting.

Encryption and integrity support the same setting levels--REJECTED, ACCEPTED, REQUESTED, and REQUIRED. Table 18-3 shows how these possible settings on the client-side and server-side combine to either enable or disable the feature.

Table 18-3 Client/Server Negotiations for Encryption or Integrity 

This table shows, for example, that if encryption is requested by the client, but rejected by the server, it is disabled. The same is true for integrity. As another example, if encryption is accepted by the client and requested by the server, it is enabled. And, again, the same is true for integrity.

The general settings are further discussed in the Oracle Advanced Security Administrator's Guide. How to set them for a JDBC application is described in the following subsections.

OCI Driver Support for Encryption and Integrity

If you are using one of the Oracle JDBC OCI drivers, which presumes a thick-client setting with an Oracle client installation, you can enable or disable data encryption or integrity and set related parameters as you would in any Oracle client situation, through settings in the SQLNET.ORA file on the client machine.

To summarize, the client parameters are shown in Table 18-4:

Table 18-4 OCI Driver Client Parameters for Encryption and Integrity 

These settings, and corresponding settings in the server, are further discussed in Appendix A of the Oracle Advanced Security Administrator's Guide.

Thin Driver Support for Encryption and Integrity

Thin driver support for data encryption and integrity parameter settings parallels the thick-client support discussed in the preceding section. Corresponding parameters exist under the oracle.net package and can be set through a Java properties object that you would then use in opening your database connection.

If you replace "SQLNET" in the parameter names in Table 18-4 with "oracle.net", you will get the parameter names supported by the Thin driver (but note that in Java, the parameter names are all-lowercase).

Table 18-5 lists the parameter information for the Thin driver. See the next section for examples of how to set these parameters in Java.

Table 18-5 Thin Driver Client Parameters for Encryption and Integrity 

Setting Encryption and Integrity Parameters in Java

Use a Java properties object (java.util.Properties) to set the data encryption and integrity parameters supported by the Oracle JDBC Thin driver.

The following example instantiates a Java properties object, uses it to set each of the parameters in Table 18-5, and then uses the properties object in opening a connection to the database:

...
Properties prop = new Properties();
prop.put("oracle.net.encryption_client", "REQUIRED");
prop.put("oracle.net.encryption_types_client", "( DES40 )");
prop.put("oracle.net.crypto_checksum_client", "REQUESTED");
prop.put("oracle.net.crypto_checksum_types_client", "( MD5 )");
Connection conn = DriverManager.getConnection
   ("jdbc:oracle:thin:@localhost:1521:main", prop);
...

The parentheses around the parameter values in the encryption_types_client and crypto_checksum_types_client settings allow for lists of values. Currently, the Thin driver supports only one possible value in each case; however, in the future, when multiple values are supported, specifying a list will result in a negotiation between the server and the client that determines which value is actually used.

Complete example

Following is a complete example of a class that sets data encryption and integrity parameters before connecting to a database to perform a query.

Note that in this example, the string "REQUIRED" is retrieved dynamically through functionality of the AnoServices and Service classes. You have the option of retrieving the strings in this manner or hardcoding them as in the previous examples.

import java.sql.*;
import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.net.ns.*;
import oracle.net.ano.*;

class Employee
{
  public static void main (String args [])
    throws Exception
  {

    // Register the Oracle JDBC driver
    System.out.println("Registring the driver...");
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    Properties props = new Properties();

    try {
      FileInputStream defaultStream = new FileInputStream(args[0]);
      props.load(defaultStream);


      int level = AnoServices.REQUIRED;
      props.put("oracle.net.encryption_client", Service.getLevelString(level));
      props.put("oracle.net.encryption_types_client", "( DES40 )");
      props.put("oracle.net.crypto_checksum_client",
                 Service.getLevelString(level));
      props.put("oracle.net.crypto_checksum_types_client", "( MD5 )");
    } catch (Exception e) { e.printStackTrace(); }


    // You can put a database name after the @ sign in the connection URL.
    Connection conn = DriverManager.getConnection
               ("jdbc:oracle:thin:@dlsun608.us.oracle.com:1521:main", props);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

    // Iterate through the result and print the employee names
    while (rset.next ())
       System.out.println (rset.getString (1));

    conn.close();
  }

}

JDBC in Applets

This section describes some of the basics of working with Oracle JDBC applets, which must use the JDBC Thin driver so that an Oracle installation is not required on the client. The Thin driver connects to the database with TCP/IP protocol.

Aside from having to use the Thin driver, and being mindful of applet connection and security issues, there is essentially no difference between coding a JDBC applet and a JDBC application. There is also no difference between coding for a JDK 1.2.x browser or a JDK 1.1.x browser, other than general JDK 1.1.x to 1.2.x migration issues discussed in "Migration from JDK 1.1.x to JDK 1.2.x".

This section describes what you must do for the applet to connect to a database, including how to use the Oracle Connection Manager or signed applets if you are connecting to a database not running on the same host as the Web server. It also describes how your applet can connect to a database through a firewall. The section concludes with how to package and deploy the applet.

The following topics are covered:

• Connecting to the Database through the Applet
• Connecting to a Database on a Different Host Than the Web Server
• Using Applets with Firewalls
• Packaging Applets
• Specifying an Applet in an HTML Page

For general information about connecting to the database, see "Open a Connection to a Database".

Connecting to the Database through the Applet

The most common task of an applet using the JDBC driver is to connect to and query a database. Because of applet security restrictions, unless particular steps are taken an applet can open TCP/IP sockets only to the host from which it was downloaded (this is the host on which the Web server is running). This means that without these steps, your applet can connect only to a database that is running on the same host as the Web server.

If your database and Web server are running on the same host, then there is no issue and no special steps are required. You can connect to the database as you would from an application.

As with connecting from an application, there are two ways in which you can specify the connection information to the driver. You can provide it in the form of host:port:sid or in the form of a TNS keyword-value syntax.

For example, if the database to which you want to connect resides on host prodHost, at port 1521, and SID ORCL, and you want to connect with user name scott with password tiger, then use either of the two following connect strings:

using host:port:sid syntax:

String connString="jdbc:oracle:thin:@prodHost:1521:ORCL";
conn = DriverManager.getConnection(connString, "scott", "tiger");

using TNS keyword-value syntax:

String connString = "jdbc:oracle:thin:@(description=(address_list=
   (address=(protocol=tcp)(port=1521)(host=prodHost)))
   (connect_data=(INSTANCE_NAME=ORCL)))";
conn = DriverManager.getConnection(connString, "scott", "tiger");

If you use the TNS keyword-value pair to specify the connection information to the JDBC Thin driver, then you must declare the protocol as TCP.

However, a Web server and an Oracle database server both require many resources; you seldom find both servers running on the same machine. Usually, your applet connects to a database on a host other than the one on which the Web server runs. There are two possible ways in which you can work around the security restriction:

• You can connect to the database by using the Oracle Connection Manager.

or:

• You can use a signed applet to connect to the database directly.

These options are discussed in the next section, "Connecting to a Database on a Different Host Than the Web Server".

Connecting to a Database on a Different Host Than the Web Server

If you are connecting to a database on a host other than the one on which the Web server is running, then you must overcome applet security restrictions. You can do this by using either the Oracle Connection Manager or signed applets.

Using the Oracle Connection Manager

The Oracle Connection Manager is a lightweight, highly-scalable program that can receive Oracle Net packets and re-transmit them to a different server. To a client running Oracle Net, the Connection Manager looks exactly like a database server. An applet that uses the JDBC Thin driver can connect to a Connection Manager running on the Web server host and have the Connection Manager redirect the Oracle Net packets to an Oracle server running on a different host.

Figure 18-1 illustrates the relationship between the applet, the Oracle Connection Manager, and the database.

Figure 18-1 Applet, Connection Manager, and Database Relationship

Using the Oracle Connection Manager requires two steps:

• Install and run the Connection Manager.
• Write the connection string that targets the Connection Manager.

There is also discussion of how to connect using multiple connection managers.

Installing and Running the Oracle Connection Manager

You must install the Connection Manager, available on the Oracle9i distribution media, onto the Web server host. You can find the installation instructions in the Oracle Net Services Administrator's Guide.

On the Web server host, create a CMAN.ORA file in the [ORACLE_HOME]/NET8/ADMIN directory. The options you can declare in a CMAN.ORA file include firewall and connection pooling support.

Here is an example of a very simple CMAN.ORA file. Replace <web-server-host> with the name of your Web server host. The fourth line in the file indicates that the Connection Manager is listening on port 1610. You must use this port number in your connect string for JDBC.

cman = (ADDRESS_LIST = 
       (ADDRESS = (PROTOCOL=TCP) 
       (HOST=<web-server-host>)
       (PORT=1610)))

cman_profile = (parameter_list = 
       (MAXIMUM_RELAYS=512) 
       (LOG_LEVEL=1) 
   (TRACING=YES) 
       (RELAY_STATISTICS=YES) 
       (SHOW_TNS_INFO=YES) 
       (USE_ASYNC_CALL=YES) 
       (AUTHENTICATION_LEVEL=0)
       )

Note that the Java Oracle Net version inside the JDBC Thin driver does not have authentication service support. This means that the AUTHENTICATION_LEVEL configuration parameter in the CMAN.ORA file must be set to 0.

After you create the file, start the Connection Manager at the operating system prompt with this command:

cmctl start

To use your applet, you must now write the connect string for it.

Writing the Connect String that Targets the Connection Manager

This section describes how to write the connect string in your applet so that the applet connects to the Connection Manager, and the Connection Manager connects with the database. In the connect string, you specify an address list that lists the protocol, port, and name of the Web server host on which the Connection Manager is running, followed by the protocol, port, and name of the host on which the database is running.

The following example describes the configuration illustrated in Figure 18-1. The Web server on which the Connection Manager is running is on host webHost and is listening on port 1610. The database to which you want to connect is running on host oraHost, listening on port 1521, and SID ORCL. You write the connect string in TNS keyword-value format:

Connection conn =
   DriverManager.getConnection ("jdbc:oracle:thin:" +
   "@(description=(address_list=" +
   "(address=(protocol=tcp)(host=webHost)(port=1610))" +
   "(address=(protocol=tcp)(host=oraHost)(port=1521)))" +
   "(source_route=yes)" +
   "(connect_data=(INSTANCE_NAME=orcl)))", "scott", "tiger");

The first element in the address_list entry represents the connection to the Connection Manager. The second element represents the database to which you want to connect. The order in which you list the addresses is important.

Notice that you can also write the same connect string in this format:

String connString = 
   "jdbc:oracle:thin:@(description=(address_list=
   (address=(protocol=tcp)(port=1610)(host=webHost))
   (address=(protocol=tcp)(port=1521)(host=oraHost)))
   (connect_data=(INSTANCE_NAME=orcl))
   (source_route=yes))";
Connection conn = DriverManager.getConnection(connString, "scott", "tiger");

When your applet uses a connect string such as the one above, it will behave exactly as if it were connected directly to the database on the host oraHost.

For more information on the parameters that you specify in the connect string, see the Oracle Net Services Administrator's Guide.

Connecting through Multiple Connection Managers

Your applet can reach its target database even if it first has to go through multiple Connection Managers (for example, if the Connection Managers form a "proxy chain"). To do this, add the addresses of the Connection Managers to the address list, in the order that you plan to access them. The database listener should be the last address on this list. See the Oracle Net Services Administrator's Guide for more information about source_route addressing.

Using Signed Applets

In either a JDK 1.2.x-based browser or a JDK 1.1.x-based browser, an applet can request socket connection privileges and connect to a database running on a different host than the Web server host. In Netscape 4.0, you perform this by signing your applet (that is, writing a signed applet). You must follow these steps:

1. Sign the applet. For information on the steps you must follow to sign an applet, see Sun Microsystem's Signed Applet Example at:

   http://java.sun.com/security/signExample/index.html 
   
   

2. Include applet code that asks for appropriate permission before opening a socket.

   If you are using Netscape, then your code would include a statement like this:

   netscape.security.PrivilegeManager.enablePrivilege("UniversalConnect"); 
   connection = DriverManager.getConnection
                ("jdbc:oracle:thin:scott/tiger@dlsun511:1721:orcl"); 
   
   

3. You must obtain an object-signing certificate. See Netscape's Object-Signing Resources page at:

   http://developer.netscape.com/software/signedobj/index.html
   

   This site provides information on obtaining and installing a certificate.

For more information on writing applet code that asks for permissions, see Netscape's Introduction to Capabilities Classes at:

http://developer.netscape.com/docs/manuals/signedobj/capabilities/contents.htm

For information about the Java Security API, including signed applet examples under JDK 1.2.x and 1.1.x, see the following Sun Microsystems site:

http://java.sun.com/security

Using Applets with Firewalls

Under normal circumstances, an applet that uses the JDBC Thin driver cannot access the database through a firewall. In general, the purpose of a firewall is to prevent unauthorized clients from reaching the server. In the case of applets trying to connect to the database, the firewall prevents the opening of a TCP/IP socket to the database.

Firewalls are rule-based. They have a list of rules that define which clients can connect, and which cannot. Firewalls compare the client's hostname with the rules, and based on this comparison, either grant the client access, or not. If the hostname lookup fails, the firewall tries again. This time, the firewall extracts the IP address of the client and compares it to the rules. The firewall is designed to do this so that users can specify rules that include hostnames as well as IP addresses.

You can solve the firewall issue by using an Oracle Net-compliant firewall and connection strings that comply with the firewall configuration. Oracle Net-compliant firewalls are available from many leading vendors; a more detailed discussion of these firewalls is beyond the scope of this manual.

An unsigned applet can access only the same host from which it was downloaded. In this case, the Oracle Net-compliant firewall must be installed on that host. In contrast, a signed applet can connect to any host. In this case, the firewall on the target host controls the access.

Connecting through a firewall requires two steps, described in the following sections:

• Configuring a Firewall for Applets that use the JDBC Thin Driver
• Writing a Connect String to Connect through a Firewall

Configuring a Firewall for Applets that use the JDBC Thin Driver

The instructions in this section assume that you are running an Oracle Net-compliant firewall.

Java applets do not have access to the local system--that is, they cannot get the hostname or environment variables locally--because of security limitations. As a result, the JDBC Thin driver cannot access the hostname on which it is running. The firewall cannot be provided with the hostname. To allow requests from JDBC Thin clients to go through the firewall, you must do the following two things to the firewall's list of rules:

• Add the IP address (not the hostname) of the host on which the JDBC applet is running.
• Ensure that the hostname "__jdbc__" never appears in the firewall's rules. This hostname has been hard-coded as a false hostname inside the driver to force an IP address lookup. If you do enter this hostname in the list of rules, then every applet using Oracle's JDBC Thin driver will be able to go through your firewall.

By not including the Thin driver's hostname, the firewall is forced to do an IP address lookup and base its access decision on the IP address, instead of the hostname.

Writing a Connect String to Connect through a Firewall

To write a connect string that allows you to connect through a firewall, you must specify the name of the firewall host and the name of the database host to which you want to connect.

For example, if you want to connect to a database on host oraHost, listening on port 1521, with SID ORCL, and you are going though a firewall on host fireWallHost, listening on port 1610, then use the following connect string:

Connection conn =
      DriverManager.getConnection ("jdbc:oracle:thin:" +
      "@(description=(address_list=" +
      (address=(protocol=tcp)(host=<firewall-host>)(port=1610))" +
      "(address=(protocol=tcp)(host=oraHost)(port=1521)))" +
      "(source_route=yes)" +
      "(connect_data=(INSTANCE_NAME=orcl)))", "scott", "tiger");

String connString =
 "jdbc:oracle:thin:@ixta.us.oracle.com:1521:orcl";
conn = DriverManager.getConnection (connString, "scott",
 "tiger");

The first element in the address_list represents the connection to the firewall. The second element represents the database to which you want to connect. Note that the order in which you specify the addresses is important.

Notice that you can also write the preceding connect string in this format:

String connString = 
      "jdbc:oracle:thin:@(description=(address_list=
      (address=(protocol=tcp)(port=1600)(host=fireWallHost))
      (address=(protocol=tcp)(port=1521)(host=oraHost)))
      (connect_data=(INSTANCE_NAME=orcl))
      (source_route=yes))";
Connection conn = DriverManager.getConnection(connString, "scott", "tiger");

When your applet uses a connect string similar to the one above, it will behave as if it were connected to the database on host oraHost.

For more information on the parameters used in the above example, see the Oracle Net Services Administrator's Guide. For more information on how to configure a firewall, please see your firewall's documentation or contact your firewall vendor.

Packaging Applets

After you have coded your applet, you must package it and make it available to users. To package an applet, you will need your applet class files and the JDBC driver class files (these will be contained in either classes12.zip, if you are targeting a browser that incorporates a JDK 1.2.x version, or classes111.zip, for a browser incorporating a JDK 1.1.x version).

Follow these steps:

1. Move the JDBC driver classes file classes12.zip (or classes111.zip) to an empty directory.

   If your applet will connect to a database with a non-US7ASCII and non-WE8ISO8859P1 character set, then also move the nls_charset12.zip or nls_charset11.zip file to the same directory.

2. Unzip the JDBC driver classes ZIP file (and character set ZIP file, if applicable).
3. Add your applet classes files to the directory, and any other files the applet might require.
4. Zip the applet classes and driver classes together into a single ZIP or JAR file. The single zip file should contain the following:
   • class files from classes12.zip or classes111.zip (and required class files from nls_charset12.zip or nls_charset11.zip if the applet requires Globalization Support)
   • your applet classes

   Additionally, if you are using DatabaseMetaData entry points in your applet, include the oracle/jdbc/driver/OracleDatabaseMetaData.class file. Note that this file is very large and might have a negative impact on performance. If you do not use DatabaseMetaData methods, omit this file.

5. Ensure that the ZIP or JAR file is not compressed.

You can now make the applet available to users. One way to do this is to add the APPLET tag to the HTML page from which the applet will be run. For example:

<APPLET WIDTH=500 HEIGHT=200 CODE=JdbcApplet ARCHIVE=JdbcApplet.zip 
      CODEBASE=Applet_Samples 
</APPLET>

You can find a description of the APPLET, CODE, ARCHIVE, CODEBASE, WIDTH, and HEIGHT parameters in the next section.

Specifying an Applet in an HTML Page

The APPLET tag specifies an applet that runs in the context of an HTML page. The APPLET tag can have these parameters: CODE, ARCHIVE, CODEBASE, WIDTH, and HEIGHT to specify the name of the applet and its location, and the height and width of the applet display area. These parameters are described in the following sections.

CODE, HEIGHT, and WIDTH

The HTML page that runs the applet must have an APPLET tag with an initial width and height to specify the size of the applet display area. You use the HEIGHT and WIDTH parameters to specify the size, measured in pixels. This size should not count any windows or dialogs that the applet opens.

The APPLET tag must also specify the name of the file that contains the applet's compiled Applet subclass--specify the file name with the CODE parameter. Any path must be relative to the base URL of the applet--the path cannot be absolute.

In the following example, JdbcApplet.class is the name of the Applet's compiled applet subclass:

<APPLET CODE="JdbcApplet" WIDTH=500 HEIGHT=200> 
</APPLET>

If you use this form of the CODE tag, then the classes for the applet and the classes for the JDBC Thin driver must be in the same directory as the HTML page.

Notice that in the CODE specification, you do not include the file name extension ".class".

CODEBASE

The CODEBASE parameter is optional and specifies the base URL of the applet; that is, the name of the directory that contains the applet's code. If it is not specified, then the document's URL is used. This means that the classes for the applet and the JDBC Thin driver must be in the same directory as the HTML page. For example, if the current directory is my_Dir:

<APPLET WIDTH=500 HEIGHT=200 CODE=JdbcApplet CODEBASE="."
</APPLET>

The entry CODEBASE="." indicates that the applet resides in the current directory (my_Dir). If the value of codebase was set to Applet_Samples, for example:

CODEBASE="Applet_Samples"

This would indicate that the applet resides in the my_Dir/Applet_Samples directory.

ARCHIVE

The ARCHIVE parameter is optional and specifies the name of the archive file (either a .zip or .jar file), if applicable, that contains the applet classes and resources the applet needs. Oracle recommends using a .zip file or .jar file, which saves many extra roundtrips to the server.

The .zip (or .jar) file will be preloaded. If you have more than one archive in the list, separate them with commas. In the following example, the class files are stored in the archive file JdbcApplet.zip:

<APPLET CODE="JdbcApplet" ARCHIVE="JdbcApplet.zip" WIDTH=500 HEIGHT=200>
</APPLET>

JDBC in the Server: the Server-Side Internal Driver

This section covers the following topics:

• Connecting to the Database with the Server-Side Internal Driver
• Exception-Handling Extensions for the Server-Side Internal Driver
• Session and Transaction Context for the Server-Side Internal Driver
• Testing JDBC on the Server
• Server-Side Character Set Conversion of oracle.sql.CHAR Data

This driver is intrinsically tied to the Oracle database and to the Java virtual machine (JVM). The driver runs as part of the same process as the database. It also runs within the default session--the same session in which the JVM was invoked.

The server-side internal driver is optimized to run within the database server and provide direct access to SQL data and PL/SQL subprograms on the local database. The entire JVM operates in the same address space as the database and the SQL engine. Access to the SQL engine is a function call; there is no network. This enhances the performance of your JDBC programs and is much faster than executing a remote Oracle Net call to access the SQL engine.

The server-side internal driver supports the same features, APIs, and Oracle extensions as the client-side drivers. This makes application partitioning very straightforward. For example, if you have a Java application that is data-intensive, you can easily move it into the database server for better performance, without having to modify the application-specific calls.

For general information about the Oracle Java platform server-side configuration or functionality, see the Oracle9i Java Developer's Guide.

Connecting to the Database with the Server-Side Internal Driver

As described in the preceding section, the server-side internal driver runs within a default session. You are already "connected". There are two methods you can use to access the default connection:

• Use the static DriverManager.getConnection() method, with either jdbc:oracle:kprb or jdbc:default:connection as the URL string.
• Use the Oracle-specific defaultConnection() method of the OracleDriver class.

Using defaultConnection() is generally recommended.

Connecting with the OracleDriver Class defaultConnection() Method

The oracle.jdbc.OracleDriver class defaultConnection() method is an Oracle extension and always returns the same connection object. Even if you invoke this method multiple times, assigning the resulting connection object to different variable names, just a single connection object is reused.

You do not need to include a connect string in the defaultConnection() call. For example:

import java.sql.*; 
import oracle.jdbc.*; 
  
class JDBCConnection 
{ 
  public static Connection connect() throws SQLException 
  { 
    Connection conn = null; 
    try {  
      // connect with the server-side internal driver
         OracleDriver ora = new OracleDriver(); 
         conn = ora.defaultConnection(); 
      } 
 
    } catch (SQLException e) {...}
    return conn; 
  } 
}

Note that there is no conn.close() call in the example. When JDBC code is running inside the target server, the connection is an implicit data channel, not an explicit connection instance as from a client. It should typically not be closed.

If you do call the close() method, be aware of the following:

• All connection instances obtained through the defaultConnection() method, which actually all reference the same connection object, will be closed and unavailable for further use, with state and resource cleanup as appropriate. Executing defaultConnection() afterward would result in a new connection object.
• Even though the connection object is closed, the implicit connection to the database will not be closed.

Connecting with the DriverManager.getConnection() Method

To connect to the internal server connection from code that is running within the target server, you can use the static DriverManager.getConnection() method with either of the following connect strings:

DriverManager.getConnection("jdbc:oracle:kprb:");

or:

DriverManager.getConnection("jdbc:default:connection:");

Any user name or password you include in the URL string is ignored in connecting to the server default connection.

The DriverManager.getConnection() method returns a new Java Connection object every time you call it. Note that although the method is not creating a new physical connection (only a single implicit connection is used), it is returning a new object.

The fact that DriverManager.getConnection() returns a new connection object every time you call it is significant if you are working with object maps (or "type maps"). A type map is associated with a specific Connection object and with any state that is part of the object. If you want to use multiple type maps as part of your program, then you can call getConnection() to create a new Connection object for each type map.

Exception-Handling Extensions for the Server-Side Internal Driver

The server-side internal driver, in addition to having standard exception-handling capabilities such as getMessage(), getErrorCode(), and getSQLState() (as described in "Processing SQL Exceptions"), offers extended features through the oracle.jdbc.driver.OracleSQLException class. This class is a subclass of the standard java.sql.SQLException class and is not available to the client-side JDBC drivers or the server-side Thin driver.

When an error condition occurs in the server, it often results in a series of related errors being placed in an internal error stack. The JDBC server-side internal driver retrieves errors from the stack and places them in a chain of OracleSQLException objects.

You can use the following methods in processing these exceptions:

• SQLException getNextException() (standard method)

  This method returns the next exception in the chain (or null if no further exceptions). You can start with the first exception you receive and work through the chain.

• int getNumParameters() (Oracle extension)

  Errors from the server usually include parameters, or variables, that are part of the error message. These may indicate what type of error occurred, what kind of operation was being attempted, or the invalid or affected values.

  This method returns the number of parameters included with this error.

• Object[] getParameters() (Oracle extension)

  This method returns a Java Object[] array containing the parameters included with this error.

Example

Following is an example of server-side error processing:

try 
{
   // should get "ORA-942: table or view does not exist"
   stmt.execute("drop table no_such_table");
}
catch (OracleSQLException e)
{
   System.out.println(e.getMessage());
   // prints "ORA-942: table or view does not exist"

   System.out.println(e.getNumParameters());
   // prints "1"

   Object[] params = e.getParameters();
   System.out.println(params[0]);
   // prints "NO_SUCH_TABLE"
}

Session and Transaction Context for the Server-Side Internal Driver

The server-side driver operates within a default session and default transaction context. The default session is the session in which the JVM was invoked. In effect, you are already connected to the database on the server. This is different from the client side where there is no default session: you must explicitly connect to the database.

Auto-commit mode is disabled in the server. You must manage transaction COMMIT and ROLLBACK operations explicitly by using the appropriate methods on the connection object:

conn.commit();

or:

conn.rollback();

Testing JDBC on the Server

Almost any JDBC program that can run on a client can also run on the server. All the programs in the samples directory can be run on the server with only minor modifications. Usually, these modifications concern only the connection statement.

For example, consider the test program JdbcCheckup.java described in "Testing JDBC and the Database Connection: JdbcCheckup". If you want to run this program on the server and connect with the DriverManager.getConnection() method, then open the file in your favorite text editor and change the driver name in the connection string from "oci" to "kprb". For example:

Connection conn = DriverManager.getConnection 
                  ("jdbc:oracle:kprb:@" + database, user, password);

The advantage of using this method is that you must change only a short string in your original program. The disadvantage is that you still must provide the user, password, and database information, even though the driver will discard it. In addition, if you issue the getConnection() method again, the driver will create another new (and unnecessary) connection object.

However, if you connect with defaultConnection(), the preferred method of connecting to the database from the server-side internal driver, you do not have to enter any user, password, or database information. You can delete these statements from your program.

For the connection statement, use:

Connection conn =  new oracle.jdbc.OracleDriver().defaultConnection();  

The following example is a rewrite of the JdbcCheckup.java program which uses the defaultConnection() connection statement. The connection statement is printed in bold. The unnecessary user, password, and database information statements, along with the utility function to read from standard input, have been deleted.

/*
 * This sample can be used to check the JDBC installation.
 * Just run it and provide the connect information.  It will select
 * "Hello World" from the database.
 */
// You need to import the java.sql package to use JDBC
import java.sql.*;
// We import java.io to be able to read from the command line
import java.io.*;

class JdbcCheckup
{
   public static void main (String args []) throws SQLException, IOException
   {
      // Load the Oracle JDBC driver
      DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
      
    Connection conn =  
           new oracle.jdbc.OracleDriver ().defaultConnection ();

       // Create a statement
       Statement stmt = conn.createStatement ();

       // Do the SQL "Hello World" thing
       ResultSet rset = stmt.executeQuery ("SELECT 'Hello World' FROM dual");

       while (rset.next ())
          System.out.println (rset.getString (1));
       System.out.println ("Your JDBC installation is correct.");
   }
}

Loading an Application into the Server

When loading an application into the server, you can load .class files that you have already compiled on the client, or you can load .java source files and have them compiled automatically in the server.

In either case, use the Oracle loadjava client-side utility to load your files. You can either specify source file names on the command line (note that the command line understands wildcards), or put the files into a JAR file and specify the JAR file name on the command line. The loadjava utility is discussed in detail in the Oracle9i Java Developer's Guide.

The loadjava script, which runs the actual utility, is in the bin subdirectory under your [Oracle Home] directory. This directory should already be in your path once Oracle has been installed.

Loading Class Files into the Server

Consider a case where you have three class files in your application: Foo1.class, Foo2.class, and Foo3.class. The following three examples demonstrate: 1) specifying the individual class file names; 2) specifying the class file names using a wildcard; and 3) specifying a JAR file that contains the class files.

Each class is written into its own class schema object in the server.

These three examples use the default OCI driver in loading the files:

loadjava -user scott/tiger Foo1.class Foo2.class Foo3.class

or:

loadjava -user scott/tiger Foo*.class

or:

loadjava -user scott/tiger Foo.jar

Or use the following command to load with the Thin driver (specifying the -thin option and an appropriate URL):

loadjava -thin -user scott/tiger@localhost:1521:ORCL Foo.jar

(Whether to use an OCI driver or the Thin driver to load classes depends on your particular environment and which performs better for you.)

Loading Source Files into the Server

If you enable the loadjava -resolve option in loading a .java source file, then the server-side compiler will compile your application as it is loaded, resulting in both a source schema object for the original source code, and one or more class schema objects for the compiled output.

If you do not specify -resolve, then the source is loaded into a source schema object without any compilation. In this case, however, the source is implicitly compiled the first time an attempt is made to use a class defined in the source.

For example, run loadjava as follows to load and compile Foo.java, using the default OCI driver:

loadjava -user scott/tiger -resolve Foo.java

Or use the following command to load with the Thin driver (specifying the -thin option and an appropriate URL):

loadjava -thin -user scott/tiger@localhost:1521:ORCL -resolve Foo.java

Either of these will result in appropriate class schema objects being created in addition to the source schema object.

Server-Side Character Set Conversion of oracle.sql.CHAR Data

The server-side internal driver performs character set conversions for oracle.sql.CHAR in C. This is a different implementation than for the client-side drivers, which perform character set conversions for oracle.sql.CHAR in Java, and offers better performance. For more information on the oracle.sql.CHAR class, see "Class oracle.sql.CHAR".