Oracle8i SQLJ Developer's Guide and Reference
Release 8.1.5
A64684-01

Library
Product
Contents
Index

Prev Next

5
Advanced Topics

This chapter describes advanced JDBC topics, including the following:

Using NLS

This section contains these subsections:

Oracle's JDBC drivers support NLS (National Language Support). NLS lets 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, the driver provides the support to perform the conversions between the database character set and the client character set.

For more information on NLS, NLS environment variables, and the character sets that Oracle supports, see the Oracle8i National Language Support Guide. See the Oracle8i 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 NLS character set conversion:

How JDBC Drivers Perform NLS Conversions

The techniques that Oracle's 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 UCS-2 which is used in Java applications.

If you are working with databases that employ a non-US7ASCII or non-WE8ISO8859P1 character set (for example, Japanese or Korean), then the driver converts the data, first to UTF-8, then to UCS-2. For example, the driver always converts CHAR and VARCHAR2 data in a non-US7ASCII, non-WE8ISO8859P1 character set. It does not convert RAW data.

Note:

The JDBC drivers perform all character set conversions transparently. No user intervention is necessary for the conversions to occur.  

JDBC OCI Driver and NLS

In the case of a JDBC OCI driver installation, note that there is a client-side character set as well as a database character set. The client character set is determined at client-installation time by the value of the NLS_LANG environment variable. The database character set is determined at database creation. The character set used by the client can be different from the character set used by the database on the server. So, when performing character set conversion, the JDBC OCI driver has to take three factors into consideration:

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.

JDBC Thin Driver and NLS

If your applications or applets use the JDBC Thin driver, then there will not be an Oracle client installation. Because of this, the OCI client conversion routines in C will not be available. In this case, the client conversion routines are different from the JDBC OCI driver.

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 UCS-2 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 UCS-2 in Java.

Note:

The OCI and Thin drivers both provide the same transparent support for NLS.  

Server Driver and NLS

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

The JDBC Server driver supports the ASCII (US7ASCII) and ISO-Latin-1 (WE8ISO8859P1) character sets only.

Note:

The Java VM supports only the English (US7ASCII) and ISO-Latin1 (WE8ISO8859P1) character sets.  

NLS Restrictions

Data Size Restriction for NLS Conversions

There is a limit to the maximum sizes for CHAR and VARCHAR2 datatypes when used in bind calls. This limitation is necessary to avoid data corruption. This problem happens only with binds (not for defines) and it affects only CHAR and VARCHAR2 datatypes if you are connected to a multi-byte character set database.

The maximum bind lengths are limited in the following way:

CHARs and VARCHAR2s experience character set conversions that could result in an increase in the length of the data in bytes. The ratio between data sizes before and after a conversion is called the NLS Ratio. After conversion, the bind values should not be greater than 4 Kbytes (in Oracle8), or 2 Kbytes (in Oracle7).

Table 5-1 New Restricted Maximum Bind Length for Client-Side Drivers
Driver  Server Version  Datatype  Old Max Bind Length (bytes)  New Restricted Max Bind Length (bytes) 

Thin and OCI  

V8  

CHAR  

2000  

min(2000,4000 / NLS_Ratio)  

VARCHAR2  

4000  

(4000 / NLS_Ratio)  

For example, when connecting to an Oracle8 server, you cannot bind more than:

OR

Table 5-2 contains examples of the NLS Ratio and maximum bind values for some common server character sets.

Table 5-2 NLS Ratio and Size Limits for Common Server Character Sets
Server Character Set  NLS Ratio  Maximum Bind Value on
Oracle8 Server (in bytes) 

WE8DEC  

1  

4000  

US7ASCII  

1  

4000  

ISO 8859-1 through 10  

1  

4000  

JA16SJIS  

2  

2000  

JA16EUC  

3  

1333  

Working with Applets

This section describes some of the basics about working with applets that use the JDBC Thin driver. It begins with a simple example of coding a JDBC applet, it then describes what you must do to allow the applet to connect to a database. This includes how to use the Oracle8 Connection Manager or signed applets if you are connecting to a database that is 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.

Coding Applets

Except for importing the JDBC interfaces to access JDBC entry points, you write a JDBC applet like any other Java applet. Depending on whether you are coding your applet for a JDK 1.1.1 browser or a JDK 1.0.2 browser, there are slight differences in the code that you use. In both cases, your applet must use the JDBC Thin driver, which connects to the database with TCP/IP protocol.

