Controlling Cloudscape Application Behavior
Page 2 of 7

The JDBC Connection and Transaction Model

Session and transaction capabilities for SQL-J are handled through JDBC methods, not by SQL-J commands.

JDBC defines a system session and transaction model for database access. A session is the duration of one connection to the database and is handled by a JDBC Connection object. This section includes the following topics:

Connections

A Connection object represents a connection with a database. Within the scope of one Connection, you access only a single Cloudscape database. (Database-side JDBC methods can allow you to access more than one database in some circumstances.) A single application, depending on your Cloudscape product's license, may be allowed to have one or more Connections to Cloudscape, either to a single database or to many different databases, provided that all the databases are within the same system (see Cloudscape System).

You get a Connection object to a database with the DriverManager.getConnection method. You use the database connection URL as an argument to the method to specify which database to connect to and other details (see Cloudscape JDBC Database Connection URL).

The following example shows an application establishing three separate connections to two different databases in the current system.

Connection conn = DriverManager.getConnection(
    "jdbc:cloudscape:toursDB");
System.out.println("Connected to database toursDB");
conn.setAutoCommit(false);
Connection conn2 = DriverManager.getConnection(
    "jdbc:cloudscape:newDB;create=true");
System.out.println("Created AND connected to newDB");
conn2.setAutoCommit(false);
Connection conn3 = DriverManager.getConnection(
    "jdbc:cloudscape:newDB");
System.out.println("Got second connection to newDB");
conn3.setAutoCommit(false);

A Connection object has no association with any specific thread; during its lifetime, any number of threads may have access to it, as controlled by the application.

Statements

To execute SQL-J statements against a database, an application uses Statements (java.sql.Statement) and PreparedStatements (java.sql.PreparedStatement). Since PreparedStatement extends Statement, this section refers to both as Statements. Statements are obtained from and are tied to a particular Connection.

ResultSets and Cursors

Executing a Statement that returns values gives a ResultSet (java.sql.ResultSet), allowing the application to obtain the results of the statement. Only one ResultSet can be open for a particular Statement at any time, as per the JDBC specification.

Thus, executing a Statement automatically closes any open ResultSet generated by an earlier execution of that Statement.

For this reason, you must use a different Statement to update a cursor (a named ResultSet) from the one used to generate the cursor.

The names of open cursors must be unique within a Connection. For more information about how to use cursors and ResultSets, see SQL-J and JDBC ResultSet/Cursor Mechanisms.

Nested Connections

SQL-J statements can include method invocations. If these methods interact with the database, they must use a Connection. When these methods use the same Connection as the calling statement, the Connection is known as a nested connection. For example:

-- findCity must not issue a commit or rollback
VALUES findCity(getCurrentConnection(), 5)

Methods using nested connections cannot commit or roll back a transaction, and thus Cloudscape silently turns off auto-commit during database-side method execution and turns it back on when the execution completes. (See Nested Connections.)

For more information, see Invoking Database-Side JDBC Methods.

Transactions

A transaction is a set of one or more SQL statements that make up a logical unit of work that you can either commit or roll back and that will be recovered in the event of a system failure. All the statements in the transaction are atomic. A transaction is associated with a single Connection object (and database). A transaction cannot span Connections (or databases).

Cloudscape permits schema and data manipulation statements (DDL) to be intermixed within a single transaction. If you create a table in one transaction, you can also insert into it in that same transaction. A schema manipulation statement is not automatically committed when it is performed, but participates in the transaction within which it is issued. Because DDL requires exclusive locks on system tables, keep transactions that involve DDL short.

Transactions When Auto-Commit Is Disabled

When auto-commit is disabled (see Using Auto-Commit), you use a Connection object's commit and rollback methods to commit or roll back a transaction. The commit method makes permanent the changes resulting from the transaction and releases locks. The rollback method undoes all the changes resulting from the transaction and releases locks. A transaction encompasses all the SQL-J statements executed against a single Connection object since the last commit or rollback.

You do not need to explicitly begin a transaction. You implicitly end one transaction and begin a new one after disabling auto-commit, changing the isolation level, or after calling commit or rollback.

Committing or rolling back a transaction closes all open ResultSet objects and currently executing Statements in that Connection. It also releases any database locks currently held by the Connection, whether or not these objects were created in different threads.

