0980-0982

Previous Table of Contents Next

Page 980

The following example creates a dynaset and loops through the result, and displays the Value property of the ename column:

 Set Employees = EmployeeDataBase.CreateDynaset("select ename, empno  from emp", &H0&) while  Employees.EOF == FALSE    ` While there are more employee rows in the result 'set    `Display the name and employee number of the current employee record    MsgBox "Name: " & Employees.Fields("ename").Value    `Move to next record    Employees.MoveNext Wend 

An alternative way of coding this would be to get a reference to the OraField object representing the ename column before the loop starts, and then using it inside the loop:

 Set Employees = EmployeeDataBase.CreateDynaset("select ename, empno  from emp", &H0&) `Get the reference to the OraFields collection once and use it in `subsequent statements curentRow = Employees.Fields `Now get the references for the OraField objects representing ename and empno set empName = currentRow(0) ` The first column - ename set empNo = currentRow ` The second column - empno while  NOT Employees.EOF ` While there are more employee rows in the result set    `Display the name and employee number of the current employee record     MsgBox "Emp. Name: " & empName.Value & "Emp. No.: " & empNo.Value    `Move to next record    Employees.MoveNext Wend 

Minimizing the Network Roundtrips

Minimizing network traffic is generally an important area of focus in the design and implementation of client/server and distributed application systems.

In heavily used systems where a large number of client applications communicate with application and/or database servers over a network, the traffic on that network can become a bottleneck and can hurt the overall performance of the system.

Most modern database access APIs offer features to aid in reducing the often termed network roundtrips.

Page 981

The parameter array (OraParams) interface is an example of such a feature provided by OO4O. Consider the case in which a large number of rows needs to be inserted into a table; calling the ExecuteSQL or the CreateSQL method that uses a parameter array is more efficient than calling the AddNew method of an OraDyanset many times for inserting individual rows. In the latter case, many SQL INSERT commands are issued against the database server, whereas with parameter arrays, only one SQL INSERT statement is executedresulting in fewer roundtrips between the client application and the database server.

OO4O also provides a customization parameter (FetchLimit) for the OraDynaset interface that controls the number of rows fetched per each request. Whenever a move to a row position is requested and that position is greater than the position of the last row in the dynaset, more rows are fetched from the database. FetchLimit is the number of rows retrieved every time this situation occurs.

In situations where the query of the dynaset is likely to return a large number of rows and the entire row set is likely to be browsed, increasing this limit can greatly reduce the number of network roundtrips and improve performance. This parameter can be set in the Windows Registry as a default value that applies to all dynasets or can be passed to each individual CreateCustomDynaset method.

Changing the OO4O default settings in the Registry can potentially affect other applications and should be avoided if possible.

A discussion of the other parameters to this call is deliberately skipped because the other parameters' impact on performance is not that significant, and their discussion will not be relevant in this context.

 Set aLargeDynaset = OraDatabase.CreateCustomDynaset("select * from aLargeTable ", &H0&,256,16,1000,20,4096) 

The capability to return PL/SQL cursor variables in OO4O is another feature that can potentially help in the area of performance and should be used when appropriate. Multiple result sets originating from multiple disjoint queries can be returned in the form of PL/SQL cursor variables.

Executing a PL/SQL stored procedure that performs a major business function is much more efficient than executing multiple queries, fetching several result sets to perform the same business function in the client application. Centralizing the application logic in the server also results in improved security and maintenance.

Reusing Database Connections

Establishing a connection to an Oracle database server is a relatively expensive process and should be minimized if possible. This is particularly true in server environments, such as Web servers, where a large number of users execute relatively small queries frequently. Consider a stock quoting system where the stock symbol is given and the trading information on that stock is

Page 982

retrieved. In such a system, opening a connection to the database and executing the query for every user requesting the stock quote will not only be slow, but will consume a lot of server and network processing cycles. In this situation, one might want to establish a number of connections when the server starts up and use a free connection to service an incoming request and returning it to the pool when the request is serviced. Because an OraDatabase in OO4O represents a distinct connection to an Oracle database, a pool of OraDatabase objects needs to be managed for this purpose. You can find an example of a connection pooling implementation in the sample code section of Version 2.2 of OO4O installation directory. One example is implemented completely in VBScript for Active Server Pages in IIS, and the other is an IIS ISAPI example.

Thread Safety

Version 2.2 of OO4O supports both the free and apartment threading models in OLE COM. This enables OO4O automation objects to be used in multithreaded applications and environments such as the Microsoft IIS Active Server Pages and ISAPI. Version 2.2 of the product is entering beta at this time and should be available by the time this book is published.

Summary

This chapter presents a brief description of the Microsoft OLE Automation technology, and discusses how the automation objects in OO4O can be used effectively to access and manipulate data stored in Oracle databases.

Examples in this chapter are presented in Visual Basic to demonstrate how to execute SQL statements and PL/SQL code, process results, and control transactions using the automation object in OO4O. Issues and techniques for improving the performance of the OO4O automation server were also discussed.

The reader should treat this chapter as an introduction to the automation objects in OO4O and should consult the documentation that accompanies the product for a complete reference and up-to-date information.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net