Oracle Free Tutorial

Web based School

Previous Page Main Page Next Page


47

Oracle OLE Automation Server

The Oracle OLE Automation Server implements a number of objects used to represent the Oracle7 database and objects within it. The objects are high level and do not require the user to understand SQL processing steps such as parse, bind, define, execute, and fetch. The user must be able to provide database connection information and a SQL statement, then he or she can edit data using familiar methods such as Add(), Edit(), and Delete().

Background

The objects of the Oracle OLE Automation Server are based on the Data Access Objects (DAO) of the Microsoft JET SQL engine. The concepts of DAO are good, but the implementation does not provide very smooth integration with Oracle and Oracle-specific features. The Oracle OLE Automation Server and its objects were designed for an Oracle database and provide tighter integration than DAO.

What Is an OLE Automation Server?

An OLE Automation Server is an OLE object (application) with exposed methods and properties you use to manipulate the object. After the methods and properties become available, you can manipulate the objects within the OLE Server from any OLE Automation client.

What Is an OLE Automation Object?

An OLE Automation object is an instance of a class of your application for which you have exposed methods and properties, which you use to manipulate the object.

A useful feature of OLE Automation objects is a reference count. A reference count is kept so an OLE Automation Server can destroy an object when it is no longer used. For this reason, the Oracle OLE Automation Server has no Close() methods. When an object goes out of scope and is no longer used, the OLE Automation Server automatically destroys it.

What Is an OLE Automation Collection?

You use a collection object to manage other objects. A collection object can contain zero or more objects. By specification, a collection should support iterating through its objects and a count property to determine the number of objects in it. A collection also should support some form of indexing so you can access individual objects in the collection. You can index the various collections in the Oracle OLE Automation Server by name or number (0 to Count - 1).

Object Hierarchy

The relationship between all the Oracle objects is hierarchical and rather straightforward, as shown in figure 47.1.


Figure 47.1. Object Hierarchy of OLE objects.

Only one OraClient object exists for all applications in the system. OraClient is created implicitly when the first OraSession is created. The OraClient object exists only as a root for all the other objects.

One default OraSession exists per OraClient, but you can create other named OraSession objects. Database transactions are normally affected at the OraSession level.

An OraConnection represents an actual connection to Oracle and may be shared by multiple OraDatabase objects. An OraConnection cannot be explicitly created, only implicitly, by creating an OraDatabase object. Database transactions can be committed at the OraConnection level, but it is not recommended.

An OraDatabase object is created using a username, password, and database name. The OraDatabase object is used to control the connection (which can be shared among OraDatabase objects), to create dynasets, and to execute any other SQL statements.

An OraDynaset represents the return set from a SQL select statement and contains one or more OraField objects.

An OraField object represents one column of the return set of a SQL select statement.

An OraParameter represents a SQL or PL/SQL bind variable and is valid for all dynasets under its parent OraDatabase.

Types of Oracle Objects

The product documentation is well-written as a reference guide, but it lacks discussion of the relationship that the following objects have with each other and with Oracle7.

OraClient

The OraClient object is more important to the OLE Server than to the developer. This object is the root of all objects on a given client system and is created implicitly by the OLE Server when the first OraSession in any Oracle Objects for OLE (OO4O) application is created.

The OraClient object is accessible only from an OraSession object. The OraClient contains a collection of all the OraSessions of a particular application, but it does not enable one application to use for accessing the objects of another.

The OraClient has only one method, CreateSession(), which has been duplicated as CreateNamedSession() and added as a helper method to the OraSession object.

OraSession

The OraSession object has much more importance to the developer than the OraClient. This object is the one that the developer most likely sees as the root of all objects in an application. An OraSession must be created first, and the initial one is the only object created directly by an OLE interface; the rest are created by methods of other objects. An OraSession is created with a call to CreateObject(), as this example shows: 

Dim OraSession as Object

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

The OraSession has three important roles. The first is to be a point of Oracle error handling; the second is be a point to begin, commit, or roll back database transactions; and the third is to enable creation of an OraDatabase. The OraSession also owns the OraConnections collection and the OraDatabase objects created from it.

The OraSession receives only errors relating to Oracle connections, the OraConnections collection, or OraDatabase creation.

Because an OraSession may be managing many connections to Oracle via the OraConnections collection, transactions are actually only guaranteed at the Oracle connection (not OraConnection) level.

