0970-0973

Previous Table of Contents Next

Page 970

 `Connect to the ExampleDB database Set EmployeeDatabase = OracleDBSession.OpenDatabase("ExampleDB", "scott/tiger", &H0&) `Create an input parameter named "ENAME" and set its initial value to "JONES" EmployeeDatabase.Parameters.Add "ENAME", "JONES", ORAPARM_INPUT `Create the input parameter "DEPTNO" with the initial value set to 30 (SALES) EmployeeDatabase.Parameters.Add "DEPTNO", 30, ORAPARM_INPUT `Update Jones's record. Set TransferQuery = EmployeeDatabase.CreateSQL("update emp set deptno = :DEPTNO  where ename = :ENAME", &H0&) `Set up the "ENAME" parameter for "CLARK" EmployeeDatabase.Parameters("ENAME").Value = "CLARK" `Set up the "DEPTNO" parameter for "CLARK". He is going to RESEARCH EmployeeDatabase.Parameters("DEPTNO").Value = 20 `Execute the query for CLARK TransferQuery.Refresh 

Parameters are used to supply data values for the placeholders used in the SQL statements at runtime. SQL statements may include DELETE, INSERT, SELECT, UPDATE, or PL/SQL blocks.

To define a parameter, use the OraParameters interface. This interface is obtained by referencing the Parameters property of an OraDatabase interface.

OraParameters contains a collection of OraParameter interfaces and provides methods for adding, removing, and obtaining references to the parameters it contains.

The following statement adds an input parameter to the Parameters collection contained in the EmployeeDatabase:

 EmployeeDatabase.Parameters.Add "ENAME", "JONES", ORAPARM_INPUT 

"ENAME" is the name of the parameter and must be the same as the name of the placeholder in the SQL statement (such as "ENAME" in our example). "JONES" is provided as the initial value, and ORAPARM_INPUT is a hint to OO4O that the parameter will be used as an input parameter in the SQL statement.

If no initial value is provided, the datatype (server type) of the parameter must also be specified. An alternative way of writing this would have been

 EmployeeDatabase.Parameters.Add "ENAME", 0, ORAPARM_INPUT EmployeeDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2 

Parameters in the collection can also be accessed by using a subscript such as EmployeeDatabase.Parameters(0).

Page 971

Similarly, the "DEPTNO" parameter is added to the collection by the statement

 EmployeeDatabase.Parameters.Add "DEPTNO", 30, ORAPARM_INPUT 

The ServerType property of this parameter is NUMBER and is determined by the type of the initial value passed (30 in this case).

We now execute the UPDATE statement using the parameter values of the "ENAME" and "DEPTNO" parameters:

 Set TransferQuery = EmployeeDatabase.CreateSQL("update emp set deptno = :DEPTNO  where ename = :ENAME", &H0&) 

