OLE DB Provider & ODBC
The OLE DB Provider for Oracle supports
most of the Oracle8 data types:
|
Data Type
|
Supported
|
Data Type
|
Supported
|
|
BFILE
|
|
LONG
RAW
|
Yes
|
|
BLOB
|
|
NCHAR
|
|
|
CHAR
|
Yes
|
NCLOB
|
|
|
CLOB
|
|
NUMBER
|
Yes
|
|
DATE
|
Yes
|
NVARCHAR2
|
|
|
FLOAT
|
Yes
|
RAW
|
Yes
|
|
INTEGER
|
Yes
|
VARCHAR2
|
Yes
|
|
LONG
|
Yes
|
MLSLABEL
|
|
This table shows that many of the standard
data types are supported but those such as the LOB
(Large Object) and object-based extensions are not supported.
The provider is a native provider,
in that it accesses the Oracle's API directly rather than through ODBC. This
provides us with generally the best performance when compared to other
methods of connecting to Oracle, but does mean that some functionality is not
available.
In order to use the provider, you must
set its name in the ConnectionString
property of the ADO Connection
object or as the ConnectionString
argument to the Open
method. As with any provider for ADO, unpredictable results can occur if you
specify the name of the provider in more than one place.
Let's start by connecting to the Oracle
database using the scott
username to execute two simple built-in Oracle functions
to retrieve the system date, sysdate,
and current username, user:
Create a new ASP script called
GetDate.asp:
<% Option Explicit
%>
<HTML>
<HEAD><TITLE>Oracle
Data Access</TITLE></HEAD>
<BODY>
<CENTER>
<H2>
Oracle
Data Access<BR>
Using
'OLE DB Provider for Oracle'<BR>
SysDate
and User Name Demo<BR>
</H2>
</CENTER>
<%
Dim objConnection
Dim objRecordset
Set objConnection =
Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString
= "Provider=MSDAORA;Data Source=Oracle8_dev;"
& _
"User ID=scott; Password=tiger;"
.Open
Response.Write
"ADO Provider=" & .Provider & "<BR>"
Set objRecordset
= .Execute("SELECT sysdate, user FROM dual")
End With
We use the Connection
object's ConnectionString
property to tell ADO how to connect to our Oracle database before calling the
Open
command to attempt to connect to the database. Don't forget that the Data Source
property, Oracle8_dev,
is the service
name that we created earlier, rather than the actual
machine name – but in my case, both are actually the same value.
The Provider
section tells ADO to use the OLE DB Provider for Oracle. We can use either
the class name of the provider, in this case MSDAORA,
or the full provider name: 'OLE
DB
Provider for
Oracle'.
As we want to use the scott
account, we need to set the User
ID
and Password accordingly.
Our Oracle8 server is located
on a server called Oracle8_dev.
You'll have to change this to reflect your own Oracle database server.
If you've not used the With...End
With
statement, it serves as a way to call multiple methods on a single object without
having to refer to the name explicitly every time. It makes your code easier
to read and actually runs slightly faster as the ASP processor doesn't have
to do extra processing to establish the address of the objConnection
object.
By calling the Open
method, we should get a connection to the Oracle database. By way of a confirmation,
we write out the name of the Provider
property. This shows us the name as defined in the Registry along with any version
number if there are multiple versions installed on the server.
The Execute
method returns back a Recordset
representing the records that were fetched from the
database, in this case a single record with a column containing the current
system date and the current user name. The argument passed to Execute
is the command that we want Oracle to run for us.
Notice the word dual
in our SELECT
statemen t? Oracle does not allow you to execute a
SELECT
statement without an accompanying FROM
clause; dual
is a logical pseudo-table, available to all accounts, provided for that purpose.
It is not a physical table that you can alter.
Response.Write
"System Date=" & objRecordset.Fields("sysdate") &
"<BR>" & _
"User=" & objRecordset("user")
Set
objConnection = Nothing
Set
objRecordset = Nothing
%>
</BODY>
</HTML>
We finish off by reading the Fields
collection of our objRecordset
object to get the value for the sysdate and user
functions. In the case of the user
field we've left out the .Fields
statement as this is the Recordset
object's default property, though you can make your code run faster if you use
it.
There's no need to navigate through the
objRecordset,
as there will only be one record returned. With any objects that we create in
our scripts, it's always a good idea to shut them down explicitly as soon as
possible using the Set
...
=
Nothing
statement in order to free up server resources.
As we mentioned earlier,
if you received the Oracle error message ORA-12545:
connect failed because target host or object does not exist
then you need to recheck the values of your host name, port number and SID
that were entered when your created the new service name using Net8 Easy Config.
That was a relatively easy example, so
let's have a look at a more complex statement in which we return a number of
records. The scott
schema comes with four sample tables that you can look at yourself. The tables
opposite represent an employee's bonus and salary tracking system:
This isn't the best schema
that Oracle could have used as their pre-configured sample database. The SALGRADE
and BONUS
tables are not referenced by any other tables and contain no primary keys.
|
Table Name
|
Purpose
|
|
DEPT
|
Stores a list of
department names
|
|
SALGRADE
|
Stores a list of
salary grades
|
|
EMP
|
Stores a list of
employees
|
|
BONUS
|
Stores a list of
employee bonuses
|
Our example ASP script will be using the
DEPT
and EMP
tables to show a list of all employees sorted by their name (later
on we'll be using them in our sample application):
The code used to produce the previous
screenshot looks like this:
<% Option Explicit
%>
<HTML>
<HEAD><TITLE>Oracle
Data Access</TITLE></HEAD>
<BODY>
<CENTER>
<H2>
Oracle
Data Access<BR>
Using
'OLE DB Provider for Oracle'<BR>
Employee
List Demo<BR>
</H2>
</CENTER>
<%
Dim objConnection
Dim objRecordset
Dim varSQL
Set objConnection =
Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString
= "Provider=MSDAORA; Data Source=Oracle8_dev;
" & _
"User ID=scott; Password=tiger;"
.Open
Response.Write
"ADO Provider=" & .Provider & "<P>"
We start off as before by defining two
variables for our Connection
and Recordset
objects and then connect to the database using the scott/tiger
combination. We've added a new variable, varSQL,
to hold a nicely formatted SQL statement:
varSQL = "SELECT
emp.empno, emp.ename, emp.job, emp.hiredate," & _
" emp.sal, emp.comm, dept.dname,
dept.loc" & _
"
FROM emp, dept" & _
" WHERE emp.deptno = dept.deptno" & _
" ORDER BY emp.ename"
Set objRecordset
= .Execute(varSQL)
End With
The SQL statement joins the employee table,
EMP, to the department,
DEPT,
to return a list of employees and their departments. Again we use the Execute command
to return back a Recordset
of data:
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>" & _
" <TD>Department</TD>" & _
" <TD>Location</TD>" & _
"</TR>"
We use Response.Write
to write out the start of our table of results:
Do While Not objRecordset.EOF
Response.Write
"<TR>" & _
" <TD>" & objRecordset("empno")
& "</TD>" & _
" <TD>" & objRecordset("ename")
& "</TD>" & _
" <TD>" & objRecordset("job")
& "</TD>" & _
" <TD>" & objRecordset("hiredate")
& "</TD>" & _
" <TD>" & objRecordset("sal")
& "</TD>" & _
" <TD>" & objRecordset("comm")
& " </TD>" & _
" <TD>"
& objRecordset("dname") & "</TD>"
& _
" <TD>" & objRecordset("loc")
& "</TD>" & _
"</TR>"
objRecordset.MoveNext
Loop
Response.Write "</TABLE>"
Now it's just a case of writing out each
record by retrieving the value for each column from the Fields
collection of our Recordset
object objRecordset
and moving to the next record using the MoveNext
method. We loop through using a Do
While...Loop
that will stop as soon as it gets to the end of the Recordset.
Some of the records in the comm
column contain a null value, so we add the HTML non-breaking space tag ( )
to ensure that the browser draws the cell border correctly.
Set objConnection =
Nothing
Set objRecordset = Nothing
%>
</BODY>
</HTML>
As with our previous example, it's a good
idea to explicitly close our objConnection
and objRecordset
objects as soon as we've finished with them.
We've now managed to connect to an Oracle8
database using the OLE DB Provider for Oracle to retrieve a single record of
the current system date and username and a full list of employees in the scott
database's emp
table. It is suggested that the OLE DB Provider for Oracle be used for the majority
of Oracle data access as it executes faster and supports Microsoft's new direction
in data access – OLE DB.
Microsoft ODBC Driver for Oracle
The Microsoft ODBC Driver for Oracle supports
the same set of Oracle8 data types as the OLE DB Provider for Oracle. When using
this driver with ADO, we are actually using the OLE
DB Provider for ODBC Drivers (MSDASQL), which
in turn uses the Microsoft ODBC for Oracle Driver.
Microsoft released the OLE DB Provider
for ODBC (MSDASQL) so that all existing ODBC-based applications could use the
new features found in OLE DB through ADO. When connecting to any data source
using ADO, this is the default provider that is used .
ODBC connection strings use the older
DRIVER=,
DSN=,
UID=,
PWD=
and SERVER=
(optionally in the place of DSN=) parameters to connect to a data source. Don't
forget that there must be a valid Data Source Name, DSN, registered through
the ODBC Data Source Administrator in the Administration Tools (or Control Panel)
if you are going to use the DSN
parameter .
Each time you connect to a
database using a DSN, ODBC must look through the Windows Registry in order to
retrieve connection details for your DSN. There may be some performance improvements
in your application if you use DSN-less connections, as the Windows Registry
is notoriously slow to access. If you do need to use DSNs, then remember to
use System DSNs rather than File DSNs as anonymous users, which your server
is more than likely to use, have access to them.
We are going to create a simple ASP script
that uses some of the principles we used with the OLE DB Provider for Oracle
to show a list of departments from the scott
database's dept
table.
The only real difference to this code
is the connection string used, so we'll just show that line of code:
.ConnectionString =
"Provider=MSDASQL;" & _
"DRIVER={Microsoft ODBC for Oracle}; " & _
"SERVER=Oracle8_dev;
UID=scott; PWD=tiger;"
We make use of the DRIVER
property to tell MSDASQL to use the Microsoft ODBC Driver for Oracle, SERVER
points to our database server, Oracle8_dev,
and we use UID
and PWD
rather than the User
ID
and Password
combination .
Notice that we didn't specify a DSN so
we don't have to create one, and although it's not actually necessary in this
case, we've specified the name of the Provider
to use.