This white paper clarifies how to properly utilize the SQLDataSet class. The primary reason for this white paper is to address an issue with Oracle databases whereby certain constructs may cause
Oracle to throw an ORA-01000: maximum open cursors exceeded error under highly repetitive circumstances. This is a known issue
when using the Oracle driver and also using the Sun Java SQL
implementation. The MicroDeveloper library does not cast to Oracle explicit types for portability with other databases. In spite of this, there are certain constructs that must be used to reproduce the error. This white paper describes them and how to avoid them.
For clarification, the SQLDataSet explicitly closes the PreparedStatement and ResultSet it uses and nullifies them when a single close() call is
invoked. Furthermore, it also attempts another close when the object is
destroyed in case the caller forgot to use the close() method explicitly. However, the SQLDataSet does not close the database connection for obvious reasons. So before somebody shouts out that this problem is the result of forgetting to close a prepared
statement or result set in the library or in the code, rest assured, that is not the source of the issue, but rather a combination of coding style, the use of the Oracle driver, and the use of native Java SQL implementations. Fortunately, the solution is simple and requires less work than coding for a standard JDBC driver implementation.
If you want to skip the comparison of JDBC implementations and SQLDataSet implementations, click here to go to the examples.
To better understand one of the strengths of using the SQLDataSet class compared to hand coding JDBC calls, lets review a few simple examples of both. The first example is an JDBC implementation followed by the same construct using the SQLDataSet class. These examples only go far enough to demonstrate the difference in a query; however, the SQLDataSet does much more than just simplify the query construct. Refer to the JavaDocs for more information. The latter examples explain the right and wrong way to use the class and how you can produce ORA-01000 errors with improper constructs when using the SQLDataSet class.
Using the JDBC Implementation
// A typical JDBC query operation
...
import com.microdeveloper.db.Connector;
...
// Establish the database connection (or your code that establishes the connection)
Connection conn = new Connector("ORCL").getConnection();
...
// Establish the SQL
String sql = "SELECT emp_name FROM scott.emp WHERE emp_name LIKE ? AND mgr_id = ?";
PreparedStatement ps = null; // create the prepared statement variable
ResultSet rs = null; // create the result set variable
try {
// Create a prepared statement
ps = conn.prepareStatement(sql,
OracleResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// Bind the variables
if (args[0] == null) { // if the argument given was null, bind it properly
ps.setNull(1, Types.VARCHAR);
} else { // otherwise bind it to the given variable type call
ps.setString(1, args[0]);
}
if (args[1] == null) { // if the argument given was null, bind it properly
ps.setNull(2, Types.NUMERIC);
} else { // otherwise bind it to the given variable type call
ps.setInt(2, args[1]);
}
// Execute the query
rs = ps.executeQuery(); // execute the query and obtain the result set
// Iterate over the records
while (rs.next()) {
System.out.println(rs.getString("EMP_NAME"));
... // do something
}
// Get the totals
int rows = rs.last();
System.out.println(rows + " rows found");
} finally { // always clean up and release the resources
// Always make sure the prepared statement and result set are closed
if (ps != null) { // close the prepared statement - this will release the result set
try {
ps.close();
ps = null;
} catch (Exception e) {}
}
if (rs != null) { // this should be closed as a result of closing the prepared statement
try {
rs.close();
rs = null;
} catch (Exception e) {}
if (conn != null) { // close the database connection if done with it
try {
conn.close();
conn = null;
} catch (Exception e) {}
}
}
This is a typical JDBC method for setting up, executing, and retrieving the results of a SQL result set. It's also important to note that in order for this to work, explicit Oracle types must be used. Now, let's do the same thing using the SQLDataSet.
Using the SQLDataSet Implementation
// A typical SQLDataSet query operation
...
import com.microdeveloper.db.Connector;
import com.microdeveloper.data.SQLDataSet;
...
// Establish the database connection (or your code that establishes the connection)
Connection conn = new Connector("ORCL").getConnection();
...
// Establish the SQL
String sql = "SELECT emp_name FROM scott.emp WHERE emp_name LIKE ? AND mgr_id = ?";
SQLDataSet ds = null; // create a new data set variable
try {
ds = new SQLDataSet(conn, sql); // create the data set object
ds.setParameter(1, Types.VARCHAR, args[0]); // this will take care of any null arguments
ds.setParameter(1, Types.NUMERIC, args[1]); // this will take care of any null arguments
ds.executeQuery(); // execute the query
while (ds.next()) { // iterate over the result set
System.out.println(ds.getString("emp_name")); // case is not an issue here
... // do something
}
// Unlike calls to last() this counter does not disturb the pointer positioning
System.out.println(ds.getRowCount()); // output the totals
} finally { // always clean up and release the resources
if (ds != null) {
try {
ds.close(); //this takes care of releasing the statement and result set
ds = null;
} catch (Exception e) {}
}
if (conn != null) { // close the database connection if done with it
try {
conn.close();
conn = null;
} catch (Exception e) {}
}
}
As you can see, the SQLDataSet usage is similar to standard JDBC syntax but conveniently bundles some of the often overlooked code into a tighter, more easily read construct, and it does not use hard coded Oracle types. This is a short example, so we do not show how the SQLDataSet also provides a wealth of meta-data related to the data set as well as the many alternative ways to address the result set. The real advantage of the SQLDataSet is this convenient packaging in one simple setup that takes less code to implement than a typical manual JDBC implementation. This makes the SQLDataSet convenient for many other uses without the typical coding overhead.
So why is there this problem with Oracle? Simply put, if you follow the models above you won't run into it with either the JDBC implementation or the SQLDataSet. However, there are ways to get yourself into trouble so, the following examples outline the wrong and right ways to use the SQLDataSet in a series of examples below.
Examples
This series of examples demonstrate how to use the SQLDataSet for basic query usage. It is not meant as a tutorial but rather an example to highlight the potential way to re-create the dreaded ORA-01000 error. Accordingly, the first example is what NOT to do. The second and third are the
alternative methods of ways to invoke and use the SQLDataSet. The last example demonstrates the portion of code necessary to implement these calls when using the SQLDataSet as a
bean in a JSP page.
One note to keep in mind is that you do not have to create separate beans or objects for
each query. In fact, it's much better to reuse the existing bean or
SQLDataSet object by simply changing the SQL and the parameters and then
firing executeQuery() again. You only need multiple SQLDataSets for nested data
constructs. This is exactly the same strategy as when using prepared statements with multiple executions and using the JDBC drivers directly.
Example #1: The WRONG Way (what NOT to do)
// This construct will leak cursors through the database driver until the object is finalized
...
import com.microdeveloper.db.Connector;
import com.microdeveloper.data.SQLDataSet;
...
// Establish the database connection (or your code that establishes the connection)
Connection conn = new Connector("ORCL").getConnection();
...
// Setup the SQL statement
String sql = "SELECT emp_name FROM scott.emp WHERE emp_name LIKE ? AND mgr_id = ?";
SQLDataSet ds = null; // create a new data set variable
try {
ds = SQLDataSet(conn, sql); // this call sets up everything we need
ds.setParameter(1, Types.VARCHAR, args[0]); // this will take care of any null arguments
ds.setParameter(2, Types.NUMERIC, args[1]); // this will take care of any null arguments
ds.executeQuery(); // execute the query
for (int fi = 0; fi < ds.getRowCount(); fi++) {// Iterate over the length of the results
try {
ds.next(); // get the next record
... // do something
} catch (Exception e) {
System.out.println("Threw exception while iterating!");
e.printStackTrace();
}
}
} finally { // always clean up and release the resources
// close the data set
if (ds != null) {
try {
ds.close(); //this takes care of releasing the statement and result set
ds = null;
} catch (Exception e) {}
}
if (conn != null) { // close the database connection if done with it
try {
conn.close();
conn = null;
} catch (Exception e) {}
}
}
Example #2: The RIGHT Way (the easiest way)
// This construct does the exact same thing but will not leak
// Get the database connection object
...
import com.microdeveloper.db.Connector;
import com.microdeveloper.data.SQLDataSet;
...
// Establish the database connection (or your code that establishes the connection)
Connection conn = new Connector("ORCL").getConnection();
...
// Setup the SQL statement
String sql = "SELECT emp_name FROM scott.emp WHERE emp_name LIKE ? AND mgr_id = ?";
SQLDataSet ds = null; // create a new data set variable
try {
ds = new SQLDataSet(conn, sql); // create the data set object
ds.setParameter(1, Types.VARCHAR, args[0]); // this will take care of any null arguments
ds.setParameter(2, Types.NUMERIC, args[1]); // this will take care of any null arguments
ds.executeQuery(); // execute the query
while (ds.next()){ // iterate over the result set
... // do something
}
} finally { // always clean up and release the resources
if (ds != null) {
try {
ds.close(); //this takes care of releasing the statement and result set
ds = null;
} catch (Exception e) {}
}
if (conn != null) { // close the database connection if done with it
try {
conn.close();
conn = null;
} catch (Exception e) {}
}
}
Example #3: The RIGHT Way (with discreet control over the result set)
// This construct does the exact same thing but will not leak
// This also demonstrates how you can control the result set directly although the
// SQLDataSet class lets you control just about everything from the ResultSet
// without needing a handle to the ResultSet
...
import com.microdeveloper.db.Connector;
import com.microdeveloper.data.SQLDataSet;
...
// Establish the database connection (or your code that establishes the connection)
Connection conn = new Connector("ORCL").getConnection();
...
// Setup the SQL statement
String sql = "SELECT emp_name FROM scott.emp WHERE emp_name LIKE ? AND mgr_id = ?";
SQLDataSet ds = null; // create a new data set variable
ResultSet rs = null; // create the result set variable
try {
ds = new SQLDataSet(conn, sql); // create the data set object
ds.setParameter(1, Types.VARCHAR, args[0]); // this will take care of any null arguments
ds.setParameter(2, Types.NUMERIC, args[1]); // this will take care of any null arguments
rs = ds.executeQuery(); // execute the query but get the ResultSet
while (rs.next()){ // now iterate over the result set
// perform other result manipulation
...
}
} finally { // always clean up and release the resources
// close the data set
if (rs != null) {
try {
rs.close(); // this is safe but redundant
rs = null;
} catch (Exception e) {}
}
// close the data set
if (ds != null) {
try {
ds.close(); // this takes care of releasing the statement and result set
ds = null;
} catch (Exception e) {}
}
if (conn != null) { // close the database connection if done with it
try {
conn.close();
conn = null;
} catch (Exception e) {}
}
}
Example #4: Using SQLDataSet as a Bean in a JSP page
If you choose to use SQLDataSet as a bean implementation, you will need to establish the connection and sql properties manually, then use the constructs from above in the same manner.
...
<jsp:useBean id="ds" class="com.microdeveloper.data.SQLDataSet" scope="page" />
...
<%
// Establish the database connection (or your code that establishes the connection)
Connection conn = new ConnectionPool("ORCL").getConnectionPoolInterface().getConnection;
ds.setConnProperty(conn); // Set the data set Connection property
ds.setSqlProperty(sql); // Set the data set SQL property
ds.setParameter(1, Types.VARCHAR, args[0]); // this will take care of any null arguments
ds.setParameter(2, Types.NUMERIC, args[1]); // this will take care of any null arguments
ds.executeQuery(); // execute the query
while (ds.next()){ // iterate over the result set
%>
...
<p>Employee Name: <%= ds.getString("emp_name") == null
? "Unknown"
: ds.getString("emp_name") %></p>
...
<%
}
ds.close(); // Do not forget to close the SQLDataSet!
ds = null; // Nullify the bean for good measure
%>
Questions? Send an E-mail to support@microdeveloper.com.