0967-0969

Previous Table of Contents Next

Page 967

CreateCustomDynaset and CreateCustomPlsqlDynaset can take additional arguments that are used as hints to OO4O for performance tuning and memory usage, and are discussed later.

ExecuteSQL and CreateSQL methods execute SQL statements and PL/SQL blocks. CreateSQL is only available in versions 2.0 and higher, and is essentially an enhanced version of the ExecuteSQL method.

The CreateDynaset Method

This method provides a convenient way for executing SQL SELECT queries and processing the results. We'll examine the previous example to introduce a few characteristics of an OraDynaset.

The CreateDynaset method of the OraDatabase is used to execute the query select ename, empno from emp, and obtain a reference to an OraDynaset ( employees ).

 Set Employees = EmployeeDataBase.CreateDynaset("select ename, empno  from emp", &H0&) 

The example then loops through the result set and displays the value of the ename and empno columns .

 Set EmployeeRecord = Employees.Fields while  Employees.EOF == FALSE        MsgBox "Name: " & EmployeeRecord("ename").Value & "Emp #: " & EmployeeRecord("empno").Value    Employees.MoveNext Wend 

EmployeeRecord is a reference to the OraFields collection interface. It contains all the columns of the current row in the OraDynaset and provides methods for accessing attributes of the individual columns (OraField) within the current row.

In the previous example, the Value property of the ename and empno columns are displayed. EmployeeRecord("ename") and & EmployeeRecord("empno") return references to an OraField object that corresponds to the ename and empno columns in the OraDynaset.

Another way to reference a column is to use the position of the column (such as EmployeeRecord(0) for ename and EmployeeRecord(1) for empno). This method executes slightly faster.

An alternative way of accessing column attributes is demonstrated in the following example. The process of getting a reference to the OraFields collection interface, using it to get a reference to the OraField, and then obtaining its value is combined.

 while  NOT Employees.EOF        MsgBox "Name: " & Employees!ename & "Emp #: " & Employees!empno    Employees.MoveNext Wend 

Page 968

Employees!ename and Employees!empno yield the values of the columns ename and empno, respectively.

What happens behind the scene is as follows . VB first gets a reference to the Fields collection property (OraFields) of Employees. The OraField interface for ename is then retrieved from this collection and its value property is obtained.

Prior to Release 2.2 of OO4O, this coding technique would result in significantly slower execution, particularly in cases where a large number of rows were browsed. Version 2.2 resolves this issue by handling access to the Fields collection property in an OraDynaset much more efficiently . It is still more efficient, however, to obtain a reference to the Fields property once, and access its property repeatedly. The difference is that in Version 2.2 the performance penalty will not be that severe.

EOF is an attribute of the OraDynaset and is set to true if you have traversed past the last rowfalse otherwise .

The MoveNext method is one of the navigational methods in the OraDynaset interface that sets the current row to one past the current position. Other navigational methods include MoveFirst, MoveLast, MovePrevious, MoveTo, MovePreviousn, MoveNextn, and MoveRel.

An OraDynaset interface also provides methods for updating and deleting rows that are retrieved from a base table or view that can be updated. In addition, it provides an easy way for inserting new rows. These methods are discussed later in this chapter.

The CreatePlsqlDynaset Method

PL/SQL cursor variables were introduced with Release 7.2 of the Oracle server and are references to cursors defined and opened in PL/SQL.

The following is the definition of a PL/SQL procedure that returns a cursor variable (EmpCursor):

 procedure GetEmployees(EmpCursor in out empcur) is begin     open EmpCursor for select * from emp; end GetEmployees; 

The following script executes the preceding stored procedure and gets a reference to an OraDynaset interface that represents the cursor variable returned:

 `Create OraDynaset based on "EmpCursor" created in stored procedure.  Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData (:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&) `Display the names of all the employees while not Employees.EOF    MsgBox Employees!ename wend 

Version 2.2 of OO4O, which is in beta at the time of this writing, will allow multiple cursor variables to be returned from stored procedures and represented as OraDynasets.

Page 969

The ExecuteSQL and CreateSQL Methods

The ExecuteSQL and CreateSQL methods may be used to execute SQL statements and PL/SQL blocks. You may use them to execute Data Definition Language (DDL) statements (such as CREATE, DROP, and GRANT), PL/SQL stored procedures or anonymous blocks, or any data manipulation language (DML) statements such as SELECT, INSERT, UPDATE, or DELETE.

The following example uses the ExecuteSQL method to execute an employee-friendly update query that updates everybody's salary in the emp table by $1,000:

 Dim OracleDBSession As Object Dim EmployeeDatabase As Object `Get the OraSession Interface Set OracleDBSession = CreateObject("OracleInProcServer.XOraSession") `Get an OraDatabase interface. Set EmployeeDatabase = OracleDBSession.OpenDatabase("ExampleDB", "scott/tiger", &H0&) `Give everyone a decent raise in salary EmployeeDatabase.ExecuteSQL ("update emp set sal = sal + 1000") 

Another way to do this is to use the CreateSQL method that was introduced in Version 2.1, which is essentially an improved version of the ExecuteSQL method.

 `Execute the query set SqlStatement = EmployeeDatabase.CreateSQL ("update emp set sal = sal + 1000") 

Both ExecuteSQL and CreateSQL execute the update statement given. The difference is that CreateSQL returns a reference to an OraSqlStmt interface, in addition to executing the statement. This interface can later be used to execute the same query via the Refresh method. Because the query has already been parsed by the server, subsequent execution of the same query will result in faster execution, especially if bind parameters are used.

Using Parameters

The example in this section executes two update queries using the same OraSqlStmt reference to transfer JONES from Research(deptno = 20) to Sales(deptno = 30) and CLARK from OPERATIONS(deptno = 10) to Research.

Prior to running this sample in Visual Basic, make sure the ORACONST.TXT file is included in your project. This file can be found in the directory where OO4O is installed and contains values for all constants, such as ORAPARM_INPUT, that are used in the examples that follow:

 Dim OracleDBSession As Object Dim EmployeeDatabase As Object Dim TransferQuery As Object `Create the OraSession interface Set OracleDBSession = CreateObject("OracleInProcServer.XOraSession") 
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