Web based School
- 46
- Introduction to Oracle Objects for OLE
- Overview of Oracle Objects for OLE
- Architecture of Oracle Objects for OLE
- Data Access Using OLE
- Performance Overview
- Development Issues
- Software Requirements
- Remote Database Access
- Local Database Access
- Memory Requirements
- Disk Requirements
- Deployment Issues
- Software Requirements
- Remote Database Access
- Local Database Access
- Memory Requirements
- Disk Requirements
- Summary
46
Introduction to Oracle Objects for OLE
This chapter introduces a new and exciting product, Oracle Objects for OLE, from Oracle Corporation. This chapter contains general information about Oracle Objects for OLE such as components and architecture, plus important issues such as performance
and deployment considerations. The following three chapters each discuss one of these major components of Oracle Objects for OLE: Oracle In Process Server, Oracle Data Control, and Oracle Objects C++ Class Library. These chapters are designed to go beyond
the reference model of the product documentation by providing important information about how all the pieces of Oracle Objects for OLE interact with each other and with Oracle7.
To get the most from these chapters, you should read the Oracle Objects for OLE documentation to gain a reasonable understanding of the product. You also should create at least one application with Oracle Objects for OLE. Code snippets and screen shots
are provided where appropriate within these chapters.
The information documented in Chapters 46 through 49 is accurate for Oracle Objects for OLE 1.0 (patch level 55). Because Oracle does periodically release patches and upgrades, please contact Oracle for information about future releases.
Overview of Oracle Objects for OLE
Oracle Objects for OLE (OO4O) consists of three distinct components. The first and most important element is the Oracle OLE Automation Server used to communicate with an Oracle7 database. Although the OLE Automation Server is very powerful by itself,
OO4O also contains the Oracle Data Control and a C++ Class Library to make using the OLE Server within those environments even easier.
If your application development environment can support VBXs, OLE Automation, or C++, and only connects to Oracle7, then Oracle Objects for OLE may be the right product for your task. Oracle Objects for OLE does not replace ODBC or the Oracle Call
Interface, but it does provide excellent alternatives. Keep in mind that Oracle Objects for OLE has a basis in these other interfaces, but it's not supposed to be a clone.
Oracle OLE Automation Server
An OLE Automation Server is an OLE object (which may or may not expose some GUI interface) that exposes a set of methods and properties which other applications can query and invoke. Each method can accept any number of parameters of different types,
just as each property can return values of different types. Applications that support OLE Automation can query the OLE Server, and it returns information on all its available methods and properties, plus their parameters and return types. By implementing
an OLE Server, you can automatically extend the macro language of OLE-enabled applications to support your interface.
The Oracle OLE Automation Server implements a number of objects and interfaces to Oracle7 that are similar to the Data Access Objects (DAO) implemented by the Microsoft JET SQL engine and often used in Microsoft Access and Visual Basic. The Oracle
objects and interfaces were designed with DAO in mind but are implemented from an Oracle7 point of view. The objects and interfaces favor "true" client/server development by allowing Oracle7 to perform much of the processing while allowing the
local OLE Server to perform the guidekeeping and interaction with the application.
Oracle Data Control
A Visual Basic Extension (VBX) is used to add functionality to various applications, most notably Microsoft Visual Basic 3.0. A VBX is actually a Dynamic Link Library (DLL) with a standard interface and the .VBX file extension. A VBX may be graphical in
nature or may provide its functionality just using properties and methods. A VBX generally encapsulates what would have taken a large amount of development and presents to the user a property palette where you can alter the control's appearance or
operation with a few mouse clicks and maybe some typing. (This explanation is an over-simplification in some cases, but it generally describes the situation.)
The data control in Visual Basic 3.0 is not actually a VBX (it is built into the product), but it does function like one. The data control provides three important features:
- A user interface for record movement
- Access to the DAO in the Microsoft JET SQL engine
- An interface to which other controls can bind
The binding is probably the most important. Binding enables other controls to "attach" themselves to the data control in order to query, process, and/or present data in some manner. These controls are called data aware or bound
controls. Data aware controls are available to present data in grid format (rows and columns), drop-down lists, radio buttons, and many other ways too numerous to mention. Bound controls aid development because they can simplify the data
querying, processing, and/or presenting step to filling in a property palette item with the name of a control.
The Oracle Data Control (ODC) is a Visual Basic 3.0 extension (VBX), implemented as level III custom control. The ODC is the only control that, for the most part, is a drop-in replacement for the Microsoft Visual Basic 3.0 data control when your
application only needs to access Oracle7. Other data controls are available, but they require the purchase of specialized bound controls that work only with that particular data control. The ODC is designed to provide the same interface that the Visual
Basic 3.0 data control provides so any standard bound control can work with it. The ODC is not a complete clone, but it does implement enough of the standard data control interface to work with many of the most popular controls available. See Chapter 48,
"Data Control," for more information.
Oracle C++ Class Library
The Oracle Objects for OLE C++ Class Library uses the Oracle OLE Automation Server to implement a set of flexible, powerful, and easy-to-use classes. These classes provide a C++ developer with object-oriented access to the relational data stored in an
Oracle database. The class library offers classes that implement all the objects available to the Visual Basic user, as well as some additional functionality. The class library also supports the idea of bound objects, giving the C++ developer the ease of
use that the Visual Basic programmer gets when using bound controls with the Oracle Data Control.
Architecture of Oracle Objects for OLE
Oracle Objects for OLE version 1.x is a 16-bit Windows product. Therefore, the OLE Server, the Oracle Data Control, and the C++ class library are 16-bit Windows components. The OLE Server requires Microsoft OLE 2.0 to operate and can communicate only
with an Oracle7 database (either local or remote). Figure 46.1 illustrates the various software layers used to communicate with an Oracle database using Oracle Objects for OLE.
Figure 46.1. Architecture of Oracle Objects for OLE
These components are 16-bit and generally cannot be used with a 32-bit host application or development environment. Specifically, the Oracle Data Control was designed for Visual Basic 3.0, although it should work with any application that can support
level III custom controls. The Oracle OLE In Process (Automation) Server and C++ Class Library were designed for use with 16-bit host applications and development environments such as Microsoft Visual Basic 3.0, Microsoft Visual C++ 1.5x, and Borland C++
4.x.
These designs do not mean, however, that Oracle objects used with a 16-bit application or development environment cannot run under a 32-bit environment. For example, a 16-bit Visual Basic application developed using Oracle Objects for OLE can run
on Windows NT within its 16-bit subsystem.
Although Oracle Objects for OLE contains an OLE Automation Server, it does not contain an OLE Custom Control (OCX). The OCX is the successor to the VBX and would be an obvious progression of the product.
Implementing Objects on the Basis of DAO/JET
The objects of Oracle Objects for OLE have their basis in the Data Access Objects (DAO) of the Microsoft JET SQL engine. JET parses queries and contains its own SQL syntax. JET performs query joins locally; using ODBC, JET might not be able to make full
use of features available in a particular database.
JET implements a number of objects used to represent and manipulate the objects of a database. The DAO concepts are good, but their implementation is poor and they suffer from having to represent data in the form of flat files up to relational database.
Oracle Objects implements some, but not all, of the DAO but does so from a client/server point of view and does so realizing the potential size and organization of Oracle7.
Oracle Objects for OLE does not contain its own SQL engine and relies on Oracle7 to parse all queries using its syntax. If an application only needs to communicate with Oracle7, why introduce an intermediate SQL engine with its own syntax? Oracle
Objects for OLE does not perform local query joins and can access practically all SQL and PL/SQL (Oracle Procedural Language SQL) features. Because Oracle7 can potentially contain millions of rows and because a server is generally more powerful than a
client, how could you and why would you perform joins locally? Finally, why not make use of Oracle-specific features and functionality such as PL/SQL?
A larger comparison of the Oracle objects and DAO is done in the following chapter, but a brief summary is appropriate here. Discussion of three objects shows the most overlap. These objects are the database, the dynaset, and the field.
Within DAO, a database object is used to represent an open database file and a connection to a database, as well as to control database-wide permissions and functionality. A dynaset object is used to represent the return set from a SQL select statement.
(A snapshot object also exists, which is basically a read-only dynaset.) A field object is used to represent a single column of a query that has been returned as a dynaset.
Using Oracle Objects for OLE, an OraDatabase object is used to represent Oracle7 and to provide options on row locking and column defaulting. An OraDynaset object is used to represent the return set from a SQL select statement and to implement backward
scrolling cursors, which Oracle7 does not natively support. (Instead of implementing a snapshot object, you can mark the dynaset as read-only.) An OraField is used to represent a single column of a query that has been returned as a dynaset.
In addition to those objects, DAO has a tabledef object that represents the definition of a table—columns and their data types. Oracle Objects for OLE does not have any such object, although it would be an interesting addition. Adding this type of
object would not necessarily violate Oracle Objects client/server model because you still could store the actual table definition in Oracle7, and use the local object to manipulate it.
DAO also contains a querydef object, used to save a query for easy reuse. Oracle Objects does not contain such an object, but Oracle7 does have views that are an excellent equivalent. This way, code is stored on the server and can be used by an even
wider range of users and does not need to be stored with the application. Again, Oracle Objects relies on Oracle7 to provide functionality where appropriate.
Data Access Using OLE
Although the acronym OLE (Object Linking and Embedding) does not mention it, OLE Automation may be the most important feature of that technology. OLE Automation enables the implementation of an interface that is registered with the Windows system and is
instantly available to any application capable of being an OLE (Automation) client. This means that the objects, methods, and properties of OO4O are presented consistently across applications such as Microsoft Visual Basic 3.0, Access 2.0, and Excel 5.0.
Code written using OO4O for accessing Oracle7 can be run unchanged in any of those and other environments.
Why OO4O Uses OLE (and Not Other Methods)
OO4O provides an interface that can be used unchanged in multiple environments, as just described. The amount of code you can reuse is large and the learning curve small.
Dynamic Link Libraries (DLL) also provide extensibility, but they do not have a specification for registering and querying their interface. Having no standards has led many applications to implement DLL interfaces differently so that even if the DLL can
be used, code is not very portable.
OLE 2.0 is still a developing technology with Microsoft adding feature support (distributed objects) and more vendors adding application support. Eventually, OLE will become the basis of Microsoft's next-generation operating systems.
How to Use OLE Automation
Using OLE Automation in the products that currently support Oracle Objects is quite easy. OLE objects are declared just like integers or strings and can be manipulated using whatever methods or properties are available with a dot notation. The following
code shows how to create an OraDynaset and loop through records:
Dim OraSession as Object
Dim OraDatabase as Object
Dim OraDynaset as Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase(ÒOracle7Ó,"scott/tiger", 0&)
Set OraDynaset = OraDatabase.CreateDynaset("select name, address from addrguide",0&)
While Not (OraDynaset.EOF)
MsgBox "Name = " & OraDynaset.Fields("Address").value & " Address = " & OraDynaset.Fields("name").value
OraDynaset.MoveNext
Wend
The dot is used to append a property or method name to an object. If the next-level method or property returns another object or collection of objects (like OraDynaset.Fields), then further clarification may be needed to identify a particular object (in
the case of a collection) or the desired property of the object (the value).
Performance Overview
Common questions are "How fast is it?" and "How does it compare to X?" and "Why is it slower than Z?". This section provides no easy answers, just recommendations to help you make the correct decision based on the correct
information.
Making an Accurate Comparison
Although Oracle Objects for OLE is based on DAO, its implementation is significantly different. For example, an OraDynaset has a local data cache that stores all fetched rows locally to implement backward scrollable cursors. A DAO dynaset only stores a
window for data locally and refetches based on a primary key when rows are needed. An OraDynaset provides read consistency and a DAO dynaset does not.
You can create an OraDynaset with three calls. Internally, using OCI, the implementation requires much more code.
Many database access methods and products are available, and none is implemented exactly the same as Oracle Objects for OLE. A fair comparison, and one that is done often, is to DAO/JET in non-SQLPASSTHROUGH mode. These two methods are related most
closely in terms of functionality; just make sure you understand the implementations of both methods before making comparisons.
Considering Oracle Tips
Although database tuning and SQL optimizations are beyond the scope of this documentation, you should remember a few basic tips when performance is an issue:
- Don't select any more rows or columns than necessary. Oracle Objects for OLE fetches rows as needed and caches all rows locally.
- Moving the processing from a flat file or single-user system to a remote multiuser database causes the network involved to play a major part in performance.
- Oracle has documentation on query optimizing or database optimizing. If performance is very important, maybe your table needs an index or maybe the database parameters need tuning.
Whatever the situation, remember that Oracle Objects for OLE is mostly a piece of middleware that uses the server for processing. The performance of all components (hardware locally, network, database, hardware remotely) is important.
Development Issues
Oracle Objects for OLE development requirements are minimal when compared to those of the additional software required. OO4O has all the typical development requirements (environment, disk, memory) plus it needs a host application to drive it. If your
current hardware/software already supports connecting to Oracle7 and can run an appropriate host application for Oracle Objects, then you should have no problem adding Oracle Objects to the equation.
You can find Oracle Objects for OLE typically packaged by itself or bundled with various versions of Personal Oracle7 and the Oracle Workgroup Server. Because you may not have Oracle7 or you may want to run it on your own system, these bundles are
excellent values. Don't assume Oracle Objects is included in all similar and future bundles of these products, because the bundling changes at Oracle's discretion.
Oracle Objects for OLE also is available as a 90-day trial product on Oracle's World Wide Web site at www.oracle.com. Again, this offering is subject to change by Oracle.
Software Requirements
Oracle Objects for OLE is not a complete development environment but a development aid. OO4O requires a host application or development environment with which to build one, plus access to Oracle7.
Oracle Objects basically requires a system capable of running 16-bit Windows applications that can also connect to Oracle7. These systems include but are not limited to Windows 3.1, Windows for Workgroups, and Windows NT 3.x.
Host Environments
The Oracle Data Control can theoretically work in any environment that supports level III custom controls, but it is documented to work only within Visual Basic 3.0.
The Oracle In Process Server requires any application that can support scripting to an OLE Automation Server. These programs are Visual Basic 3.0 (Standard or Professional), Access 2.0, and Excel 5.0. Word 6.0 can act as an OLE Server, but it does not
support scripting to an OLE Server.
You could use C or C++ to write your own OLE scripting client, but you probably should not because no help is offered in the product documentation. If you want to try, C++ is easier because of the automatic code-generation tools shipped with most
popular development environments.
Even easier than writing an OLE client yourself is to use the Oracle Objects C++ Class Library. The class library provides the same objects that the OLE Server does and takes care of various OLE initialization tasks and some memory management. The C++
class library also contains a class that enables dynaset-to-data widget binding much like the Oracle Data Control.
The Oracle C++ Class Library contains two components and is built in two flavors. The first component contains classes that are implemented as a DLL over the objects found in the OLE Server. The second component contains classes to enable the
dynaset-to-data widget binding. The dynaset-to-data widget binding functionality is implemented as a static library and implementations are provided for MFC (Microsoft Foundation Classes) and OWL (Object Windows Library).
Each of the components has been built for use with Microsoft Visual C++ 1.5x and Borland C++ 4.x. These two formats are necessary because of differences in C++ name-mangling that affects how classes are exported from a DLL.
The documentation says that no other compiler is supported, although others may work if they follow the calling conventions and C++ name-mangling conventions of MSVC++ or BC++.
Remote Database Access
If your database is a remote server, Oracle SQL*Net for 16-bit Windows is required. Some of the original packaging may have suggested that Oracle SQL*Net was built into the product, but this is not the case.
Oracle Objects for OLE does not depend on any particular protocol, version (V1 versus V2), or patch level of Oracle SQL*Net. You can use any valid combination of SQL*Net client to SQL*Net server that is available.
Local Database Access
If your database is local, you probably don't need any additional software to connect to the database. For example, although Personal Oracle7 for Windows 3.1 is Win32s-based, it contains a layer to communicate locally from 16-bit applications to the
32-bit database.
The exception is if you are developing on the same machine as the server. For example, if you are developing on Windows NT, you are still accessing Oracle7 in a client/server mode and you still need Oracle SQL*Net as just mentioned.
Memory Requirements
OO4O is actually a very small piece of software. In terms of size, the Oracle Data Control is only about 64K, the Oracle In Process Server is about 239K, and the C++ Class Libraries for Borland and Microsoft are 74K and 137K respectively. Does this mean
that the memory requirement for OO4O is no more than 300K? No, unfortunately it doesn't.
Although OO4O is not a large amount of code, it does store data in memory per the settings of its cache parameters in ORAOLE.INI (discussed in more detail in Chapter 47, "OLE Server"). You can tune these settings so that data is swapped to
disk, but performance suffers. An exact number is not possible, because operating and guidekeeping overhead is involved. After reading the "Tuning and Customization" topic in the product documentation, my best estimate is MemoryUsage = (SliceSize
* PerBlock * CacheBlocks).
Because OO4O depends on other software to operate, you must take into account the memory requirements of the other software. Check the documentation of the host application you're using to determine its development memory requirements.
Disk Requirements
The complete OO4O package has typically been distributed on four high-density (1.4M) disks. Although almost all the files on the disks are compressed, a closer look reveals that most of the files are not part of OO4O proper, but either Microsoft OLE 2.0
or the Oracle Required Support Files (RSF).
A complete OO4O installation including all sample applications, Oracle Required Support Files, and Microsoft OLE 2.0 requires about 8M of disk space. You may not use that much disk space for several reasons: OLE 2.0 is most likely included with your
host application or development environment; the Oracle Required Support Files are shipped with almost every Oracle Windows application (which you may have); or you may choose not to install all the sample applications (which are about 1.5M by themselves).
Considering that a typical installation of Microsoft Visual C++ 1.5 needs 45M of disk space and a Visual Basic 3.0 needs about 12M, OO4O does not require an unreasonable amount of space.
Finally, Oracle Objects for OLE swaps OraDynaset data to disk as it is fetched and cannot fit into memory. A reasonable estimate of disk space required for an OraDynaset is the size of the columns and rows of the data fetched. (This size is not
necessarily the size of the entire OraDynaset if you have not reached the end.) The size of the data fetched is close to the size of the data from the first row to the farthest row reached. You don't necessarily need to add LONG and LONG RAW data, because
that data is fetched only on demand. Because estimates are not easy to calculate, the best way to determine run-time disk usage is to try Oracle Objects in a simulated user environment.
Deployment Issues
Even though you have been developing with Oracle Objects and everything works fine, you need to make some extra considerations for deployment.
Software Requirements
The run-time requirements for Oracle Objects are very similar to the development requirements, except for the licensing or acquiring the needed components for connecting to Oracle7 on each client.
The product documentation lists exactly what Oracle Objects for OLE files are needed and what Microsoft OLE 2.0 files are needed under the on-line documentation topic "Redistributable Files."
Oracle Software
Although run-time distribution of specific OO4O files is free, deployment of other required Oracle software is not. OO4O requires the Oracle7 Required Support Files for 16-bit Windows. You usually can obtain this software from the Personal Oracle7
package if the end user will be connecting to a personal database. Or you can obtain the software from the Oracle SQL*Net for Windows package if the end user will be connecting to a remote database.
Host Environments
Again, the host environments that are supported for development are the same for deployment. Some of the supported environments do have run-time versions; some do not.
Of the known supported host environments, Visual Basic 3.0 (Standard or Professional) and Access 2.0 have run-time versions. Excel 5.0 does not. Microsoft Visual C++ 1.5 and Borland C++ 4.x can obviously build an executable that you ship.
Always consult the particular host product documentation as to which files are needed and which are distributable because this information can change between versions.
Visual Basic 3.0 does not ship with a complete set of OLE 2.0 run-time files. The file TYPELIB.DLL is missing. This point is mentioned briefly in the "Troubleshooting" topic of the on-line
documentation and is well worth mentioning here.
Check the on-line documentation topic "Redistributable Files" for complete details.
Remote Database Access
If your deployment system will be accessing Oracle7 remotely, then a copy (license) of Oracle SQL*Net is necessary. Even though this product may have been included with the bundle you purchased for development, Oracle SQL*Net has historically not been
licensed for free distribution. The same requirements listed earlier in "Remote Database Access" for development also apply here.
Local Database Access
If your deployment system will be accessing Oracle7 locally, then a copy of that database (typically a Personal Oracle7 bundle) is necessary. Even though this database may have been included with the bundle you purchased for development, the Oracle
database has historically not been licensed for free distribution. The same requirements and exceptions listed earlier in "Local Database Access" for development also apply here.
Memory Requirements
As noted earlier in "Memory Requirements" for development, OO4O by itself has a very small code base and is highly dependent on the cache settings in ORAOLE.INI and the amount of data fetched. All other requirements listed in that section are
valid at run-time because the OO4O code is the same for both.
Disk Requirements
As with almost every other deployment issue, disk requirements are mostly dependent on the host application and on the other required files. The complete set of run-time files for Oracle Objects is roughly about 400K, but the other needed files can run
into multi-megabytes. Again, check the documentation of the host application and other software required because this information may change between releases.
Summary
Oracle Objects for OLE provides excellent access to Oracle7 from many popular applications. The interface is designed to resemble the Microsoft Data Access Objects, but it should not be judged strictly on its duplication of the DAO interface. Oracle
Objects for OLE provides better integration with Oracle7, leverages more of the power of Oracle7, and uses one of the newest and most popular Windows technologies (OLE 2.0) to do so.
A natural progression of this product would be to add more of the DAO interface and provide easier access to Oracle7 features. Oracle has made improvements even in the small patch releases and will presumably continue developing Oracle Objects for OLE for use on 32-bit platforms using OLE controls. This product is small but provides formidable competition to similar interfaces.
Popular Tutorials
-
MS Access
1109
-
C++
1222
-
HTML
584
-
JavaScript
616
-
Vbscript
873
-
Oracle
473
-
VC++
875
-
SQL
2959
-
XML
514
-
Java
814
-
Perl
455
-
Linux
451
