JDBC and CLOB retrieval question

[Martin Gregorie]

I'm using JDBC to access a PostgresQL database which contains TEXT
fields. That's the Postgres equivalent of a CLOB. I'm storing strings in
this field with PreparedStatement.setCharacterStream() and getting them
back with ResultSet.getCharacterStream() - this works well, but the code
I use to read the data back is ugly:

private String retrieveText(ResultSet rs, String column)
throws SQLException
{
StringBuffer buff = new StringBuffer();
try
{
Reader tr = rs.getCharacterStream(column);
char[] cbuf = new char[50];
int n;


while ((n = tr.read(cbuf, 0, 50)) != -1)
buff.append(cbuf);

tr.close();
}
catch (IOException e)
{
....
}
return buff.toString();
}

It works, but is there something a bit more elegant that I should be
using instead of reading chunks via the the loop?

I've probably missed something that should be obvious, so any hints
would be welcome.

 

[Lew]

I'm using JDBC to access a PostgresQL [sic] database which contains TEXT
fields. That's the Postgres equivalent of a CLOB. I'm storing strings in
this field with PreparedStatement.setCharacterStream() and getting them
back with ResultSet.getCharacterStream() - this works well, but the code
I use to read the data back is ugly:

private String retrieveText(ResultSet rs, String column)
throws SQLException
{
StringBuffer buff = new StringBuffer();
try
{
Reader tr = rs.getCharacterStream(column);
char[] cbuf = new char[50];
int n;


while ((n = tr.read(cbuf, 0, 50)) != -1)
buff.append(cbuf);

tr.close();
}
catch (IOException e)
{
....
}
return buff.toString();
}

It works, but is there something a bit more elegant that I should be
using instead of reading chunks via the the loop?

I've probably missed something that should be obvious, so any hints
would be welcome.

Try ResultSet.getString( String column ).
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getString(java.lang.String)>
and the setString() equivalent.

TEXT is also Postgres's version of LONG VARCHAR and works like an
unlimited-length VARCHAR.

 

[Peter Fourneau]

I'm using JDBC to access a PostgresQL database which contains TEXT
fields. That's the Postgres equivalent of a CLOB. I'm storing strings in
this field with PreparedStatement.setCharacterStream() and getting them
back with ResultSet.getCharacterStream() - this works well, but the code
I use to read the data back is ugly:

private String retrieveText(ResultSet rs, String column)
throws SQLException
{
StringBuffer buff = new StringBuffer();
try
{
Reader tr = rs.getCharacterStream(column);
char[] cbuf = new char[50];
int n;

while ((n = tr.read(cbuf, 0, 50)) != -1)
buff.append(cbuf);

tr.close();
}
catch (IOException e)
{
....
}
return buff.toString();
}

It works, but is there something a bit more elegant that I should be
using instead of reading chunks via the the loop?

I've probably missed something that should be obvious, so any hints
would be welcome.

if you work with a Clob:

Clob data = rs.getClob(column);
InputStream is;
is = data.getAsciiStream();
StringBuffer sb = new StringBuffer();
try {
int ch;
while ((ch = is.read()) > END_OF_STREAM) {
sb.append((char) ch);
}
is.close();
} catch (IOException e) {
throw new SQLException(e.getMessage());
}
sb.toString()

Regards

Peter

 

[Martin Gregorie]

Try ResultSet.getString( String column ).
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getString(java.lang.String)>

