You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "ASF subversion and git services (JIRA)" <ji...@apache.org> on 2014/09/18 09:36:34 UTC
[jira] [Commented] (DERBY-6737) CLOB retrieve exceptions after
moving cursor around
[ https://issues.apache.org/jira/browse/DERBY-6737?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14138644#comment-14138644 ]
ASF subversion and git services commented on DERBY-6737:
--------------------------------------------------------
Commit 1625904 from [~knutanders] in branch 'code/trunk'
[ https://svn.apache.org/r1625904 ]
DERBY-6737: CLOB retrieve exceptions after moving cursor around
Always fetch the row again from the server when moving the position of
a scrollable result set that contains LOB columns.
Without this fix, if one of the absolute positioning methods is used
(first(), last() or absolute(int)), and the old position is the same
as the new position, the result set will use the values it already has
for the row on that position. Any locators will have been released,
though, so accessing LOBs in the row will fail with 'invalid locator'.
By fetching the row again from the server, we get a fresh and valid
locator for the LOB columns in that row.
> 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
> 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)