Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII
Date: Wed, 28 Apr 2010 21:29:39 +0200
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
Subject: Re: Truncation error on a CLOB
In-reply-to: <o2pa723af781004280929vc7dd90b0k83a17f971eaa89e3@mail.gmail.com>
Sender: Knut.Hatlen@Sun.COM
To: derby-user@db.apache.org
Message-id: <4BD88CA3.4070800@sun.com>
References: <o2pa723af781004280929vc7dd90b0k83a17f971eaa89e3@mail.gmail.com>
User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; en-US;
 rv:1.9.1.9) Gecko/20100318
 Lightning/1.0b1 Thunderbird/3.0.4

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