I noticed that Cayenne does not properly handle LOBs in Oracle. When Cayenne
adds a record with a LOB, it simply writes it in the PreparedStatement with
setBytes(). However, as demonstrated by the Oracle Q&A article below, this
only satisfies BLOBs <4k. To have a blob more than 4k, the row must be
inserted, followed by an EMPTY_BLOB() call (or EMPTY_CLOB()), followed by
the blob write. An example pulled (and stripped) from Metalink is as
follows:
ps = conn.prepareStatement("INSERT INTO table (fields...,blobField) VALUES
(..., ?)");
...
ps.setBinaryStream(idx, null, 0);
ps.executeUpdate();
ps.close();
ps = conn.prepareStatement("UPDATE table SET blobField=EMPTY_BLOB() WHERE
...");
...
ps.executeUpdate();
ps.close();
ps = conn.prepareStatement("SELECT blobField FROM table WHERE ...");
...
rs = ps.executeQuery();
Blob dbBlob = null;
if (rs.next()) {
dbBlob = writeToBlob(rs, data);
}
rs.close();
ps.close();
ps = conn.prepareStatement("UPDATE table SET blobField=? WHERE ...");
...
ps.setBlob(idx, dbBlob);
See this Oracle Q&A article as an example:
-----------------------
When Do I Use EMPTY_BLOB() and EMPTY_CLOB()?
Question
When must I use EMPTY_BLOB() and EMBPTY_CLOB()? I always thought it was
mandatory for each insert of a CLOB or BLOB to initialize the LOB locator
first with either EMPTY_CLOB() or EMPTY_BLOB().
Answer
In Oracle, you can initialize a LOB with data using the INSERT statement as
long as the data is <4K. This is why your insert statement worked.
Note that you can also update a LOB with data that is <4K using the UPDATE
statement. If the LOB is larger than 4K perform the following
steps:
1. Insert into the table initializing the LOB using EMPTY_BLOB() or
EMPTY_CLOB() and use the returning clause to get back the locator
2. For LOB attributes, call ocilobwrite() to write the entire data to the
LOB. For other than LOB attributes, you can insert all the data using the
INSERT statement.
Note the following:
* Oracle has now removed the <4K restriction and you can insert >4K
worth of data into the LOB using the insert or even the update statement for
LOB columns. Note however, that you cannot initialize a LOB attribute which
is part of an object type with data and you must use
EMPTY_BLOB()/EMPTY_CLOB().
* Also you cannot use >4K as the default value for a LOB even though you
can use >4k when inserting or updating the LOB data.
* Initializing the LOB value with data or using
EMPTY_BLOB()/EMPTY_CLOB() is orthogonal to how the data is stored. If the
LOB value is less than approximately 4K, then the value is stored inline (as
long as the user doesn't specify DISABLE STORAGE IN ROW) and once it grows
larger than 4K, it is moved out of line.
-----------------------
Is this behavior feasible in Cayenne?
This archive was generated by hypermail 2.0.0 : Wed Nov 10 2004 - 16:29:40 EST