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

Library
Product
Contents
Index

Prev Next

3
Basic Features

This chapter covers the most basic steps taken in any JDBC application. It also describes additional basic features of Java and JDBC supported by the Oracle JDBC drivers. It includes the following topics:

First Steps in JDBC

This section describes how to get up and running with the Oracle JDBC drivers. When using the Oracle JDBC drivers, you must include certain driver-specific information in your programs. This section describes, in the form of a tutorial, where and how to add the information. The tutorial guides you through creating code to connect to and query a database from the client.

To connect to and query a database from the client, you must provide code for these tasks:

  1. Importing Packages

  2. Registering the JDBC Drivers

  3. Opening a Connection to a Database

  4. Creating a Statement Object

  5. Executing a Query and Returning a Result Set Object

  6. Processing the Result Set

  7. Closing the Result Set and Statement Objects

  8. Closing the Connection

You must supply Oracle driver-specific information for the first three tasks, which allow your program to use the JDBC API to access a database. For the other tasks, you can use standard JDBC Java code as you would for any Java application.

Importing Packages

Regardless of which Oracle JDBC driver you use, you must include the following import statements at the beginning of your program.

import java.sql.*  

JDBC packages.  

import java.math.*  

Java math packages; for example, these are required for the BigDecimal classes.  

You will need to add the following Oracle packages to your program when you want to access the extended functionality provided by the Oracle drivers. However, they are not required for the example presented in this section:

oracle.jdbc.driver.* and oracle.sql.*  

Add these packages if you use any Oracle-specific extensions to JDBC in your program. For more information on Oracle extensions, see Chapter 4, "Oracle Extensions".  

Registering the JDBC Drivers

You must provide the code to register your installed driver with your program. You do this with the static registerDriver() method of the JDBC DriverManager class. This class provides a basic service for managing a set of JDBC drivers.

Note:

Alternatively, you can use the forName() method of the java.lang.Class class to load the JDBC drivers directly. For example:

Class.forName ("oracle.jdbc.driver.OracleDriver");

However, this method is valid only for JDK-compliant Java virtual machines. It is not valid for Microsoft Java virtual machines.  

Because you are using one of Oracle's JDBC drivers, you declare a specific driver name string to registerDriver(). You register the driver only once in your Java application. 

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

Note:

If you are registering a Thin driver in an applet, you must enter a driver string that is different from the one used in these examples. For more information on registering a Thin driver for an applet, see "Coding Applets".  

Opening a Connection to a Database

You open a connection to the database with the static getConnection() method of the JDBC DriverManager class. This method returns an object of the JDBC Connection class which needs as input a userid, password, connect string that identifies the JDBC driver to use, and the name of the database to which you want to connect.

Connecting to a database is a step where you must enter Oracle JDBC driver-specific information in the getConnection() method. If you are not familiar with this method, continue reading the "Understanding the Forms of getConnection()" section below.

If you are already familiar with the getConnection() method, you can skip ahead to either of these sections, depending on the driver you installed:

Understanding the Forms of getConnection()

The getConnection() method is an overloaded method that you declare by the techniques described in these sections:

If you want to specify a database name in the connection, it must be in one of the following formats:

For information on how to specify a keyword-value pair or a TNSNAMES entry, see your Net8 Administrator's Guide.

Specifying a Database URL, Userid, and Password

getConnection(String URL, String user, String password);

where the URL is of the form: 

jdbc:oracle:<drivertype>:@<database>

The following example connects user scott with password tiger to a database with SID orcl through port 1521 of host myhost, using the Thin driver. 

Connection conn = 
   DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:orcl",
       "scott", "tiger");

If you want to use the default connection for an OCI driver, specify either: 

Connection conn = DriverManager.getConnection 
     

("jdbc:oracle:oci8:scott/tiger@");

OR 

Connection conn = 
     

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

For all JDBC drivers you can also specify the database with a Net8 keyword-value pair. The Net8 keyword-value pair substitutes for the TNSNAMES entry. The following example uses the same parameters as the preceding example, but in the keyword-value format: 

Connection conn = 
     

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

OR 

Connection conn = 
DriverManager.getConnection("jdbc:oracle:oci8:@(description=(address=(host= 
myhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))",
"scott", "tiger");

Specifying a Database URL That Includes Userid and Password

getConnection(String URL);

where the URL is of the form: 

jdbc:oracle:<drivertype>:<user>/<password>@<database>

The following example connects user scott with password tiger to a database using the OCI driver. In this case, however, the URL includes the userid and password, and is the only input parameter. 