If you are targeting a JDK 1.1.1 browser (such as Netscape 4.x or Internet Explorer 4.x), then you must:

If you are targeting a JDK 1.0.2 browser (such as Netscape 3.x or Internet Explorer 3.x), then you must:

The following sections illustrate the differences in coding an applet for a JDK 1.1.1 browser compared with a JDK 1.0.2 browser.

Coding Applets for a JDK 1.1.1 Browser

If you are coding an applet for a JDK 1.1.1 browser, then import the JDBC interfaces from the java.sql package and load the Oracle JDBC Thin driver. 

import java.sql.*;
public class JdbcApplet extends java.applet.Applet
{
 Connection conn; // Hold the connection to the database
 public void init()
 {
 // Register the driver.
 DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
 // Connect to the database.
 conn = DriverManager.getConnection
 ("jdbc:oracle:thin:scott/tiger@www-aurora.us.oracle.com:1521:orcl");
 ...
 }
}

In this example, the connect string contains the username and password, but you can also pass them as arguments to getConnection() after obtaining them from the user. For more information on connecting to the database, see "Opening a Connection to a Database".

Coding Applets for a JDK 1.0.2 Browser

If you are coding an applet for a JDK 1.0.2 browser, then import the JDBC interfaces from the jdbc.sql package, load the driver from the oracle.jdbc.dnlddriver.OracleDriver() class, and use the dnldthin sub-protocol in your connect string: 

import jdbc.sql.*;
public class JdbcApplet extends java.applet.Applet
{
 Connection conn; // Hold the connection to the database
 public void init ()
 {
 // Register the driver
 DriverManager.registerDriver (new oracle.jdbc.dnlddriver.OracleDriver());
 // Connect to the database
 conn = DriverManager.getConnection
 ("jdbc:oracle:dnldthin:scott/tiger@www-aurora.us.oracle.com:1521:orcl");
 ...
 }
}

Connecting an Applet to a Database

This section includes the following subsections:

The most common task of an applet using the JDBC driver is to connect to and query a database. Because of applet security restrictions, 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 your applet can connect only to a database that is running on the same host as the web server. In this case, the applet can connect to the database directly; no additional steps are required.

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:

OR

This section begins with describing the most simple case, connecting to a database on the same host from which the applet was downloaded (that is, the same host as the web server). It then describes the two different ways in which you can connect to a database running on a different host.

Connecting to a Database on the Same Host as the Web Server

