ASP 101 - Active Server Pages 101 - Web03
The Place ASP Developers Go!



Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates
ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
What is ASP?
VBScript Classes: Part 1 of N
Migrating to ASP.NET

QUICK TIP:
How to Debug an ASP Project with Visual Studio .NET
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


Make Your ASP Work With An Oracle Database

by Colin Tong

     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))
%>

     3) Retrieve data and remove created object

<%
  Do While(OraDynaset.EOF = FALSE) 
    Response.write(OraDynaset.Fields("ID"))
    Response.write(OraDynaset.Fields("UserName"))
    ... others ... 
    ... ...
    OraDynaset.MoveNext
  Loop
  'remove OraSession
  Set OraSession = Nothing
%>

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.

Comments are welcome and thank you for visiting!


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers