You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Marco Ferretti <ma...@gmail.com> on 2010/04/28 18:29:43 UTC
Truncation error on a CLOB
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
Do you have any clue as of how I can work around this problem ?
For as much as I have understood this looks a lot like
http://mail-archives.apache.org/mod_mbox/db-derby-dev/200501.mbox/%3C41F5CBF0.32BF97D3@Remulak.Net%3Ebut
I thought it was patched and fixed in version 10.2.something .
TIA
Marco
Re: Truncation error on a CLOB
Posted by Marco Ferretti <ma...@gmail.com>.
On Wed, Apr 28, 2010 at 9:29 PM, Knut Anders Hatlen <Kn...@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 . :(
Re: Truncation error on a CLOB
Posted by Marco Ferretti <ma...@gmail.com>.
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
Re: Truncation error on a CLOB
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
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