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?

The Teradata JDBC Driver Engineering team receives a lot of questions about what happens when a JDBC connection is created. Let's clarify the concepts Laddered Concurrent Connect (LCC) versus COP Discovery versus logon, and review what the Teradata JDBC Driver does to create a JDBC connection.

First, some definitions...

"COP" stands for Communications Processor, and is a term originating from Teradata's earliest database appliances in the 1980s. In modern usage, a "COP" is a Teradata Database node that is running a Teradata Database Gateway process.

Oracle (Sun) JDK 6.0 and JDK 7.0 include the Apache Derby database, which is implemented in 100% Java. 

This article is not about the Apache Derby database; instead, this is about a command-line interactive SQL tool that is included with Apache Derby. 

The Apache Derby "ij" interactive SQL tool is quite useful. It is a command-line SQL tool similar to BTEQ, and it works with any JDBC Driver and any database, not just the Apache Derby database. 

The following instructions show how to use the ij tool with the Teradata JDBC Driver. 

"Pagination" refers to the technique commonly used by web applications to display a section of a larger query result, and let the user jump backwards and forwards to particular sections. Google, for example, uses this technique to display search results.

This article assumes that your queries routinely return more row data than can fit into a single 1 MB response message sent from the Teradata Database to the Teradata JDBC Driver.

Question

We can establish a JDBC Connection with URL that specifies a database name, e.g.

jdbc:teradata://<some host>/DATABASE=<some database>

After the Connection is opened, is there any way to determine what database is in use? And is there any way to switch to a different database?

Answer

A "parameterized" SQL request is when you specify a question-mark parameter marker in place of an expression within the SQL request text. Parameter markers are recommended over SQL literals for a couple of reasons.

1. Because the SQL request text stays the same as the SQL request is executed multiple times, the Teradata Database statement cache hit ratio can be higher, resulting in better performance.
2. They help protect against SQL injection attacks.

The Teradata JDBC Driver provides both forward-only and scrollable result sets. With a forward-only result set, an application can only fetch rows in the forward direction, using the ResultSet.next method. A scrollable result set enables an application to fetch rows in any order.

Since a scrollable result set offers significantly more functionality than a forward-only result set, application developers may wonder whether there is a performance penalty for using a scrollable result set.