If your database is running on the same host from which the applet was downloaded, then you can connect to the database by specifying it in your applet. You specify the database in the connect string of the getConnection() method in the DriverManager class.

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 username 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=(sid=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.

Connecting to a Database on a Different Host

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 the applet's security restrictions. You can do this by using either the Oracle8 Connection Manager or signed applets.

Using the Oracle8 Connection Manager

Oracle8 Connection Manager is a lightweight, highly-scalable program that can receive Net8 packets and re-transmit them to a different server. To a client running Net8, 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 Net8 packets to an Oracle server running on a different host.

Figure 5-1 illustrates the relationship between the applet, the Oracle8 Connection Manager, and the database.

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


Using the Oracle8 Connection Manager requires two steps that are described in these sections:

Installing and Running the Oracle8 Connection Manager

You must install the Connection Manager on the web server host. You install it from the Oracle8 distribution media. Please refer to the Net8 Administrator's Guide if you need more help to install the Connection Manager.

On the web server host you must 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. Please refer to the Net8 Administrator's Guide for more information on the options you can enter in a CMAN.ORA file.

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 Net8 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.

You can find a description of the options listed in the CMAN.ORA file in the Net8 Administrator's Guide.

After you create the file, start the Oracle8 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 Oracle8 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 situation illustrated in Figure 5-1. The web sever 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=(sid=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=(sid=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 Net8 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 Net8 Administrator's Guide for more information about source_route addressing.

Using Signed Applets

If your browser supports JDK 1.1.x, (for example, Netscape 4.0), then you can use signed applets. Signed applets can request socket connection privileges to other machines. To set this up, you must:

  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 conn = DriverManager.getConnection(...);

    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
  3. You must obtain an object-signing certificate. See Netscape's Object-Signing Resources page at: 

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

    for information on obtaining and installing a certificate.

For a complete example of a signed applet that uses the Netscape Capabilities classes, see "Creating Signed Applets".

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 requests from 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.

You can solve this problem by using a Net8-compliant firewall and connect strings that comply with the firewall configuration. Net8-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 Net8-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.

The following sections describe these topics:

How Firewalls Work

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 connect 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.

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

Configuring a Firewall for Applets that use the JDBC Thin Driver

The instructions in this section assume that you are running a Net8-compliant firewall.

Java applets do not have access to the local system (that is, they cannot get the hostname locally or environment variables) 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:

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 and not on 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 and 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=(sid=orcl)))", "scott", "tiger");
     




             











Note:




To connect through a firewall, you cannot specify the connection string in host:port:sid syntax. For example, a connection string specified as: 



String connString = "jdbc:oracle:thin:@ixta.us.oracle.com:1521:orcl";



conn =DriverManager.getConnection (connString, "scott", "tiger");



will not work.  

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=(sid=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.

Note:

All of the parameters shown in the preceding example are required. In the address_list, the firewall address must precede the database server address.  

For more information on the parameters used in the above example, see the Net8 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 need your applet classes files and the JDBC driver classes file (this will be either classes111.zip if you are targeting the applet to a browser running JDK 1.1.1, or classes102.zip if you are targeting the applet to a browser running JDK 1.0.2).

Follow these steps:

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

  2. Unzip the driver classes zip file.

    If you are targeting a browser running the JDK 1.0.2, then DELETE the packages listed in the left-hand column of the following table. Next, ensure that the packages listed in the right-hand column are present. All of the packages listed in the table are included in the JDBC distribution.

    DELETE these packages:   Ensure that these packages are present:  

    java.sql  

    jdbc.sql  

    java.math  

    jdbc.math  

    oracle.jdbc.driver  

    oracle.jdbc.dnlddriver  

    oracle.jdbc.dbaccess  

    oracle.jdbc.dnlddbaccess  

    oracle.jdbc.oracore  

    oracle.jdbc.dnldoracore  

    oracle.jdbc.util  

    oracle.jdbc.dnldutil  

    oracle.jdbc.ttc7  

    oracle.jdbc.dnldttc7  

    oracle.sql  

    oracle.sdnldql  

    oracle.jdbc2  

    oracle.dnldjdbc2  

    java.io.Reader  

    jdbc.io.Reader  

    java.io.Writer  

    jdbc.io.Writer  

  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.

    To target a browser running the JDK 1.1.1, the single zip file should contain:

    • the files from classes111.zip

    • your applet classes

    • 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 entry points, omit this file.

    To target a browser running the JDK 1.0.2, the single zip file should contain:

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

then 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) that contains the applet classes and resources the applet needs. Oracle recommends the use of a .zip 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>

Note:

Version 3.0 browsers do not support the ARCHIVE parameter.  

Browser Security and JDK Version Considerations

The communication between an applet that uses the JDBC Thin driver and the Oracle database happens on top of Java TCP/IP sockets.

In a JDK 1.0.2-based web browser, such as Netscape 3.0, an applet can open sockets only to the host from which it was downloaded. For Oracle8 this means that the applet can only connect to a database running on the same host as the web server. If you want to connect to a database running on a different host, then you must connect through the Oracle8 Connection Manager. For more information, see "Using the Oracle8 Connection Manager".

In a JDK 1.1.1-based web browser, such as Netscape 4.0, an applet can request socket connection privileges and connect to a database running on a different host from the web server host. In Netscape 4.0 you perform this by signing your applet (that is, writing a signed applet), then opening your connection as follows: 

netscape.security.PrivilegeManager.enablePrivilege
     

("UniversalConnect"); 


     connection = DriverManager.getConnection
     

("jdbc:oracle:thin:scott/tiger@dlsun511:1721:orcl");

Please refer to your browser documentation for more information on how to work with signed applets. You can also refer to "Using Signed Applets".

JDBC on the Server: the Server Driver

This section has the following subsections:

Any Java program, Enterprise JavaBean (EJB), or Java stored procedure that runs in the database, can use the Server driver to access the SQL engine.

The Server driver is intrinsically tied to the 8.1 database and to the Java VM. The driver runs as part of the same process as the database. It also runs within the default session: this is the same session in which the Java VM was invoked.

The Server 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 Java VM 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 Net8 call to access the SQL engine.

The server-side driver supports the same features, APIs, and Oracle extensions as the client-side drivers. This makes application partitioning very straight forward. 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.

Connecting to the Database with the Server Driver

As described in the preceding section, the Server driver runs within a default session. You are already "connected". You can use either the Oracle-specific API defaultConnection() method or the standard Java DriverManager.getConnection() method to access the default connection.

Connecting with defaultConnection()

The defaultConnection() method of the oracle.jdbc.driver.OracleServerDriver class is an Oracle extension and always returns the same connection object. You do not need to include a connect string with the statement. For example: 

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

Note that there is no conn.close statement. You cannot close a default connection made by the Server driver. Calling close() on the connection is just a no-op.

Connecting with DriverManager.getConnection()

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 connection, 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.

If you connect to the database with the DriverManager.getConnection() method, then use the connect string jdbc:oracle:kprb:. For example: 

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

Note that you could include a user name and password in the string, but because you are connecting from the server, they would be ignored.

Session and Transaction Context for the Server Driver

The server-side driver operates within a default session and default transaction context. The default session is the session in which the Java VM 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.

If you run Java application code in the server, then you can manage the transaction (COMMITs and ROLLBACKs) explicitly.

Testing JDBC on the Server

Almost any JDBC program that can run on a client can also run on the server. All of 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 "oci8" to "kprb". For example: 

Connection conn =
     

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

The advantage of using this method is that you need to 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 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.driver.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
{
     

Connection conn =  new oracle.jdbc.driver.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.");
     

}


           }

