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.32BF97D3@Remulak.Net%3E but I thought it was patched and fixed in version 10.2.something .


TIA

Marco