Oracle is one of the most popular
databases in the world, also Active Server Pages (ASP) is a powerful server-side
scripting language widely used to build dynamic Web pages. There are many ASP
developers who wonder if they can use the ASP technology with Oracle
database to build a web application, E-commerce and E-business web sites or
internet management systems. The answer is YES! You can access Oracle using VB
to create Oracle Applications as well. Here, I will discuss with you how to
use ASP dealing with Oracle data.
Before
we start, there are a few things you need to know. The Oracle Objects for OLE (OO4O) method is one of
them. The OO4O is an Oracle middleware that allows
native access to Oracle from client applications using the Microsoft Object
Linking and Embedding (OLE) standard. Some of you may think that the ODBC
can be used. Surely, you can use the standard database access method developed
by Microsoft Corporation to access Oracle, but in my opinion the OO4O is better than
ODBC because the OO4O is thread safe and provides
full support for PL/SQL. PL/SQL stands for Procedural Language/SQL. It is an
Oracle extension of the SQL statement set which allows the developer to impose
flow control and logic design onto unstructured SQL command blocks. If you have
fully installed Oracle8i, I am sure the OO4O is
there for use already. If not, you can download it from Oracle web site.
The other thing is, we need know
two objects and one interface that Oracle developed for Visual Basic Development,
OraSession
and OraDynaset objects, and OraDatabase interface. The OraSession object manages
collections of OraDatabase, OraConnection, and OraDynaset used within an
application. It is the object created by the CreateObject ASP and not by an OO4O
method. The OraDatabase
interface represents a user session to an Oracle database and provides methods
for SQL and PL/SQL execution. Each of them has some of properties, and methods.
For instance, the OraDynaset has some of properties, such as BOF, EOF, Bookmark, Connection, and so on, and ten
methods, such as AddNew, Update, Delete, Edit, Refresh,
Clone, and so on.
Now, let's start working on
Oracle data using ASP technology.
Preparation What do you need?
1) Development and running
environments I am using are Oracle8i, IIS5.0, Windows2000 Professional.
2) Build a table into the
Oracle database, and called "MYTABLE1" something
like this.
ID (type: number)
User Name(type:
varchar2)
Phone(type:
varchar2)
Email(type:
varchar2)
100
Colin Tong
999-999-8888
colinjava@hotmail.com
111
John White
888-888-8888
johnw@yahoo.com
101
Don Wod
416-333-3344
donwod@test.com
Access and retrieve data 1) Instantiate OO4O Object, OraSession and interface OraDatabase for connecting to ORACLE. First of all, create the
OraSession Object by using CreateObject, then create the OraDatabase Object by
opening a connection to Oracle, as shown below.
<%
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("", _
"username/password", Cint(0))
%>
The "username" and
"password" are your relational database's user name and password.
2) Create an OraDynaset Object
to execute SQL statement. You may use either CreateDynaset or DbCreateDynaset to
create the recordset.
<%
'execute SQL
Set OraDynaset = OraDatabase.DbCreateDynaset( _
"select * from mytable1", cint(0))
%>
Edit data record We are going to use the methods
of OraDynaset to implement the editing
data purpose.
1) Create OraDynaset object
with SQL Statement
<%
'Create the OraDynaset Object for ID= fID record.
Set OraDynaset = OraDatabase.CreateDynaset(_
"select * from MYTABLE1 where ID= "& fID, cint(0))
%>
The fID is the value of
the ID field that you want to update or insert.
2) Execute
OraDynaset for updating or adding
<%
'update the field of the record(ID=fID) using Edit method.
'or use the AddNew to insert a new record
OraDynaset.Edit
OraDynaset.Fields("Phone").Value = fPhone
OraDynaset.Update
' remove the created session
Set OraSession = Nothing
%>
Delete data record Some of you might already know how
to delete record(s) from Oracle DB using OraDynaset if you really understand the methods
that we used (Edit, Update and AddNew) at above sections. Actually,
we simply use the method Delete of
OraDynaset for deleting.
<%
'Delete all records that with above condition.
OraDynaset.Delete
%>
Sample codes for search and update data records
from Oracle8i
1) Searching
<%
'*************************************************************
'RetrieveRecProc.asp -Retrieve records using OO4O in ASP
'Original Author: Colin Tong
'Modified Date: 9/26/2001
'Note: You are free to use this code, however, please keep
'the original author name.
'*************************************************************
%>
<%
'Declare variables as OLE Objects.
Dim OraSession
Dim OraDatabase
Dim OraDynaset
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
'Be sure your own username and password to access your Oracle db
Set OraDatabase = OraSession.OpenDatabase("", "user/password", _
Cint(0))
'Create the OraDynaset Object to execute SQL statement
Set OraDynaset = OraDatabase.DbCreateDynaset(_
"select * from mytable1", cint(0))
%>
<html><body>
<H3>Retrieve All Records in MYTABLE1 Table ( in Oracle)
Using oo4o</H3>
<table border=1 ID="Table1">
<%
Do While(OraDynaset.EOF = FALSE)
Response.Write("<tr><td>")
Response.write(OraDynaset.Fields("ID"))
Response.Write("</td><td>")
Response.write(OraDynaset.Fields("UserName"))
Response.Write("</td><td>")
Response.write(OraDynaset.Fields("Phone"))
Response.Write("</td><td>")
Response.write(OraDynaset.Fields("Email"))
Response.Write("</td></tr>")
OraDynaset.MoveNext
Loop
'remove OraSession
Set OraSession = Nothing
%>
</table>
<a href="javascript:window.history.go(-1)">
Back previous Page</a> |
<a href="index.html"> Back home Page</a>
</body></html>
2) Updating
<%
'**************************************************************
'UpdateRecProc.asp -Update a record using OO4O in ASP
'Original Author: Colin Tong 'Modified Date: 9/26/2001
'Note: You are free to use this code, however please keep
'the original author name.
'**************************************************************
%>
<%
'Declare variables as OLE Objects. Dim OraSession
Dim OraDatabase
Dim OraDynaset
'get field values from submitted form
fID = request.form("ID")
fUserName = request.form("UserName")
fPhone = request.form("Phone")
fEmail = request.form("Email")
'Create the OraSession Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle
Set OraDatabase = OraSession.OpenDatabase("", "user/password", _
Cint(0))
'Create the OraDynaset Object for ID= fID record
Set OraDynaset = OraDatabase.CreateDynaset(_
"select * from MYTABLE1 where ID= "& fID, cint(0))
'update the field of the record(ID=fID) using Edit method
Do While(OraDynaset.EOF = FALSE)
OraDynaset.Edit
OraDynaset.Fields("UserName").Value = fUserName
OraDynaset.Fields("Phone").Value = fPhone
OraDynaset.Fields("Email").Value = fEmail
OraDynaset.Update
OraDynaset.MoveNext
Loop
%>
<html><body>
<H3>Update A Record in MYTABLE1 Table (Oracle) Using oo4o</H3>
The record (ID=<%=fID%>) has been updated successfully!<br>
You can view the result <a href="RetrieveAllRec.asp"> here</a>
<p>
<a href="javascript:window.history.go(-1)"> Back previous Page</a>
&bnsp;&bnsp;
<a href="javascript:window.history.go(-2)"> Back home Page</a>
<%
'remove OraSession
Set OraSession = Nothing
%>
</body></html>
Now, you should know how to use
OO4O to deal with Oracle database in your ASP code.
Use Stored Procedures So far we have discussed how ASP pages
access Oracle database, and all SQL statements are embedded in ASP
pages. As most of you know, if the stored procedures have been used, it
will definitely make extracting data more efficient. I strongly
recommend you use PL/SQL Stored Procedures in the Database except embedding sql
in ASP code. Creating Stored Procedures in Oracle is beyond this topic, however,
I would like to introduce it next time, if number of visitors are interested in
it. Email me if you are interested in.
Because of time and the limit of
my own knowledge, some errors might be found on this article, you are welcome to
comment. Feel free to use any of the codes in this article, however, do so at
your own risk.