Server Driver Support for NLS

For a description of how the Server driver handles database character set conversions for Java programs, see "Server Driver and NLS".

Character Set Conversion of oracle.sql.CHAR Data

The Server driver performs character set conversions for oracle.sql.CHAR in C; this is a different implementation than for the client-side drivers. The client-side drivers perform character set conversions for oracle.sql.CHAR in Java. For more information on the oracle.sql.CHAR class, see "Class oracle.sql.CHAR".

Embedded SQL92 Syntax

Oracle's JDBC drivers support some embedded SQL92 syntax. This is the syntax that you specify between curly braces. The current support is basic. This section describes the support offered by the drivers for the following SQL92 constructs:

Where driver support is limited, these sections also describe possible work-arounds.

Disabling Escape Processing

Escape processing for SQL92 syntax is enabled by default. The JDBC drivers perform escape substitution before sending the SQL code to the database. If you want the driver to use regular Oracle SQL syntax instead of SQL92 syntax, then use this statement: 

stmt.setEscapeProcessing(false)

Note:

Since prepared statements have usually been parsed prior to making a call to setEscapeProcessing(), disabling escape processing for prepared statements will probably have no affect.  

Time and Date Literals

Databases differ in the syntax they use for date, time, and timestamp literals. JDBC supports dates and times written only in a specific format. This section describes the formats you must use for date, time, and timestamp literals in SQL statements.

Date Literals

The JDBC drivers support date literals in SQL statements written in the format: 

{d 'yyyy-mm-dd'}

where yyyy-mm-dd represents the year, month, and day; for example,
{d '1998-10-22'}. The JDBC drivers will replace this escape clause with the equivalent Oracle representation: "22 OCT 1998".

This code snippet contains an example of using a date literal in a SQL statement. 

// Connect to the database
// You can put a database name after the @ sign in the connection URL.
Connection conn = 
     

DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");


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

// Select the ename column from the emp table where the hiredate is Jan-23-1982
ResultSet rset = stmt.executeQuery 
     

("SELECT ename FROM emp WHERE hiredate = {d '1982-01-23'}");


     
// Iterate through the result and print the employee names
while (rset.next ())
     

System.out.println (rset.getString (1));

Time Literals

The JDBC drivers support time literals in SQL statements written in the format: 

{t 'hh:mm:ss'}

where hh:mm:ss represents the hours, minutes, and seconds; for example,
{t '05:10:45'}. The JDBC drivers will replace this escape clause with the equivalent Oracle representation: "05:10:45". If the time is specified as
{t '14:20:50'}, then the equivalent Oracle representation would be "14:20:50", assuming the server is using a 24-hour clock.

This code snippet contains an example of using a time literal in a SQL statement. 

ResultSet rset = stmt.executeQuery 
     

("SELECT ename FROM emp WHERE hiredate = {t '12:00:00'}");

Timestamp Literals

The JDBC drivers support timestamp literals in SQL statements written in the format: 

{ts 'yyyy-mm-dd hh:mm:ss.f...'}

