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 "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2014/09/15 17:24:34 UTC
[jira] [Updated] (DERBY-6737) CLOB retreieve exceptions after
moving cursor around
[ https://issues.apache.org/jira/browse/DERBY-6737?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-6737:
--------------------------------------
Attachment: Derby6737.java
Attached is a standalone test case that reproduces the bug.
Some things to notice:
- it only reproduces with the client driver, not with the embedded driver
- it only reproduces if the SELECT statement returns a single row, not if it returns multiple rows
> CLOB retreieve 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
> Attachments: Derby6737.java
>
>
> 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)