The original product documentation stated that one application could share an Oracle connection (login) of another application by using ConnectSession()and passing in the name of the desired OraSession. This feature would be very powerful, because making a connection to Oracle is more expensive than using an existing one. Unfortunately, this feature did not work and was removed from the product. In the current product, ConnectSession() does not recognize OraSessions of other applications at all.

OraConnection

An OraConnection object is created implicitly when an OraDatabase is created (OraDatabase has one-to-one mapping). An OraConnection may represent an actual Oracle connection (login) or a shared one. Try to share Oracle connections when possible, because the server requires more overhead to manage connections than it does to manage extra cursors created within a connection.

A shared OraConnection is created when the username, password, and database name of an existing OraDatabase match those of a new OraDatabase being created. Also, the existing OraDatabase must have been created under the same OraSession. Connection sharing is not possible across different OraSessions.

The most important item to remember is that Oracle transactions are accomplished at the Oracle connection level, while OO4O transactions are done at the OraSession and then the OraConnection level. Therefore, committing a transaction that is making use of a shared OraConnection will commit all other outstanding transactions within the actual Oracle connection. Unfortunately, OpenDatabase() doesn't have an option to not attempt to share connections. The workaround is to create a different OraSession (using OraClient.CreateSession() or OraSession.CreateNamedSession(), which are exactly the same) and then create an OraDatabase using that new OraSession.

No method or property exists to tell whether an OraConnection is sharing an Oracle connection, but there is a way to tell. Oracle connections are shared based on username, password, and database name. The username and database name are available as properties of an OraDatabase and of an OraConnection. Comparing these two properties is enough because a database cannot have more than one user with the same user name.

OraDatabase

An OraDatabase initiates connections (when an OraConnection is created) and security to Oracle7. The OraDatabase also handles another layer of error reporting, the execution of SQL and PL/SQL statements, OraDynaset creation, and the behavior of database-wide settings.

The OraDatabase receives errors relating to the execution of SQL and PL/SQL statements using CreateDynaset() and ExecuteSQL(). These errors will also include any encountered while processing any SQL statement executed on behalf of the user to support adding, editing, or deleting of rows or the manipulating of binary data (LONG or LONG RAW columns) of an OraDynaset.

Although many OraDatabase objects may be created within an OraSession, these objects are not kept as part of a formal collection available to the developer.

The OpenDatabase() method of the OraSession object is used to create an OraDatabase. The connection information to the database is needed, and you can set some options that affect database-wide behavior. Building on the OraSession example, an OraDatabase is created like this: 

Dim OraSession as Object

Dim OraDatabase as Object

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

Set OraDatabase = OraSession.OpenDatabase("t:prod:orcl", "scott/tiger", 0&)

OraParameter

An OraParameter is used to represent a scalar value for bind variable replacement in a SQL or PL/SQL statement. Using an OraParameter object allows for better Oracle integration with SQL and PL/SQL, and for better code reuse. Without the OraParameter object, all SQL and PL/SQL statements would need to use literal values and need to be rebuilt to change WHERE clause values and (stored procedure) parameters.

Using an OraParameter may require some initial coding, but it never impacts performance negatively because Oracle bind variable (which an OraParameter represents) operations do not cause trips to the Oracle server (including setting and getting values).

Consider a case where you would like to query the name and address from a table named ADDRESSguide: 

...

'build my sql query

query$="select name, number from addressguide where name = 'SMITH'"

'create the dynaset

dyn = CreateDynaset(query$, ... )

'OK, now I can do something with SMITH

'rebuild my sql query

query$="select name, number from addressguide where name = 'JONES'"

'recreate the dynaset

dyn = CreateDynaset(query$, ... )

'OK, now I can do something with JONES

...

You could modify the preceding code to use a string variable for the name, and you could move the recreation to a helper function, but that's not the point. The point is that you must rebuild the query manually, every time, and resubmit it to Oracle. Using a SQL bind variable (:NAME) with an OraParameter makes this task much easier and can improve performance. 

...

'build my sql query

query$="select name, number from addressguide where name = :NAME"

'set the parameter value

parameters("NAME").value = 'SMITH'

'create the dynaset

dyn = CreateDynaset(query$, ... )

'reset the parameter value

