How to Use ResultSet

How to Use ResultSet#

This section describes the types of ResultSets supported by the Altibase JDBC driver and how to use them.

Creating ResultSet#

A ResultSet is created when a query statement is executed on the database, and it corresponds to the ResultSet object of JDBC.

The following methods create the ResultSet object in JDBC.

public Statement createStatement(int aResultSetType, int aResultSetConcurrency) throws SQLException;

public Statement createStatement(int aResultSetType, int aResultSetConcurrency, int aResultSetHoldability) throws SQLException;

public PreparedStatement prepareStatement(String aSql, int aResultSetType, int aResultSetConcurrency) throws SQLException;

public PreparedStatement prepareStatement(String aSql, int aResultSetType, int aResultSetConcurrency, int aResultSetHoldability) throws SQLException;

public CallableStatement prepareCall(String aSql, int aResultSetType, int aResultSetConcurrency) throws SQLException

public CallableStatement prepareCall(String aSql, int aResultSetType, int aResultSetConcurrency, int aResultSetHoldability) throws SQLExc

ResultSet Types#

The ResultSet object of JDBC manages and retains the cursor which points to the current position within the result set. The cursor of a basic ResultSet object is not updateable and only moves forward; however, a scrollable and updateable ResultSet object can be created with the use of options.

The following are ResultSet object types available for user specification.

TYPE_FORWARD_ONLY
Unscrollable; the cursor can be moved only forward. Data of the ResultSet is determined at the point in time at which the cursor opens in the database server.
TYPE_SCROLL_INSENSITIVE
Scrollable; the cursor can be moved forward, backwards, or moved to a specified location. Data of the ResultSet is determined at the point in time at which the cursor opens in the database server. Memory can become scarce, due to caching the ResultSet retrieved from the server on the client.
TYPE_SCROLL_SENSITIVE
Scrollable; the cursor can be moved forward, backwards, or moved to a specified location. The ResultSet is determined at the point in time at which the cursor opens in the database server; however, data within the ResultSet is determined at the point in time at which the client retrieves or updates it.

Concurrency#

This option determines whether or not to allow updates through the ResultSet object. One of the following two constants is available for use:

CONCUR_READ_ONLY
Does not allow updates; the default value.
CONCUR_UPDATABLE
Allows updates with the ResultSet object.

Holdability#

This option determines whether or not to retain the ResultSet object after the transaction has been committed. One of the following two constants are available for use:

CLOSE_CURSORS_AT_COMMIT
The cursor is closed when the transaction is committed.
HOLD_CURSORS_OVER_COMMIT
The cursor is left open, even if the transaction is committed. If the transaction has been committed at least once after the cursor has been opened, the cursor is left open during future commit and rollback operations. If the transaction has not been committed even once since the cursor has been opened, however, the cursor is closed when the transaction is rolled back.

Notes#

Since the JDBC driver caches as many number of rows as the value set for FetchSize for the ResultSet object on the client, data left in the cache can be retrieved by the application, even if the cursor is closed. If you want the application to immediately detect that the cursor has been closed, set FetchSize to 1.

The default value of Holdability for the Altibase JDBC driver is CLOSE_CURSORS_AT_COMMIT, and is different from the default value for the JDBC specification, HOLD_CURSORS_OVER_COMMIT.
Open ResultSet objects must be closed prior to switching the autocommit mode with the setAutoCommit() method in a session where Holdability is HOLD_CURSORS_OVER_COMMIT. The following is a code example which raises an error.

sCon = getConnection();
sStmt = sCon.createStatement();
byte[] br;
byte[] bb = new byte[48];
for(byte i = 0; i < bb.length;i++) bb[i] = i;

sCon.setAutoCommit(false);

sStmt.executeUpdate("insert into Varbinary_Tab values(null)");
sCon.commit();

sPreStmt = sCon.prepareStatement("update Varbinary_Tab set VARBINARY_VAL=?");
sPreStmt.setObject(1, bb, java.sql.Types.VARBINARY);
sPreStmt.executeUpdate();

sRS = sStmt.executeQuery("Select VARBINARY_VAL from Varbinary_Tab");
sRS.next();
br = sRS.getBytes(1);

sCon.commit();
sCon.setAutoCommit(true); -> (1)

The following exception is raised at (1).

java.sql.SQLException: Several statements still open
    at Altibase.jdbc.driver.ex.Error.processServerError(Error.java:320)
    at Altibase.jdbc.driver.AltibaseConnection.setAutoCommit(AltibaseConnection.java:988)
    at HodabilityTest.testHoldability(HodabilityTest.java:46)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:616)

sRs.close() must be called before sCon.setAutoCommit(true) to not raise an exception.

The client session must be in Non-Autocommit mode or the clientside_auto_commit connection attribute must be set to on to use a ResultSet object whose Holdability type is HOLD_CURSORS_OVER_COMMIT. If the clientside_auto_commit connection attribute is set to on, the Holdability type is automatically changed to HOLD_CURSORS_OVER_COMMIT.

