[
https://issues.apache.org/jira/browse/DERBY-6728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14125502#comment-14125502
]
Knut Anders Hatlen commented on DERBY-6728:
-------------------------------------------
The SELECT statement makes the connection auto-commit because the
[specification of
Connection.setAutoCommit()|http://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#setAutoCommit-boolean-]
says:
"The commit occurs when the statement completes. (...) For Select statements,
the statement is complete when the associated result set is closed."
For more information, the discussions about the changes that added the
restriction that Clob objects wouldn't survive a commit, happened as part of
DERBY-208 and its sub-tasks.
> 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)
