db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Truncation error on a CLOB
Date Wed, 28 Apr 2010 19:29:39 GMT
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


Mime
View raw message