Example#

Statement sUpdStmt = sConn.prepareStatement("UPDATE t1 SET val = ? WHERE id = ?");
Statement sSelStmt = sConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
ResultSet sRS = sSelStmt.executeQuery("SELECT * FROM t1");
while (sRS.next())
{
    // TODO : set parameters

    sUpdStmt.execute();
    sConn.commit();
}
sRS.close();

Restrictions#

To use an Updatable ResultSet or a Scrollable ResultSet, a SELECT query statement which retrieves the ResultSet is restricted in the following ways:

To use an Updatable ResultSet,

Only one table can be specified in the FROM clause.
Only pure columns can be specified in the SELECT list; expressions or functions cannot be included. Columns with a NOT NULL constraint, and without a default value must be included in the SELECT list.

To use a Scrollable-Sensitive ResultSet,

Only one table can be specified in the FROM clause.

When executing PSM, only ResultSet objects of the default type are available for use. If the user specifies an option which is not of the default type, the option is ignored.

Since for a ResultSet object which is CONCUR_UPDATABLE and TYPE_SCROLL_SENSITIVE, one more Statement is used within the JDBC driver, it can easily exceed the limited number of Statements; therefore, the maximum number of Statements must be set for occasions on which such ResultSet types are used a lot.

Since an updateable and scrollable ResultSet contains a large amount of data, its memory usage is higher than a forward only ResultSet. A large ResultSet can cause memory to become scarce, so its use is not recommended.

The characteristics of the ResultSet are determined by the ResultSet type, concurrency type and holdability type described above. The user can specify random combinations for these three values; however, depending on the query statement that generates the ResultSet, the user-defined combination can be invalid. In this case, the driver does not raise an exception, but converts it to a valid combination. In the following example, the invalid types on the left side are automatically converted to the valid types on the right side.

TYPE_SCROLL_SENSITIVE → TYPE_SCROLL_INSENSITIVE
CONCUR_UPDATABLE → CONCUR_READ_ONLY
HOLD_CURSORS_OVER_COMMIT → CLOSE_CURSORS_AT_COMMIT

When a conversion is made internally, whether or not a conversion has occurred can be confirmed through warnings.

If the type of ResultSet object is TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE, the result of the result set is limited to 349,502 due to the increase of memory usage. If this value is exceeded, a dynamic array cursor overflow error may occur.

Detecting Holes#

A ResultSet object of the TYPE_SCROLL_SENSITIVE type retrieves the newest data from the server when performing a FETCH. Therefore, a row which was visible when the cursor opened can become invisible as the row is scrolled. For example, if a row in a ResultSet object is deleted by another Statement, the row is no longer visible in the ResultSet object. Such an invisible row is called a Hole.

The following is a code example which detects Holes in JDBC.

while (sRS.next())
{
    if (sRS.rowDeleted())
    {
        // HOLE DETECTED!!!
    }
    else
    {
        // do something ...
    }
}

Valid data cannot be obtained from a Hole, and a ResultSet returns one of the following values for a Hole

A SQL data type NULL
A reference type NULL
The value 0.

Fetch Size#

When retrieving data for the ResultSet object from the server, the Altibase JDBC driver retrieves multiple rows at once, instead of retrieving one row each time, and caches them in the client to enhance performance. This is called a prefetch, and the number of rows to be fetched can be set with the setFetchSize() method of the Statement object.

public void setFetchSize(int aRows) throws SQLException;

A value between the range of 0 to 2147483647 can be set for the Altibase JDBC driver. The JDBC specification defines that an exception must be raised when a value outside of this range is specified; however, the Altibase JDBC driver does not raise an exception and ignores it, for the sake of convenience.

If the value is set to 0, the Altibase server voluntarily determines the size to return to the client in one go. In this case, the number of rows to be returned differ, according to the size of a row.

The FetchSize value is especially important for the Scroll-Sensitive ResultSet. When the user retrieves data from a Scroll-Sensitive ResultSet, the driver returns the prefetched rows first. Even if data of the database has been updated, as long as the row exists in the prefetched cache, data of the cache is returned to the user. If the user wants to see the newest data of the database, FetchSize should be set to 1. By doing so, however, the frequency of retrieving data from the server increases and performance can be lowered.

Refreshing Rows#

With the refreshRow() method of the ResultSet object, data which has been previously retrieved from the server can be re-fetched, without executing the SELECT statement. The refreshRow() method retrieves as many number of rows as the value set for FetchSize, based on the current row. To use this method, a cursor must be pointing to any row in the ResultSet.

This method operates when the ResultSet object is of the following types:

TYPE_SCROLL_SENSITIVE & CONCUR_UPDATABLE
TYPE_SCROLL_SENSITIVE & CONCUR_READ_ONLY

If this method is called for a TYPE_FORWARD_ONLY type, an exception is raised; for a TYPE_SCROLL_INSENSITIVE type, nothing happens.

Administrator

Reference

Developer

Tools

External Tools

How to Use ResultSet