Major Description
There is a table include BLOB and CLOB data,column 3 is CLOB and column 4 is BLOB
when i try to get the data from this table,in some cases,SQLException is thrown :The data in this BLOB or CLOB is no longer available. The BLOB/CLOB's transaction may be committed, or its connection is closed
when i get the CLOB and BLOB data without the stream read,it is ok,my test source code as below:
import java.io.InputStream;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class Test {
public static String driverClass = "org.apache.derby.jdbc.EmbeddedDriver";
public static String driverURL = "jdbc:derby:d:
BirtSample";
public static String user = "ClassicModels";
public static String squery = "select * from CLASSICMODELS.PRODUCTLINES,CLASSICMODELS.PRODUCTS where CLASSICMODELS.PRODUCTS.PRODUCTLINE = CLASSICMODELS.PRODUCTLINES.PRODUCTLINE";
private static int i = 0;
public static void main( String[] args )
{
try {
Class.forName(driverClass);
Connection c = DriverManager.getConnection(driverURL, user, "");
PreparedStatement s = c.prepareStatement(squery);
c.setAutoCommit(false);
ResultSet r = s.executeQuery();
ResultSetMetaData meta = r.getMetaData();
int count = meta.getColumnCount();
try{
while(r.next()) {
r.getString(1);
r.getString(2);
Clob clob = r.getClob(3);
// byte[] b = new byte[(int)clob.length()];
// InputStream stream = clob.getAsciiStream();
// stream.read(b);
// stream.close();
// Reader reader = clob.getCharacterStream();
// reader.read(b);
// reader.close();
System.out.println( r.getBlob(4));
System.out.println( "row count=" + i);
i++;
}}catch( SQLException ex)
s.close();
}
catch(Exception e)
{ e.printStackTrace(); }
}
}
but when i read after get the CLOB,source code as below:
import java.io.InputStream;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class Test {
public static String driverClass = "org.apache.derby.jdbc.EmbeddedDriver";
public static String driverURL = "jdbc:derby:d:
BirtSample";
public static String user = "ClassicModels";
public static String squery = "select * from CLASSICMODELS.PRODUCTLINES,CLASSICMODELS.PRODUCTS where CLASSICMODELS.PRODUCTS.PRODUCTLINE = CLASSICMODELS.PRODUCTLINES.PRODUCTLINE";
private static int i = 0;
public static void main( String[] args )
{
try {
Class.forName(driverClass);
Connection c = DriverManager.getConnection(driverURL, user, "");
PreparedStatement s = c.prepareStatement(squery);
c.setAutoCommit(false);
ResultSet r = s.executeQuery();
ResultSetMetaData meta = r.getMetaData();
int count = meta.getColumnCount();
try{
while(r.next()) {
r.getString(1);
r.getString(2);
Clob clob = r.getClob(3);
byte[] b = new byte[(int)clob.length()];
InputStream stream = clob.getAsciiStream();
stream.read(b);
stream.close();
// Reader reader = clob.getCharacterStream();
// reader.read(b);
// reader.close();
System.out.println( r.getBlob(4));
System.out.println( "row count=" + i);
i++;
}}catch( SQLException ex)
{ ex.printStackTrace(); }
s.close();
}
catch(Exception e)
}
}
the SQLException is thrown when get the row 89,error info as below:
...
org.apache.derby.impl.jdbc.EmbedBlob@a9255c
row count=89
java.sql.SQLException: The data in this BLOB or CLOB is no longer available. The BLOB/CLOB's transaction may be committed, or its connection is closed.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.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.EmbedResultSet.getBlob(Unknown Source)
at Test.main(Test.java:42)
Caused by: java.sql.SQLException: The data in this BLOB or CLOB is no longer available. The BLOB/CLOB's transaction may be committed, or its connection is closed.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 8 more
Caused by: ERROR XJ073: The data in this BLOB or CLOB is no longer available. The BLOB/CLOB's transaction may be committed, or its connection is closed.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedBlob.<init>(Unknown Source)
... 2 more
when i try to this test on oracle ,this problem will not happen,so i think maybe this is a bug of Derby
your help will be highly apperaciated