int rows = 0;
FileInputStream fin = null;
OutputStream out = null;
ResultSet rslt = null;
Statement stmt = null;
CLOB biography = null; // NOTE: oracle.sql.CLOB!!!
long person_id = 0;
try {
conn.setAutoCommit(false);
// get Tim's person_id
stmt = conn.createStatement();
rslt = stmt.executeQuery(
"select person_id " +
"from person " +
"where last_name = 'O''Reilly' " +
"and first_name = 'Tim'");
while (rslt.next()) {
rows++;
person_id = rslt.getLong(1);
}
if (rows > 1) {
System.err.println("Too many rows!");
System.exit(1);
}
else if (rows == 0) {
System.err.println("Not found!");
System.exit(1);
}
rslt.close();
rslt = null;
// check to see the row already exists
rows = 0;
rslt = stmt.executeQuery(
"select biography " +
"from person_information " +
"where person_id = " + Long.toString( person_id ) + " " +
"for update nowait");
while (rslt.next()) {
rows++;
biography = ((OracleResultSet)rslt).getCLOB(1);
}
rslt.close();
rslt = null;
if (rows == 0) {
// insert a row in the information table
// this creates the LOB locators
rows = stmt.executeUpdate(
"insert into person_information " +
"( person_id, biography, photo ) " +
"values " +
"( " + Long.toString( person_id ) +
", empty_clob(), empty_blob() )");
System.out.println(rows + " rows inserted");
// retrieve the locator
rows = 0;
rslt = stmt.executeQuery(
"select biography " +
"from person_information " +
"where person_id = " + Long.toString( person_id ) + " " +
"for update nowait");
rslt.next();
biography = (CLOB)rslt.getClob(1);
rslt.close();
rslt = null;
}
// Now that we have the locator, lets store the biography
File asciiFile = new File("tim.txt");
fin = new FileInputStream(asciiFile);
byte[] buffer = new byte[biography.getBufferSize()];
out = biography.getAsciiOutputStream();
int length = 0;
while ((length = fin.read(buffer)) != -1) {
out.write(buffer, 0, length);
}
// You've got to close the output stream before
// you commit, or the changes are lost!
out.close();
out = null;
fin.close();
fin = null;
conn.commit();
}
catch (SQLException e) {
System.err.println("SQL Error: " + e.getMessage());
}
catch (IOException e) {
System.err.println("IO Error: " + e.getMessage());
}
finally {
if (rslt != null)
try { rslt.close(); } catch (SQLException ignore) { }
if (stmt != null)
try { stmt.close(); } catch (SQLException ignore) { }
if (out != null)
try { out.close(); } catch (IOException ignore) { }
if (fin != null)
try { fin.close(); } catch (IOException ignore) { }
}
