[
https://issues.apache.org/jira/browse/DERBY-6728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14125335#comment-14125335
]
Knut Anders Hatlen commented on DERBY-6728:
-------------------------------------------
The connection used in the example seems to be in auto-commit mode. Since a
java.sql.Clob object is only valid for the duration of the transaction, it is
recommended to disable auto-commit (by calling {{conn.setAutoCommit(false)}})
while working with CLOBs. (The recommendation is given in
[this|http://db.apache.org/derby/docs/10.11/ref/rrefjdbc96386.html] section of
the reference manual.)
I think what's happening in the example, is the following:
# The call to {{rs.next()}} fetches the CLOB's locator from the server.
# The execution of {{SELECT MAX(LID) FROM APP}} makes the connection
auto-commit and release the locator.
# The application tries to access the CLOB, but it fails because the locator
has been released.
I think this is the intended behaviour and not a bug.
> Reading from a Clob fails.
> --------------------------
>
> Key: DERBY-6728
> URL: https://issues.apache.org/jira/browse/DERBY-6728
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.11.1.1
> Reporter: Johannes Lichtenberger
>
> In the Java-Code below the reading from a CLOB-column fails with an
> SQLException. It somehow seems to be related to reading the maximum integer
> primary key value beforehand.
> import java.sql.Clob;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> public class DerbyClobTest
> {
> public static void main(String[] args)
> {
> try
> {
> Connection conn = DriverManager
>
> .getConnection("jdbc:derby://localhost:1527/database;user=derby;password=derby");
> Statement stmt = conn.createStatement();
> ResultSet rs = stmt.executeQuery("SELECT * FROM APP");
> while (rs.next())
> {
> PreparedStatement l_stmt = null;
> int l_iMaxLid = -1;
> try
> {
> l_stmt = conn.prepareStatement("SELECT
> MAX(LID) FROM APP");
> ResultSet l_rs = l_stmt.executeQuery();
> try
> {
> if (l_rs.next())
> l_iMaxLid =
> l_rs.getInt(1);
> }
> finally
> {
> l_rs.close();
> l_rs = null;
> }
> }
> finally
> {
> l_stmt.close();
> l_stmt = null;
> }
> PreparedStatement stmtNew = conn
> .prepareStatement("INSERT INTO
> APP(LID, TXTBODY) VALUES(?, ?)");
> stmtNew.setInt(1, l_iMaxLid);
> final Clob clob = rs.getClob("TXTBODY");
> if (clob == null)
> return;
> final String str = clob.getSubString(1, (int)
> clob.length());
> // Do something with str.
> stmtNew.setClob(2, clob);
> stmtNew.executeUpdate();
> stmtNew.close();
> }
> }
> catch (Exception except)
> {
> except.printStackTrace();
> }
> }
> }
> Stacktrace:
> java.sql.SQLException: Es können keine weiteren
> java.sql.Clob/java.sql.Blob-Methoden aufgerufen werden, nachdem die
> free()-Methode aufgerufen oder nachdem die Blob/Clob-Transaktion ausgeführt
> oder wiederholt (Rollback) wurde.
> at
> org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.SqlException.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.Clob.length(Unknown Source)
> at
> de.uplanet.lucy.server.calendar.DerbyClobTest.main(DerbyClobTest.java:73)
> Caused by: org.apache.derby.client.am.SqlException: Es können keine weiteren
> java.sql.Clob/java.sql.Blob-Methoden aufgerufen werden, nachdem die
> free()-Methode aufgerufen oder nachdem die Blob/Clob-Transaktion ausgeführt
> oder wiederholt (Rollback) wurde.
> 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.Clob.getLocatorLength(Unknown Source)
> at org.apache.derby.client.am.Lob.sqlLength(Unknown Source)
> ... 2 more
> Caused by: org.apache.derby.client.am.SqlException: Bei der Auswertung eines
> Ausdrucks wurde die Ausnahme 'java.sql.SQLException: Der für dieses CLOB/BLOB
> angegebene Locator ist ungültig' ausgelöst.
> at org.apache.derby.client.am.Statement.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.Statement.readExecuteCall(Unknown Source)
> at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown
> Source)
> at org.apache.derby.client.am.PreparedStatement.executeX(Unknown Source)
> ... 5 more
> Caused by: org.apache.derby.client.am.SqlException: Der für dieses CLOB/BLOB
> angegebene Locator ist ungültig
> ... 13 more
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
