[
https://issues.apache.org/jira/browse/DERBY-6737?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen resolved DERBY-6737.
---------------------------------------
Resolution: Fixed
Fix Version/s: 10.12.0.0
Issue & fix info: Repro attached (was: Patch Available,Repro attached)
> CLOB retrieve exceptions after moving cursor around
> ---------------------------------------------------
>
> Key: DERBY-6737
> URL: https://issues.apache.org/jira/browse/DERBY-6737
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.8.2.2, 10.11.1.1
> Environment: Linux/x64
> Reporter: Brian Fabec
> Assignee: Knut Anders Hatlen
> Fix For: 10.12.0.0
>
> Attachments: Derby6737.java, d6737-1a.diff
>
>
> The version we are running is a bit older (10.8.2.2), but I have tried latest
> version of Derby @ 10.11.1.1 with it's JDBC drivers that are included with
> it. I am having problems with CLOB after moving the cursor
> forward/backwards. The CLOB(s) themselves are roughly 500000+ characters
> each.
> Sample Code:
> -------------------------------------------------------------------------------------------
>
> import java.sql.Clob;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.Statement;
> public class testZ {
> private static String dbURL =
> "jdbc:derby://9.42.11.34:1527/TestDB;create=true;user=test;password=test";
>
> public static void main(String[] args) {
> try {
>
> Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
> Connection conn = DriverManager.getConnection(dbURL);
> conn.setAutoCommit(false);
> Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
> ResultSet rs = stmt.executeQuery("select * from
> TESTCLOB where EVENTID=30266");
> rs.last();
> System.out.println(rs.getRow());
> rs.first();
>
> Clob clob = rs.getClob("GROUPASC");
> int len = (int) ((java.sql.Clob) clob).length();
> String clobData = ((java.sql.Clob)
> clob).getSubString(1, len);
> System.out.println("Clob Data: " + clobData);
> } catch (Exception e) {
> e.printStackTrace();
> }
> }
> }
> ---------------------------------------------------------------------------------------------
>
> Notice I am moving the cursor around. I wanted to get the size of the result
> set prior to getting the Clob data. So I do a rs.last() and rs.getNum() to
> get the size of the result set. I then move the cursor back to the first row
> and obtain the Clob data for first row. If I remove the rs.first() statement
> and get the Clob data of the rs.last() row, it works fine. It seems as long
> as I go forward (not backwards), the CLOB data is retrievable. Note from the
> code, I am using ResultSet.TYPE_SCROLL_SENSITIVE. I have tried
> TYPE_SCROLL_INSENSITIVE, but with the same problem. I also tried
> enabling/disabling the auto commit, also still have this error. For this
> code, I am getting the following error:
> java.sql.SQLException: You cannot invoke other java.sql.Clob/java.sql.Blob
> methods after calling the free() method or after the Blob/Clob's transaction
> has been committed or rolled back.
> at
> org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.SqlException.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.ClientClob.length(Unknown Source)
> at testZ.main(testZ.java:25)
> Caused by: ERROR XJ215: You cannot invoke other java.sql.Clob/java.sql.Blob
> methods after calling the free() method or after the Blob/Clob's transaction
> has been committed or rolled back.
> at
> org.apache.derby.client.am.CallableLocatorProcedures.handleInvalidLocator(Unknown
> Source)
> at
> org.apache.derby.client.am.CallableLocatorProcedures.clobGetLength(Unknown
> Source)
> at org.apache.derby.client.am.ClientClob.getLocatorLength(Unknown
> Source)
> at org.apache.derby.client.am.Lob.sqlLength(Unknown Source)
> ... 2 more
> Caused by: ERROR 38000: The exception 'java.sql.SQLException: The locator
> that was supplied for this CLOB/BLOB is invalid' was thrown while evaluating
> an expression.
> at org.apache.derby.client.am.ClientStatement.completeExecute(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatementReply.readExecuteCall(Unknown Source)
> at org.apache.derby.client.net.StatementReply.readExecuteCall(Unknown
> Source)
> at org.apache.derby.client.net.NetStatement.readExecuteCall_(Unknown
> Source)
> at org.apache.derby.client.am.ClientStatement.readExecuteCall(Unknown
> Source)
> at
> org.apache.derby.client.am.ClientPreparedStatement.flowExecute(Unknown
> Source)
> at
> org.apache.derby.client.am.ClientPreparedStatement.executeX(Unknown Source)
> ... 5 more
> Caused by: ERROR XJ217: The locator that was supplied for this CLOB/BLOB is
> invalid
> at org.apache.derby.client.am.SqlException.<init>(Unknown Source)
> at org.apache.derby.client.am.SqlException.<init>(Unknown Source)
> ... 13 more
> On a related note, when a result set contains a CLOB, when doing a
> rs.last()/rs.first(), and then calling rs.next(), we get a "Container has
> been closed." SQL exception.
> Sample class:
> ----------------------------------------------------------------------------------------------
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.Statement;
> public class testZ {
> private static String dbURL =
> "jdbc:derby://9.42.11.34:1088/TestDB;create=true;user=test;password=derbypass";
>
>
> public static void main(String[] args) {
> try {
>
> Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
> Connection conn = DriverManager.getConnection(dbURL);
>
> Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
> //GROUPASC is column with CLOBS
> ResultSet rs = stmt.executeQuery("select GROUPASC
> from RE_EVENTGROUPASC where EVENTID=5 OR EVENTID=6");
> //ResultSet rs = stmt.executeQuery("select EVENTID
> from RELATEDEVENTS.RE_EVENTGROUPASC where EVENTID=29419 OR EVENTID=29420");
>
> rs.last();
> rs.first();
>
> if (!rs.next()) {
> //exception here
> }
>
> rs.close();
> stmt.close();
> conn.close();
> } catch (Exception e) {
> e.printStackTrace();
> }
> }
> }
> ----------------------------------------------------------------------------------------------
>
> Note there are two queries (one is commented out). The GROUPASC is the column
> with the CLOB data type. When I call the second one (without the CLOB
> column), it works fine. Only happens with CLOBs!
> java.sql.SQLTransactionRollbackException: Container has been closed.
> at
> org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.SqlException.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.ClientResultSet.next(Unknown Source)
> at testZ.main(testZ.java:23)
> Caused by: ERROR 40XD0: Container has been closed.
> at org.apache.derby.client.am.ClientResultSet.completeSqlca(Unknown
> Source)
> at
> org.apache.derby.client.net.NetResultSetReply.parseFetchError(Unknown Source)
> at
> org.apache.derby.client.net.NetResultSetReply.parseCNTQRYreply(Unknown
> Source)
> at
> org.apache.derby.client.net.NetResultSetReply.readScrollableFetch(Unknown
> Source)
> at
> org.apache.derby.client.net.ResultSetReply.readScrollableFetch(Unknown
> Source)
> at
> org.apache.derby.client.net.NetResultSet.readScrollableFetch_(Unknown Source)
> at org.apache.derby.client.am.ClientResultSet.flowGetRowset(Unknown
> Source)
> at org.apache.derby.client.am.ClientResultSet.getNextRowset(Unknown
> Source)
> at org.apache.derby.client.am.ClientResultSet.nextX(Unknown Source)
> ... 2 more
> See posting on derby user forums:
> http://apache-database.10148.n7.nabble.com/CLOB-data-errors-after-moving-cursor-around-td142101.html
> I tried searching the forums and the existing defects, and couldn't find this
> issue is already being tracked. Thanks!
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
