You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Daniel Noll <da...@nuix.com.au> on 2005/10/04 09:37:18 UTC
CLOBs and the Client driver
Hi.
Are there any known problems in Derby 10.1 with regards to retrieving
CLOBs? We have a system which can work in both embedded and client
mode, and I've discovered that using it in client mode results in an
empty string (not null, but "") returned for a CLOB-type column.
The same issue doesn't happen on the embedded driver, and I can't find
any immediately obvious records in JIRA which describe a similar problem.
Daniel
--
Daniel Noll
NUIX Pty Ltd
Level 8, 143 York Street, Sydney 2000
Phone: (02) 9283 9010
Fax: (02) 9283 9020
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: CLOBs and the Client driver
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Hi Daniel,
>>>>> "Daniel" == Daniel Noll <da...@nuix.com.au> wrote:
Daniel> Yeah, I found that too. It does still seem odd that I should be getting
Daniel> an error about the result set being forward only, when I'm only moving
Daniel> forward, but maybe that's just a poor error message on the part of
Daniel> either JDBC or Derby.
I guess the JDBC specification (Derby is compliant here, it is *not*
an error) is the way it is to allow implementations to offer scroll
forward without requiring any means of explicit positioning (skipping
forward).
Daniel> Stepping through with next() is a bit unreasonable though. Is it fast?
I would assume most implementations fetch the data over the network
once you do next. Derby does, using a prefetch of 64 rows.
Daniel> I guess as long as it doesn't result in any network traffic due to the
Daniel> row data itself, it's fine. But if it does transfer the row data when I
Daniel> won't be using it, then stepping over 10,000 rows would be
Daniel> slow.
If you know you wouldn't be visiting 10,000 rows without looking at
them, maybe there a way to make the query result set smaller?
If not, I guess it will be slow...
Daniel>
Daniel> Is there really no way to do OFFSET/LIMIT in Derby? That would have
Not to my knowledge.
Thanks,
Dag
Daniel> been the traditional way to do paging.
Daniel>
Daniel> Daniel
Daniel>
Daniel> --
Daniel> Daniel Noll
Daniel>
Daniel> NUIX Pty Ltd
Daniel> Level 8, 143 York Street, Sydney 2000
Daniel> Phone: (02) 9283 9010
Daniel> Fax: (02) 9283 9020
Daniel>
Daniel> This message is intended only for the named recipient. If you are not
Daniel> the intended recipient you are notified that disclosing, copying,
Daniel> distributing or taking any action in reliance on the contents of this
Daniel> message or attachment is strictly prohibited.
Daniel>
--
Dag H. Wanvik
Sun Microsystems, Web Services, Database Technology Group
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax: +47 73842101
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTICE: This email message is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or distribution
is prohibited. If you are not the intended recipient, please contact
the sender by reply email and destroy all copies of the original
message.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Re: CLOBs and the Client driver
Posted by Daniel Noll <da...@nuix.com.au>.
Dag H. Wanvik wrote:
>The JDBC 3.0 JavaDoc says both relative and absolute are illegal for
>FORWARD_ONLY, I am afraid, cf.
>
>
Yeah, I found that too. It does still seem odd that I should be getting
an error about the result set being forward only, when I'm only moving
forward, but maybe that's just a poor error message on the part of
either JDBC or Derby.
Stepping through with next() is a bit unreasonable though. Is it fast?
I guess as long as it doesn't result in any network traffic due to the
row data itself, it's fine. But if it does transfer the row data when I
won't be using it, then stepping over 10,000 rows would be slow.
Is there really no way to do OFFSET/LIMIT in Derby? That would have
been the traditional way to do paging.
Daniel
--
Daniel Noll
NUIX Pty Ltd
Level 8, 143 York Street, Sydney 2000
Phone: (02) 9283 9010
Fax: (02) 9283 9020
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: CLOBs and the Client driver
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Hi Daniel,
>>>>> "Daniel" == Daniel Noll <da...@nuix.com.au> wrote:
Daniel> However, absolute(101) throws an error, and relative(101) throws an error.
Daniel>
Daniel> Am I really expected to call next() N times to get to the row I want?
Daniel> What if I want row 10,000?
The JDBC 3.0 JavaDoc says both relative and absolute are illegal for
FORWARD_ONLY, I am afraid, cf.
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#relative(int)
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#absolute(int)
See also, the 3.0 specification (section 14.2.2) which states: "For a
ResultSet object that is of type TYPE_FORWARD_ONLY, the only valid
cursor movement method is next. All other cursor movement methods
throw an SQLException."
So, yes, you will have to step through with next().
Dag
Re: CLOBs and the Client driver
Posted by Daniel Noll <da...@nuix.com.au>.
Rajesh Kartha wrote:
> Hi Daniel,
>
> Please note that the DerbyClient does not support
> ResultSet.TYPE_SCROLL_SENSITIVE or ResultSet.TYPE_SCROLL_INSENSITIVE
> if the ResultSet has LOB.
Ah. That would be it, then. Now that I knew what to look for, I found
an issue on JIRA to subscribe myself to. :-)
> 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.
In this case I'm moving, say, forward 100 rows, and then forward one row
at a time until row 200. I'm sure this behaviour comes under the
umbrella of "forward only", as I'm never moving backward.
However, absolute(101) throws an error, and relative(101) throws an error.
Am I really expected to call next() N times to get to the row I want?
What if I want row 10,000?
Or is there some SQL syntax I'm supposed to use to limit the returned
slice to rows 101-200 (I tried OFFSET/LIMIT, and can't see anything
obvious in the Derby SQL reference which would suggest a feature like this.)
Daniel
--
Daniel Noll
NUIX Pty Ltd
Level 8, 143 York Street, Sydney 2000
Phone: (02) 9283 9010
Fax: (02) 9283 9020
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: CLOBs and the Client driver
Posted by Rajesh Kartha <ka...@Source-Zone.Org>.
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
>
Re: CLOBs and the Client driver
Posted by Daniel Noll <da...@nuix.com.au>.
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
--
Daniel Noll
NUIX Pty Ltd
Level 8, 143 York Street, Sydney 2000
Phone: (02) 9283 9010
Fax: (02) 9283 9020
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: CLOBs and the Client driver
Posted by Sunitha Kambhampati <ks...@gmail.com>.
Daniel Noll wrote:
> Rajesh Kartha wrote:
>
>> Hi Daniel,
>>
>> Can you post the way you are retrieving the CLOBs from the
>> ResultSet. I have used the rs.getAsciiStream() and
>> rs.getCharacterStream()
>> methods for getting CLOBs in the past (with the Client driver) and it
>> seemed to work just fine.
>
>
> Hmm, maybe that's it. In this case we knew we needed the whole CLOB
> in advance, so we were reading it with getString(). Perhaps the
> client driver always returns "" for this whereas the embedded driver
> actually returns us the data.
>
>
I'd expect getString() to return the data when you use the client
driver, similar to the embedded driver. If it is possible, can you
post the code that seems to have a problem and open a jira entry for it.
Thanks.
Sunitha.
Re: CLOBs and the Client driver
Posted by Daniel Noll <da...@nuix.com.au>.
Rajesh Kartha wrote:
> Hi Daniel,
>
> Can you post the way you are retrieving the CLOBs from the ResultSet.
> I have used the rs.getAsciiStream() and rs.getCharacterStream()
> methods for getting CLOBs in the past (with the Client driver) and it
> seemed to work just fine.
Hmm, maybe that's it. In this case we knew we needed the whole CLOB in
advance, so we were reading it with getString(). Perhaps the client
driver always returns "" for this whereas the embedded driver actually
returns us the data.
It's a lead, anyway.
Daniel
--
Daniel Noll
NUIX Pty Ltd
Level 8, 143 York Street, Sydney 2000
Phone: (02) 9283 9010
Fax: (02) 9283 9020
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: CLOBs and the Client driver
Posted by Rajesh Kartha <ka...@Source-Zone.Org>.
Hi,
The snippet I sent earlier has a bug while writing to the file, so
please ignore that.
Here is the fixed one.
Example: Getting the AsciiStream from the clob column and writing it to
a file
InputStream is = rs.getAsciiStream(2);
BufferedInputStream bs=new BufferedInputStream(is);
FileOutputStream fo=new FileOutputStream("asciiClob"+i+".txt");
int numread=0;
byte[] buff = new byte[128];
while (true) {
numread = bs.read(buff);
if (numread<0) break;
fo.write(buff,0,numread);
}
fo.close();
bs.close();
Regards,
Rajesh
Rajesh Kartha wrote:
> Daniel Noll wrote:
>
>> Hi.
>>
>> Are there any known problems in Derby 10.1 with regards to retrieving
>> CLOBs? We have a system which can work in both embedded and client
>> mode, and I've discovered that using it in client mode results in an
>> empty string (not null, but "") returned for a CLOB-type column.
>>
>> The same issue doesn't happen on the embedded driver, and I can't
>> find any immediately obvious records in JIRA which describe a similar
>> problem.
>>
>> Daniel
>>
>>
> Hi Daniel,
>
> Can you post the way you are retrieving the CLOBs from the ResultSet.
> I have used the rs.getAsciiStream() and rs.getCharacterStream()
> methods for getting CLOBs in the past (with the Client driver) and it
> seemed to work just fine.
>
> Example: Getting the AsciiStream from the clob column and writing it
> to a file
>
> InputStream is = rs.getAsciiStream(2);
> BufferedInputStream bs=new BufferedInputStream(is);
> FileOutputStream fo=new
> FileOutputStream("asciiClob"+i+".txt");
> byte[] b=new byte[128];
> while ((bs.read(b))>0){
> fo.write(b);
> }
> fo.close();
> bs.close();
>
>
> -Rajesh
>
>
>
>
Re: CLOBs and the Client driver
Posted by Rajesh Kartha <ka...@Source-Zone.Org>.
Daniel Noll wrote:
> Hi.
>
> Are there any known problems in Derby 10.1 with regards to retrieving
> CLOBs? We have a system which can work in both embedded and client
> mode, and I've discovered that using it in client mode results in an
> empty string (not null, but "") returned for a CLOB-type column.
>
> The same issue doesn't happen on the embedded driver, and I can't find
> any immediately obvious records in JIRA which describe a similar problem.
>
> Daniel
>
>
Hi Daniel,
Can you post the way you are retrieving the CLOBs from the ResultSet. I
have used the rs.getAsciiStream() and rs.getCharacterStream()
methods for getting CLOBs in the past (with the Client driver) and it
seemed to work just fine.
Example: Getting the AsciiStream from the clob column and writing it to
a file
InputStream is = rs.getAsciiStream(2);
BufferedInputStream bs=new BufferedInputStream(is);
FileOutputStream fo=new FileOutputStream("asciiClob"+i+".txt");
byte[] b=new byte[128];
while ((bs.read(b))>0){
fo.write(b);
}
fo.close();
bs.close();
-Rajesh