7.2 Large Objects (LOBs)
SQLJ supports three large object types, each of which stores a particular type of data. When you write an application, you need to choose the particular LOB type suited to the type of data you are manipulating. The three LOB types are as follows:
- CLOB
-
The Character LOB type, used to store single-byte character data.
- BLOB
-
The Binary LOB type, used to store binary data.
- BFILE
-
The Binary file type, used to store pointers to files located outside the database. You can read the contents of such a file as a stream of bytes via a BFILE pointer. The external files can be on a hard disk, CD-ROM, DVD-ROM, or other device.
Prior to Oracle8, your only choice for storing large amounts of character or binary data was to use the LONG and LONG RAW types. The newer CLOB and BLOB types have three advantages over the LONG and LONG RAW types:
-
LOBs can store up to 4GB of data, double the 2GB capacity of LONG and LONG RAW columns.
-
LOB contents can be accessed randomly, whereas LONG and LONG RAW contents can only be accessed sequentially.
-
A table can contain multiple LOB columns, but only one LONG or LONG RAW column.
LOBs are composed of two parts:
- locator
-
A LOB's locator is a pointer that specifies the location of the LOB content.
- content
-
Content refers to the actual characters, or bytes, that are stored in the LOB. If the content is less than 4KB in size, it is stored in the table containing the LOB column; otherwise, it is stored outside the table. In the case of BFILE columns, the only thing stored in the database is the locator, which points to an external file containing the LOB content.
I will use two tables for the examples in this section. The first table is named clob_content, and it contains a CLOB column named clob_column that is used to store textual data. The second table is named blob_content, and it contains a BLOB column named blob_column that is used to store binary data. Each table also contains a column to store the name of the file from which the LOB content was originally read. The two tables are defined as follows:
CREATE TABLE clob_content ( file_name VARCHAR2(20) NOT NULL, clob_column CLOB NOT NULL ); CREATE TABLE blob_content ( file_name VARCHAR2(20) NOT NULL, blob_column BLOB NOT NULL );
From a SQLJ program, there are two ways to store content in CLOB and BLOB columns:
-
Use put methods
-
Use streams
The put methods are defined by SQLJ and allow you to write content to a CLOB or BLOB directly. Streams are long strings of bytes that allow you to move information from one place to another. All Java input/output (I/O) is based on streams. For example, when you read from or write to file using a Java program, you are using a stream to access the file.
Using the put methods is faster than using streams when you are storing files less than 10KB or so in size, while streams offer higher performance when dealing with larger files. The java.io package contains many classes, such as InputStream and OutputStream, that allow you to manipulate stream data. The InputStream class is used to read a stream of bytes from a source, and the OutputStream class is used to write a stream of bytes to a destination. In this context, a source or destination can be any of several entities, such as a file, array, CLOB, or BLOB.
As you may have surmised by now, when you transfer LOB data back and forth between a SQLJ program and a database, you need to work with host objects to represent LOB data in your SQLJ program. The package oracle.sql contains the classes oracle.sql.CLOB, oracle.sql.BLOB, and oracle.sql.BFILE. SQLJ Version 9i or above also supports NLS characters via the oracle.sql.NCLOB class. These classes are used to define host objects for LOB values in your SQLJ programs.
7.2.1 Storing Content in CLOB and BLOB Columns Using the put Methods
In this section you will learn how to read content from a file and store that content in a CLOB or BLOB column using the put methods. LOB content is accessed in blocks, known as chunks. You use the putChars( ) method for CLOB columns and the putBytes( ) method for BLOB columns. The process of using the put methods involves the following 10 steps:
-
Initialize the LOB column to set the LOB locator.
-
Declare a host object of the LOB type.
-
Retrieve the LOB locator into the LOB host object.
-
Get the chunk size of the LOB from the LOB host object.
-
Create a buffer to hold a block of data from the file.
-
Create a file object.
-
Create input stream objects to read the file content.
-
Perform the following in a loop until the end of the file is reached:
-
Read a block of data from the file into the buffer created in Step 5.
-
Copy the buffer content to the LOB host object.
-
Perform a COMMIT.
-
Close the objects used to read the file.
For each step outlined here, I use example code to illustrate how to store the content of the textContent.txt file in the clob_content table. I also show how to store the image contained in the imageContent.jpg file in the blob_content table.
7.2.1.1 Step 1: Initialize the LOB column
LOB columns store a locator that points to the location of the actual contents of the LOB. Before you can add content to a LOB column, you must initialize the LOB column using a function that generates a value for the locator. A CLOB column is initialized using Oracle's built-in EMPTY_CLOB( ) function, and a BLOB column is initialized using the EMPTY_BLOB( ) function. The code in the following example adds a row to the clob_content table. Notice the use of the EMPTY_CLOB( ) function to generate the locator value for clob_column, and of the EMPTY_BLOB( ) function to generate a locator for blob_column:
String source_directory = "C:\\sample_files\\"; String file_name = source_directory + "textContent.txt"; #sql { INSERT INTO clob_content( file_name, clob_column ) VALUES ( :file_name, EMPTY_CLOB( ) ) }; file_name = source_directory + "imageContent.jpg"; #sql { INSERT INTO blob_content( file_name, blob_column ) VALUES ( :file_name, EMPTY_BLOB( ) ) }; Note the use of the two backslash characters (\) in the source_directory string; the first backslash is used to escape the second one, so you actually end up with just one backslash in the string's value.
7.2.1.2 Step 2: Declare a LOB host object
In order to access a LOB column using your SQLJ program, you must declare a host object of the same LOB type as the column. The following statements declare two host objects: a CLOB object named host_clob that will be used to access clob_column, and a BLOB object named host_blob that will be used to access blob_column:
CLOB host_clob; BLOB host_blob;
Prior to these statements, you should import the oracle.sql.* classes, which include the CLOB and BLOB classes. You can also use LOB host objects in named and positional iterator class definitions; the following statement defines a named iterator class for processing the rows in the clob_content table:
#sql iterator ClobIteratorClass(String file_name, CLOB host_clob);
7.2.1.3 Step 3: Retrieve the LOB locator
Your next step is to retrieve the empty LOB locator that you inserted into the database back in Step 1, and place that locator into your host object. Wouldn't it be nice if you could just use a statement like host_clob = EMPTY_CLOB( )? Well, unfortunately, you can't. You must use a SELECT statement to retrieve the LOB locator from the table. The SELECT statement in the following example retrieves the locator from clob_column into host_clob. Notice the FOR UPDATE clause, which indicates that I will shortly be updating the row that I've selected:
#sql { SELECT clob_column INTO :host_clob FROM clob_content WHERE file_name = :file_name FOR UPDATE }; A similar statement may be used to retrieve the locator from blob_column into host_blob.
7.2.1.4 Step 4: Get the LOB chunk size
Content in LOBs is accessed in blocks known as chunks. The getChunkSize( ) object method returns the optimal size for LOB chunks in terms of bytes. This optimal size is determined by Oracle, and is currently 8132 bytes for both CLOB and BLOB objects. The following Java statement gets the chunk size for host_clob and stores it in the variable named size_of_chunk:
int size_of_chunk = host_clob.getChunkSize( );
7.2.1.5 Step 5: Create a buffer
LOB data is usually read from a file, and this is no exception. The file textContent.txt contains data to be loaded into clob_column, and the file imageContent.jpg contains data to be loaded into blob_column. To buffer characters read from the textContent.txt file until they can be stored in the CLOB column, you need to declare an array of characters. Likewise, you need an array of bytes to buffer image data read from the imageContent.jpg file. The following two declarations create these necessary buffers:
char [] text_buffer = new char[size_of_chunk]; byte [] byte_buffer = new byte[size_of_chunk];
Notice that in each case, the buffer size is based on the chunk size obtained from the previous step.
7.2.1.6 Step 6: Create a file object
Before you can read the contents of a file using Java, you need a File object. The File object acts as a handle to a specific file and allows you to read that file's contents. The following Java statement creates a File object:
File file = new File(file_name);
7.2.1.7 Step 7: Create input stream objects
Unfortunately, the File object doesn't contain methods that enable you to read a file's contents; to do that, you need to create a number of additional objects. For example, to read the contents of the textContent.txt file as ASCII text, you need objects of the following Java classes: FileInputStream, InputStreamReader, and BufferedReader. The following example shows objects of these three classes being instantiated:
FileInputStream file_input_stream = new FileInputStream(file); InputStreamReader reader = new InputStreamReader(file_input_stream); BufferedReader buffered_reader = new BufferedReader(reader);
The file_input_stream object provides the low-level functionality to read files. The reader object is a wrapper around the file_input_stream object and allows you to read the file contents as a stream of ASCII characters. Finally, the buffered_reader object allows you to read the file contents in blocks instead of one character at a time.
To read the contents of the imageContent.jpg file, you need only a FileInputStream object because imageContent.jpg is interpreted as bytes, not characters.
7.2.1.8 Step 8: Read the file content and write it to the LOB
Read a block from the file into the buffer and populate the LOB host object with the buffer contents, repeating this process until you reach the end of the file. A CLOB host object is populated using the putChars( ) method, which copies characters to a specified position in the CLOB host object. You specify the position by passing a long value as a parameter. The first character of a CLOB is considered position 1. A BLOB host object is populated using the putBytes( ) method, which functions in the same manner as putChars( ).
 | The putChars( ) and putBytes( ) methods provide random access to a LOB host object and allow you to copy content to any point within a LOB host object. |
