![]() |
Controlling Cloudscape Application Behavior
|
Reference Manual |
The JDBC Connection and Transaction ModelSession 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: ConnectionsA 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. StatementsTo 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 CursorsExecuting 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 ConnectionsSQL-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 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. TransactionsA 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 DisabledWhen 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-CommitA 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:
Turning Off Auto-CommitYou 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 Explicitly Closing Statements, ResultSets, and ConnectionsYou 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 RollbackWhen 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. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |