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.
Comments