Every so often, the Teradata JDBC Driver Engineering team is asked about Teradata Database Error 5766 “The Locator is invalid because the response spool has been dropped.”
Why does it happen? How can it be avoided?
Some databases offer LOB locators that are valid for the lifetime of the transaction.
Some databases offer LOB locators that are valid for the lifetime of the session.
Some databases offer LOB locators that are permanently valid.
The Teradata Database does not offer any of those options.
Instead, Teradata Database LOB locators are valid for the lifetime of the response spool. The response spool must remain open in order to read data using any of that response spool’s LOB locators.
The most common reason for Teradata Database Error 5766 is that the response spool was closed, and then the application attempted to read LOB data after the response spool was closed.
Another common reason for Teradata Database Error 5766 is the application obtained a LOB locator from one session and attempted to use the LOB locator with a different session. Teradata Database LOB locators are only valid for the session they are obtained from.
Some background about how JDBC objects correspond to Teradata Database concepts…
A response spool corresponds to a JDBC Statement/PreparedStatement/CallableStatement object and any ResultSet objects obtained from that Statement object.
A LOB locator corresponds to a JDBC Blob/Clob object and any InputStream/Reader objects obtained from that Blob/Clob object.
For an application using the Teradata JDBC Driver, the application must keep open the Statement/PreparedStatement/CallableStatement object, and the ResultSet object, and the Blob/Clob object, while LOB data is read from the InputStream/Reader object. All the associated objects must be kept open.
When an application wants to copy LOB data from one Teradata JDBC connection to another, the application cannot obtain the Blob/Clob object from the source JDBC connection and bind it directly to a PreparedStatement on the destination JDBC connection. Instead, the application must materialize the LOB data on the client system as an intermediate step. To do this, the application must call the Blob.getBinaryStream, Clob.getAsciiStream, or Clob.getCharacterStream method to obtain an InputStream or Reader, and then bind the InputStream or Reader to a PreparedStatement on the destination JDBC connection. All the associated objects from the source JDBC connection must be kept open while the insert or update is occurring on the destination JDBC connection.