Re: CLOBs and the Client driver

[Rajesh Kartha]

Hi Daniel,

Please note that the DerbyClient does not support 
ResultSet.TYPE_SCROLL_SENSITIVE or ResultSet.TYPE_SCROLL_INSENSITIVE 
if the ResultSet has LOB.

For more info see:
http://db.apache.org/derby/papers/DerbyClientSpec.html

One has to use the ResultSet.TYPE_FORWARD_ONLY to retrieve the LOB.

In you example changing the PreparedStatement line to:
reparedStatement 
pstmt=conn.prepareStatement(selectTable,ResultSet.TYPE_FORWARD_ONLY,
                  ResultSet.CONCUR_READ_ONLY);

will take care of getting the actual CLOBs.  Do post to the list if you 
come across any issues.

-Rajesh


Daniel Noll wrote:

Rajesh Kartha wrote:

Can you post the way you are retrieving the CLOBs from the ResultSet.



Darn, the idea didn't work.  Okay, here's the code we're using, with a 
bit of our framework for free. ;-)

   PreparedStatement ps = database.getPreparedStatementCache().get(
       "SELECT date, type, point, detailclass, detailparams " +
       "FROM HistoryRecord",
       ResultSet.TYPE_SCROLL_INSENSITIVE,
       ResultSet.CONCUR_READ_ONLY);
   ResultSet rs = ps.executeQuery();
   try
   {
       // Seek directly to the one we want...
       rs.absolute(i + 1);

       Date date = rs.getTimestamp("date");
       HistoryRecord.Type type = HistoryRecord.Type
           .values()[rs.getShort("type")];
       HistoryRecord.Point point = HistoryRecord.Point
           .values()[rs.getShort("point")];
       HistoryDetail detail = (HistoryDetail)
           Class.forName(rs.getString("detailclass")).newInstance();

       // String detailParamString = rs.getString("detailparams");
       String detailParamString = IOUtils.readToString(
           rs.getCharacterStream("detailparams"));

       // Now we parse the XML which was found in detailparams,
       // using the particular HistoryDetail class we just instantiated.
   }
   finally
   {
       rs.close();
   }

We noticed this problem when the XML parse started failing every 
time.  On closer investigation, it was always an empty string being 
returned from the results.  It looks like it doesn't matter whether we 
use getString() or getCharacterStream(), the result is always the 
same... works with the embedded driver, but not with the client driver.

I've checked the database at the other end using the embedded driver 
and the values do show up there, so it's not corruption of the 
database itself.

In case it affects things, auto-commit has been turned off, and the 
table is being created like this:

   CREATE TABLE HistoryRecord (
       date TIMESTAMP NOT NULL,
       type SMALLINT NOT NULL,
       point SMALLINT NOT NULL,
       detailclass VARCHAR(256),
       detailparams CLOB(1M)
   )

...yes, I realise we gave this no primary key... but that seems to be 
how it is.

Anyone have any ideas?  This seems to be a real stumper, but maybe I'm 
missing something obvious.

Daniel