[
https://issues.apache.org/jira/browse/DERBY-6728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14126927#comment-14126927
]
Knut Anders Hatlen commented on DERBY-6728:
-------------------------------------------
In Derby the locator belongs to the transaction in which it was created. Since
both statements execute in the same transaction (because they use the same
connection object), an auto-commit that is triggered by one of the statements,
will end the (shared) transaction for both statements and thereby release the
locator. If the inner statement (SELECT MAX...) had executed in a separate
connection, its auto-committing would not have affected the outer statement.
However, when they execute interleaved in the same transaction, there is no way
to commit one of them while leaving the other one uncommitted.
What's the desired behaviour, depends on who you ask and what your desires are,
I guess... :)
The current behaviour was chosen deliberately when the CLOB/BLOB support was
redesigned in Derby 10.3. The chosen approach was what the implementers at that
time regarded as the best compromise between usability, performance and ease of
implementation, while complying with the JDBC specification. I suppose they
concluded that recommending to disable auto-commit when working with CLOB/BLOB,
was an acceptable price to pay in order to avoid the problems around
auto-commit, and that it did not break the requirements of the JDBC
specification. The JDBC specification isn't very strict on every detail of how
CLOBs should be handled, so I'm not surprised there are variations between
different JDBC drivers.
> 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)