Connection conn =   
 DriverManager.getConnection("jdbc:oracle:oci8:scott/tiger@myhost);

Specifying a Database URL and Properties Object

getConnection(String URL, Properties info);

where the URL is of the form: 

jdbc:oracle:<drivertype>:@<database>

In addition to the URL, use an object of the standard Java Properties class as input. For example: 

java.util.Properties info = new java.util.Properties();
info.put ("user", "scott");
info.put ("password","tiger");
info.put ("defaultRowPrefetch","15");
getConnection ("jdbc:oracle:oci8:@",info);
Oracle Extensions to Connection Properties Object

Oracle has defined several extensions to the connection properties that Oracle JDBC drivers support. For more information on this form of the getConnection() method and the Oracle extensions to the Properties object, see "Oracle Extensions for Connection Properties".

Opening a Connection for the JDBC OCI Driver

For the JDBC OCI driver, you can specify the database by a TNSNAMES entry. You can find the available TNSNAMES entries listed in the file tnsnames.ora on the client computer from which you are connecting. On Windows NT this file is located in [ORACLE_HOME]\NETWORK\ADMIN. On UNIX systems, you can find it in /var/opt/oracle.

For example, if you want to connect to the database on host myhost as user scott with password tiger that has a TNSNAMES entry of MyHostString, enter: 

Connection conn = 
     

DriverManager.getConnection ("jdbc:oracle:oci8:@MyHostString",
     

"scott", "tiger");

Note that both the ":" and "@" characters are necessary.

For the JDBC OCI driver (as with the Thin driver), you can also specify the database with a Net8 keyword-value pair. This is less readable than a TNSNAMES entry but does not depend on the accuracy of the TNSNAMES.ORA file. The Net8 keyword-value pair also works with other JDBC drivers.

For example, if you want to connect to the database on host myhost that has a TCP/IP listener up on port 1521, and the SID (system identifier) is orcl, use a statement such as: 

Connection conn = 
     

DriverManager.getConnection("jdbc:oracle:oci8:@(description=(address=(host= 
myhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))",
     

"scott", "tiger");

Opening a Connection for the JDBC Thin Driver

Because you can use the JDBC Thin driver in applets that do not depend on an Oracle client installation, you cannot use a TNSNAMES entry to identify the database to which you want to connect. You have to either:

OR

For example, use this string if you want to connect to the database on host myhost that has a TCP/IP listener on port 1521 for the database SID (system identifier) orcl. You can logon as user scott, with password tiger: 

Connection conn = 
     

DriverManager.getConnection 
("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");

You can also specify the database with a Net8 keyword-value pair. This is less readable than the first version, but also works with the other JDBC drivers. 

Connection conn = 
     

      DriverManager.getConnection 


      ("jdbc:oracle:thin:@(description=(address=(host=myhost)(protocol=tcp)
     

(port=1521))(connect_data=(sid=orcl)))", "scott", "tiger"); 



       











Note:




If you are writing a connection statement for an applet, you must enter a connect string that is different from the one used in these examples. For more information on connecting to a database with an applet, see "Coding Applets".   

Creating a Statement Object

Once you connect to the database and, in the process, create your Connection object, the next step is to create a Statement object. The createStatement() method of your JDBC Connection object returns an object of the JDBC Statement class. To continue the example from the previous section where the Connection object conn was created, here is an example of how to create the Statement object: 

Statement stmt = conn.createStatement();

Note that there is nothing Oracle-specific about this statement; it follows standard JDBC syntax.

Executing a Query and Returning a Result Set Object

To query the database, use the executeQuery() method of your Statement object. This method takes a SQL statement as input and returns an object of the JDBC ResultSet class.

To continue the example, once you create the Statement object stmt, the next step is to execute a query that populates a ResultSet object with the contents of the ENAME (employee name) column of a table of employees that is named EMP: 

ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp");

Again, there is nothing Oracle-specific about this statement; it follows standard JDBC syntax.

Note:

The JDBC drivers actually return an OracleResultSet object, but into a standard ResultSet output variable. If you want to use Oracle extensions to process the result set, then you must cast the output to OracleResultSet. This is further discussed in "Classes of the oracle.jdbc.driver Package".  

Processing the Result Set

Once you execute your query, use the next() method of your ResultSet object to iterate through the results. This method loops through the result set row by row, detecting the end of the result set when it is reached.

To pull data out of the result set as you iterate through it, use the various getXXX() methods of the ResultSet object, where XXX corresponds to a Java datatype.

For example, the following code will iterate through the ResultSet object rset from the previous section, and will retrieve and print each employee name: 

while (rset.next())
     

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

