What is Oracle Objects for OLE?
Oracle Objects for OLE (OO4O) is a middleware product manufactured by Oracle Corporation that allows native access to Oracle7 databases from client applications via the Microsoft OLE (Object Linking and Embedding) standard. OO4O's predecessor was called Oracle Glue.
Oracle Objects consists of the following three components:
An OLE 2.0 Automation (InProcess) Server - This provides an OLE Automation interface to applications that support OLE automation scripting such as Visual Basic (VB)
An Oracle Data Control - This is the Visual Basic custom control (VBX)
Two C++ Class Libraries - C-Libraries for Microsoft Foundation Classes (MFC) and Borland (OWL)
How does OO4O compare to ODBC?
OO4O provides native access to Oracle and only Oracle databases. It is faster than ODBC access but one can use ODBC to connect to a variety of data sources.
Since OO4O closely follows the ODBC query interface model, one can retain over 95% code compatibility between the OO4O and ODBC versions of the data layer procedures. This translates to very short retooling times in porting between the two products.
Can I use OO4O to access Oracle data from Microsoft applications?
Yes, you can use OO4O from any Microsoft application that uses the VB-style macro language. This includes Visual Basic, Excel, and MS-Access.
How does one connect to an Oracle Database?
Sub Form_Load ()
Dim OraSession As Object 'Declare variables as OLE Objects
Dim OraDatabase As Object
Dim OraDynaset As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("SQL*Net_Connect_String", "scott/tiger", 0&)
MsgBox "Connected to " & OraDatabase.Connect & "@" & OraDatabase.DatabaseName
'Create the OraDynaset Object and display the first value
Set OraDynaset = OraDatabase.DbCreateDynaset("select empno, ename from emp", 0&)
MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & OraDynaset.Fields("ename").value
End Sub
What are the major VB/ODBC limitations that OO4O overcomes?
(From the Oracle Objects Technical Overview by Keith Majkut):
Visual Basic 3.0 database access is not very client/server oriented. In fact, the Visual Basic database access is really designed for flatfile databases, but Visual Basic can use ODBC to access SQL databases. Unfortunately, Visual Basic, by default, makes use of a local SQL engine, called JET to process queries (using the MS JET SQL implementation, NOT Oracle's). You may bypass the JET engine (using something called SQLPASSTHROUGH, which causes the SQL to be sent directly to the database), but doing so causes your dynaset to be read-only. You would then have to update data by constructing SQL insert/delete/update statements. You would not be able to use the built in AddNew, Delete and Update methods. (Note: SQL statements cannot contain text data > 32K and cannot contain binary data.)
The drawbacks of the VB/JET/ODBC combination are many:
Rows are accessed via a primary key (unique index). Your table MUST contain a unique index, or else the resulting dynaset will NOT be updatable. This is an ODBC feature related to the fact that ODBC is based on Microsoft's SQLServer.
When a dynaset is created, only the primary keys are fetched; rows are not fetched until they are needed. This sounds reasonable until you realize that it results in multiple queries and many trips to the server.
When a snapshot is created, all rows are immediately fetched. This could potentially be millions of rows.
Table joins are performed locally.
Although views and synonyms can be used, they will cause your dynaset to be read-only.
Column aliases and schema references (such as "SCOTT.EMP") cannot be used without SQLPASSTHROUGH.
Object names are referenced in UPPER case unless you use SQLPASSTHROUGH. This only works for the (default) Oracle setting of case-insensitive objects.
There is no support for SQL bind variables (i.e. "SELECT * FROM EMP WHERE ENAME = :name"). If the value of your variable changes, you need to recreate the SQL statement and dynaset. This means that the server must reparse the query and refetch the data.
There is no support for PL/SQL bind variables (in/out parameters). The workaround is to pass values in as literals, and store the return values in a temporary table that you can then query on to get the results.
Multiple data controls (or database opens) use multiple database connections, even if they all reference the same account on the same database. This can be a serious problem with databases that have a limit on the total number of connections they will accept.
Oracle Objects has no such drawbacks:
Rows are accessed via their rowid. If a rowid can be obtained, then the data is updatable.
When creating a dynaset, some data is initially fetched and cached locally. Data is fetched and stored locally as needed, as rows are traversed. Admittedly, this can lead to a large local data cache, so many tunable cache parameters are provided to improve performance.
Snapshots have not been implemented, since a read-only dynaset is a reasonable equivalent.
Table joins are performed by the server.
Views, synonyms, column aliases and schema references can be used freely. The updatability of database objects is only dependent SQL updatability rules and on the access you have been granted .
Objects names are not modified in any way. You may use upper or lower case in the names, which are case-insensitive.
Support for SQL bind variables (i.e. "SELECT * FROM EMP WHERE ENAME = :name") is offered via the Parameters collection. The server does not have to reparse the query before refetching data.
Support for PL/SQL bind variables is offered (in/out vars) via the Parameters collection.
Multiple data controls (or database opens) referencing the same account on the same database will all share a single Oracle connection.
At first, the VB/JET/ODBC limitations may not sound serious, but consider a typical production Oracle environment where most users only have access to data via synonyms and views. This means that VB/JET/ODBC users can only read, not write data.
No comments:
Post a Comment