where yyyy-mm-dd hh:mm:ss.f... represents the year, month, day, hours, minutes, and seconds. The fractional seconds portion (".f...") is optional and can be omitted. For example: {ts '1997-11-01 13:22:45'} represents, in Oracle format, NOV 01 1997 13:22:45.

This code snippet contains an example of using a timestamp literal in a SQL statement. 

ResultSet rset = stmt.executeQuery 
     

("SELECT ename FROM emp WHERE hiredate = {ts '1982-01-23 12:00:00'}");

Scalar Functions

The Oracle JDBC drivers do not support all scalar functions. To find out which functions the drivers support, use the following methods which are supported by the Oracle-specific oracle.jdbc.driver.OracleDatabaseMetaData and the standard Java java.sql.DatabaseMetadata interfaces:

Oracle's JDBC drivers do not support the function keyword, 'fn'. If you try to use this keyword, for example: 

{fn concat ("Oracle", "8i") }

you will get the error "Non supported SQL92 token at position xx: fn" when you run your Java application. The work-around is to use Oracle SQL syntax.

For example, instead of using the fn keyword in embedded SQL92 syntax: 

Statement stmt = conn.createStatement ();
stmt.executeUpdate("UPDATE emp SET ename = {fn CONCAT('My', 'Name')}");

use Oracle SQL syntax: 

stmt.executeUpdate("UPDATE emp SET ename = CONCAT('My', 'Name')");

LIKE Escape Characters

The characters "%" and "_" have special meaning in SQL LIKE clauses (you use "%" to match zero or more characters, "_" to match exactly one character). If you want to interpret these characters literally in strings, you precede them with a special escape character. For example, if you want to use the ampersand "&" as the escape character, you identify it in the SQL statement as {escape '&'}: 

Statement stmt = conn.createStatement ();

// Select the empno column from the emp table where the ename starts with '_'
ResultSet rset = stmt.executeQuery("SELECT empno FROM emp WHERE ename LIKE '&_%' 
{ESCAPE '&'}");

// Iterate through the result and print the employee numbers
while (rset.next ())
     

System.out.println (rset.getString (1));


     
  











Note:




 If you want to use the back slash character (\) as an escape character, you must enter it twice (that is, \\). For example:



ResultSet rset = stmt.executeQuery("SELECT empno FROM emp WHERE ename LIKE '\\_%' {escape '\\'}");  

Outer Joins

Oracle's JDBC drivers do not support outer join syntax: {oj outer-join}. The work-around is to use Oracle outer join syntax:

Instead of: 

Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery
     

("SELECT ename, dname 
 FROM {OJ dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno} 
 ORDER BY ename");

Use Oracle SQL syntax: 

Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery
     

("SELECT ename, dname 
 FROM emp a, dept b WHERE a.deptno = b.deptno(+)
 ORDER BY ename");

Function Call Syntax

Oracle's JDBC drivers support the function call syntax shown below:

Calls without a return value: 

{ call procedure_name (argument1, argument2,...) }

Calls with a return value: 

{ ? = call procedure_name (argument1, argument2,...) }

SQL92 to SQL Syntax Example

You can write a simple program to translate SQL92 syntax to standard SQL syntax. The following program prints the comparable SQL syntax for SQL92 statements for function calls, date literals, time literals, and timestamp literals. In the program, the oracle.jdbc.driver.OracleSql.parse() method performs the conversions. 

import oracle.jdbc.driver.OracleSql; 
 
public class Foo 
{ 
     

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

show ("{call foo(?, ?)}"); 
show ("{? = call bar (?, ?)}"); 
show ("{d '1998-10-22'}"); 
show ("{t '16:22:34'}"); 
show ("{ts '1998-10-22 16:22:34'}"); 
} 


      
public static void show (String s) throws Exception { 
     

System.out.println (s + " => " + new OracleSql().parse (s)); 


       } 
      }

The following code is the output which prints the comparable SQL syntax. 

{call foo(?, ?)} => BEGIN foo(:1, :2); END; 
{? = call bar (?, ?)} => BEGIN :1 := bar (:2, :3); END; 
{d '1998-10-22'} => TO_DATE ('1998-10-22', 'YYYY-MM-DD') 
{t '16:22:34'} => TO_DATE ('16:22:34', 'HH24:MI:SS') 
{ts '1998-10-22 16:22:34'} => TO_DATE ('1998-10-22 16:22:34', 'YYYY-MM-DD 
HH24:MI:SS')


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index