Once again, this is standard JDBC syntax. The next() method returns false when it reaches the end of the result set. The employee names are materialized as Java Strings.

Closing the Result Set and Statement Objects

You must explicitly close the ResultSet and Statement objects after you finish using them. This applies to all ResultSet and Statement objects you create when using the Oracle JDBC drivers. The drivers do not have finalizer methods; cleanup routines are performed by the close() method of the ResultSet and Statement classes. If you do not explicitly close your ResultSet and Statement objects, serious memory leaks could occur. You could also run out of cursors in the database. Closing a result set or statement releases the corresponding cursor in the database.

For example, if your ResultSet object is rset and your Statement object is stmt, close the result set and statement with these lines: 

rset.close()
stmt.close();

When you close a Statement object that a given Connection object creates, the connection itself remains open.

Closing the Connection

You must close your connection to the database once you finish your work. Use the close() method of the Connection class to do this. For example, if your Connection object is conn, close the connection with this statement: 

conn.close();

Sample: Connecting, Querying, and Processing the Results

The steps in the preceding sections are illustrated in the following example, which registers an Oracle JDBC Thin driver, connects to the database, creates a Statement object, executes a query, and processes the result set.

Note that the code for creating the Statement object, executing the query, returning and processing the ResultSet object, and closing the statement and connection all follow standard JDBC syntax. 

import java.sql.*; 
import java.math.*;
import java.io.*;
import java.awt.*;

class JdbcTest { 
     

public static void main (String args []) throws SQLException { 
    // Load Oracle driver
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
     
    // Connect to the local database
    Connection conn = 
     

     DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:ORCL",   
     

    "scott", "tiger");


               
    // Query the employee names 
    Statement stmt = conn.createStatement (); 
    ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp");
    
    // Print the name out 
    while (rset.next ())
     

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


         
    //close the result set, statement, and the connection
    rset.close();
    stmt.close();
    conn.close();
} 
      }

If you want to adapt the code for the OCI driver, replace the Connection statement with the following: 

Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@MyHostString",  
     

"scott", "tiger");

where MyHostString is an entry in the TNSNAMES.ORA file.

Note:

If you are creating code for an applet, the getConnection() and registerDriver() strings will be different. For more information, see "Coding Applets".  

Datatype Mappings

The Oracle JDBC drivers support the SQL datatypes required by JDBC 1.22. In addition, the Oracle JDBC drivers support the Oracle-specific ROWID datatype and user-defined types of the REF CURSOR category.

For reference, the following table shows the default mappings between JDBC datatypes, native Java datatypes, SQL datatypes, and the corresponding Java datatypes defined by Oracle extensions.

The Standard JDBC Datatypes column lists the datatypes supported by the JDBC 1.22 standard. All of these dataypes are defined in the java.sql.Types class.

The Java Native Datatypes column lists the datatypes defined by the Java language.

The SQL Datatypes column lists the SQL datatypes that exist in the database.

The Oracle Extensions--Java Classes that Represent SQL Datatypes column lists the oracle.sql.* Java types that correspond to each SQL datatype in the database. These are Oracle extensions that let you retrieve all SQL data in the form of a oracle.sql.* Java type. Mapping SQL datatypes into the oracle.sql datatypes lets you store and retrieve data without losing information. Refer to "Classes of the oracle.sql Package" for more information on the oracle.sql.* package.

For a list of all of the Java datatypes to which you can validly map a SQL datatype, see "Valid SQL-JDBC Datatype Mappings".

Table 3-1 Mapping Between JDBC, Java Native, and Oracle Datatypes
Standard JDBC
Datatypes 		 Java Native Datatypes  SQL Datatypes  Oracle Extensions--Java Classes
that Represent SQL Datatypes 

java.sql.Types.CHAR  

java.lang.String  

CHAR  

oracle.sql.CHAR  

java.sql.Types.VARCHAR  

java.lang.String  

VARCHAR2  

oracle.sql.CHAR  

java.sql.Types.LONGVARCHAR  

java.lang.String  

LONG  

oracle.sql.CHAR  

java.sql.Types.NUMERIC  

java.math.BigDecimal  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.DECIMAL  

java.math.BigDecimal  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.BIT  

boolean  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.TINYINT  

byte  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.SMALLINT  

short  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.INTEGER  

int  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.BIGINT  

long  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.REAL  

float  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.FLOAT  

double  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.DOUBLE  

double  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.BINARY  

byte[]  

NUMBER  

oracle.sql.NUMBER  

java.sql.Types.VARBINARY  

byte[]  

RAW  

oracle.sql.RAW  

java.sql.Types.LONGVARBINARY  

byte[]  

LONGRAW  