parameters("NAME").value = 'JONES'

'refresh the dynaset

dyn.refresh

...

OraDynaset

An OraDynaset represents the return set (columns and rows) of a SQL select statement. Although an OraDynaset seems to map directly to an Oracle cursor, that is not the case. The initial processing (parse, describe, bind, define, execute, fetch) of a SQL select statement only requires one cursor, but subsequent adding, editing, or deleting of rows or the manipulating of binary data (LONG or LONG RAW columns) requires extra cursors.

The OraDynaset object internally implements a data cache of fetched rows to provide the appearance of backward scrolling cursors, which Oracle (as of Oracle7.2) does not support. The data cache also provides read-consistency because rows are not refetched.

You create an OraDynaset object using the CreateDynaset() method of the OraDatabase object. A SQL select statement is needed, and you can set some options that affect dynaset-wide behavior. Building on the OraDatabase example, an OraDynaset is created like this: 

Dim OraSession as Object

Dim OraDatabase as Object

Dim OraDynaset as Object

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

Set OraDatabase = OraSession.OpenDatabase("t:prod:orcl", "scott/tiger", 0&)

Set OraDynaset = OraDatabase.CreateDynaset("select * from addressguide", 0&)

OraField

An OraField represents a single column of the return set (columns and rows) of a SQL select statement. All column data of a row is fetched on demand except for binary data stored in LONG or LONG RAW columns. Those columns can hold up to a maximum of two gigabytes of data, and retrieving even one column of that size would exhaust the storage of most PCs.

The OraField object contains properties to determine the data type and methods to operate on binary data.

Collections

Think of a collection as an array of objects you can access by subscripting (using ordinal integers) or by using the name the object was given at its creation. A collection should support a Count property that returns the number of items in the collection, and the indices of the collection should go from 0 (zero) to Count - 1.

OraSessions

One OraSessions collection exists per application and is owned by the OraClient. This collection does not provide any other information other than the OraSession objects contained in it.

OraConnections

One OraConnections collection exists per OraSession. This collection does not provide any information other than the OraConnection objects contained in it.

The Count property is the only property available to the OraConnections collection. No methods are available because OraConnection objects are created automatically upon OraDatabase creation and cannot be added or removed pragmatically.

OraParameters

One OraParameters collection exists per OraDatabase. All OraParameter objects within the collection are available to use with any CreateDynaset() or ExecuteSQL() call made from the OraDatabase. This practice causes a problem only if the code was written to use two parameters of different types but the same name. You can fix this dilemma easily because bind variables (implemented using OraParameter objects) can have any name; the name is used for a substitution marker when the call is made.

The Count property is the only property available to the OraParameters collection. You can add and remove OraParameter objects by using the Add() and Remove() methods, respectively.

OraFields

One OraFields collection exists per OraDynaset. The OraFields collection represents all the columns returned in the select list.

The Count property is the only property available to the OraFields collection. No methods are available because OraField objects are automatically created upon OraDynaset creation and cannot be added or removed programmatically.

Dynaset Data Issues

Generally speaking, most applications want to create an OraDynaset and also view, add, edit, or delete data. In addition to the options available with OpenDatabase(), the following options are available with CreateDynaset():

  • The NoAutoBind option stops the binding of any OraParameters to the SQL statement. Binding does not affect performance because it does not cause a round trip. This option is not very useful but is probably available for completeness.

  • The NoBlankStrip option is important, especially when using CHAR columns that are padded to the column length. By default, trailing blanks are stripped from character data returned from the database.

  • The ReadOnly option causes the dynaset to be read-only regardless of the updatability of the SQL statement. This condition does not affect performance much, except that each row retrieved is slightly smaller because it doesn't contain a ROWID.

  • The NoCache option stops the saving of rows fetched to a local data cache. System resources are saved, but backward record movement is no longer possible. You should use this option when an application needs to make a single pass through a large number of rows.

Using Valid SQL Select Statements

You may use any valid SQL select statement, including but not limited to statements using unions, joins, subqueries, and the FOR UPDATE clause.

Whenever data is updated, the OLE Automation Server must do a SELECT. . .FOR UPDATE to lock the row in question, causing a trip to the Oracle database. Within a transaction, you can use the FOR UPDATE clause to eliminate these extra per-row trips. Use caution when taking this step because although all rows may not be returned to the dynaset, they are all locked during the life of the dynaset.