The following lines of code set new values for the parameters and execute the same query again by invoking the Refresh method of the OraSqlStmt interface (TransferQuery):

 `Set up the "ENAME" parameter for "CLARK" EmployeeDatabase.Parameters("ENAME").Value = "CLARK" `Set up the "DEPTNO" parameter for "CLARK". He is going to RESEARCH EmployeeDatabase.Parameters("DEPTNO").Value = 20 `Execute the query for CLARK TransferQuery.Refresh 

Using parameters in conjunction with OraSqlStmts for SQL statements that are frequently executed is a more optimized method than using the ExecuteSql statement repeatedly.

When the Refresh method of OraSqlStmt is executed, the statement no longer needs to be parsed by the database server. In application servers such as Web servers in which the same queries with different parameters values are executed frequently and for a long period of time, there can be significant savings in Oracle database server processing.

Using parameters and placeholders also allows native database types (such as NUMBER) to be sent to the server in their native form, thus eliminating the conversion step and reducing the number of bytes sent over the network.

Parameters (only input) and placeholders can also be used with the CreateDynaset method as shown in the following example:

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

Parameter Arrays

Parameter arrays (OraParamrArray) are another type of parameter that OO4O provides. Let's look at an example that uses them.

This example creates a table with one column (value int) and inserts 1,000 rows in it with values from 0 to 999:

Page 972

 Dim InputArray As Object Dim OutPutArray As Object Dim I As Integer `Create the table OraDatabase.ExecuteSQL ("create table numbers (value int)") ` Create the parameter array OraDatabase.Parameters.AddTable "INPUT_VALUES", 1, 3, 1000 Set InputArray = OraDatabase.Parameters("INPUT_VALUES") `Fill up the parameter array For I = 0 To 999  InputArray.put_Value I,I Next I `Insert a 1000 rows Set OraSqlStmt = OraDatabase.CreateSQL("insert into numbers values(:INPUT_VALUES", &H0&) 

AddTable is a method of the OraParameters collection interface. "INPUT_VALUES" is the name of the parameter array, 1 states that it is an input parameter, 3 is the datatype (INT), and 1000 is the size of the array. There is also a fourth optional argument (element size ) that is valid for only character and string type table (array) parameters.

The following statement gets a reference to this parameter array interface:

 Set InputArray = OraDatabase.Parameters("INPUT_VALUES") 

The put_Value method of this interface is then used to insert values into the array:

 For I = 0 To 999  InputArray.put_Value I,I Next I 

The first argument to the put_Value method is the position, and the second argument is the value to be inserted at that position.

The CreateSQL method then executes the INSERT statement using the values in the parameter array:

 Set OraSqlStmt = OraDatabase.CreateSQL("insert into numbers values(:INPUT_VALUES", &H0&) 

You may use parameter arrays to fetch, update, insert, or delete multiple rows in a table. Using parameter arrays for manipulating multiple rows is more efficient than executing multiple queries that operate on individual rows.

Executing PL/SQL Blocks

PL/SQL is a procedural language extension to SQL in Oracle. The extensions are constructs such as variable declaration, loop, assignment, flow of control (such as IF-THEN-ELSE) statements and exception handling.

Page 973

PL/SQL blocks in OO4O are typically used to execute stored procedures or stored functions. CreateSQL and ExecuteSql are the methods most often used to do this.

The CreatePlsqlDynaset introduced in Version 2.1 can also be used to execute a stored procedure that returns a cursor variable. Cursor variables were introduced in Version 7.2 of Oracle and are used to return result sets from stored procedures or functions. In Version 2.2, multiple cursor variables can be returned from stored procedures in the form of parameters that are of type OraDynaset.

Let's look at an example that executes a PL/SQL block that calls a stored procedure using the CreateSQL method in OO4O. The procedure takes a department number as input and returns the name and location of the department.

The following is the script for creating the stored procedure in the employee database. Use either SQL*Plus or the ExecuteSql method in OO4O to run the script:

 create or replace package Department as PROCEDURE GetDeptName (inDeptNo IN NUMBER, outDeptName OUT VARCHAR2, outDeptLoc OUT VARCHAR2); end Department; create or replace package body Department as PROCEDURE GetDeptName(inDeptNo IN NUMBER, outDeptName OUT VARCHAR2, outDeptLoc OUT VARCHAR2) is BEGIN  SELECT DNAME, LOC into outDeptName, outDeptLoc from DEPT WHERE DEPTNO = inDeptNo; END; end Department; 

Here is the VB code that executes the procedure previously created to get the name and location of the department, where DEPTNO is 10:

 `ORACONST.TXT has been included in the project. empDatabase.Parameters.Add "DEPTNO", 10, 1 empDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER empDatabase.Parameters.Add "DNAME", 0, 2 empDatabase.Parameters("DNAME").ServerType = ORATYPE_VARCHAR2 empDatabase.Parameters.Add "DLOC", 0, 2 empDatabase.Parameters("DLOC").ServerType = ORATYPE_VARCHAR2  Set PlSqlStmt = OraDatabase.CreateSQL("Begin Department.GetDeptname (:DEPTNO, :DNAME, :DLOC); end;", &H0&) `Display Department name and loation MsgBox empDatabase.Parameters("DNAME").Value & empDatabase.Parameters("DLOC").Value 

As you might have noticed, there is no difference between how you execute SQL statements and PL/SQL blocks when using the ExecuteSQl or CreateSQL methods.

Previous Table of Contents Next