oracle.sql.NUMBER  

java.sql.Types.DATE  

java.sql.Date  

DATE  

oracle.sql.DATE  

java.sql.Types.TIME  

java.sql.Time  

DATE  

oracle.sql.DATE  

java.sql.Types.TIMESTAMP  

javal.sql.Timestamp  

DATE  

oracle.sql.DATE  

Oracle JDBC Extension Types

In addition, the following JDBC extensions for SQL datatypes (most of which comply with the JDBC 2.0 standard) are supported. They are not described until Chapter 4, "Oracle Extensions", but are summarized here for reference. Table 3-2 shows their mappings to Oracle datatypes.

The SQL Datatype column lists the SQL datatypes that exist in the database.

The JDBC Extensions for SQL Datatypes column lists the types into which Oracle datatypes should map according to the JDBC 2.0 standard. The class oracle.jdbc.driver.OracleTypes.* includes the definitions of Oracle-specific types that do not exist in the JDBC standard and is a superset of oracle.sql.*.

The Oracle Extensions--Java Classes that Represent SQL Datatypes column lists the oracle.sql.* Java types that correspond to each SQL datatype in the database. These are Oracle extensions that let you retrieve all SQL data in the form of a oracle.sql.* Java type. Refer to "Classes of the oracle.sql Package" for more information on the oracle.sql.* package.

For a list of all of the Java datatypes to which you can validly map a SQL datatype, see "Valid SQL-JDBC Datatype Mappings".

Table 3-2 Mapping Oracle Extension JDBC Types to Oracle Datatypes
SQL Datatype
 		 JDBC Extensions
for SQL Datatypes  		 Oracle Extensions--Java Classes
that Represent SQL Datatypes 

ROWID  

oracle.jdbc.driver.OracleTypes.ROWID  

oracle.sql.ROWID  

user-defined types of the REF CURSOR category  

oracle.jdbc.driver.OracleTypes.CURSOR  

java.sql.ResultSet  

BLOB  

oracle.jdbc.driver.OracleTypes.BLOB  

oracle.sql.BLOB  

CLOB  

oracle.jdbc.driver.OracleTypes.CLOB  

oracle.sql.CLOB  

BFILE  

oracle.jdbc.driver.OracleTypes.BFILE  

oracle.sql.BFILE  

Object Value  

oracle.jdbc.driver.OracleTypes.STRUCT  

If there is no entry for the object value in the type map:

  • oracle.sql.STRUCT

If there is an entry for the object value in the type map:

  • customized Java class

 

Object Reference  

oracle.jdbc.driver.OracleTypes.REF  

class that extends oracle.sql.REF  

Collections (varrays and nested tables)  

oracle.jdbc.driver.OracleTypes.ARRAY  

oracle.sql.ARRAY  

See Chapter 4, "Oracle Extensions", for more information on type mappings. In Chapter 4 you can also find more information on:

Using Java Streams in JDBC

This section has the following subsections:

This section describes how the Oracle JDBC drivers handle Java streams for several datatypes. Data streams allow you to read LONG column data of up to 2 gigabytes. Methods associated with streams let you read the data incrementally.

Oracle JDBC drivers support the manipulation of data streams in either direction between server and client. The drivers support all stream conversions: binary, ASCII, and Unicode. Following is a brief description of each type of stream:

The methods getBinaryStream(), getAsciiStream(), and getUnicodeStream(), return the bytes of data in an InputStream object. These methods are described in greater detail in Chapter 4, "Oracle Extensions".

Streaming LONG or LONG RAW Columns

When a query selects one or more LONG or LONG RAW columns, the JDBC driver transfers these columns to the client in streaming mode. After a call to executeQuery() or next(), the data of the LONG column is waiting to be read.

To access the data in a LONG column, you can get the column as a Java InputStream and use the read() method of the InputStream object. As an alternative, you can get the data as a string or byte array, in which case the driver will do the streaming for you.

You can get LONG and LONG RAW data with any of the three stream types. The driver performs NLS conversions for you depending on the character set of your database and the driver. For more information about NLS, see "Using NLS".

LONG RAW Data Conversions

A call to getBinaryStream() returns RAW data "as-is". A call to getAsciiStream() converts the RAW data to hexadecimal and returns the ASCII representation. A call to getUnicodeStream() converts the RAW data to hexadecimal and returns the Unicode bytes.

For example, if your LONG RAW column contains the bytes 20 21 22, you receive the following bytes:

LONG RAW   BinaryStream   ASCIIStream   UnicodeStream  

20 21 22  

20 21 22  

49 52 49 53 49 54

which is also

