Working with LOBs and BFILEs

Working with BFILEs

This section describes how to read and write data to and from external binary files (BFILEs), using file locators.

Getting and Passing BFILE Locators

Getter and setter methods are available for retrieving or passing BFILE locators from or to the database.

Retrieving BFILE Locators

Given a standard JDBC result set or callable statement object that includes BFILE locators, you can access the locators by using the standard result set getObject() method. This returns an oracle.sql.BFILE object.

You can also access the locators by casting your result set to OracleResultSet or your callable statement to OracleCallableStatement and using the getOracleObject() or getBFILE() method.

Notes:

In the OracleResultSet and OracleCallableStatement classes, getBFILE() and getBfile() both return oracle.sql.BFILE. There is no java.sql interface (or oracle.jdbc2 interface) for BFILEs.

If using getObject() or getOracleObject(), remember to cast the output, as necessary. For more information, see "Casting Your get Method Return Values".

Example: Getting a BFILE locator from a Result Set

Assume that the database has a table called bfile_table with a single column for the BFILE locator bfile_col. This example assumes that you have already created your Statement object stmt.

Select the BFILE locator into a standard result set. If you cast the result set to an OracleResultSet, you can use getBFILE() to get the BFILE locator:

// Select the BFILE locator into a result set
ResultSet rs = stmt.executeQuery("SELECT bfile_col FROM bfile_table"); 
while (rs.next()) 
{
   oracle.sql.BFILE my_bfile = ((OracleResultSet)rs).getBFILE(1); 
}

Note that as an alternative, you can use getObject() to return the BFILE locator. In this case, because getObject() returns a java.lang.Object, cast the results to BFILE. For example:

oracle.sql.BFILE my_bfile = (BFILE)rs.getObject(1);

Example: Getting a BFILE Locator from a Callable Statement

Assume you have an OracleCallableStatement object ocs that calls a function func that has a BFILE output parameter. The following code example sets up the callable statement, registers the output parameter as OracleTypes.BFILE, executes the statement, and retrieves the BFILE locator:

OracleCallableStatement ocs = 
   (OracleCallableStatement)conn.prepareCall("{? = call func()}");
ocs.registerOutParameter(1, OracleTypes.BFILE);
ocs.execute();
oracle.sql.BFILE bfile = ocs.getBFILE(1);

Passing BFILE Locators

To pass a BFILE locator to a prepared statement or callable statement (to update a BFILE locator, for example), you can do one of the following:

Use the standard setObject() method.

or:

Cast the statement to OraclePreparedStatement or OracleCallableStatement, and use the setOracleObject() or setBFILE() method.

These methods take the parameter index and an oracle.sql.BFILE object as input.

Example: Passing a BFILE Locator to a Prepared Statement

Assume you want to insert a BFILE locator into a table, and you have an OraclePreparedStatement object ops to insert data into a table. The first column is a string (to designate a row number), the second column is a BFILE, and you have a valid oracle.sql.BFILE object (bfile). Write the BFILE to the database as follows:

OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                            ("INSERT INTO my_bfile_table VALUES (?,?)");
ops.setString(1,"one");
ops.setBFILE(2, bfile);
ops.execute();

Example: Passing a BFILE Locator to a Callable Statement

Passing a BFILE locator to a callable statement is similar to passing it to a prepared statement. In this case, the BFILE locator is passed to the myGetFileLength() procedure, which returns the BFILE length as a numeric value.

OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall
                               ("begin ? := myGetFileLength (?); end;");
try
{
   cstmt.registerOutParameter (1, Types.NUMERIC);
   cstmt.setBFILE (2, bfile);
   cstmt.execute ();
   return cstmt.getLong (1);
}

Reading BFILE Data

To read BFILE data, you must first get the BFILE locator. You can get the locator from either a callable statement or a result set. "Getting and Passing BFILE Locators" describes this.

Once you obtain the locator, you can invoke a number of methods on the BFILE without opening it. For example, you can use the oracle.sql.BFILE methods fileExists() and isFileOpen() to determine whether the BFILE exists and if it is open. If you want to read and manipulate the data, however, you must open and close the BFILE, as follows:

Use the openFile() method of the oracle.sql.BFILE class to open a BFILE.
When you are done, use the closeFile() method of the BFILE class.

BFILE data is materialized as a Java stream. To read from a BFILE, use the getBinaryStream() method of an oracle.sql.BFILE object to retrieve the entire file as an input stream. This returns a java.io.InputStream object.

As with any InputStream object, use one of the overloaded read() methods to read the file data, and use the close() method when you finish.

Notes:

BFILEs are read-only. You cannot insert data or otherwise write to a BFILE.

You cannot use JDBC to create a new BFILE. They are created only externally.

Example: Reading BFILE Data

The following example uses the getBinaryStream() method of an oracle.sql.BFILE object to read BFILE data into a byte stream and then read the byte stream into a byte array. The example assumes that the BFILE has already been opened.

// Read BFILE data from a BFILE locator
Inputstream in = bfile.getBinaryStream();
byte[] byte_array = new byte{10};
int byte_read = in.read(byte_array);

Creating and Populating a BFILE Column

This section discusses how to create a BFILE column in a table with SQL operations and specify the location where the BFILE resides. The examples below assume you have already created your Connection object conn and Statement object stmt.