Selecting Data

When selecting data, keep in mind the OpenDatabase() and CreateDynaset() settings as well as the definition of the table from which you are selecting. All of these criteria can affect updatability and performance.

Editing Rows

An OraDynaset is updatable if the select statement used to create it selects only from one table, does not include column aliases or functions in the select list, and allows the Oracle ROWID to be selected. You can edit rows if they are uniquely identifiable. The Oracle ROWID is used to identify each row.

When you invoke Edit(), the row cached locally is compared to the original row stored in the database. If the rows match, the row is locked for update and the edit can proceed. If the rows do not match, an error is generated. The comparison is not done for LONG or LONG RAW columns because they can hold up to two gigabytes of data and most likely could not be fetched and stored locally if filled.

When editing, the same column defaulting issues listed in the following "Adding" section apply to editing as well.

Adding Rows

When you add rows, the most important issue to consider may be default column values. You have two options for default column values. This behavior is set at the OraDatabase level when OpenDatabase() is called.

In the default mode, the OLE Server sets to NULL any columns that are represented in the OraFields collection but have not been explicitly set. A SQL insert statement is then created and the new row is added to the database. Only columns that were selected into the OraDynaset are inserted, so Oracle still defaults any column values that were not specified. Also, you can set database triggers to execute, which may change column values. This change causes problems with the OraDynaset if you attempt to edit the row again, because the OraDynaset selects the row from the database to see if the currently cached copy matches the one in the database. If any of the previously mentioned conditions were true, the rows do not match and an error occurs.

To prevent that type of error, use the ORAMODE flag when calling OpenDatabase(). This flag causes the OLE Server to immediately reselect rows added (or updated) to the database. Oracle defaults any column values and executes any triggers. The row stored in the local cache now should match the one in the database (unless another user changes or removes it).

Deleting Data

You delete data using the Delete() method of the OraDynaset object. The current row is removed from the OraDynaset and a delete statement is issued to the database. The current row position does not change, although the current row is invalid (cannot be accessed) after Delete() is called. When record movement occurs after a delete, the deleted row no longer appears in the OraDynaset.

SQL Parameters

You can use an OraParameter in SQL statements to represent a scalar bind variable. If you use an OraParameter in a WHERE clause, for example, you can change the OraParameter value and refresh the OraDynaset. Refreshing an OraDynaset is preferable over recreating it because the query needs to be re-executed only with the new OraParameter value (instead of with all the steps: parse, describe, bind, define, execute, fetch).

Binary (LONG/LONG RAW) Columns

Operating on binary data stored in a LONG or LONG RAW column of the database is different than using any other data type, mostly because of the potential size of one of these columns.

The normal behavior of an OraDynaset is to fetch all column data and cache locally. This technique works fine for most data types, even strings (column of type VARCHAR) that can hold up to 2048 bytes. This behavior does not work well for binary (column of type LONG or LONG RAW) data because the columns in Oracle7 can hold up to two gigabytes of data. Most PCs don't have two gigabytes of disk space to hold one column of one row, never mind multiple rows.

To overcome this problem, the GetChunk() and AppendChunk() methods of the OraField object are available. GetChunk() enables fetching of a piece of binary data and AppendChunk() enables putting of a piece of binary data. GetChunk() normally causes a trip to the server to retrieve the requested piece.

AppendChunk() does not immediately cause a trip to the server to update the column, but it updates a locally stored item. After the Update() method is called, the whole column is updated at once. This type of update occurs because the Oracle Call Interface does not have a method for piece-wise updating of LONG or LONG RAW columns.

Because the basis of row identification in Oracle Objects is the Oracle ROWID and because binary data must be fetched separately, a ROWID is necessary to fetch LONG or LONG RAW columns. Therefore, you must give careful consideration to a SQL select statement with a LONG or LONG RAW column in it.

Transactions

You can begin database transactions only at the OraSession level, although transactions can be committed, rolled back, or reset (rolled back without events) at both the OraSession and OraConnection level. Rollback savepoints are not supported.

Although you could conceivably begin/end transactions using ExecuteSQL, this method is not recommended because it undermines the OraDynaset operations. The OraDynaset would not know when a rollback occurred, so it could not refresh data. You definitely should not start a transaction with OraSession.BeginTrans() and then end it with an OraDatabase.ExecuteSQL(), because the internal flag will think a transaction is still in progress.

