Re: Truncation error on a CLOB

[Marco Ferretti]

On 28/apr/10, at 21:29, Knut Anders Hatlen wrote:

On 04/28/10 06:29 PM, Marco Ferretti wrote:

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

Hi Marco,

If I read the code correctly, it attempts to insert the contents of the

stream into the plain_message column. The type of that column is LONG

VARCHAR, not CLOB. Does it work if you change the type of the column to

CLOB?

--
Knut Anders

Dho !
you're right ... been looking at the code 4 too long 2 see the obvious .
Can't try now ... will let you know in a few hours

--
Marco