[
https://issues.apache.org/jira/browse/DERBY-6728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14126803#comment-14126803
]
Johannes Lichtenberger commented on DERBY-6728:
-----------------------------------------------
But isn't it strange, that the "SELECT MAX(LID) FROM APP"-statement releases
the locator of the preceding query (where I'm still inside if (rs.next()) //
execute SELECT MAX query and do other stuff)? I think it should release its own
locator from its own result set or are locators "shared"? It probably shouldn't
close the locator from the outer query with the result set "rs"? We have
implemented the Clob-handling for individual database systems differently as
sometimes a simple rs.getString(...) seems to be sufficient, but nontheless it
seems that the outer locator hasn't been closed in other relational DBMSes. So,
I don't know if it's the desired behavior.
> 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)