When transactions are committed at the OraSession level, the OraConnections collection is traversed, and each connection is committed individually. No verification or two-phase commit behavior takes place, so if an error occurs, some connections can be left uncommitted.

Remember that DDL SQL statements made on the same connection as a dynaset with open transactions will cause the pending dynaset changes to be committed, which you probably don't want. For less confusion and errors, creating two OraSession objects is best—one for OraDynaset operations and one for ExecuteSQL() operations.

ExecuteSQL() Method

ExecuteSQL() is a very useful method because it allows the execution of any non-select SQL statement or PL/SQL block. These statements include Data Definition Language (DDL), Data Manipulation Language (DML), Session Control, and System Control statements.

Transactional control statements such as COMMIT and ROLLBACK are possible but discouraged. Embedded SQL is not supported.

Data Definition Language (DDL)

You can use any valid DDL statement with ExecuteSQL(), but use caution when using a statement that may undermine any internal operations of the OLE Server, such as committing transactions.

DDL statements issued within the same OraSession and OraDatabase using the same actual Oracle connection (login) causes any pending transactions in an OraDynaset to be committed. The best way to avoid this problem is to create separate OraSession and OraDatabase objects for DDL and for OraDynaset operations. This step is easy using the OraSession.CreateNamedSession() method, as shown in the following code. Do not simply call CreateObject() again, because it will return the same default OraSession.

...
Dim OraSession as Object
Dim OraSessionDDL as Object
Dim OraDatabase as Object
Dim OraDatabaseDDL as Object

'Create the default OraSession and a second named one
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraSessionDDL = OraSession.CreateNamedSession("DDL")

'Create two OraDatabase objects, one for each set of operations
'These will not share a connection because they are using
' different OraSession objects
Set OraDatabase = OraSession.OpenDatabase(ÒOracle7Ó, "scott/tiger", 0&)
Set OraDatabaseDDL = OraSessionDDL.OpenDatabase(ÒOracle7Ó, "scott/tiger", 0&)

...

Data Manipulation Language (DML)

You can use any valid DML statement with ExecuteSQL(). These statements include but are not limited to ALTER, CREATE, DROP, and GRANT.

Stored Procedures/Functions

Calling a stored procedure is slightly different than executing SQL statements. When calling a stored procedure or function, you must place a begin and end around the call, as shown in this example: 

...

OraParameters(":oldnum").value = '555-1234'

OraParameters(":newnum").value = '555-7890'

sp$="begin Addrguide.NumberChange(:oldnum, :newnum); end;"

OraDatabase.ExecuteSQL(sp$, 0&)

...

The OraParameter object can represent any scalar data type available to a PL/SQL stored procedure or function. Like SQL statements, using parameters means better code reuse. Using parameters does not mean better performance because after a call to a stored procedure is made, no object in the OLE Server is created to represent the code and save it for reuse. An OraParameter can also have values returned into it.

Some More Properties

You should remember these few useful miscellaneous properties.

The RecordCount property of the OraDynaset is useful but has one side effect to note. Accessing RecordCount forces all rows in the OraDynaset to be traversed. The rows are fetched and stored locally if you are in default mode, but not if you are in NoCache mode. In normal mode, you potentially could fetch thousands or more rows. In NoCache mode, the rows aren't all stored locally, but they also are no longer available (a new OraDynaset must be created).

The RecordCount property works this way for two reasons. First, the OLE Server does not have a SQL parser to decompose the SQL statement such that the OLE Server could create a new statement with the same conditions to issue a count. Second, even if the OLE Server could process the SQL statement to obtain a record count, this behavior would violate the read consistency model on the OraDynaset. If a second Oracle cursor needs to be opened, you have no guarantee that the OraDynaset created will be equal to the first. This is because the first OraDynaset does not cause all rows to be locked, and another user may have added, removed, or updated some rows.

You can use the guidemark property to save a pointer to a row and immediately jump back to the row later without having to execute multiple move operations. guidemarks are valid between an OraDynaset and its clone. You can use this feature to perform record movement on a clone (possibly to find a certain record) and then move the current row of the original OraDynaset directly to that row.

Portability of Source Code