Using Auto-Commit

A new connection to a Cloudscape database is in auto-commit mode by default, as specified by the JDBC standard. Auto-commit mode means that when a statement is completed, the method commit is called on that statement automatically. Auto-commit in effect makes every SQL-J statement a transaction. The commit occurs when the statement completes or the next statement is executed, whichever comes first. In the case of a statement returning a ResultSet, the statement completes when the last row of the ResultSet has been retrieved or the ResultSet has been closed explicitly.

Some applications may prefer to work with Cloudscape in auto-commit mode; some may prefer to work with auto-commit turned off. You should be aware of the implications of using either model.

The most important reason for turning off auto-commit is that you may want to keep multiple ResultSets open, which you cannot do easily if auto-commit is turned on. When you commit one Statement, it closes open ResultSets that belong to other Statements in the connection. Working in auto-commit mode may not give you enough control over keeping multiple ResultSets open. For a detailed example, see ResultSets and Auto-Commit.

NOTE: This behavior is mandated by the JDBC standard.

There are other things to be aware of when using auto-commit. They are:

  • Cursors

    Auto-commit automatically closes cursors if you do any in-place updates or deletes (that is, an update or delete statement with a "WHERE CURRENT OF" clause). (For more information about cursors, see SQL-J and JDBC ResultSet/Cursor Mechanisms.) This means you cannot work with updatable cursors in auto-commit mode.

  • Database-side JDBC Methods (methods using nested connections)

    You cannot execute methods within SQL-J statements if those methods perform a commit or rollback on the current connection. Since in the auto-commit mode all SQL-J statements are implicitly committed, Cloudscape silently turns off auto-commit during execution of database-side methods and turns it back on when the method completes.

    Methods that use nested connections are not permitted to turn auto-commit on or off or to commit or roll back.

  • Table-level locking and the SERIALIZABLE isolation level

    When an application uses table-level locking and the SERIALIZABLE isolation level, all statements that access tables hold at least shared table locks. Shared locks prevent other transactions that update data from accessing the table. A transaction holds a lock on a table until the transaction commits. So even a SELECT statement holds a shared lock on a table until its connection commits and a new transaction begins.

Table 6-1 Summary of Application Behavior with Auto-Commit On or Off

Topic

Auto-Commit On

Auto-Commit Off

Transactions

Each statement is a separate transaction.

Commit() or rollback() begins a transaction.

Database-side JDBC methods (methods using nested connections)

Auto-commit is silently turned off.

Works (no explicit commits or rollbacks are allowed).

Updatable cursors

Does not work.

Works.

Multiple connections accessing the same data

Works.

Works. Lower concurrency when applications use SERIALIZABLE isolation mode and table-level locking.

Turning Off Auto-Commit

You can disable auto-commit with a URL attribute (see Turning Off Auto-Commit) or with the Connection class's setAutoCommit method.

-- using the URL attribute
Connection conn = DriverManager.getConnection(
    "jdbc:cloudscape:toursDB;autocommit=false");

-- using the JDBC setAutoCommit method
conn.setAutoCommit(false);

Explicitly Closing Statements, ResultSets, and Connections

You should explicitly close Statements, ResultSets, and Connections when you no longer need them. Connections to Cloudscape are resources external to an application, and the garbage collector will not close them automatically.

For example, close a Statement object using its close method; close a Connection object using its close method.

Statement vs. Transaction Runtime Rollback

When an SQL-J statement generates an exception, this exception results in a runtime rollback. A runtime rollback is a system-generated rollback of a statement or transaction by Cloudscape, as opposed to an explicit rollback call from your application.

Extremely severe exceptions, such as disk-full errors, shut down the system, and the transaction is rolled back when the database is next booted. Severe exceptions, such as deadlock, cause transaction rollback; Cloudscape rolls back all changes since the beginning of the transaction and implicitly begins a new transaction. Less severe exceptions, such as syntax errors, result in statement rollback; Cloudscape rolls back only changes made by the statement that caused the error. The application developer can insert code to explicitly roll back the entire transaction if desired.

The severity level of an SQLException, which is documented in COM.cloudscape.types.JBMSExceptionSeverity, is provided by the getErrorCode call. See Working with Cloudscape SQLExceptions in an Application for more information.