db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marco Ferretti <marco.ferre...@gmail.com>
Subject Re: Truncation error on a CLOB
Date Thu, 29 Apr 2010 07:18:06 GMT
On Wed, Apr 28, 2010 at 9:29 PM, Knut Anders Hatlen <Knut.Hatlen@sun.com>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
>
>
Yep ... it was definitely that . :(

Mime
View raw message