The idea of implementing an OLE Server is to present the same interface to many applications. If the applications implement OLE client scripting per the specification, then the code to access the OLE Server is absolutely portable between host environments.

Unfortunately, though, none of these applications allow saving of an external file format understood by them all so the code would be easily reusable.

Host Environments

Of the three known supported OLE scripting environments, only one provides true OLE client scripting support—Excel 5.0. Visual Basic 3.0 and Access 2.0 each have a number of reserved words that those applications interpret to be internal commands and not OLE interface methods. These words are listed in the product documentation, and the list contains practically every method of the OLE Server. To work around this limitation, the OLE Server enables all of its method names to be prefixed by "Db" if necessary.

Performance

Because performance is always an issue, you should consider at least three areas of performance—Database, OLE Server, and OLE. You can manipulate some tuning capabilities of each area when you are trying to improve performance.

Database Performance

Oracle documentation contains a large amount of information about tuning the database, database schemas, and SQL queries. Review this documentation.

As far as the OLE Server is concerned, the object that most affects performance is the OraParameter used with SQL select statements. You must perform a number of steps before rows can be fetched from a query—parse, describe, bind, define, execute, fetch. When using parameters, you skip the parse, describe, bind, and define steps, and you simply re-execute the query. In addition, the query may still be available in the Oracle shared SQL pool, which also improves performance. If the query did not use an OraParameter, you would need to do all steps, and the query would not match the previous query in the shared SQL pool, which would degrade performance.

OLE Server Performance

The OLE Server fetches and caches rows so that backward scrolling is possible. The data fetching and caching have some tunable parameters. These parameters should be set in ORAOLE.INI, normally located in the WINDOWS directory. If the file does not exist, create one with a text editor.

The section named FetchParameters has two settings: FetchLimit and FetchSize. Set FetchLimit to the number of rows to fetch in one single call. The higher the FetchLimit, the more memory required for the fetch. Set FetchSize to the size of the initial chunk to fetch from a LONG or LONG RAW column. Again, the higher the FetchSize, the more memory required to fetch. Also, remember that in addition to the other column data, the number of FetchSize * FetchLimit bytes will be allocated just for the LONG or LONG RAW columns.

The section named CacheParameters has three settings: SliceSize, PerBlock, and CacheBlocks. SliceSize represents the smallest data block allocated for any data item (column). PerBlock represents how many Slices of SliceSize will be in each Block. CacheBlocks represents how many Blocks will be kept in memory before swapping to disk. SliceSize * PerBlock * CacheBlocks is the total amount of memory used by the data cache.

Most importantly, don't raise any of the settings so high that Windows needs to swap just to meet your memory needs. This causes double swapping in the case of the data cache and decreases performance. The best advice is to experiment with your particular OraDynaset needs.

The only problem with the cache and fetch settings is that they are per system and not per OraDynaset. OraDynasets that are very different in the data types and number of rows fetched may perform better with very different fetch and cache settings. Through testing, you can find a medium.

OLE Performance

OLE Automation is not without overhead. Every time you reference a method, property, or sub-object, you are making an object reference. Reducing the number of these references can greatly improve performance. If you are referencing some object many times, you should create a temporary object and set it to the object you are referencing too often. The product documentation describes this issue in detail in the topic "Coding Techniques."

Error Handling

The known supported applications handle errors with an ON ERROR GOTO statement that enables jumping to a label in the code when an error occurs. After this step, the application must determine if the error was an application or an Oracle error.

Application Errors

Application errors are generally all raised via one error variable. A number of errors that normally occur have predefined error numbers. For the OLE Server, only one error number is formally raised: 440, OLE Automation Error. When this error occurs, the application must check for an Oracle error to see if more information is available.

Oracle Errors

After an error is raised, you can query Oracle errors from the LastServerErr properties of the corresponding OraDatabase or OraSession object. Errors are set at either the OraDatabase or OraSession, but never both. Error codes are not automatically cleared, so you must use the LastServerErrReset property to set the error code to zero. This point is important to the developer but not the OLE Server, because the OLE Server only sets that error number; the OLE Server doesn't use the number to determine whether an error has occurred. The following code fragment demonstrates how to trap errors on either the OraSession or OraDatabase objects, depending on what methods are being called. 

...

Dim OraSession as Object

Dim OraDatabase as Object

