[jira] [Commented] (DERBY-6728) Reading from a Clob fails.

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

    [ 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)