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