Dim OraDynaset as Object

'Set a handler for OraSession creation

on error goto OraSessionErr

'Generic OLE error could occur, especially ones related to

'installation and configuration of needed Oracle and OLE files

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

on error goto OraDatabaseErr

'The only error that could occur here is a connection problem.

Set OraDatabase = OraSession.OpenDatabase("Oracle7", "scott/tiger", 0&)

on error goto OraSessionErr

'Notice that "select" is spelled wrong

Set OraDynaset = OraDatabase.CreateDynaset("seletc * from houses", 0&)

exit sub

OraSessionErr:

'Process OraSession Errors

MsgBox ÒError creating an OraSessionÒ

exit sub

OraDatabaseErr:

'Process OraDatabase Errors

MsgBox ÒError creating an OraDatabaseÒ

If OraSession.LastServerErr <> 0 Then

 MsgBox OraSession.LastServerErrText

 OraSession.LastServerErrReset

Else

 MsgBox Error$

End If

exit sub

OraDynasetErr:

'Process OraDynaset Errors

MsgBox ÒError creating an OraDynasetÒ

If OraDatabase.LastServerErr <> 0 Then

 MsgBox OraDatabase.LastServerErrText

 OraDatabase.LastServerErrReset

Else

 MsgBox Error$

End If

exit sub

...

Comparison with DAO/JET

Although the Oracle objects are conceptually close to the Microsoft Data Access Objects (implemented using the JET SQL engine), the Oracle implementation has a number of fundamental differences. These differences affect, among other things, data access, data updatability, and Oracle feature access.

DAO can make use of a local SQL engine, called JET, to process queries and data from flat files or ODBC data sources. You can bypass this engine (using SQLPASSTHROUGH mode), but all the built-in data updatability of DAO is lost and must be coded manually.

Differences Between Oracle Objects and DAO

Microsoft's DAO are conceptually sound. A number of database objects are represented at a high level. The interface is simple, requiring only a few lines to open a database, create a dynaset, and edit or add data. Unfortunately, the implementation using JET may not be very desirable. Instead of operating in a client/server mode, JET (a database engine) turns the implementation into client-server/server mode. Instead of letting Oracle7 process requests, JET first processes the request locally; then JET passes the request to Oracle7 (remotely), and again locally, and then returns it to the application. Too much processing is involved if your application is going to work only with Oracle7.

Oracle Objects for OLE was designed by taking some of the DAO objects and concepts and building them into a product that operated with less overhead and in more environments, communicated directly with Oracle, and could access Oracle-specific features.

Oracle-Specific Features

The JET SQL engine understands its own SQL variant and is not necessarily compatible with the Oracle SQL engine that you ultimately want to process your query. Queries are routinely parsed and reconstructed so the JET engine can understand them. Therefore, the Oracle SQL you have created may look nothing like the query that is submitted to Oracle. OO4O does not contain a SQL engine, and as a rule, does not reconstruct the queries provided to it. The OO4O model is to pass the query to Oracle, let Oracle parse it, and then ask Oracle for the query description.

Oracle SQL and PL/SQL allow for the embedding of bind variables (parameters), which enables code reuse and improved performance. DAOs do not contain an object to make use of bind variables. OO4O contains the OraParameters collection so that SQL and PL/SQL statements can be reused.

Data Updatability

Ideally, you should control data updatability by the rules of the database and the privileges you have been granted. This is not the case with JET. JET requires that a table have a primary key (unique index) to be updatable. The key is used to identify a particular row; you may need to modify the table definition to accommodate JET users. OO4O does not have this requirement because it uses the native Oracle ROWID to identify a particular row. OO4O does not place any extra restrictions on data updatability.

A database may have many large tables cryptically named or tables containing large amounts of information that not every user needs to access. Database views and synonyms are frequently used to provide simpler or restricted access to database objects. Using views and synonyms with JET automatically causes your dynaset to be read-only. OO4O does not automatically cause a dynaset to be read-only, and the data is updatable if Oracle normally allows it.

Data Access

Column aliases and schema references make queries more readable and qualify object access. You can use these techniques only with the SQLPASSTHROUGH mode of JET, which means the data is read-only. OO4O freely accepts those references, and the data is updatable if Oracle normally allows it.

