Derby should throw a better error message if a BLOB/CLOB column is accessed more than once

• Key: DERBY-3583
• Type: Improvement
• Status: Closed
• Resolution: Won't Fix
• Priority: Minor
• Assignee: Kathey Marsden
• Reporter: Kathey Marsden
• Component/s: JDBC

Description

With the DERBY-2892 fix revision 642974, users can no longer access a BLOB/CLOB column more than once for each row. Currently the error message is
XJ217 - You cannot invoke other java.sql.Clob/java.sql.Blob methods after calling the free() method or after the Blob/Clob's transaction has been committed or rolled back.

I don't know that that makes sense given the user called getString()

For getCharacterStream()/getBinaryStream() there is no error on the call, just an IOException on the read.

We should throw a clearer error message, that the user is not allowed to make multiple getXXX calls on a BLOB/CLOB column for each row.

Comments

[Kathey Marsden]

This issue is related to the DERBY-2892 fix.

[Kathey Marsden]

When I implemented this change I saw test failures when caling getClob() more than once, for example in BlobClob4BLOBTest
 if (origRS.getClob(1) != null) {
                assertEquals("FAIL - Invalid checksum for row trigger",
                        getStreamCheckSum(origRS.getClob(1).getAsciiStream()),
                        getStreamCheckSum(trigARS.getClob(1).getAsciiStream()));

I guess that DERBY-2892 by itself does not restrict multiple calls to getClob() or getBlob(), but only restricts getString(), getBytes(), getCharacterStream() and getBinaryStream(), because they free the locator. I wonder if this change should restrict getBlob() and getClob() calls as well or should it continue to allow multiple calls. Thoughts?

[Kristian Waagan]

As I noted in DERBY-3571, I believe that patch should make it a lot easier to support either option.

When it comes to which option to choose, I'm not sure.
These are some of my thoughts (in random order):
 1) Allowing only a single call forces more portable code.
 2) Allowing multiple calls makes the client driver consistent with the embedded driver.
 3) Allowing multiple calls is more convenient.
 4) Allowing multiple calls might have some side-effects if two LOB objects are used concurrently (?).

Seems like I'm more in favor of allowing multiple calls, so I guess I would go for that if it works technically.

Regarding the comment about the other get methods (String, Bytes, XStream), I don't think they are disallowed because the locator is freed. That is just how it is implemented, and it's in line with the spec to close the streams.
I think it might be just as good to let the LOB tracker release the locators. The only case where the locators cannot be released implicitly, is when a Blob or Clob object is published to the user.
As soon as DERBY-3571 is resolved, I can post a patch demonstrating the approach.

[Kathey Marsden]

With Kristian's alternate DERBY-2892 patch this won't be necessary as multiple calls will still be allowed.