I did that initially by mistake (having used
PrepareStatement.setCharacterStream() to fill the TEXT field but there
was a character code mistranslation between that and getString(). That,
and the ugliness involved in reading via getCharacterStream was what
prompted this query.

and the setString() equivalent.

I'll try using setString() / getString() and see what happens.

TEXT is also Postgres's version of LONG VARCHAR and works like an
unlimited-length VARCHAR.

I thought I'd read the manual fairly carefully and came to the
conclusion that a TEXT was a CLOB in JDBC terms. Thanks for the correction.

 

[Martin Gregorie]

if you work with a Clob:

I tried to avoid using Clob because that chews up memory if you're using
large amounts of data and Clobs could be multi-megabytes on occasion.
I'm handed the data to be stored as a String or StringBuffer, so I'd be
going String -> Clob -> PreparedStatement buffer to store the data (3x
the buffer space of the actual data rather than merely 2x).

In any case I'm couldn't find anything in the Postgres manual to confirm
what a Clob maps to: I only assumed it maps to a TEXT field. Do you know
whether it maps to a BLOB or a TEXT field?

int ch;
while ((ch = is.read()) > END_OF_STREAM) {
sb.append((char) ch);
}
>

In any case, this type of loop is what I'm trying to avoid. I used an
arbitrary 50 char buffer rather than a single char on the assumption
that fewer loop iterations would be cheaper to execute.

Thanks,
Martin

 

[Lew]

I did that initially by mistake (having used
PrepareStatement.setCharacterStream() to fill the TEXT field but there
was a character code mistranslation between that and getString(). That,
and the ugliness involved in reading via getCharacterStream was what
prompted this query.

Do you mean character /encoding/ error?

The problem with the getString() approach was a mismatch in character
encoding. Fix the mismatch. Perhaps it arose from using
setCharacterStream(), eh? How about you use setString() with getString(),
instead of getCharacterStream() with setCharacterStream()?

What you did is like curing your kid's cold by having another kid.

I don't know why you consider the getCharacterStream() ugly. It's perfectly
acceptable. If it works, you should stick with it. Any pre-packaged method
would just be a wrapper for what you did anyway. I'd stick with what you've
got, if it's working.

 

[Lew]

InputStream is;
is = data.getAsciiStream();

I have never understood why people use this idiom.

Why not
InputStream is = data.getAsciiStream();
?

Another bugbear is explicit initialization of instance variables to null.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I tried to avoid using Clob because that chews up memory if you're using
large amounts of data and Clobs could be multi-megabytes on occasion.

The whole point of Clob is that it avoids reading the entire object
into memory.

Arne

 

[Guest]

I'm using JDBC to access a PostgresQL [sic] database which contains
TEXT fields. That's the Postgres equivalent of a CLOB.

Try ResultSet.getString( String column ).
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getString(java.lang.String)>

Not good if it is a GB size object.

TEXT is also Postgres's version of LONG VARCHAR and works like an
unlimited-length VARCHAR.

Are there any difference between a CLOB and an
unlimited-length VARCHAR ?

Arne

 

[Lew]

Are there any difference between a CLOB and an
unlimited-length VARCHAR ?

Yes, up to many, depending on the RDBMS. Some RDBMSes have different rules
for indexing and searching CLOBs than they do for VARCHAR.

For example, in Oracle:

Restrictions on LOB Columns LOB columns are subject to a number of rules and restrictions.
See Oracle Database SecureFiles and Large Objects Developer's Guide for a complete listing. <http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#sthref170>

and
You cannot specify a LOB as a primary key column.

<http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28393/adlob_working.htm#ADLOB2010>
which lists several more difference between LOBs, including CLOBs, and other
data types like VARCHAR2.

Furthermore,

Most SQL character functions are enabled to accept CLOBs as parameters,
and Oracle performs implicit conversions between CLOB and character types.
Therefore, functions that are not yet enabled for CLOBs can accept CLOBs
through implicit conversion. In such cases, Oracle converts the CLOBs to
CHAR or VARCHAR2 before the function is invoked. If the CLOB is larger
than 4000 bytes, then Oracle converts only the first 4000 bytes to CHAR.

<http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements002.htm#i55214>

Also, CLOBs are often implemented differently from VARCHAR columns.

A "true" unlimited VARCHAR would not have such restrictions, nor would it need
conversion functions like TO_CLOB and TO_CHAR to make the types commensurate.

Postgres does not have such differences between its TEXT and VARCHAR types,
and in fact supports declaring an unlimited VARCHAR (one without a length
specifier).

Every RDBMS has its own deviations from the SQL "standard".

 

[Martin Gregorie]

The whole point of Clob is that it avoids reading the entire object
into memory.

Thats the case if you're moving it through a Stream as some functions
do. My problem is that I need to shift it between a TEXT (the nearest
Postgres has to a CLOB and the content field in a JavaMail Message or
Part (a byte array). If I do a quick and dirty conversion via standard
objects it looks like I'd need to go through a String or StringBuffer.

Looks like I'd best extend the MimeMessage and MimePart objects to let
be do byte <-> char at a time conversions.

Luckily I've been away doing other things and thinking over this problem
in idle moments. At least now I have a better idea of which way I need
to go. I'll report back once I've worked on it.