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 Wed, 28 Apr 2010 20:52:15 GMT

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

Mime
View raw message