Oracle provides us with its own native client software that sits above the Oracle Call Interface, as mentioned earlier, allowing us to communicate with an Oracle database using a COM/OLE component.
Oracle Objects for OLE, or OO4O as it is usually abbreviated to, allows us to execute SQL and PL/SQL statements in a native "pass-through" format. This means we can make use of all Oracle data types as well as additional features, such as bind variables.
Bind variables are an efficient way to
execute the same SQL statement with differing parameters
without Oracle having to re-parse the statement each time .
Unfortunately, it won't make that much
of a performance difference to our web page, as we will only execute the statement
twice and then
close our database connection. However, this feature is ideal for client/server
applications that maintain the database connection until the application is
closed. We will be discussing bind variables in the following examples.
Unfortunately, by using OO4O we'll have to forfeit the usual methods found in ADO. However, OO4O does implement the same, or very similar methods, so the learning curve is not that steep.
Version 2.3 of OO4O,which shipped with Oracle8, has the following object model:
|
|
You'll find that the later version, 8.1, as shipped with Oracle8i, has a similar model with a number of extra objects:
|
|
The table gives a brief description of each object:
|
Name |
OO4O |
Description |
|
OraSession |
2.3 |
This is the first top-level object needed before we connect to an Oracle database. |
|
OraServer |
8.1 |
Represents a physical connection to an Oracle database server instance. The OpenDatabase function can be used to create client sessions by returning an OraDatabase object. |
|
OraConnection |
2.3 |
Returns various pieces of user information about the current OraSession object. It can be shared by many OraDatabase objects, but each OraDatabase must exist in the same OraSession object. |
|
Name |
OO4O |
Description |
|
OraDatabase |
2.3 |
Represents a single login to an Oracle database. Similar to the ADO Connection object. OraDatabase objects are returned by the OraSession.OpenDatabase function. |
|
OraDynaset |
2.3 |
Similar to an ADO Recordset object. Represents the results retrieved by a call to the OraDatabase.CreateDynaset function. |
|
OraField |
2.3 |
Represents a column of data within an OraDynaset object. Similar to the ADO Field object of an ADO Recordset. |
|
OraClient |
2.3 |
Automatically created by OO4O as needed. Maintains a list of all active OraSession objects currently running on the workstation. |
|
OraParameter |
2.3 |
Represents a bind variable for a SQL statement or PL/SQL block to be executed using the OraDynaset object. Similar to the Parameter object in an ADO Command object. |
|
OraParamArray |
2.3 |
Allows arrays of parameters to be set for the OraDatabase.Parameters function. |
|
OraSQLStmt |
2.3 |
Represents a single SQL statement. Typically used with SQL statements that include bind variables to improve performance as Oracle does not have to parse the statement each time it is executed. Can be thought of as conceptually similar to the ADO Command object. |
|
OraMetaData |
8.1 |
Returns meta data to describe a particular schema such as column names. Similar to the SQL Server DMO object library. See the meta data example below. |
|
OraAQ |
8.1 |
The CreateAQ method of the OraDatabase returns an OraAQ object. This provides access to Oracle's Advanced Queuing message system that allows messages to be passed between applications, much like MSMQ. |
We are going to create a sample ASP script that executes a SQL statement to return a list of employees for a specific department number using bind variables. This example, which is compatible with both the 2.3 and 8.1 versions of OO4O, will use OraSession, OraDatabase, OraDynaset and OraFields objects, as they are the most commonly used objects in OO4O.
Using Bind Variables
Our script contains simple VBScript function, CreateEmployeeTable, declared at the bottom of the script, to handle the refreshing of the Parameters collection and writing out of the HTML results table each time .
|
|
<% Option Explicit
%>
<HTML>
<HEAD><TITLE>Oracle
Data Access</TITLE>
<BODY>
<CENTER>
<H2>
Oracle
Data Access<BR>
Using
'OO4O'<BR>
Employee
Bind Variable Demo<BR>
</H2>
</CENTER>
<%
Const cAccountingDeptCode
= 10
Const cResearchDeptCode
= 20
Dim varOraSession
Dim varOraDatabase
Dim varOraDynaset
Dim varSQL
We've made use of two constants to store the department code for the Accounting and Research departments. These are passed into our objDatabase.Parameters object for each department.
Each of the remaining variables defined stores a reference to the OraSession, OraDatabase, and OraDynaset objects respectively. Again we use a variable called varSQL to store our nicely formatted SQL statement:
Set varOraSession =
Server.CreateObject("OracleInProcServer.XOraSession")
Set varOraDatabase =
varOraSession.OpenDatabase("Oracle8_dev",
_
"scott/tiger", 0)
The only object that we have to create ourselves explicitly is the OraSession object as all other objects are created from other existing objects. We use the familiar Server.CreateObject to create an instance of the OO4O component whose internal ProgID is OracleInProcServer.XOraSession.
The OpenDatabase function returns an OraDatabase object, which in our case is the scott account on the Oracle8_dev service. OpenDatabase is called in the following way:
Set oraDatabase=oraSession.OpenDatabase(db_name,
connectstring, options)
Where db_name is the service name to connect to, connectstring is the standard Oracle connection format of "username/password", and options is a collection of bit flags to indicate the mode in which the database should be opened. In our case we are passing in 0 to indicate that we want the database to be opened in the default mode, which means that any fields that we do not explicitly set a value for using the AddNew or Edit methods will be set to Null (this will incidentally override any server column default values!), rows will be locked as soon as the Edit method is called, and that non-blocking SQL functionality will not be used. A non-blocking call provides the same concept as an asynchronous ADO call in which the calling application does not have to wait until the server completes a request before continuing.
Some client installations
may cause the Oracle error "Credential Retrieval Failed" whenever
you try to connect. If this is the case then your client installation is trying
to use a different form of client authentication to that of the server. Client
authentication should be set to none, rather than native (NTS), so edit your
sqlnet.ora
file, located in the same folder as tnsnames.ora,
and replace the line
SQLNET.AUTHENTICATION_SERVICES=(NTS)
with
SQLNET.AUTHENTICATION_SERVICES=(NONE).
Response.Write "OO4O
Version:" & varOraSession.OIPVersionNumber & "<BR>"
&_
"Connect: " & varOraDatabase.connect & "<BR>"
& _
"DatabaseName: " & varOraDatabase.DatabaseName &
"<BR>" & _
"Oracle Version: " & varOraDatabase.RDBMSVersion &
"<P>"
Just for our benefit we write out some information about the version of OO4O and the Oracle server that we are connecting to:
varSQL = "SELECT
empno, ename, job, hiredate," & _
" sal, comm" & _
" FROM emp" & _
" WHERE deptno = :deptnoparam" & _
" ORDER BY ename"
varSQL stores the SQL statement to execute using an Oracle bind variable, :deptnoparam. As we hinted at above, using bind variables can improve the performance of data access when you have the same SQL statement to execute, but need to alter a parameter. Each time Oracle executes a statement it has to go through the statement to understand how it should be executed. By using the bind variable, we can get Oracle to parse it only once. Remember, though, that this only exists for the life of your OraDatabase object – which should only be kept around for the life of the script and not in an ASP Session variable .
varOraDatabase.Parameters.Add
"deptnoparam", 0, 1
Before we can tell Oracle about the bind variable we need to add it to the OraDatabase object's Parameters collection using the Add command, which is called in this way:
OraParameters.Add Name,
Value, IOType
The Name argument is a string that represents the name of the parameter to add, and it must match that of the bind variable defined in the SQL statement, Value is a variant, IOType indicates the direction of this parameter:
|
Enumerator |
Value |
Description |
|
ORAPARM_INPUT |
1 |
Use as an input variable only |
|
ORAPARM_OUTPUT |
2 |
Use as output variable only |
|
ORAPARM_BOTH |
3 |
For variables that are both input and output |
IOType is much like the adDirection enumerator used in the ADO Command object's Parameters collection to set the direction of SQL parameters.
In our example we passed in a value of 0 as a default because we don't actually have a value to use and a 1 to indicate that is for input use only.
Set varOraDynaset =
varOraDatabase.CreateDynaset(varSQL, &H4)
Now it's just a case of calling the OraDatabase.CreateDynaset to retrieve an OraDynaset object. CreateDynaset is called in this way:
Set oradynaset = oradatabase.CreateDynaset(sql_statement,
options)
Where sql_statement is the SQL to execute and options contains a bit flag of settings to define how the OraDynaset object behaves, such as whether it is updateable, or to cache data on the client. In our case we're passing in the hex value &H4 to indicate that it should be opened in read-only mode as we only want to display some data.
Behind the scenes, Oracle parses the statement ready for execution. It doesn't actually fetch any data until we set the deptnoparam parameter's Value property in OraDatabase.Parameters collection and ask OO4O to refresh the dynaset using OraDynaset.Refresh:
Response.Write "<B>Accounting
Department Employees:</B><BR>"
CreateEmployeeTable
cAccountingDeptCode
Response.Write "<BR><B>Research
Department Employees:</B><BR>"
CreateEmployeeTable
cResearchDeptCode
Here we can see our VBScript procedure CreateEmployeeTable being called to set the value for each of the department number parameters to create a nicely formatted table, as defined below:
Set varOraDatabase =
Nothing
Set varOraDynaset =
Nothing
Set varOraSession =
Nothing
As always, we close down all of our objects as soon as possible in order to save server resources. Now to the CreateEmployeeTable function:
Sub CreateEmployeeTable(ByVal
varDeptCode)
varOraDatabase.Parameters("deptnoparam").Value
= varDeptCode
varOraDynaset.Refresh
CreateEmployeeTable is passed the required department code, which it binds to the original SQL statement's bind variable deptnoparam. Each time you specify a new value for a bind variable, you must call the Refresh method to fetch the new data.
Calling Refresh cancels all record edit operations that may have been pending through the Edit and AddNew methods, executes the SQL statement, and then moves to the first row of the resulting dynaset.
Response.Write
"<TABLE BORDER=1><TR>" & _
" <TD>Number</TD>" & _
" <TD>Employee</TD>" & _
" <TD>Job</TD>" & _
" <TD>Hire Date</TD>"
& _
" <TD>Salary</TD>" & _
" <TD>Commission</TD>" & _
"</TR>"
Do While Not
varOraDynaset.EOF
So now we create the TABLE tag and loop through the records until we come to the end of file, EOF, exactly as we would with the ADO Recordset.EOF property.
Response.Write
"<TR>" & _
" <TD>" & varOraDynaset.Fields("empno").Value
& _
"</TD>" & _
" <TD>" & varOraDynaset.Fields("ename").Value
& _
"</TD>" & _
" <TD>" & varOraDynaset.Fields("job").Value
& _
"</TD>" & _
" <TD>" & varOraDynaset.Fields("hiredate").Value
& _
"</TD>" & _
" <TD>" & varOraDynaset.Fields("sal").Value
& _
"</TD>" & _
" <TD>" & varOraDynaset.Fields("comm").Value
& _
" </TD>"
& _
"</TR>"
The Fields collection returns a named list of columns for the current record, which we use to create a new table row for each record. We haven't shown it, but as with an ADO Recordset object, the Fields property is the default value, and for a Field object, the Value is the default so varOraDynaset.Fields ("sal").Value is equal to varOraDynaset("sal"). For better performance you should use the latter.
varOraDynaset.MoveNext
Loop
Response.Write
"</TABLE>"
End Sub
%>
</BODY>
</HTML>
As with ADO, the MoveNext method moves to the next record.
Getting Meta Data
For our final look at OO4O we will use the OraMetaData object found in version 8.1 to retrieve a list of attributes for the emp table within the scott schema. As we said earlier, OraMetaData can retrieve all sorts of information about a schema, by calling the OraDatabase object's Describe("schema_name") function to return an OraMetaData object. The OraMetaData object returns a collection of OraMDAttribute objects that actually describe the data found and contains the following methods and properties :
|
Name |
Description |
|
Count |
Returns the number of OraMDAttribute objects contained in the collection. |
|
Type |
The type of object described, for example ORAMD_TABLE which enumerates to the value 1 for an Oracle table. |
|
Attribute(pos) |
Returns an OraMDAttribute object at the specified position. This can be the 0 based index or a string name, such as "ColumnList". |
To make things slightly complicated the OraMDAttribute object has a property called IsMDObject that returns True if the Value property contains yet another OraMetaData object. This allows you to recursively search through a hierarchy of OraMetaData objects. If it returns False then Value contains a string representation of the item .
|
|
The following code produces the screenshot shown above. We start off with the usual header:
<%Option Explicit%>
<HTML>
<HEAD><TITLE>Oracle
Data Access</TITLE>
</HEAD>
<BODY>
<CENTER>
<H2>
Oracle
MetaData Example<BR>
Using
'OO4O'<BR>
</H2>
</CENTER>
The cTableName constant contains the name of the table that we want to describe. As usual we are using the OraSession object to hold a reference to OO4O and OraDatabase to connect to our Oracle8i server:
<%
Const cTableName = "emp"
Dim objOraSession
Dim objOraDatabase
Dim objOraMetaData
Dim objOraMDAttribute
Dim objColumnList
Dim objiColCount
Dim objColumnDetails
objOraMetaData is used to store our top-level OraMetaData object returned by the Describe function and objOraMDAttribute stores the item name "ColumnList" from the objOraMetaData object, which represents the list of columns in the emp table. The actual Value for objOraMDAttribute is stored in objColumnList.
Set objOraSession =
CreateObject("OracleInProcServer.XOraSession")
Set objOraDatabase =
objOraSession.OpenDatabase("Oracle8i_dev",
_
"scott/tiger", 0)
Set objOraMetaData =
objOraDatabase.Describe(cTableName)
Set objOraMDAttribute
= objOraMetaData("ColumnList")
We connect to the Oracle8i_dev service and call the OraDatabase object's Describe function to return our first OraMetaData object to objOraMetaData for the emp table. objOraMetaData will contain a collection of OraMDAttribute items, so we pass in ColumnList to retrieve the list of column names.
If objOraMDAttribute.IsMDObject
Then
Response.Write
"Column definition for table <B>" & cTableName & _
"</B><P>" & _
"<TABLE BORDER=1><TR>" & _
"<TD>Name</TD><TD>Type</TD><TD>Size</TD>"
& _
"<TD>IsNull</TD><TD>Precision</TD>"
& _
"<TD>Scale</TD>" & _
"</TR>"
Set objColumnList
= objOraMDAttribute.Value
Even though it's not strictly necessary with this example, we check the IsMDObject property to see if the Value property contains another objMetaData object. In our case, it will always be True, since we asked for the list of column names, which is another objMetaData object.
IsMDObject is a property so if you try to call it as a function by adding () to the end you'll get runtime error 'Object doesn't support this property or method' .
To make the code easier to read and run quicker we transfer the Value property into a new variable objColumnList:
For iColCount
= 0 To objColumnList.Count - 1
Set
objColumnDetails = objColumnList(iColCount).Value
Response.Write
"<TR>" & _
"<TD>" & objColumnDetails("Name")
& "</TD>" & _
"<TD>" & objColumnDetails("DataType")
& "</TD>" & _
"<TD>" & objColumnDetails("DataSize")
& "</TD>" & _
"<TD>" & objColumnDetails("IsNull")
& "</TD>" & _
"<TD>" & objColumnDetails("Precision")
& "</TD>" & _
"<TD>" & objColumnDetails("Scale")
& "</TD>" & _
"</TR>"
Next
Response.Write
"</TABLE>"
End If
Now it's just a case of moving through the zero-based collection of column details and writing out the value for each item. We finish off by shutting down our objects:
Set objColumnDetails
= Nothing
Set objColumnList =
Nothing
Set objOraMDAttribute
= Nothing
Set objOraMetaData =
Nothing
Set objOraDatabase =
Nothing
Set objOraSession =
Nothing
%>
</BODY>
</HTML>
That covers our introduction into the common objects you'll come across in OO4O. OO4O offers a rather flexible approach to connecting to an Oracle database and also provides us with additional PL/SQL functionality not available through ADO, such as the use of input arrays for stored procedures.
So, which one should you use in your ASP applications? Unfortunately, there is no simple answer. Each method claims to be faster than the next whilst providing support for additional functionality. It really does pay to try each of the methods in your own environment before committing to any particular one.
Comments