References to object names (tables, views, synonyms, etc.) in Oracle are normally not case-sensitive, but JET, by default, uses uppercase unless you are in SQLPASSTHROUGH mode. This situation can cause problems when Oracle database objects are defined case-sensitively. OO4O does not modify the case of any object's reference.

Implementation of the OLE Server

JET performs a large amount of processing locally, such as table joins. JET provides updatability of joins (which Oracle does not allow), but what if the Oracle database contained millions of rows? Would the JET engine be able to retrieve and process that much data on your local PC? OO4O does not contain its own SQL engine and does not perform that type of processing locally—it lets Oracle do it.

JET fetches data, as needed, and stores a "window" of rows locally while the dynaset is traversed. This method means that data must be continually fetched using multiple trips to the server to keep the "window" fresh. Because the data is not locked, the data also could change during the time you're selecting it (no read consistency). OO4O fetches rows as needed and stores them all locally as the dynaset is traversed. Admittedly, this method can lead to a large amount of local storage, but it does provide read consistency.

JET requires Close() methods on objects, but Oracle Objects does not. The OLE objects keep a reference count and are automatically destroyed when they go out of scope and the count reaches zero.

Performance of the OLE Server

Because their implementations are different, OO4O and DAO/JET are not easy to compare directly. Because their features are similar, this comparison is frequently made. OO4O ships with a sample application called Object Test. This application's main purpose is to show that using OO4O to access and update Oracle database objects is much easier than using DAO/JET. Object Test also performs some timing, but the small amount of sample data cannot provide relevant performance data.

If you modify this application to use a larger data set, OO4O becomes faster. The main reason is that OLE Automation has overhead, and while fetching a small amount of data, that overhead is a large percentage of the time.

After the data of a dynaset has been entirely fetched, OO4O is simply accessing a disk cache while JET may need to refetch the data. This situation is a benefit in performance, but it may cause a large amount of temporary disk space to be used.

Again, as with memory and disk usage, testing your particular application is the only way to determine if it will meet your performance needs. You should consider hardware, software, and networking.

JET/DAO Migration Issues

Apart from understanding the different objects and error handling, you should consider a few important migration issues when moving from DAO/JET to Oracle Objects.

One of the fundamental migration issues is data types. DAOs are declared as some native data type (dynaset, database, etc.), and the Oracle objects are always declared as type "object." Also, you cannot compare data of type objects to zero or null or empty as easily as you can with other data types.

As mentioned earlier, the OLE Server objects do not provide all the same objects, methods, and properties as DAO/JET.

One of the most popular sets of methods of the dynaset object is the FIND method set (FindFirst, FindNext, FindLast, FindPrevious). The OraDynaset does not provide these methods, because the OLE Server would need to decompose the SQL statement, add a new WHERE clause, and reprocess it. Oracle Objects does not have a local SQL engine and cannot easily complete that task. Implementing this feature, although helpful, would defeat the current client/server mode that OO4O tries very hard to maintain.

The best workaround for the "missing" FIND methods is to use an OraParameter within the WHERE clause. You then can change the OraParameter value and refresh the OraDynaset. This workaround is limited. A second choice workaround is to make use of dynaset clones and guidemarks. Because guidemarks are valid between a dynaset and its clone, you could create a clone, search it for the desired record, set a guidemark, and then set the current row of the original dynaset to the guidemark. Again, this workaround is not complete for all cases.

Summary

The Oracle OLE Automation Server is a very complex tool. When developing an application, you must consider the relationship of objects to each other and to Oracle because some operations can cause side effects. Make sure to use built-in functionality and don't try to undermine features implemented in the OLE Server, even if they are not complete. You usually cause more trouble than it's worth. Try to make use of the OLE Server and its features without comparing the OLE Server to its design basis DAO. The OLE Server's features (data caching, read consistency) and unique objects (OraParameters) and its tight integration with Oracle7 make it a very powerful interface you should thoroughly consider when choosing a database connectivity layer.

Previous Page Main Page Next Page

Mobile News

New Huawei Phones

3 new phones from "Huawei" .. here is the date of its launch

Samsung phone 2020

According to the experts ... this is the best Samsung phone for 2020

Tech News

Moto G8 Power phone

5.0 New

Motorola unveils the Lite version of its Moto G8 Power phone!

New IPhone SE

5.0 New

The announcement of the low-spec iPhone SE is imminent!