Cannot get BLOB value in the result set
---------------------------------------
Key: DERBY-5183
URL: https://issues.apache.org/jira/browse/DERBY-5183
Project: Derby
Issue Type: Bug
Components: Eclipse Plug-in
Reporter: dingyan
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)
{
ex.printStackTrace();
}
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)
{
e.printStackTrace();
}
}
}
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
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
