7.3 LONG and LONG RAW Columns
To access LONG and LONG RAW columns, you can use the SQLJ stream classes. SQLJ stream classes are subclasses of the java.io.InputStream and java.io.OutputStream Java classes, and are contained in the sqlj.runtime package. There are three SQLJ stream classes that may be used to define SQLJ host objects:
- AsciiStream
-
An AsciiStream host object is used to process ASCII text to be stored or retrieved from a LONG column. You can also use an AsciiStream host object to read VARCHAR2 columns rather than using a Java string to store the content. You might want to do that if a VARCHAR2 column contains a lot of data.
- BinaryStream
-
A BinaryStream host object is used to process binary data to be stored or retrieved from a LONG RAW or RAW column. A RAW column has a capacity of 4KB, smaller than the 2GB capacity of a LONG RAW column.
- UnicodeStream
-
A UnicodeStream host object is used to process text in the Unicode character set to be stored or retrieved from a LONG or VARCHAR2 column. Unicode uses a two-byte character set and can represent over 65,000 characters. Unicode may be used to represent non-English characters.
I use two new tables in this section:
- long_content
-
Contains a LONG column named long_column.
- long_raw_content
-
Contains a LONG RAW column named long_raw_column.
Both tables also contain a column to store the name of the file from which content was read. The two tables are defined as follows:
CREATE TABLE long_content ( file_name VARCHAR2(40) NOT NULL, long_column LONG NOT NULL ); CREATE TABLE long_raw_content ( file_name VARCHAR2(40) NOT NULL, long_raw_column LONG RAW NOT NULL );
The examples in this section read content from the textContent.txt and imageContent.jpg files and store that content in the columns long_column and long_raw_column.
7.3.1 Storing Content in LONG and LONG RAW Columns Using SQLJ Streams
There are seven steps that you must perform when using a LONG or LONG RAW column to store content read from a file:
-
Create a file object.
-
Get the length of the file in bytes.
-
Create a file input stream object to read the file content.
-
Create an appropriate SQLJ stream host object to store the content.
-
Use the SQLJ stream host object to add the content to the database.
-
Perform a COMMIT.
-
Close the SQLJ stream and file input stream objects.
For each step outlined here, I use example code to illustrate how to store the textContent.txt file in the long_content table and the imageContent.jpg file in the long_raw_content table.
7.3.1.1 Step 1: Create a file object
First, you need a File object, which acts as a handle to the file:
String source_directory = "C:\\sample_files\\"; String file_name = "textContent.txt"; File file = new File(source_directory + file_name);
The File object shown here allows you to read the textContent.txt file. You can create a similar File object to read the imageContent.jpg file.
7.3.1.2 Step 2: Get the length of the file in bytes
Later, in Step 4, you will need the length of the file in bytes. You can get the file length using a call to the length( ) method. The length( ) method returns a long value. Because you will need an int value later, you should cast the value returned by the length( ) method to an int:
int file_length = (int) file.length( );
7.3.1.3 Step 3: Create a file input stream object
To read the content of the file, you must create a Java FileInputStream object, which provides the low-level functionality to read files:
FileInputStream file_input_stream = new FileInputStream(file);
7.3.1.4 Step 4: Create an appropriate SQLJ stream host object
You cannot simply insert a FileInputStream object into the database: you need a SQLJ stream host object to temporarily store the content read from the file. This SQLJ stream host object will be used in Step 5 to add the content to the database.
The exact type of stream host object you need to create depends on how the content in the file is to be interpreted. If the file is to be interpreted as ASCII text, you should create an AsciiStream host object. If the file is to be interpreted as binary data, create a BinaryStream host object. Finally, if the file is to be interpreted as Unicode, create a UnicodeStream host object. The constructor for the SQLJ stream class accepts two parameters: a FileInputStream object and the length of the file in bytes.
To retrieve the content from the textContent.txt file, which will be interpreted as ASCII text, you need an AsciiStream host object:
AsciiStream ascii_stream = new AsciiStream(file_input_stream, file_length);
For the imageContent.jpg file, which will be interpreted as binary data, you need a BinaryStream host object:
BinaryStream binary_stream = new BinaryStream(file_input_stream, file_length);
7.3.1.5 Step 5: Add the content to the database
You use the SQLJ stream host object in an INSERT statement to add the content to the database. The following example uses the ascii_stream host object, created in the previous step, in an INSERT statement to add the content of the textContent.txt file to the long_column of the long_content table:
#sql { INSERT INTO long_content ( file_name, long_column ) VALUES ( :file_name, :ascii_stream ) };
Similarly, the following example uses the binary_stream host object to add the content of the imageContent.jpg file to the long_raw_column of the long_raw_content table:
#sql { INSERT INTO long_raw_content ( file_name, long_raw_column ) VALUES ( :file_name, :binary_stream ) };
7.3.1.6 Step 6: Perform a COMMIT
You must perform a COMMIT to permanently record the results of any INSERT statements that you execute:
#sql { COMMIT };
7.3.1.7 Step 7: Close the SQLJ stream and file input stream objects
To free resources, close the ascii_stream, file_input_stream, and binary_stream objects. For example:
ascii_stream.close( ); file_input_stream.close( ); binary_stream.close( );
7.3.2 Example Program: StreamExample1.sqlj
This section contains a complete program, StreamExample1.sqlj (Example 7-3), that adds the text stored in the textContent.txt file to the long_content table. It also adds the bytes stored in the imageContent.jpg file to the long_raw_content table. The program performs the following major steps:
-
Copies the text from the textContent.txt file and stores it in the long_column column of the long_content table using a SQLJ AsciiStream object. This step is performed in the program's addLONG( ) procedure.
-
Copies the bytes from the file imageContent.jpg and stores them in the long_raw_column of the long_raw_content table using a SQLJ BinaryStream object. This step is performed in the program's addLONGRAW( ) procedure.
Example 7-3. StreamExample1.sqlj
/* The program StreamExample1.sqlj illustrates how to use the LONG and LONG RAW types to store text and images in the database. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import java.io.*; import sqlj.runtime.*; public class StreamExample1 { 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\\"; addLONG(source_directory, "textContent.txt"); addLONGRAW(source_directory, "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 addLONG( String source_directory, String file_name ) throws IOException, SQLException { // step 1: create a file object File file = new File(source_directory + file_name); // step 2: get the length of the file in bytes int file_length = (int) file.length( ); // step 3: create a file input stream object to read the file contents FileInputStream file_input_stream = new FileInputStream(file); // step 4: create an appropriate SQLJ stream host object to store // the content AsciiStream ascii_stream = new AsciiStream(file_input_stream, file_length); // step 5: use the SQLJ stream host object to add the content to the // database #sql { INSERT INTO long_content ( file_name, long_column ) VALUES ( :file_name, :ascii_stream ) }; // step 6: perform a COMMIT #sql { COMMIT }; // step 7: close the SQLJ stream and file input stream objects ascii_stream.close( ); file_input_stream.close( ); System.out.println("Successfully added content from file " + file_name + " to LONG"); } // end of addLONG( ) private static void addLONGRAW( String source_directory, String file_name ) throws IOException, SQLException { // step 1: create a file object File file = new File(source_directory + file_name); // step 2: get the length of the file in bytes int file_length = (int) file.length( ); // step 3: create a file input stream object to read the file contents FileInputStream file_input_stream = new FileInputStream(file); // step 4: create an appropriate SQLJ stream host object to store // the content BinaryStream binary_stream = new BinaryStream(file_input_stream, file_length); // step 5: use the SQLJ stream host object to add the content to the // database #sql { INSERT INTO long_raw_content ( file_name, long_raw_column ) VALUES ( :file_name, :binary_stream ) }; // step 6: perform a COMMIT #sql { COMMIT }; // step 7: close the SQLJ stream and file input stream objects binary_stream.close( ); file_input_stream.close( ); System.out.println("Successfully added content from file " + file_name + " to LONG RAW"); } // end of addLONGRAW( ) }
The output from the program StreamExample1.sqlj is as follows:
Successfully added content from file textContent.txt to LONG Successfully added content from file imageContent.jpg to LONG RAW
7.3.3 Retrieving Content from LONG and LONG RAW Columns Using Java Strings and Byte Arrays
Before I show how to retrieve the content from LONG and LONG RAW columns using SQLJ streams, I want to show you how to retrieve such content using Java strings and byte arrays. This technique is useful only if your LONG and LONG RAW columns don't contain large amounts of information. If they do, use SQLJ streams.
The following example shows how to retrieve text into a Java string from the column named long_column in the long_content table:
String file_name = "textContent.txt; String text = null; #sql { SELECT long_column INTO :text FROM long_content WHERE file_name = :file_name };
This example shows how to retrieve the bytes from the long_raw_content table's column named long_raw_column, and store those bytes into a byte array:
String file_name = "imageContent.txt; byte [] byte_array = new byte[10000]; #sql { SELECT long_raw_column INTO :byte_array FROM long_raw_content WHERE file_name = :file_name };
As you can see, these examples use simple SELECT INTO statements. Remember, this approach to retrieving data from LONG and LONG RAW columns is useful only when the amount of data to be retrieved is small. I show how to retrieve large amounts of data in the following section.
7.3.4 Retrieving Content from LONG and LONG RAW Columns Using SQLJ Streams
When you have large amounts of data to retrieve from a LONG or LONG RAW column, you need to retrieve that content using SQLJ stream objects. Unfortunately, it is not possible to use a SQLJ stream object in a SELECT INTO statement, so you must use one of the following methods to retrieve the content from a LONG or LONG RAW column:
-
Use an iterator that contains a SQLJ stream object. Iterators were discussed in Chapter 3.
-
Use a PL/SQL function that retrieves the LONG or LONG RAW column into a SQLJ stream object and returns the object. You then call this PL/SQL function using the SQLJ VALUES statement and store the returned object in a SQLJ stream object. The VALUES statement was also discussed in Chapter 3.
Just as a reminder: an AsciiStream object is used to process ASCII text to be retrieved from a LONG or VARCHAR2 column; a BinaryStream object is used to process binary data to be retrieved from a LONG or LONG RAW column; and a UnicodeStream object is used to process Unicode text to be retrieved from a LONG or VARCHAR2 column.
7.3.5 Using Iterators with SQLJ Streams
Iterators represent one method for working with SQLJ streams when retrieving LONG and LONG RAW values. You can use either a named or positional iterator with SQLJ streams, but there is one critical difference between the two. If you use a positional iterator, the iterator class definition can contain only one SQLJ stream object, and it must be the last object listed in the class. A named iterator can contain multiple SQLJ stream objects.
There are eight steps that you must perform when using SQLJ streams to retrieve content from a LONG or LONG RAW column using an iterator:
-
Declare an iterator class containing an appropriate SQLJ stream object column.
-
Declare an iterator object from the iterator class.
-
Declare an appropriate SQLJ stream object to store the content from the iterator SQLJ stream object column.
-
Populate the iterator object using a database query.
-
Retrieve the content of the iterator object. If you are using a named iterator, you must use the iterator's accessor methods, and if you are using a positional iterator, you must use the FETCH statement to retrieve the iterator column objects. Because a SQLJ stream object is being read, there are two important rules that you must follow; if you do not, data in the SQLJ stream may be lost:
-
You must retrieve the iterator column objects in the same order in which the columns appear in the database query in Step 4.
-
Once the SQLJ stream object in the iterator is retrieved, it must immediately be written to the SQLJ stream object created in Step 3.
-
Save the SQLJ stream object to a new file.
-
Close the SQLJ stream object.
-
Close the iterator object.
For each step outlined here, I use example code to illustrate how to retrieve the rows from the long_content table using a named iterator. I also show example code to retrieve the rows from the long_raw_content table using a positional iterator.
7.3.5.1 Step 1: Declare an iterator class
First, you declare an iterator class containing an appropriate SQLJ stream object column. An AsciiStream object must be used to process the text stored in the column named long_column, and because a named iterator is used, the names of the column objects in the named iterator class must match the names of the columns in the long_content table:
#sql private static iterator LongContentIteratorClass ( String file_name, AsciiStream long_column );
A BinaryStream object must be used to process the bytes stored in the long_raw_column. Because a positional iterator is used for the binary data, you do not specify the names of the column objects:
#sql private static iterator LongRawContentIteratorClass ( String, BinaryStream );
7.3.5.2 Step 2: Declare an iterator object
You then declare an iterator object from the iterator class. The following example declares an iterator object named long_content_iterator to process the rows in the long_content table:
LongContentIteratorClass long_content_iterator;
Similarly, the following example declares an iterator object named long_raw_content_iterator to process the rows in the long_raw_content table:
LongRawContentIteratorClass long_raw_content_iterator;
7.3.5.3 Step 3: Declare a SQLJ stream object
The next step is to declare an appropriate SQLJ stream object to store the content from the iterator. An AsciiStream object named ascii_stream is used to store the text from the long_column:
AsciiStream ascii_stream = null;
A BinaryStream object named binary_stream is used to store the bytes from the long_raw_column:
BinaryStream binary_stream = null;
7.3.5.4 Step 4: Populate the iterator object
Then, you populate the iterator object using a database query. The following example populates long_content_iterator with the row from the long_content table:
#sql long_content_iterator = { SELECT file_name, long_column FROM long_content };
The following example populates long_raw_content_iterator with the row from the long_raw_content table:
#sql long_raw_content_iterator = { SELECT file_name, long_raw_column FROM long_raw_content };
7.3.5.5 Step 5: Retrieve the content of the iterator object
Next, you retrieve the content of the iterator object. Iterator column objects must be accessed in the order in which they were listed in the query. In addition, once a SQLJ stream object is accessed, it must be processed immediately and then closed. In the following examples, the save_file_name variable is used to specify the name of the file in which the retrieved stream is to be saved using the saveFile( ) function (shown earlier in Section 7.2.6). The following example uses the named iterator's accessor methods to retrieve the filename and AsciiStream object from long_content_iterator:
while (long_content_iterator.next( )) { String file_name = long_content_iterator.file_name( ); String save_file_name = target_directory + "retrievedLONG" + file_name; // retrieve the SQLJ stream object ascii_stream = long_content_iterator.long_column( ); ...
The following example uses the FETCH statement to retrieve the filename and BinaryStream object from long_raw_content_iterator:
boolean finished = false; String file_name = null; while (!finished) { #sql { FETCH :long_raw_content_iterator INTO :file_name, :binary_stream }; // if the end of rows has been reached, set finished to true if (long_raw_content_iterator.endFetch( )) { finished = true; } else { String save_file_name = target_directory + "retrievedLONGRAW" + file_name; ...
7.3.5.6 Step 6: Save the SQLJ stream object
You then save the SQLJ stream object to a new file. The following example calls the saveFile( ) method previously described to save the ascii_stream SQLJ stream object:
saveFile(ascii_stream, save_file_name);
Similarly, for the binary_stream stream object:
saveFile(binary_stream, save_file_name);
7.3.5.7 Step 7: Close the SQLJ stream object
When you are finished with the SQLJ stream object created in Step 3, you must close it using the close( ) method. The following example closes the ascii_stream object:
... ascii_stream.close( ); } // end of while
Similarly, for the binary_stream object:
... binary_stream.close( ); } // end of while
7.3.5.8 Step 8: Close the iterator object
Finally, close the iterator object using the close( ) method. The following statements close both long_content_iterator and long_raw_content_iterator:
long_content_iterator.close( ); long_raw_content_iterator.close( );
7.3.6 Using PL/SQL Functions with SQLJ Streams
An alternative to using iterators with SQLJ streams is to create a PL/SQL function that returns a LONG, LONG RAW, RAW, or VARCHAR2 variable. The following PL/SQL function, named retrieve_LONG_func( ), is created in the lob_user schema by the lob_user_schema.sql script; the function selects and returns the long_column from the long_content table:
CREATE OR REPLACE FUNCTION retrieve_LONG_func( p_file_name IN VARCHAR2 ) RETURN LONG IS long_column_var LONG; BEGIN -- retrieve the LONG column into a LONG variable SELECT long_column INTO long_column_var FROM long_content WHERE file_name = p_file_name; -- return the LONG variable RETURN long_column_var; END retrieve_LONG_func; /
You can then use the following three steps in your SQLJ program to read the variable returned by the PL/SQL function:
-
Declare an appropriate SQLJ stream object to store the variable returned by the PL/SQL function. A stream object is compatible with a LONG.
-
Call the PL/SQL function using the VALUES statement and store the returned variable in the SQLJ stream object created in Step 1.
-
Save the SQLJ stream object to a new file.
-
Close the SQLJ stream object.
As an illustration of Step 2, which is the only step in this process that you haven't seen before, the following example uses the VALUES statement to call the PL/SQL function retrieve_LONG_func( ), and stores the returned LONG variable in the ascii_stream host object:
#sql ascii_stream = { VALUES(retrieve_LONG_func(:file_name)) };
7.3.7 Example Program: StreamExample2.sqlj
This section contains a complete program, StreamExample2.sqlj (Example 7-4), which retrieves the content previously stored in the long_content and long_raw_content tables by the program StreamExample1.sqlj. The program StreamExample2.sqlj performs the following major steps:
-
Reads the content previously stored in the long_column of the long_content table, and writes that content to a new file named retrievedLONGtextContent.txt in the directory C:\sample_files\retrieved. This step is performed by the program's retrieveLONG( ) procedure using a named iterator and an AsciiStream object.
-
Reads the content previously stored in the long_raw_column of the long_raw_content table and writes that retrieved content to a new file named retrievedLONGRAWimageContent.jpg. This step is performed by the program's retrieveLONGRAW( ) procedure using a positional iterator and a BinaryStream object.
-
Reads the text from long_column and writes it to a new file named retrievedLONGplsqltextContent.txt. This step is performed by the program's retrieveLONGplsql( ) procedure, which uses a call to the PL/SQL function retrieve_LONG_func( ).
Example 7-4. StreamExample2.sqlj
/* The program StreamExample2.sqlj illustrates how to retrieve content from LONG and LONG RAW types. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import java.io.*; import sqlj.runtime.*; public class StreamExample2 { // step 1: declare an iterator class containing an appropriate // SQLJ stream object column // a named iterator class #sql private static iterator LongContentIteratorClass ( String file_name, AsciiStream long_column ); // a positional iterator class #sql private static iterator LongRawContentIteratorClass ( String, BinaryStream ); public static void main(String [] args) { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "lob_user", "lob_password" ); String target_directory = "C:\\sample_files\\retrieved\\"; retrieveLONG(target_directory); retrieveLONGRAW(target_directory); retrieveLONGplsql("textContent.txt", 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); } } // end of main( ) private static void retrieveLONG( String target_directory ) throws IOException, SQLException { // step 2: declare an iterator object from the iterator class LongContentIteratorClass long_content_iterator; // step 3: declare an appropriate SQLJ stream object to store // the content from the iterator SQLJ stream object column AsciiStream ascii_stream = null; // step 4: populate the iterator object using a database query #sql long_content_iterator = { SELECT file_name, long_column FROM long_content }; // step 5: retrieve the contents of the iterator object while (long_content_iterator.next( )) { String file_name = long_content_iterator.file_name( ); String save_file_name = target_directory + "retrievedLONG" + file_name; // retrieve the SQLJ stream object ascii_stream = long_content_iterator.long_column( ); // step 6: save the SQLJ stream object to a new file saveFile(ascii_stream, save_file_name); System.out.println("Successfully retrieved LONG and saved file " + save_file_name); // step 7: close the SQLJ stream object ascii_stream.close( ); } // end of while // step 8: close the iterator object long_content_iterator.close( ); } // end of retrieveLONG( ) private static void retrieveLONGRAW( String target_directory ) throws IOException, SQLException { // step 2: declare an iterator object from the iterator class LongRawContentIteratorClass long_raw_content_iterator; // step 3: declare an appropriate SQLJ stream object to store // the content from the iterator SQLJ stream object column BinaryStream binary_stream = null; // step 4: populate the iterator object using a database query #sql long_raw_content_iterator = { SELECT file_name, long_raw_column FROM long_raw_content }; // step 5: retrieve the contents of the iterator object boolean finished = false; String file_name = null; while (!finished) { #sql { FETCH :long_raw_content_iterator INTO :file_name, :binary_stream }; // if the end of rows has been reached, set finished to true if (long_raw_content_iterator.endFetch( )) { finished = true; } else { String save_file_name = target_directory + "retrievedLONGRAW" + file_name; // step 6: save the SQLJ stream object to a new file saveFile(binary_stream, save_file_name); System.out.println("Successfully retrieved LONG RAW and " + "saved file " + save_file_name); // step 7: close the SQLJ stream object created in step 3 binary_stream.close( ); } // end if } // end of while // step 8: close the iterator object long_raw_content_iterator.close( ); } // end of retrieveLONGRAW( ) private static void retrieveLONGplsql( String file_name, String target_directory ) throws IOException, SQLException { // step 1: declare an appropriate SQLJ stream object to store // the stream returned from the PL/SQL function AsciiStream ascii_stream; // step 2: call the PL/SQL function using the VALUES statement // and store the returned variable in the SQLJ stream object // created in step 1 #sql ascii_stream = { VALUES(retrieve_LONG_func(:file_name)) }; // step 3: save the SQLJ stream object to a new file String save_file_name = target_directory + "retrievedLONGplsql" + file_name; saveFile(ascii_stream, save_file_name); System.out.println("Successfully retrieved LONG and saved file " + save_file_name); // step 4: close the SQLJ stream object ascii_stream.close( ); } // end of retrieveLONGplsql( ) 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 StreamExample2.sqlj is as follows:
Successfully retrieved LONG and saved file C:\sample_files\retrieved\retrievedLONGtextContent.txt Successfully retrieved LONG RAW and saved file C:\sample_files\retrieved\retrievedLONGRAWimageContent.jpg Successfully retrieved LONG and saved file C:\sample_files\retrieved\retrievedLONGplsqltextContent.txt