'1' '4' '1' '5' '1' '6'  

0049 0052 0049 0053 0049 0054

which is also:

'1' '4' '1' '5' '1' '6'  

For example, the LONG RAW value 20 is represented in hexadecimal as 14 or "1" "4". In ASCII, 1 is represented by "49" and "4" is represented by "52". In Unicode, a padding of zeros is used to separate individual values. So, the hexadecimal value 14 is represented as 0 "1" 0 "4". The Unicode representation is 0 "49" 0 "52".

LONG Data Conversions

When you get LONG data with getAsciiStream(), the drivers assume that the underlying data in the database uses an US7ASCII or WE8ISO8859P1 character set. If the assumption is true, the drivers return bytes corresponding to ASCII characters. If the database is not using an US7ASCII or WE8ISO8859P1 character set, a call to getAsciiStream() returns gibberish.

When you get LONG data with getUnicodeStream(), you get a stream of Unicode characters in the UCS-2 encoding. This applies to all underlying database character sets that Oracle supports.

When you get LONG data with getBinaryStream(), there are two possible cases:

For more information on how the drivers return data based on character set, see "Using NLS".

Note:

Receiving LONG or LONG RAW columns as a stream (the default case) requires you to pay special attention to the order in which you receive data from the database. For more information, see "Data Streaming and Multiple Columns".  

Table 3-3 summarizes LONG and LONG RAW data conversions for each stream type.

Table 3-3 LONG and LONG RAW Data Conversions
Datatype  BinaryStream  AsciiStream  UnicodeStream 

LONG  

bytes representing characters in Unicode UTF-8. The bytes can represent characters in US7ASCII or WE8ISO8859P1 if:

  • the value of NLS_LANG on the client is US7ASCII or WE8ISO8859P1.

OR

  • the database character set is US7ASCII or WE8ISO8859P1.

 

bytes representing characters in ISO-Latin-1 (WE8ISO8859P1) encoding  

bytes representing characters in Unicode UCS-2 encoding  

LONG RAW  

as-is  

ASCII representation of hexadecimal bytes  

Unicode representation of hexadecimal bytes  

Streaming Example for LONG RAW Data

One of the features of a getXXXStream() method is that it allows you to fetch data incrementally. In contrast, getBytes() fetches all of the data in one call. This section contains two examples of getting a stream of binary data. The first version uses the getBinaryStream() method to obtain LONG RAW data; the second version uses the getBytes() method.

Getting a LONG RAW Data Column with getBinaryStream()

This Java example writes the contents of a LONG RAW column to a file on the local file system. In this case, the driver fetches the data incrementally.

The following code creates the table that stores a column of LONG RAW data associated with the name LESLIE: 

-- SQL code:
create table streamexample (NAME varchar2 (256), GIFDATA long raw);
insert into streamexample values ('LESLIE', '00010203040506070809');

The following Java code snippet writes the data from the LESLIE LONG RAW column into a file called leslie.gif: 

