Truncation error on a CLOB

[Marco Ferretti]

Hi there

I tried to google the problem but did not find a solution .
I am trying to insert data on a CLOB on a derby 10.5.3.0 database

This is the table :
CREATE TABLE plainmessagenew (
  mailID varchar(50) NOT NULL default '',
  msg_part decimal(10,0) NOT NULL default 0,
  attachment CLOB(16M),
  plain_message LONG VARCHAR,
  PRIMARY KEY  (mailID,msg_part)
) ;


and am copying data from another database ( older version of the software I
am working on ) as follows  :

    try{
        st = fromConnection.createStatement();
        rs = st.executeQuery(selectSQL);
        insert = toConnection.prepareStatement("insert into plainmessagenew
(mailID,msg_part,plain_message) values (?,?,?)");
        String temp = null;
        int len = 0;
        while ( rs.next() ) {
        if ( insert == null ) {
            insert = toConnection.prepareStatement(insertSQL);
        }
        try{
                insert.setString(1, rs.getString(1));
                insert.setLong(2, rs.getLong(2));
                temp = rs.getString(3);
                len = temp.length();
                if ( temp != null ){
                    insert.setAsciiStream(3,
DerbyUtils.derbyAsciiStream(temp),len);
                } else {
                    insert.setNull(3, Types.CLOB);
                }

                insert.executeUpdate();
        } catch ( Exception sqlE ) {
            Logger.getLogger("TEST").error("error importing line, probably
attachment too long\n",sqlE);
        }

Where DerbyUtils.derbyAsciiStream simply creates a ByteArrayInputStream from
a string as
    ByteArrayOutputStream b = new ByteArrayOutputStream();
    BufferedWriter w = new BufferedWriter(new OutputStreamWriter(b,
"UTF8"));
    w.write(string);
    w.close();
    return new ByteArrayInputStream(b.toByteArray());


During the test I have run I encounter the following exception

java.sql.SQLException: A truncation error was encountered trying to shrink
LONG VARCHAR '
VQK5vK66dgR+53a64xQVTqVVwQqTzG/9Zwfk3MjX4G6lwLyF/Vxn8TgUlB/&' to length
32700.
    at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
    at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
    at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
Source)
    at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown
Source)
    at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown
Source)
    at
codecs.database.operations.derby.ImportMysql.importPlainMessageNew(ImportMysql.java:517)
...

Every time that there is some significant data into the string that is
supposed to fit into the CLOB . The first time the error occurs when the
lenght of the string is 64000

Do you have any clue as of how I can work around this problem ?

For as much as I have understood this looks a lot like
http://mail-archives.apache.org/mod_mbox/db-derby-dev/200501.mbox/%3c41f5cbf0.32bf9...@remulak.net%3ebut
I thought it was patched and fixed in version 10.2.something .


TIA

Marco