Creating a BFILE Column in a New Table

To work with BFILE data, create a BFILE column in a table, and specify the location of the BFILE. To specify the location of the BFILE, use the SQL CREATE DIRECTORY...AS statement to specify an alias for the directory where the BFILE resides. Then execute the statement. In this example, the directory alias is test_dir, and the BFILE resides in the /home/work directory.

String cmd;
cmd = "CREATE DIRECTORY test_dir AS '/home/work'";
stmt.execute (cmd);

Use the SQL CREATE TABLE statement to create a table containing a BFILE column, then execute the statement. In this example, the name of the table is my_bfile_table.

// Create a table containing a BFILE field 
cmd = "CREATE TABLE my_bfile_table (x varchar2 (30), b bfile)";
stmt.execute (cmd);

In this example, the VARCHAR2 column designates a row number, and the BFILE column stores the locator of the BFILE data.

Populating a BFILE Column

Use the SQL INSERT INTO...VALUES statement to populate the VARCHAR2 and BFILE fields, then execute the statement. The BFILE column is populated with the locator to the BFILE data. To populate the BFILE column, use the bfilename function to specify the directory alias and the name of the BFILE file.

cmd ="INSERT INTO my_bfile_table VALUES ('one', bfilename(test_dir, 
                                         'file1.data'))";
stmt.execute (cmd);
cmd ="INSERT INTO my_bfile_table VALUES ('two', bfilename(test_dir,  
                                         'jdbcTest.data'))";
stmt.execute (cmd);

In this example, the name of the directory alias is test_dir. The locator of the BFILE file1.data is loaded into the BFILE column on row one, and the locator of the BFILE jdbcTest.data is loaded into the bfile column on row two.

As an alternative, you might want to create the row for the row number and BFILE locator now, but wait until later to insert the locator. In this case, insert the row number into the table, and null as a place holder for the BFILE locator.

cmd ="INSERT INTO my_bfile_table VALUES ('three', null)";
stmt.execute(cmd);

Here, three is inserted into the row number column, and null is inserted as the place holder. Later in your program, insert the BFILE locator into the table by using a prepared statement.

First get a valid BFILE locator into the bfile object:

rs = stmt.executeQuery("SELECT b FROM my_bfile_table WHERE x='two'");
rs.next();
oracle.sql.BFILE bfile = ((OracleResultSet)rs).getBFILE(1);

Then, create your prepared statement. Note that because this example uses the setBFILE() method to identify the BFILE, the prepared statement must be cast to an OraclePreparedStatement:

OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                      (UPDATE my_bfile_table SET b=? WHERE x = 'three');
ops.setBFILE(1, bfile);
ops.execute();

Now row two and row three contain the same BFILE.

Once you have the BFILE locators available in a table, you can access and manipulate the BFILE data. The next section, "Accessing and Manipulating BFILE Data", describes this.

Accessing and Manipulating BFILE Data

Once you have the BFILE locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you must first select its locator from a result set or a callable statement.

The following code continues the example from "Populating a BFILE Column", getting the locator of the BFILE from row two of a table into a result set. The result set is cast to an OracleResultSet so that oracle.sql.* methods can be used on it. Several of the methods applied to the BFILE, such as getDirAlias() and getName(), do not require you to open the BFILE. Methods that manipulate the BFILE data, such as reading, getting the length, and displaying, do require you to open the BFILE.

When you finish manipulating the BFILE data, you must close the BFILE. For a complete BFILE example, see "BFILEs--FileExample.java".

// select the bfile locator
cmd = "SELECT *  FROM my_bfile_table WHERE x = 'two'";
rset = stmt.executeQuery (cmd);

if (rset.next ())
    BFILE bfile = ((OracleResultSet)rset).getBFILE (2);

// for these methods, you do not have to open the bfile
println("getDirAlias() = " + bfile.getDirAlias());
println("getName() = " + bfile.getName());
println("fileExists() = " + bfile.fileExists());
println("isFileOpen() = " + bfile.isFileOpen());

// now open the bfile to get the data
bfile.openFile();

// get the BFILE data as a binary stream
InputStream in = bfile.getBinaryStream();
int length ;

// read the bfile data in 6-byte chunks
byte[] buf = new byte[6];

while ((length = in.read(buf)) != -1)
{
    // append and display the bfile data in 6-byte chunks 
   StringBuffer sb = new StringBuffer(length);
   for (int i=0; i<length; i++)
      sb.append( (char)buf[i] );
   System.out.println(sb.toString());
}

// we are done working with the input stream. Close it.   
in.close();

// we are done working with the BFILE. Close it.  
bfile.closeFile();

Additional BFILE Features

In addition to the features already discussed in this chapter, the oracle.sql.BFILE class has a number of methods for further functionality, including the following:

openFile(): Opens the external file for read-only access.
closeFile(): Closes the external file.
getBinaryStream(): Returns the contents of the external file as a stream of bytes.
getBytes(): Reads from the external file, starting at a specified point, into a supplied buffer.
getName(): Gets the name of the external file.
getDirAlias(): Gets the directory alias of the external file.
length(): Returns the length of the BFILE in bytes.
position(): Determines the byte position at which the given byte pattern begins.
isFileOpen(): Determines whether the BFILE is open (for read-only access).