Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...

Java Forum / General / October 2007

Tip: Looking for answers? Try searching our database.

JDBC and CLOB retrieval question

Thread view: 
Martin Gregorie - 26 Sep 2007 23:14 GMT
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.

Signature

martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

Lew - 27 Sep 2007 00:11 GMT
> 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
[quoted text clipped - 29 lines]
> 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.lan
g.String
)>
and the setString() equivalent.

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

Signature

Lew

Martin Gregorie - 27 Sep 2007 13:18 GMT
> Try ResultSet.getString( String column ).
> <http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getString(java.lan
g.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.

Signature

martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

Lew - 27 Sep 2007 14:44 GMT
>> Try ResultSet.getString( String column ).
>> <http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getString(java.lan
g.String
)>
[quoted text clipped - 4 lines]
> 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.

Signature

Lew

Arne Vajhøj - 15 Oct 2007 02:33 GMT
>> 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.lan
g.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 - 15 Oct 2007 03:22 GMT
Lew wrote:
>> 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 ?

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.h
tm#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.h
tm#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".

Signature

Lew

Peter Fourneau - 27 Sep 2007 12:03 GMT
> 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
[quoted text clipped - 34 lines]
> gregorie. | Essex, UK
> org       |

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 - 27 Sep 2007 13:34 GMT
> 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

Signature

martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

Arne Vajhøj - 15 Oct 2007 02:30 GMT
>> 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.

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

Arne
Martin Gregorie - 15 Oct 2007 23:48 GMT
>>> if you work with a Clob:
>> I tried to avoid using Clob because that chews up memory if you're
[quoted text clipped - 3 lines]
> 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.

Signature

martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

Lew - 27 Sep 2007 14:47 GMT
> 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.

Signature

Lew



Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.