Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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 method 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 that 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).

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index