|
The following example uses a loop to read characters from the textContent.txt file and store them into the host_clob host object. The characters are read one block at a time using the buffered_reader object's buffered_reader.read( ) method. Each block of characters is read into the array named text_buffer. The buffered_reader.read( ) method returns -1 when the end of the file has been reached. After each read, the text_buffer content is copied to the end of host_clob using the putChars( ) method; the end position is incremented after each iteration of the loop.
long position = 1; int chars_read; while ((chars_read = buffered_reader.read(text_buffer)) != -1) { // write the buffer contents to host_clob using the putChars( ) method host_clob.putChars(position, text_buffer); // increment the end position position += chars_read; } // end of while The following example does the same thing for the BLOB. It reads bytes from imageContent.jpg using the file_input_stream.read( ) method, places those bytes into the byte_buffer array, and then copies the contents of that array to host_blob using the putBytes( ) method.
long position = 1; int bytes_read; while ((bytes_read = file_input_stream.read(byte_buffer)) != -1) { // copy the buffer contents to host_blob using the putBytes( ) method host_blob.putBytes(position, byte_buffer); // increment the end position position += bytes_read; } // end of while Of course, the array of characters and bytes that are copied to the LOB host objects using the putChars( ) and putBytes( ) methods do not have to come from a file. The arrays could just as well be populated directly using Java statements. The following example creates and populates an array of characters and copies the array to host_clob, beginning at position 1000:
char [] text_array = {'t', 'e', 's', 't'}; host_clob.putChars(1000, text_array); Similarly, the following example creates and populates an array of bytes and copies that array to host_blob, beginning at position 1000:
byte [] byte_array = {1, 2, 3, 4}; host_blob.putBytes(1000, byte_array); 7.2.1.9 Step 9: Perform a COMMIT
You must perform a COMMIT to permanently record the new contents of the LOB host objects to the database:
#sql { COMMIT }; You will notice that I didn't perform an UPDATE statement in any of the previous steps. This is because the put methods write the content to the columns without any need for an UPDATE statement to be executed.
7.2.1.10 Step 10: Close the objects used to read the file
Finally, you should close the objects created in Step 7 that were used to read the file. The following statements close the objects used to read the textContent.txt file:
buffered_reader.close( ); reader.close( ); file_input_stream.close( );
Of course, if you are reading from a binary file such as imageContent.jpg, you need only to close the file_input_stream object. The other two object types aren't used for binary files.
7.2.2 Storing Content in CLOB and BLOB Columns Using Streams
In this section, you will learn how to read content from a file and store that content in a CLOB or BLOB column using Java streams. There are 10 steps that you need to perform in order to do this:
-
Initialize the LOB column to set the LOB locator.
-
Declare a host object of the LOB type.
-
Retrieve the LOB locator into the LOB host object.
-
Get the buffer size of the LOB from the LOB host object. This is different from the chunk size described earlier.
-
Create a byte buffer to hold a block of data from the file, even if you are storing text content in a CLOB.
-
Create a file object.
-
Create a file input stream object to read the file content.
-
Create an output stream object.
-
While the end of the file has not been reached:
-
Read a block of data from the file into the buffer created in Step 5.
-
Write the buffer content to the LOB host object via the output stream created in Step 8.
-
Close the stream objects.
Steps 1-3, 6, and 7 are the same as in the process for using the put methods. You will notice that the step to perform a COMMIT is absent from the process of using streams. This is intentional, since content streamed to the LOB column is sent directly to the database, and therefore you cannot COMMIT or ROLLBACK the change.
I'll now describe the steps that differ from the previous process.
7.2.2.1 Step 4: Get the LOB buffer size
Rather than sending content to the LOB one chunk at a time, you will be sending a buffer containing multiple chunks. To obtain the default buffer size in bytes, you must call the getBufferSize( ) method. The default returned by this function is three times the default LOB chunk size, or 24,396 bytes. The following statement calls the getBufferSize( ) method using the host_clob object defined in the previous section:
int buffer_size = host_clob.getBufferSize( );
7.2.2.2 Step 5: Create a byte buffer
Because you will be using a Java OutputStream object, which accepts byte arrays, in Step 8, you must now create a byte array to hold each block of data read from the file. You can use byte arrays even when dealing with text files, and in Step 8 you will actually be treating the bytes sent to the LOB host object as ASCII text. The following statement creates an array of bytes using the buffer size obtained in Step 4:
byte [] byte_buffer = new byte[buffer_size];
7.2.2.3 Step 8: Create an output stream object
You use a Java OutputStream object to send content to a CLOB or BLOB host object as a stream of bytes. In order to open an output stream to a CLOB host object, you must call the host object's getAsciiOutputStream( ) method; this causes the byte stream to be interpreted as ASCII text by the CLOB. For a BLOB host object, the method to call is getBinaryOutputStream( ).
 | Once a stream is opened, you must not perform any other database operations until it is closed. Otherwise, stream data may be lost. |
|
The following example creates an OutputStream object named output_stream by calling the host_clob object's getAsciiOutputStream( ) method:
OutputStream output_stream = host_clob.getAsciiOutputStream( );
The host_clob object is now ready to receive a stream of bytes, and it will interpret those bytes as ASCII characters. The getAsciiOutputStream( ) and getBinaryOutputStream( ) methods return SQLJ streams. These stream objects are based on subclasses of the java.io.OutputStream class.
7.2.2.4 Step 9: Populate the LOB
It's time to read the file and populate the LOB. Read a block from the file into the buffer, and then write the buffer content to the LOB host object via the output stream. Repeat this process until the end of file is reached. The loop in the following example reads blocks from file_input_stream into byte_buffer. It then writes the contents of byte_buffer to output_stream using the output_stream object's write( ) method. The write( ) method sends the bytes to the host_clob host object.
int bytes_read; while ((bytes_read = file_input_stream.read(byte_buffer)) != -1) { // write the buffer contents to the LOB output stream // using the write( ) method in the output stream output_stream.write(byte_buffer); } // end of while In this example, the bytes written to the host_clob host object are interpreted as ASCII characters, because in Step 8, the getAsciiOutputStream( ) method was used to open the output stream.
7.2.2.5 Step 10: Close the stream objects
The final step is to close the stream objects. The following statements close file_input_stream and output_stream using each object's close( ) method:
file_input_stream.close( ); output_stream.close( );
7.2.3 Storing Pointers to External Files in BFILE Columns
The BFILE LOB type enables you to store a pointer to a file that is stored outside the database. Although the files are interpreted by the database as binary data, they can be any type of file you want as long as they can be accessed via the filesystem. For example, files on a hard disk, CD-ROM, or DVD-ROM are all valid for use with a BFILE object. The point to remember is that the file must be accessible through the filesystem.
| A BFILE represents a pointer to an external file. The actual file content is not stored in the database. |
|
7.2.3.1 Creating a directory object
To reference an external file, you must first create a directory object in the database to represent the operating system directory in which the file is stored. You create a directory object using the CREATE DIRECTORY statement, and to issue that statement, you must have the CREATE ANY DIRECTORY database privilege. The following statement creates a directory object named SAMPLE_FILES_DIR for the operating system directory C:\sample_files:
CREATE DIRECTORY SAMPLE_FILES_DIR AS 'C:\sample_files';
| Remember that Windows uses the backslash character (\) in directories, while Unix uses the forward slash character (/). |
|
In general, when you create a directory object, you must ensure that:
-
The physical directory exists in the filesystem.
-
The user account used to install the Oracle software has read permission on the physical directory and on the files in that directory that are to be added to the database. In the case of Windows NT, you shouldn't need to worry about this because the Oracle software should have been installed by a user with administrator privileges. Consequently, the account should have access to all directories and files on the system. In the case of Unix, you may have to grant read access to the physical directory and to the files by using the chmod command.
7.2.3.2 Creating a BFILE column
Creating a BFILE column is a fairly simple process: you just create a table with a column based on the BFILE type. The following example creates a table named bfile_content that contains a BFILE column named bfile_column:
CREATE TABLE bfile_content ( file_name VARCHAR2(20) NOT NULL, bfile_column BFILE NOT NULL );
7.2.3.3 Populating a BFILE column
Adding a value to a BFILE column is much easier than adding a value to a CLOB or BLOB column. You don't need to go through the 10 steps shown in the previous section to add a row to the bfile_content table, because you are only storing a pointer to the external file, not the file itself. All you have to do is use Oracle's built-in BFILENAME( ) function to initialize the BFILE pointer to point to the external file of your choice. The BFILENAME( ) function accepts a database directory object name and a filename as parameters.
The following example adds a row to the bfile_content table. Notice the use of the BFILENAME( ) function in this example. It returns a pointer to the file named textContent.txt in the operating system directory pointed to by the SAMPLE_FILES_DIR directory object. That pointer is then stored in the column named bfile_column.
String file_name = "textContent.txt"; String directory = "SAMPLE_FILES_DIR"; #sql { INSERT INTO bfile_content ( file_name, bfile_column ) VALUES ( :file_name, BFILENAME(:directory, :file_name) ) }; The statement shown here stores a pointer to a text file. A similar statement can be used to add a row containing a pointer to a binary file. For example, to store a pointer to the imageContent.jpg file, simply change the file_name string so that it contains that filename.
7.2.4 Example Program: LobExample1.sqlj
This section contains a complete program, named LobExample1.sqlj (Example 7-1), that adds the text stored in the textContent.txt file to the clob_content table and adds the bytes stored in the imageContent.jpg file to the blob_content table. It also adds pointers to those two files to the bfile_content table. The program performs the following major steps:
-
Reads the file textContent.txt and stores the text content from that file in the clob_column of the clob_content table. The program's addCLOB( ) procedure performs this work.
-
Reads the JPEG file imageContent.jpg and stores the binary content from that file in the blob_column of the blob_content table. This work is done by the program's addBLOB( ) procedure.
-
Stores pointers to the textContent.txt and imageContent.jpg files in the bfile_column of the bfile_content table. This is done by the addBFILE( ) procedure. Two rows are created in the bfile_content table one for each file.
Example 7-1. LobExample1.sqlj
/* The program LobExample1.sqlj illustrates how to use the large object types CLOB, BLOB and BFILE to store text and images in the database. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import java.io.*; import sqlj.runtime.*; import oracle.sql.*; public class LobExample1 { public static void main(String [] args) { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "lob_user", "lob_password" ); String source_directory = "C:\\sample_files\\"; addCLOB(source_directory + "textContent.txt"); addBLOB(source_directory + "imageContent.jpg"); addBFILE("SAMPLE_FILES_DIR", "textContent.txt"); addBFILE("SAMPLE_FILES_DIR", "imageContent.jpg"); Oracle.close( ); } catch (IOException e) { System.err.println("IOException " + e); System.exit(1); } catch (SQLException e) { System.err.println("SQLException " + e); System.exit(1); } } // end of main( ) private static void addCLOB(String file_name) throws IOException, SQLException { // step 1: initialize the LOB column to set the locator #sql { INSERT INTO clob_content( file_name, clob_column ) VALUES ( :file_name, EMPTY_CLOB( ) ) }; // step 2: declare a host object of the LOB type CLOB host_clob; // step 3: retrieve the LOB locator into the LOB host object #sql { SELECT clob_column INTO :host_clob FROM clob_content WHERE file_name = :file_name FOR UPDATE }; // step 4: get the chunk size of the LOB from the LOB host object int size_of_chunk = host_clob.getChunkSize( ); // step 5: create a buffer to hold a block of data from the file char [] text_buffer = new char[size_of_chunk]; // step 6: create a file object File file = new File(file_name); // step 7: create input stream objects to read the file contents FileInputStream file_input_stream = new FileInputStream(file); InputStreamReader reader = new InputStreamReader(file_input_stream); BufferedReader buffered_reader = new BufferedReader(reader); // step 8: while the end of the file has not been reached, // read a block from the file into the buffer, and copy the // buffer contents to the LOB host object long position = 1; int chars_read; while ((chars_read = buffered_reader.read(text_buffer)) != -1) { // write the buffer contents to host_clob using the putChars( ) method host_clob.putChars(position, text_buffer); // increment the end position position += chars_read; } // end of while // step 9: perform a COMMIT, which permanently records the LOB // host object contents in the database #sql { COMMIT }; // step 10: close the objects used to read the file buffered_reader.close( ); reader.close( ); file_input_stream.close( ); System.out.println("Successfully added content from file " + file_name + " to CLOB"); } // end of addCLOB( ) private static void addBLOB(String file_name) throws IOException, SQLException { // step 1: initialize the LOB column to set the locator #sql { INSERT INTO blob_content( file_name, blob_column ) VALUES ( :file_name, EMPTY_BLOB( ) ) }; // step 2: declare a host object of the LOB type BLOB host_blob; // step 3: retrieve the LOB locator into the LOB host object #sql { SELECT blob_column INTO :host_blob FROM blob_content WHERE file_name = :file_name FOR UPDATE }; // step 4: get the chunk size of the LOB from the LOB host object int size_of_chunk = host_blob.getChunkSize( ); // step 5: create a buffer to hold a block of data from the file byte [] byte_buffer = new byte[size_of_chunk]; // step 6: create a file object to open the file File file = new File(file_name); // step 7: create an input stream object to read the file contents FileInputStream file_input_stream = new FileInputStream(file); // step 8: while the end of the file has not been reached, // read a block from the file into the buffer, and copy the // buffer contents to the LOB host object long position = 1; int bytes_read; while ((bytes_read = file_input_stream.read(byte_buffer)) != -1) { // copy the buffer contents to host_blob using the putBytes( ) method host_blob.putBytes(position, byte_buffer); // increment the end position position += bytes_read; } // end of while // step 9: perform a COMMIT, which permanently records the LOB // host object contents in the database #sql { COMMIT }; // step 10: close the objects used to read the file file_input_stream.close( ); System.out.println("Successfully added content from file " + file_name + " to BLOB"); } // end of addBLOB( ) private static void addBFILE( String directory, String file_name ) throws SQLException { #sql { INSERT INTO bfile_content( file_name, bfile_column ) VALUES ( :file_name, BFILENAME(:directory, :file_name) ) }; #sql { COMMIT }; System.out.println("Successfully added pointer to file " + file_name + " to BFILE in database directory " + directory); } // end of addBFILE( ) } The output from the program LobExample1.sqlj is as follows:
Successfully added content from file C:\sample_files\textContent.txt to CLOB Successfully added content from file C:\sample_files\imageContent.jpg to BLOB Successfully added pointer to file textContent.txt to BFILE in database directory SAMPLE_FILES_DIR Successfully added pointer to file imageContent.jpg to BFILE in database directory SAMPLE_FILES_DIR
The program LobExample3.sqlj, not printed in this book but downloadable from the programs directory on the book's web site, represents an example that uses Java streams to store the content in clob_column and blob_column. Before running this program, you should re-run the lob_user_schema.sql script; otherwise the program will generate an error because the tables already contain rows for the files. Of course, you could also use the DELETE statement to remove the rows manually.
7.2.5 Retrieving Content from CLOB and BLOB Columns Using the get Methods
In this section, I show you the procedure for retrieving content from CLOB and BLOB columns using the getChars( ) and getBytes( ) methods. You'll see how to retrieve LOB data from the database and write that data into a new file. There are nine steps that you need to perform in order to do this:
-
Declare a host variable of the LOB type.
-
Retrieve the LOB locator into the LOB host object.
-
Get the chunk size of the LOB from the LOB host object.
-
Create a buffer to hold a chunk of data retrieved from the LOB.
-
Create a file object.
-
Create output stream objects to write the LOB content to the new file.
-
Get the length of the LOB content from the LOB host object.
-
Perform the following in a loop until the end of the file is reached:
-
Read a chunk of data from the LOB into the buffer.
-
Write the buffer content to the new file.
-
Close the stream objects.
Only Steps 6, 7, and 8 in this procedure are new; the others have been shown previously in this chapter. For each of these new steps, I use example code to illustrate the process of retrieving text content from clob_column and writing it to the textContent.txt file. In addition, I show how to retrieve the binary content from blob_column and write it to the imageContent.jpg file.
7.2.5.1 Step 6: Create output stream objects
To write the text content contained in the clob_column to a file, you need objects of the Java FileOutputStream, OutputStreamWriter, and BufferedWriter classes. The following statements declare such objects:
FileOutputStream file_output_stream = new FileOutputStream(file); OutputStreamWriter writer = new OutputStreamWriter(file_output_stream); BufferedWriter buffered_writer = new BufferedWriter(writer);
The file_output_stream object provides the low-level functionality to write content to files. The writer object is a wrapper around the file_output_stream object and allows you to write the file contents as a stream of characters. Finally, the buffered_writer object allows you to write text to the file in blocks, instead of one character at a time. These three objects are needed to write text files, but not to write binary files. To write binary content from a column such as blob_column to a file, you need only a FileOutputStream object.
7.2.5.2 Step 7: Get the LOB content length
The length of the LOB content is required in Step 8 when reading the content from the LOB. The length of the content in bytes is returned by the length( ) function. The following statement gets the length of the text content stored in the host CLOB object host_clob:
long clob_length = host_clob.length( );
7.2.5.3 Step 8: Read the LOB content and write it to the file
Read a chunk of data from the LOB into the buffer, write the buffer content to the file, and repeat this process until you reach the end of the file. The content of a CLOB host object is retrieved using the getChars( ) method, which gets characters from the host object beginning from a position that you specify. This position is a long value, and is passed as a parameter. The first character of a CLOB is considered position 1. Likewise, the content of a BLOB host object is retrieved using the getBytes( ) method.
| The getChars( ) and getBytes( ) methods provide random access to a LOB host object, allowing you to retrieve content from any point within the LOB host object. |
|
The following example uses a loop to read a chunk of characters from host_clob into the text_buffer using the getChars( ) method. The text_buffer content is then appended to the file using the buffered_writer.write( ) method, which accepts an array of characters. The loop repeats until the end of the host_clob content is reached. The end is determined by checking the current position value against the content length contained in clob_length.
for ( long position = 1; position <= clob_length; position += size_of_chunk ) { // read a chunk of data from host_clob into the buffer using the // getChars( ) method int chars_read = host_clob.getChars(position, size_of_chunk, text_buffer); // write the buffer to the file buffered_writer.write(text_buffer); } // end of for The following example is similar, but operates on the binary data contained in host_blob. The code uses a loop to read a chunk of bytes from host_blob into byte_buffer using the getBytes( ) method. The byte_buffer content is then appended to the file using the file_output_stream.write( ) method, which accepts an array of bytes. The loop repeats until the end of the host_blob content is reached.
for ( long position = 1; position <= blob_length; position += size_of_chunk ) { // read a chunk of data from host_blob into the buffer using the // getBytes( ) method int bytes_read = host_blob.getBytes(position, size_of_chunk, byte_buffer); // write the buffer contents to the file file_output_stream.write(byte_buffer); } // end of for Later in this chapter, you will examine a complete program, named LobExample2.sqlj, that retrieves the content from the clob_column and blob_column LOBs and stores that content in new files.
7.2.6 Retrieving Content from CLOB and BLOB Columns Using Streams
In this section, I show you the process for retrieving content from CLOB and BLOB columns using streams. Through those streams, the CLOB and BLOB content is written to new operating system files. There are five steps in this process, and Steps 3 through 5 are new:
-
Declare a host variable of the LOB type.
-
Retrieve the LOB locator into the LOB host object.
-
Create an input stream object and call the appropriate LOB host object input stream function in preparation for reading the content of the LOB.
-
Save the content of the LOB to a new file, reading the LOB content from the input stream object.
-
Close the input stream object.
You've already seen how to perform Steps 1 and 2 of this process. For Steps 3 through 5, I will use example code to illustrate the process for retrieving the text from clob_column.
7.2.6.1 Step 3: Create an input stream object
You must create an input stream object and call the appropriate LOB host object input stream function in preparation for reading the contents of the LOB. You can use a Java InputStream object to retrieve content from a CLOB or BLOB host object. In order to open an input stream to a CLOB host object, you must call that object's getAsciiStream( ) method. For a BLOB host object, call getBinaryStream( ).
| Once a stream is opened, you must not perform any other database operations until it is closed. Otherwise, stream data may be lost. |
|
The following Java statement invokes the host_clob object's getAsciiStreamMethod( ) to create an InputStream object named input_stream:
InputStream input_stream = host_clob.getAsciiStream( );
The input_stream object is now ready for you to begin reading ASCII characters from host_clob.
7.2.6.2 Step 4: Read the LOB content and write it to a file
You must now read the LOB content from the input stream object and save that content to a new file. The content is read from host_clob via an InputStream object. To save the content, you can use a new method, which I've written, named saveFile( ). The saveFile( ) method accepts two parameters: an InputStream object and a filename. The saveFile( ) method creates a new file using the filename parameter and copies the contents read from the specified input stream to the new file. The saveFile( ) method is defined as follows:
private static void saveFile( InputStream input_stream, String file_name ) throws IOException { // create a file object File file = new File(file_name); // create a file output stream FileOutputStream file_output_stream = new FileOutputStream(file); // read the contents of the input stream until // the end of the input stream has been reached (indicated // by -1 being returned from the read( ) method) byte [] byte_buffer = new byte[8132]; int bytes_read; while ((bytes_read = input_stream.read(byte_buffer)) != -1 ){ // write the input to the file file_output_stream.write(byte_buffer); } // end of while // close the file output stream file_output_stream.close( ); } // end of saveFile( ) Notice the declaration for byte_buffer: it is an array of 8132 bytes, the same size as a default chunk. You can choose the size for your programs based on the total length of your LOB content, using a bigger buffer when your LOBs contain a large amount of data.
The following example creates a variable named save_file_name. It then calls saveFile( ) to read the LOB content from input_stream and save the content to a new file, created with the filename specified in save_file_name:
String save_file_name = target_directory + "retrievedCLOB2" + file_name; saveFile(input_stream, save_file_name);
Because input_stream accepts the content from host_clob, the new file created by saveFile( ) contains the text previously stored in host_clob.
7.2.6.3 Step 5: Close the input stream object
The last step, once the LOB content has been retrieved and written to the destination file, is to close the InputStream object:
input_stream.close( );
7.2.7 Retrieving Content from BFILE Columns
Although a BFILE column only stores a pointer to the external file, you can access and read the actual file content via that pointer. Remember, though, that the external file is not stored in the database; only the pointer to the file is stored. The process for reading a file through a BFILE pointer is as follows:
-
Declare a BFILE host object.
-
Retrieve the BFILE column into the BFILE host object.
-
Get the filename from the BFILE host object.
-
Verify that the external file pointed to by the BFILE host object exists.
-
Open the external file via the BFILE host object.
-
Create an input stream object to read the external file content via the BFILE host object.
-
Save the file content read from the input stream to a new file.
-
Close the input stream.
-
Close the external file via the BFILE host object.
For each step outlined here, I use example code to illustrate how to retrieve, from the bfile_content table, the bfile_column value that points to the external file textContent.txt. I then show how to read the content of the file through the pointer that was retrieved, and how to write that content to a new operating system file.
7.2.7.1 Step 1: Declare a BFILE host object
The first step is to declare a BFILE host object so that you can manipulate the pointer from your Java code. The following statement declares a BFILE host object named host_bfile:
BFILE host_bfile;
7.2.7.2 Step 2: Retrieve the BFILE column
The next step is to retrieve BFILE pointer into the BFILE host object. The following example retrieves the bfile_column value that points to the textContent.txt file. (The row being retrieved was created earlier in the chapter.) The pointer is being retrieved into the host_bfile host object.
String file_name = "textContent.txt"; #sql { SELECT bfile_column INTO :host_bfile FROM bfile_content WHERE file_name = :file_name }; 7.2.7.3 Step 3: Get the filename
After retrieving the BFILE pointer, you need to the get the name of the file to which it points from the BFILE host object. You use the getName( ) method for that purpose:
String bfile_name = host_bfile.getName( );
7.2.7.4 Step 4: Verify that the external file exists
Once you have the filename, check to be sure the file actually exists before you attempt to open it. You should use the fileExists( ) method for this purpose:
host_bfile.fileExists( );
7.2.7.5 Step 5: Open the external file
Once you're sure the file exists, open it using the openFile( ) method:
host_bfile.openFile( );
7.2.7.6 Step 6: Create an input stream object
Then, create an input stream object to read the external file content via the BFILE host object. The external file is interpreted as a stream of bytes, which means that the example code in this section will also work if you attempt to read a binary file such as the external file imageContent.jpg. Before you can read an external file, you must create a Java InputStream object and set it to the binary stream returned by the BFILE host object's getBinaryStream( ) function. For example:
InputStream input_stream = host_bfile.getBinaryStream( );
The external file pointed to by host_bfile is now ready to be read as a stream of bytes via the input_stream object.
7.2.7.7 Step 7: Save the file content to a new file
Next, save the file content read from the input stream to a new file. The following example uses the saveFile( ) method described earlier to save the file content read using the InputStream object to a new file:
String target_directory = "C:\\sample_files\\retrieved\\"; String save_file_name = target_directory + "retrievedBFILE" + bfile_name; saveFile(input_stream, save_file_name);
7.2.7.8 Step 8: Close the input stream object
Once the LOB content has been retrieved and written to the destination file, close the InputStream object:
input_stream.close( );
7.2.7.9 Step 9: Close the external file
Finally, close the external file using the closeFile( ) method in the BFILE host object:
host_bfile.closeFile( );
7.2.8 Example Program: LobExample2.sqlj
This section contains a complete program, named LobExample2.sqlj (Example 7-2), that retrieves the text and image content previously stored in the clob_content and blob_content tables by the LobExample1.sqlj program. The content retrieved is then written to new operating system files. The program also retrieves the pointers to the external files in the bfile_content table, reads those files, and copies their contents to new files. The program performs the following major steps, with each step corresponding to a procedure defined in the program:
-
Reads the content previously stored in the clob_column of the clob_content table from the text file textContent.txt, and writes the retrieved content to a new file named retrievedCLOBtextContent.txt in the directory C:\sample_files\retrieved. This step is performed using the procedure retrieveCLOB( ).
-
Reads the content previously stored in the blob_column of the blob_content table from the JPEG file imageContent.jpg, and writes the retrieved content to a new file named retrievedBLOBimageContent.jpg in the directory C:\sample_files\retrieved. This step is performed using the procedure retrieveBLOB( ).
-
Reads the BFILE pointers to the textContent.txt and imageContent.jpg files from the bfile_column of the bfile_content table, retrieves the content for those files, and writes that content to two new files named retrievedBFILEtextContent.txt and retrievedBFILEimageContent.jpg. This step is performed using the procedure retrieveBFILE( ).
Example 7-2. LobExample2.sqlj
/* The program LobExample2.sqlj illustrates how to retrieve content from the large object types CLOB, BLOB and BFILE. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import java.io.*; import sqlj.runtime.*; import oracle.sql.*; public class LobExample2 { public static void main(String [] args) { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "lob_user", "lob_password" ); String source_directory = "C:\\sample_files\\"; String target_directory = "C:\\sample_files\\retrieved\\"; retrieveCLOB("textContent.txt", source_directory, target_directory); retrieveBLOB("imageContent.jpg", source_directory, target_directory); retrieveBFILE("textContent.txt", target_directory); retrieveBFILE("imageContent.jpg", target_directory); Oracle.close( ); } catch (IOException e) { System.err.println("IOException " + e); System.exit(1); } catch (SQLException e) { System.err.println("SQLException " + e); System.exit(1); } } private static void retrieveCLOB( String file_name, String source_directory, String target_directory ) throws IOException, SQLException { // step 1: declare a host object of the LOB type CLOB host_clob; // step 2: retrieve the LOB locator into the LOB host object #sql { SELECT clob_column INTO :host_clob FROM clob_content WHERE file_name = :(source_directory + file_name) }; // step 3: get the chunk size of the LOB from the LOB host object int size_of_chunk = host_clob.getChunkSize( ); // step 4: create a buffer to hold a chunk of data retrieved from // the LOB host object char [] text_buffer = new char[size_of_chunk]; // step 5: create a file object String save_file = target_directory + "retrievedCLOB" + file_name; File file = new File(save_file); // step 6: create output stream objects to write the LOB contents // to the new file FileOutputStream file_output_stream = new FileOutputStream(file); OutputStreamWriter writer = new OutputStreamWriter(file_output_stream); BufferedWriter buffered_writer = new BufferedWriter(writer); // step 7: get the length of the LOB contents from the LOB host object long clob_length = host_clob.length( ); // step 8: while the end of the LOB contents has not been reached, // read a chunk of data from the LOB into the buffer, // and write the buffer contents to the file for ( long position = 1; position <= clob_length; position += size_of_chunk ) { // read a chunk of data from host_clob into the buffer using the // getChars( ) method int chars_read = host_clob.getChars(position, size_of_chunk, text_buffer); // write the buffer contents to the file buffered_writer.write(text_buffer); } // end of for // step 9: close the stream objects buffered_writer.close( ); writer.close( ); file_output_stream.close( ); System.out.println("Successfully retrieved CLOB and saved file " + save_file); } // end of retrieveCLOB( ) private static void retrieveBLOB( String file_name, String source_directory, String target_directory ) throws IOException, SQLException { // step 1: declare a host object of the LOB type BLOB host_blob; // step 2: retrieve the LOB locator into the LOB host object #sql { SELECT blob_column INTO :host_blob FROM blob_content WHERE file_name = :(source_directory + file_name) }; // step 3: get the chunk size of the LOB from the LOB host object int size_of_chunk = host_blob.getChunkSize( ); // step 4: create a buffer to hold a chunk of data retrieved from // the LOB host object byte [] byte_buffer = new byte[size_of_chunk]; // step 5: create a file object String save_file_name = target_directory + "retrievedBLOB" + file_name; File file = new File(save_file_name); // step 6: create output stream object to write the LOB contents // to the new file FileOutputStream file_output_stream = new FileOutputStream(file); // step 7: get the length of the LOB contents from the LOB host object long blob_length = host_blob.length( ); // step 8: while the end of the LOB contents has not been reached, // read a chunk of data from the LOB into the buffer, // and write the buffer contents to the file for ( long position = 1; position <= blob_length; position += size_of_chunk ) { // read a chunk of data from host_blob into the buffer using the // getBytes( ) method int bytes_read = host_blob.getBytes(position, size_of_chunk, byte_buffer); // write the buffer contents to the file file_output_stream.write(byte_buffer); } // end of for // step 9: close the stream objects file_output_stream.close( ); System.out.println("Successfully retrieved BLOB and saved file " + save_file_name); } // end of retrieveBLOB( ) private static void retrieveBFILE( String file_name, String target_directory ) throws IOException, SQLException { // step 1: create a BFILE host object BFILE host_bfile; // step 2: retrieve the BFILE column into the BFILE host object #sql { SELECT bfile_column INTO :host_bfile FROM bfile_content WHERE file_name = :file_name }; // step 3: get the file name from the BFILE host object String bfile_name = host_bfile.getName( ); // step 4: check that the external file pointed to // by the BFILE host object exists host_bfile.fileExists( ); // step 5: open the external file via the BFILE host object host_bfile.openFile( ); // step 6: create an input stream object to read the external // file contents via the BFILE host object InputStream input_stream = host_bfile.getBinaryStream( ); // step 7: save the file contents read from the input stream to a // new file String save_file_name = target_directory + "retrievedBFILE" + bfile_name; saveFile(input_stream, save_file_name); // step 8: close the input stream input_stream.close( ); // step 9: close the external file via the BFILE host object host_bfile.closeFile( ); System.out.println("Successfully retrieved BFILE and saved file " + save_file_name); } // end of retrieveBFILE( ) private static void saveFile( InputStream input_stream, String file_name ) throws IOException { // create a file object File file = new File(file_name); // create a file output stream FileOutputStream file_output_stream = new FileOutputStream(file); // read the contents of the input stream until // the end of the input stream has been reached (indicated // by -1 being returned from the read( ) method) byte [] byte_buffer = new byte[8132]; int bytes_read; while ((bytes_read = input_stream.read(byte_buffer)) != -1 ) { // write the input to the file file_output_stream.write(byte_buffer); } // end of while // close the file output stream file_output_stream.close( ); } // end of saveFile( ) } The output from the program LobExample2.sqlj is as follows:
Successfully retrieved CLOB and saved file C:\sample_files\retrieved\retrievedCLOBtextContent.txt Successfully retrieved BLOB and saved file C:\sample_files\retrieved\retrievedBLOBimageContent.jpg Successfully retrieved BFILE and saved file C:\sample_files\retrieved\retrievedBFILEtextContent.txt Successfully retrieved BFILE and saved file C:\sample_files\retrieved\retrievedBFILEimageContent.jpg
The program LobExample4.sqlj, not printed in this book but available from the book's web site, contains an example that uses streams to retrieve the content from clob_column and blob_column.
