Release 8.1.5
A64685-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i JDBC Developer's Guide and Reference Release 8.1.5 A64685-01 |
|
This section contains sample code for the following Oracle extensions:
This sample demonstrates basic support for LOBs in the OCI 8 driver. It illustrates how to create a table containing LOB columns, and includes utility programs to read from a LOB, write to a LOB, and dump the LOB contents. For more information on LOBs, see "Working with LOBs".
Except for some changes to the comments, the following sample is similar to the LobExample.java program in the Demo/samples/oci8/object-samples directory.
import java.sql.*; // line 1 import java.io.*; import java.util.*; // Importing the Oracle Jdbc driver package // makes the code more readable import oracle.jdbc.driver.*; // Import this to get CLOB and BLOB classes import oracle.sql.*; public class NewLobExample1 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database. You can put a database // name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // It's faster when auto commit is off conn.setAutoCommit (false); // line 26 // Create a Statement Statement stmt = conn.createStatement (); try { stmt.execute ("DROP TABLE basic_lob_table"); } catch (SQLException e) { // An exception could be raised here if the table did // not exist already but we gleefully ignore it } // line 38 // Create a table containing a BLOB and a CLOB line 40 stmt.execute ("CREATE TABLE basic_lob_table (x varchar2 (30), b blob, c clob)"); // Populate the table stmt.execute ("INSERT INTO basic_lob_table VALUES ('one', '010101010101010101010101010101', 'onetwothreefour')"); stmt.execute ("INSERT INTO basic_lob_table VALUES ('two', '0202020202020202020202020202', 'twothreefourfivesix')"); // line 49 System.out.println ("Dumping lobs"); // Select the lobs ResultSet rset = stmt.executeQuery ("SELECT * FROM basic_lob_table"); while (rset.next ()) { // Get the lobs BLOB blob = ((OracleResultSet)rset).getBLOB (2); CLOB clob = ((OracleResultSet)rset).getCLOB (3); // Print the lob contents dumpBlob (conn, blob); dumpClob (conn, clob); // Change the lob contents fillClob (conn, clob, 2000); fillBlob (conn, blob, 4000); } // line 68 System.out.println ("Dumping lobs again"); rset = stmt.executeQuery ("SELECT * FROM basic_lob_table"); while (rset.next ()) { // Get the lobs BLOB blob = ((OracleResultSet)rset).getBLOB (2); CLOB clob = ((OracleResultSet)rset).getCLOB (3); // Print the lobs contents dumpBlob (conn, blob); dumpClob (conn, clob); } } // line 82 // Utility function to dump Clob contents static void dumpClob (Connection conn, CLOB clob) throws Exception { // get character stream to retrieve clob data Reader instream = clob.getCharacterStream(); // create temporary buffer for read line 91 char[] buffer = new char[10]; // length of characters read int length = 0; // fetch data line 98 while ((length = instream.read(buffer)) != -1) { System.out.print("Read " + length + " chars: "); for (int i=0; i<length; i++) System.out.print(buffer[i]); System.out.println(); } // Close input stream instream.close(); } // line 108 // Utility function to dump Blob contents static void dumpBlob (Connection conn, BLOB blob) throws Exception { // Get binary output stream to retrieve blob data InputStream instream = blob.getBinaryStream(); // Create temporary buffer for read byte[] buffer = new byte[10]; // length of bytes read line 120 int length = 0; // Fetch data while ((length = instream.read(buffer)) != -1) { System.out.print("Read " + length + " bytes: "); for (int i=0; i<length; i++) System.out.print(buffer[i] + " "); System.out.println(); } // Close input stream instream.close(); } // line 135 // Utility function to put data in a Clob static void fillClob (Connection conn, CLOB clob, long length) throws Exception { Writer outstream = clob.getCharacterOutputStream(); int i = 0; int chunk = 10; while (i < length) { outstream.write(i + "hello world", 0, chunk); // line 147 i += chunk; if (length - i < chunk) chunk = (int) length - i; } outstream.close(); } // line 154 // Utility function to write data to a Blob static void fillBlob (Connection conn, BLOB blob, long length) throws Exception { OutputStream outstream = blob.getBinaryOutputStream(); int i = 0; byte [] data = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }; // line 165 int chunk = data.length; while (i < length) { data [0] = (byte)i; outstream.write(data, 0, chunk); i += chunk; if (length - i < chunk) chunk = (int) length - i; } outstream.close(); } } // line 175
Import the necessary java.* and oracle.* classes. Register the driver with the DriverManager.registerDriver() method and connect to the database with DriverManager.getConnection(). Use the database URL jdbc:oracle:oci8:@ and connect as user scott with password tiger. You can optionally enter a database name following the @ symbol.
Use setAutoCommit(false) to disable the AUTOCOMMIT feature and enhance performance. If you do not, the driver will issue execute and commit commands after every SQL statement.
Create a statement object. Drop any pre-existing table named basic_lob_table. Then, create a new basic_lob_table directory to store the LOBs in-line.
Use SQL statements to create a table with three columns: a column to store the row number as a VARCHAR2, a BLOB column, and a CLOB column. Then insert data into two rows of the table.
SELECT the contents of the table into a result set.
Retrieve the LOBs. The getBLOB() and getCLOB() methods return locators to the LOB data; to retrieve the LOB contents, you must write additional code (which is defined later in this program). To use the getBLOB() and getCLOB() methods, cast the result set to an OracleResultSet object. Then call the "dump" functions to display the contents of the LOBs, and the "fill" functions to change the contents of the LOBs. The dump and fill functions are defined later in this program.
Display the LOBs again, after their contents have been changed. SELECT the contents of the table into a result set, and then apply the dump functions. The dump functions are defined later in this program.
Define the utility function dumpClob to display the contents of a CLOB. Read the CLOB contents as a character stream. Use the getCharacterStream() method to get a READER stream object. Set up the temporary character array to read the character data in 10-character chunks.
Set up a loop to read and display the contents of the CLOB. The length of the CLOB is displayed as well. Close the input stream when you are done.
Define the utility function dumpBlob to display the contents of a BLOB. Read the BLOB contents as a binary stream. Use the getBinaryStream() method to get an InputStream stream object. Set up the temporary byte array to read the binary data in 10-byte chunks.
Set up a loop to read and display the contents of the BLOB. The length of the BLOB is displayed as well. Close the input stream when you are done.
Define the utility function fillClob to write data to a CLOB. The fillClob function needs the CLOB locator and the length of the CLOB. To write to the CLOB, use the getCharacterOutputStream() method to get a WRITER object.
Set up a loop to write an index value and part of the string Hello World to the CLOB. Close the WRITER stream when you are done.
Define the utility function fillBlob to write data to a BLOB. The fillBlob function needs the BLOB locator and the length of the BLOB. To write to the BLOB, use the getBinaryOutputStream() method to get an OutputStream object.
Define the byte array of data that you want to write to the BLOB. The while loop causes a variation of the data to be written to the BLOB. Close the OutputStream object when you are done.
This sample demonstrates basic BFILE support in the OCI 8 driver. It illustrates filling a table with BFILEs and includes a utility for dumping the contents of a BFILE. For more information on BFILEs, see "Working with LOBs".
Except for some changes to the comments, the following sample is similar to the FileExample.java program in the Demo/samples/oci8/object-samples directory.
import java.sql.*; // line 1 import java.io.*; import java.util.*; //including this import makes the code easier to read import oracle.jdbc.driver.*; // needed for new BFILE class import oracle.sql.*; // line 10 public class NewFileExample1 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver line 16 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. // // The example creates a DIRECTORY and you have to be connected as // "system" to be able to run the test. // If you can't connect as "system" have your system manager // create the directory for you, grant you the rights to it, and // remove the portion of this program that drops and creates the directory. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "system", "manager"); // It's faster when auto commit is off conn.setAutoCommit (false); // line 32 // Create a Statement Statement stmt = conn.createStatement (); try // line 36 { stmt.execute ("DROP DIRECTORY TEST_DIR"); } catch (SQLException e) { // An error is raised if the directory does not exist. Just ignore it. } // line 43 stmt.execute ("CREATE DIRECTORY TEST_DIR AS '/temp/filetest'"); try // line 46 { stmt.execute ("drop table test_dir_table"); } catch (SQLException e) { // An error is raised if the table does not exist. Just ignore it. } // line 54 // Create and populate a table with files // The files file1 and file2 must exist in the directory TEST_DIR created // above as symbolic name for /private/local/filetest. stmt.execute ("CREATE TABLE test_dir_table (x varchar2 (30), b bfile)"); stmt.execute ("INSERT INTO test_dir_table VALUES ('one', bfilename ('TEST_DIR', 'file1'))"); stmt.execute ("INSERT INTO test_dir_table VALUES ('two', bfilename ('TEST_DIR', 'file2'))"); // Select the file from the table // line 64 ResultSet rset = stmt.executeQuery ("SELECT * FROM test_dir_table"); while (rset.next ()) { String x = rset.getString (1); BFILE bfile = ((OracleResultSet)rset).getBFILE (2); System.out.println (x + " " + bfile); // Dump the file contents dumpBfile (conn, bfile); } } //line 75 // Utility function to dump the contents of a Bfile line 77 static void dumpBfile (Connection conn, BFILE bfile) throws Exception { // line 80 System.out.println ("Dumping file " + bfile.getName()); System.out.println ("File exists: " + bfile.fileExists()); System.out.println ("File open: " + bfile.isFileOpen()); System.out.println ("Opening File: "); // line 84 bfile.openFile(); System.out.println ("File open: " + bfile.isFileOpen()); long length = bfile.length(); System.out.println ("File length: " + length); int chunk = 10; InputStream instream = bfile.getBinaryStream(); // Create temporary buffer for read byte[] buffer = new byte[chunk]; // Fetch data while ((length = instream.read(buffer)) != -1) { System.out.print("Read " + length + " bytes: "); for (int i=0; i<length; i++) System.out.print(buffer[i] + " "); System.out.println(); } // line 108 // Close input stream instream.close(); // close file handler bfile.closeFile(); } // line 115 }
Import the necessary java.* and oracle.* classes. Register the driver with the DriverManager.registerDriver() method and connect to the database with the getConnection() method. Use the database URL jdbc:oracle:oci8:@ and connect as user system with password manager. You can optionally enter a database name following the @ symbol.
Use setAutoCommit(false) to disable the AUTOCOMMIT feature and enhance performance. If you do not, the driver will issue execute and commit commands after every SQL statement.
Create a statement object. Drop any pre-existing directory named TEST_DIR. Then, create a new TEST_DIR directory to store the BFILE. You or your System Administrator can use whatever file name you wish.
Drop any pre-existing table named test_dir_table.
Create and populate a table with files. Use SQL statements to create a table, test_dir_table, with two columns: one column to indicate the row number as a VARCHAR2 (for example, "one" or "two"), and one column to hold the BFILE locator.
Use SQL statements to insert some data into the table. For the first row, insert a row number in the first column, and use the BFILENAME keyword to insert a BFILE, file1, located in TEST_DIR, in the second column. Do the same thing for the second row.
SELECT the contents of the table into a result set. Set up a loop to retrieve the contents of the table. Use getString() to retrieve the row number data, and use getBFILE() to retrieve the BFILE locator. Since BFILE is an Oracle-specific datatype, and getBFILE() is an Oracle extension, cast the result set object to an OracleResultSet object.
Use the dumpBfile() method (defined later in the program) to display the BFILE contents and various statistics about the BFILE.
Define the dumpBfile() method to display the BFILE contents and various statistics about the BFILE. The dumpBfile() method takes the BFILE locator as input.
Use the getName(), fileExists(), and isFileOpen() methods to return the name of the BFILE, and whether the BFILE exists and is open. Note that the BFILE does not have to be open to apply these methods to it.
Read and display the BFILE contents. First open the BFILE. You can read the BFILE contents as a binary stream. Use the getBinaryStream() method to get an input stream object. Determine the size of the "chunk" in which the stream will read the BFILE data, and set up the temporary byte array to store the data.
Set up a loop to read and display the contents of the BFILE. The length of the BFILE is displayed as well.
When you are finished, close the input stream and the BFILE.
