Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Message-ID: <43443D7E.6090907@Source-Zone.Org>
Date: Wed, 05 Oct 2005 13:54:22 -0700
From: Rajesh Kartha <kartha@Source-Zone.Org>
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: CLOBs and the Client driver
References: <4342312E.600@nuix.com.au> <4342BA4D.3000902@Source-Zone.Org>
 <4343109A.5080800@nuix.com.au>
In-Reply-To: <4343109A.5080800@nuix.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>