On Wed, Apr 28, 2010 at 9:29 PM, Knut Anders Hatlen <[email protected]>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 . :(
