Oracle Unleashed- P17

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

78
lượt xem 4
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle Unleashed- P17: When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle Unleashed- P17

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) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 addrbook",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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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: q Don't select any more rows or columns than necessary. Oracle Objects for OLE fetches rows as needed and caches all rows locally. q 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. q 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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Microsoft Visual C++ 1.5x supports level I custom controls only and cannot load other higher-level controls. 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++. The first production release of OO4O (1.0.42) did not work properly with Borland C++ 4.5 (you received the error Undefined symbol: v_U_U_W_Dispatch when linking). This problem was fixed in a later patch (1.0.55). 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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 bookkeeping 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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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. The Oracle7 Required Support Files are numbered for the various Oracle7 releases such as 7.0, 7.1, and 7.2. Because filenames typically change between releases, you must make sure that the RSF on the deployment system matches the one required by OO4O. OO4O originally required a version 7.1 RSF (up to release 1.0.57) and in the future could require a higher version. You can find out which version of the RSF your deployment system has by checking the release media or running the Oracle Installer (which stores a list of installed products and their versions). 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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. Previous Next TOC Home Page Page Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous Next TOC Home Page Page q 47 r Oracle OLE Automation Server s Background s What Is an OLE Automation Server? s What Is an OLE Automation Object? s What Is an OLE Automation Collection? s Object Hierarchy s Types of Oracle Objects s OraClient s OraSession s OraConnection s OraDatabase s OraParameter s OraDynaset s OraField s Collections s OraSessions s OraConnections s OraParameters s OraFields s Dynaset Data Issues s Using Valid SQL Select Statements s Selecting Data s Editing Rows s Adding Rows s Deleting Data s SQL Parameters s Binary (LONG/LONG RAW) Columns s Transactions s ExecuteSQL() Method s Data Definition Language (DDL) s Data Manipulation Language (DML) s Stored Procedures/Functions s Some More Properties s Portability of Source Code s Host Environments s Performance s Database Performance s OLE Server Performance s OLE Performance s Error Handling s Application Errors s Oracle Errors s Comparison with DAO/JET s Differences Between Oracle Objects and DAO s Oracle-Specific Features Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Data Updatability s s Data Access s Implementation of the OLE Server s Performance of the OLE Server s JET/DAO Migration Issues s Summary 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 ADDRESSBOOK: ... 'build my sql query query$="select name, number from addressbook 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 addressbook where name = 'JONES'" 'recreate the dynaset dyn = CreateDynaset(query$, ... ) 'OK, now I can do something with JONES ... Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 addressbook 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 addressbook", 0&) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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(): q 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. q 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. q 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. q 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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&) ... Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 AddrBook.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 Bookmark property to save a pointer to a row and immediately jump back to the row later without having to execute multiple move operations. Bookmarks 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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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&) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.