0977-0979 | Oracle Development Unleashed (3rd Edition)

Previous Table of Contents Next

Page 977

Only one LONG and LONG RAW type column may be included in the SQL SELECT query for the dynaset. In addition, the dynaset's updatable property needs to be true in order to be able to call GetChunk and AppendChunk methods of the OraField interface.

Transactions

The most commonly used example for describing a database transaction is the case of transferring money between two bank accounts. When one account is debited, the system should guarantee that the account to which the funds will be transferred is credited. Both actions should either succeed or fail as a single unit of work. This single unit of work is called a transaction. Transactions consist of SQL statements or PL/SQL blocks that comprise one consistent change to the data. Transactions are committed or rolled back.

In the examples presented so far in this book, all transactions begin and end implicitly. The Delete method in the dynaset was committed right after the statement executed successfully. The Edit and AddNew operations committed their work immediately after the update was called. The update statements issued using the ExecuteSql and CreateSQL methods also resulted in immediate commits if no error had occurred. This is the default behavior in OO4O.

 BeginTrans, ResetTrans, CommitTrans, andRollback are methods of the OraSession interface used to control transactions in OO4O.

BeginTrans starts a transaction for all connections that belong to that session. These transactions may then be cancelled, committed, or rolled back using the ResetTrans, CommitTrans, and Rollback methods.

Transactions may be committed or rolled back for individual connections using the CommitTrans and Rollback methods of the OraConnection interface.

The OraConnection interface is created implicitly when the OpenDatabase method of the OraSession is called, and can be obtained from either the Connections collection property of the OraSession or the Connection property of the OraDatabase interface.

In Versions prior to 2.2, a database connection is shared among multiple OraDatabases if the connection properties (username, password, and database alias) are the same. This means that a commit or a rollback operation in one connection could potentially affect transactions in progress in multiple OraDatabases.

Version 2.2 establishes a connection for every database interface created when the OpenDatabase method is called.

Issuing DDL (such as CREATE or DROP table) statements always force an immediate commit for the transaction associated with a connection.

Page 978

For example, all dynaset updates that are inside a transaction are automatically committed if a DDL statement is executed before CommitTrans or Rollback methods are called.

The following is an example that uses the BeginTrans and CommitTrans methods of OraSession for transaction control:

 aSession.BeginTrans Employees = empDatabase.CreateDynaset("Select * from emp where ename = `CLARK'", &H0&) Employees.Edit Employees.Fields("sal").Value = 5000 `Set CLARK's salary to 00 Empoyees.Update ` Flush this change to the database aSession.CommitTrans

The following example commits a transaction using the OraConnection interface:

 aSession.BeginTrans set connection = empDatabase.Connection Employees = empDatabase.CreateDynaset("Select * from emp where ename = `CLARK'", &H0&) Employees.Edit Employees.Fields("sal").Value = 5000 `Set CLARK's salary to 00 Empoyees.Update ` Flush this change to the database connection.CommitTrans

Error Handling

All methods that return errors in OO4O raise the OLE Automation Error (error number 440). To get the specific number and text of the error, the LastServerErr and LastServerErrText methods of OraSession and OraDatabase are used.

The number and text of the last error related to connections in a session are obtained using the LastServerErr and LastServerErrText methods of the OraSession.

All errors related to executing SQL statements and PL/SQL blocks are retrieved using the LastServerErr and LastServerErrText methods of the OraDatabase method.

The LastServerErrReset method of OraSession and OraDatabase clears the last error number and text:

 Set aSession = CreateObject("OracleInProcServer.XOraSession")  `Set up an error handler.  On Error GoTo errhandler  `Create the OraDatabase Object by opening a connection to Oracle.  Set exampleDB = OraSession.OpenDatabase("ExampleDb", "scott/tiger", &H0&)  `Try to create a Dynaset with an erroneous SQL SELECT statement  `Issue an invalid query.  Set OraDynaset = OraDatabase.CreateDynaset("select names from emp", &H0&) Exit Sub errhandler:  `Check to see if an Oracle error has occurred.  If exampleDB.LastServerErr <> 0 Then   MsgBox exampleDB.LastServerErrText End If

Page 979

Performance Tuning

This section discusses a few coding techniques that can result in faster execution of scripts or programs that utilize the automation objects in OO4O.

These techniques are presented in the following contexts:

Minimizing the OLE COM overhead
Reducing the network I/O between the client application and the database server
Reusing database connections

Minimizing the OLE COM Overhead

All the examples presented so far in this chapter have declared the instance variables for the automation objects in OO4O as type Object in VB (such as Dim empDatabase as Object). Object variables in VB are 32-bit (4-byte) addresses that refer to automation objects. A variable declared as an Object can have any type of object reference assigned to it.

Binding to the object referenced by that variable is always at runtime (late or runtime binding). Late binding imposes an extra overhead that becomes significant when a large number of object references exist in the application.

Use early binding if possible in VB or other environments that support it. This speeds up access to methods and properties of automation objects in OO4O.

To use early binding (compile-time binding), the type of the instance variable referencing an OO4O automation object should be the specific type of the OO4O it references. For example:

 Dim empDatabase as OraDatabase Dim Employees as OraDynaset ...

Consult your development environment documentation on specifics on using the early binding technique.

In addition, try to avoid cases when an object reference is repeatedly obtained. This issue was raised when the OraDynaset interface was discussed earlier and is repeated here to reinforce the issue.

Previous Table of Contents Next