ResultSet rset = stmt.executeQuery ("select GIFDATA from streamexample where 
NAME='LESLIE'");

// get first row
if (rset.next())
{
    // Get the GIF data as a stream from Oracle to the client
    InputStream gif_data = rset.getBinaryStream (1);
     

try
{
     

FileOutputStream file = null;
file = new FileOutputStream ("leslie.gif");
int chunk;
while ((chunk = gif_data.read()) != -1)
file.write(chunk);


     }
catch (Exception e)
{
     

String err = e.toString();
System.out.println(err);


     }
finally
{
     

if file != null()
     

file.close();


           }
      }

In this example the contents of the GIFDATA column are transferred incrementally in chunk-sized pieces between the database and the client. The InputStream object returned by the call to getBinaryStream() reads the data directly from the database connection.

Getting a LONG RAW Data Column with getBytes()

This version of the example gets the content of the GIFDATA column with getBytes() instead of getBinaryStream(). In this case, the driver fetches all of the data in one call and stores it in a byte array. The previous code snippet can be rewritten as: 

ResultSet rset2 = stmt.executeQuery ("select GIFDATA from streamexample where 
NAME='LESLIE'"); 

// get first row
if (rset2.next())
{
     

// Get the GIF data as a stream from Oracle to the client
byte[] bytes = rset2.getBytes(1);
try
{
     

FileOutputStream file = null;
file = new FileOutputStream ("leslie2.gif");
file.write(bytes);


     }
catch (Exception e)
{
     

String err = e.toString();
System.out.println(err);


     }
finally
{
     

if file != null()
     

file.close();


           }
      }

Because a LONG RAW column can contain up to 2 gigabytes of data, the getBytes() example will probably use much more memory than the getBinaryStream() example. Use streams if you do not know the maximum size of the data in your LONG or LONG RAW columns.

Avoiding Streaming for LONG or LONG RAW

The JDBC driver automatically streams any LONG and LONG RAW columns. However, there may be situations where you want to avoid data streaming. For example, if you have a very small LONG column, you might want to avoid returning the data incrementally and instead, return the data in one call.

To avoid streaming, use the defineColumnType() method to redefine the type of the LONG column. For example, if you redefine the LONG or LONG RAW column as type VARCHAR or VARBINARY, then the driver will not automatically stream the data.

If you redefine column types with defineColumnType(), you must declare the types of all columns in the query. If you do not, executeQuery() will fail. In addition, you must cast the Statement object to the type oracle.jdbc.driver.OracleStatement.

As an added benefit, using defineColumnType() saves the driver two round trips to the database when executing the query. Without defineColumnType(), the JDBC driver has to request the datatypes of the column types.

Using the example from the previous section, the Statement object stmt is cast to the OracleStatement and the column containing LONG RAW data is redefined to be of the type VARBINARAY. The data is not streamed; instead, data is returned by writing it to a byte array. 

//cast the statement stmt to an OracleStatement
oracle.jdbc.driver.OracleStatement ostmt = 
   (oracle.jdbc.driver.OracleStatement)stmt;

//redefine the LONG column at index position 1 to VARBINARY
ostmt.defineColumnType(1, Types.VARBINARY);

// Do a query to get the images named 'LESLIE'
ResultSet rset = ostmt.executeQuery
         ("select GIFDATA from streamexample where NAME='LESLIE'");

// The data is not streamed here
rset.next();
byte [] bytes = rset.getBytes(1);

Streaming CHAR, VARCHAR, or RAW Columns

If you use the defineColumnType() Oracle extension to redefine a CHAR, VARCHAR, or RAW column as a LONGVARCHAR or LONGVARBINARY, then you can get the column as a stream. The program will behave as if the column were actually of type LONG or LONG RAW. Note that there is not much point to this, because these columns are usually short.

If you try to get a CHAR, VARCHAR, or RAW column as a data stream without redefining the column type, the JDBC driver will return a Java InputStream, but no real streaming occurs. In the case of these datatypes, the JDBC driver fully fetches the data into an in-memory buffer during a call to executeQuery() or next(). The getXXXStream() entry points return a stream that reads data from this buffer.

Note:

In version 8.1.5, the setXXXStream() methods are not available for CHAR, VARCHAR, and RAW datatypes.  

Data Streaming and Multiple Columns

If your query selects multiple columns and one of the columns contains a data stream, then the contents of the columns following the stream column are usually not available until the stream has been read. This is because the database sends each row as a set of bytes representing the columns in the SELECT order: the data after a streaming column can be read only after the stream has been read.

For example, consider the following query: 

ResultSet rset = stmt.executeQuery
        ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");
while rset.next()
{
     

//get the date data
java.sql.Date date = rset.getDate(1);
     




     // get the streaming data
InputStream is = rset.getAsciiStream(2); 

// Open a file to store the gif data
FileOutputStream file = new FileOutputStream ("ascii.dat");

// Loop, reading from the ascii stream and 
// write to the file
int chunk;
while ((chunk = is.read ()) != -1)
     

file.write(chunk);


     // Close the file
file.close();

//get the number column data
int n = rset.getInt(3);  
      }

The incoming data for each row has the following shape: 

<a date><the characters of the long column><a number>

When you call rset.next(), the JDBC driver stops reading the row data just before the first character of the LONG column. Then the driver uses rset.getAsciiStream() to read the characters of the LONG column directly out of the database connection as a Java stream. The driver reads the NUMBER data from the third column only after it reads the last byte of the data from the stream.

An exception to this behavior is LOB data, which is also transferred between server and client as a Java stream. For more information on how the driver treats LOB data, see "Streaming LOBs and External Files".

Bypassing Streaming Data Columns

There might be situations where you want to avoid reading a column that contains streaming data. If you do not want to read the data for the streaming column, then call the close() method of the stream object. This method discards the stream data and allows the driver to continue reading data for all the non-streaming columns that follow the stream. Even though you are intentionally discarding the stream, it is good programming practice to call the columns in SELECT list order.

In the following example, the stream data in the LONG column is discarded and the data from only the DATE and NUMBER column is recovered: 

ResultSet rset = stmt.executeQuery
        ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");
while rset.next()
     

{
     

//get the date
java.sql.Date date = rset.getDate(1);

//access the stream data and discard it with close()
InputStream is = rset.getAsciiStream(2);
    is.close();   

//get the number column data
int n = rset.getInt(3); 


     }

Streaming Data Precautions

This section describes some of the precautions you must take to ensure that you do not accidentally discard or lose your stream data. The drivers automatically discard stream data if you perform any JDBC operation that communicates with the database, other than reading the current stream. Two common precautions are described in the following sections:

Use the Stream Data after You Access It

To recover the data from a column containing a data stream, it is not enough to get the column; you must read and store its contents. Otherwise, the contents will be discarded when you get the next column.

Call the Stream Column in SELECT List Order

If your query selects multiple columns, the database sends each row as a set of bytes representing the columns in the SELECT order. If one of the columns contains stream data, the database sends the entire data stream before proceeding to the next column.

If you do not use the SELECT list order to access data, then you can lose the stream data. That is, if you bypass the stream data column and access data in a column that follows it, the stream data will be lost. For example, if you try to access the data for the NUMBER column before reading the data from the stream data column, the JDBC driver first reads then discards the streaming data automatically. This can be very inefficient if the LONG column contains a large amount of data.

If you try to access the LONG column later in the program, the data will not be available and the driver will return a "Stream Closed" error. This is illustrated in the following example: 

ResultSet rset = stmt.executeQuery
        ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");
while rset.next()
{
     

int n = rset.getInt(3);  // This discards the streaming data
InputStream is = rset.getAsciiStream(2);
                         // Raises an error: stream closed.


     }

If you get the stream but do not use it before you get the NUMBER column, the stream still closes automatically: 

ResultSet rset = stmt.executeQuery
     

("select DATECOL, LONGCOL, NUMBERCOL from TABLE");


     while rset.next()
{
     

InputStream is = rset.getAsciiStream(2); // Get the stream
int n = rset.getInt(3);
// Discards streaming data and closes the stream


     }
int c = is.read(); // c is -1: no more characters to read-stream closed

Streaming and Row Prefetching

If the JDBC driver encounters a column containing a data stream, row prefetching is set back to 1.

Closing a Stream

You can discard the data from a stream at any time by calling the stream's close() method. You can also close and discard the stream by closing its result set or connection object. You can find more information about the close() method for data streams in "Bypassing Streaming Data Columns". For information on how to avoid closing a stream and discarding its data by accident, see "Streaming Data Precautions".

Streaming LOBs and External Files

The term large object (LOB) refers to a data item that is too large to be stored directly in a database table. Instead, a locator is stored in the database table and points to the location of the actual data. The JDBC drivers provide support for three types of LOBs: BLOBs (unstructured binary data), CLOBs (single-byte character data) and BFILEs (external files). The Oracle JDBC drivers support the streaming of CLOB, BLOB, and BFILE data.

LOBs behave differently from the other types of streaming data described in this chapter. The driver transfers LOB data between server and client as a Java stream. However, unlike most Java streams, a locator representing the LOB data is stored in the table. Thus, you can access the LOB data at any time during the life of the connection.

Streaming BLOBs and CLOBs

When a query selects one or more CLOB or BLOB columns, the JDBC driver transfers to the client the data pointed to by the locator. The driver performs the transfer as a Java stream. To manipulate CLOB or BLOB data from JDBC, use methods in the Oracle extension classes oracle.sql.BLOB and oracle.sql.CLOB. These classes provide functionality such as reading from the CLOB or BLOB into an input stream, writing from an output stream into a CLOB or BLOB, determining the length of a CLOB or BLOB, and closing a CLOB or BLOB.

For a complete discussion of how to use streaming CLOB and BLOB data, see "Reading and Writing BLOB and CLOB Data".

Streaming BFILEs

An external file, or BFILE, is used to store a locator to a file that is outside the database, stored somewhere on the filesystem of the data server. The locator points to the actual location of the file.

When a query selects one or more BFILE columns, the JDBC driver transfers to the client the file pointed to by the locator. The transfer is performed in a Java stream. To manipulate BFILE data from JDBC, use methods in the Oracle extension classes oracle.sql.BFILE. These classes provide functionality such as reading from the BFILE into an input stream, writing from an output stream into a BFILE determining the length of a BFILE, and closing a BFILE.

For a complete discussion of how to use streaming BFILE data, see "Reading BFILE Data".

Using Stored Procedures in JDBC Programs

This section describes how the Oracle JDBC drivers support stored procedures and includes these subsections:

PL/SQL Stored Procedures

Oracle JDBC drivers support execution of PL/SQL stored procedures and anonymous blocks. They support both SQL92 escape syntax and Oracle escape syntax. The following PL/SQL calls are all available from any Oracle JDBC driver: 

// SQL92 Syntax
CallableStatement cs1 = conn.prepareCall
     

                       ( "{call proc (?,?)}" ) ;


     CallableStatement cs2 = conn.prepareCall
     

                       ( "{? = call func (?,?)}" ) ;


     // Oracle Syntax
CallableStatement cs3 = conn.prepareCall
     

                       ( "begin proc (:1, :2); end;" ) ;


     CallableStatement cs4 = conn.prepareCall
     

                       ( "begin :1 := func(:2,:3); end;" ) ;

As an example of using Oracle syntax, here is a PL/SQL code snippet that creates a stored function. The PL/SQL function gets a character and concatenates a suffix to it: 

create or replace function foo (val1 char)
return char as
begin
     

return val1 || 'suffix';


     end;

Your invocation call in your JDBC program should look like: 

Connection conn = DriverManager.getConnection 
     

("jdbc:oracle:oci8:@<hoststring>", "scott", "tiger");


     
CallableStatement cs = 
     

conn.prepareCall ("begin ? := foo(?); end;");


     cs.registerOutParameter(1,Types.CHAR);
cs.setString(2, "aa");
cs.executeUpdate();
String result = proc.getString(1);

Java Stored Procedures

You can use JDBC to invoke Java stored procedures through the SQL and PL/SQL engines. The syntax for calling Java stored procedures is the same as the syntax for calling PL/SQL stored procedures. See the Oracle8i Java Stored Procedures Developer's Guide for more information on using Java stored procedures.

Error Messages and JDBC

To handle exceptions, the Oracle JDBC drivers throw a java.sql.SQLException(). Two types of errors can be returned. The first type, Oracle database errors, are returned from the Oracle database itself and consist of an error number and a text message describing the error. These errors are documented in the publication Oracle8i Error Messages.

The second type of error is returned by the JDBC driver itself. These messages consist of a text message, but do not have an error number. These messages describe the error and identify the method that threw the error.

You can return errors with these methods:

This example uses both getMessage() and printStackTrace() to return errors. 

catch(SQLException e);
{
     

System.out.println("exception: " + e.getMessage());
e.printStackTrace();


     }

The text of all error messages has been internationalized. That is, they are available in all of the languages and character sets supported by Oracle. These error messages are listed in Appendix A, "JDBC Error Messages".

Server-Side Basics

This section has the following subsections:

The tutorial presented in "First Steps in JDBC", describes connecting to and querying a database using the client-side driver. The following sections describe some of the basic differences if you run the tutorial using the server-side driver. For a complete discussion of the server-side driver, see "JDBC on the Server: the Server Driver".

Session and Transaction Context

The server-side driver operates within a default session and default transaction context. For more information on default session and transaction context for the server-side driver, see "Session and Transaction Context for the Server Driver".

Connecting to the Database

The Server driver uses a default connection to the database. You can connect to the database with either the DriverManager.getConnection() method or the Oracle-specific API defaultConnection() method. For more information on connecting to the database with the server-side driver, see "Connecting to the Database with the Server Driver".

Application Basics versus Applet Basics

This section has the following subsections:

Application Basics

You can use either the Oracle JDBC Thin driver or the JDBC OCI driver to create an application. Because the JDBC OCI driver uses native methods, there can be significant performance advantages in using this driver for your applications.

An application that can run on a client can run on the server by using the JDBC Server driver.

If you are using a JDBC OCI driver in an application, then the application will require an Oracle installation on its clients. For example, the application will require the installation of Net8 and client libraries.

Applications and Encryption

For applications that use the Oracle OCI driver, you can data can encrypt data by using Net8 ANO (Advanced Networking Option). For more information on ANO, please refer to the Net8 Administrator's Guide.

Applet Basics

This section describes the issues you should take into consideration if you are writing an applet that uses the JDBC Thin driver.

Applets and Security

An applet cannot open network connections except to the host machine from which it was downloaded. Therefore, an applet can connect to databases only on the originating machine. If you want to connect to a database running on a different machine, either:

Both of these topics are described in greater detail in "Connecting an Applet to a Database".

Applets and Firewalls

An applet that uses the JDBC Thin driver can connect to a database through a firewall. See "Using Applets with Firewalls" for more information on configuring the firewall and on writing connect strings for the applet.

Applets and Encryption

Applets that use the JDBC Thin driver do not support data encryption.

Packaging and Deploying Applets

To package and deploy an applet, you must place the JDBC Thin driver classes and the applet classes in the same zip file. This is described in detail in "Packaging Applets".


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

Library
Product
Contents
Index