Using Oracle Database techniques in ASP (i)

Oracle is one of the most used databases in the world, and the Active Server Web page (ASP) is a powerful server-side scripting language that is widely used to create dynamic Web pages. Many ASP developers have been considering the possibility of using ASP and Oracle databases in the development of Internet applications, E-commerce sites, and Internet management systems. The answer to this question is yes, we can also use VB to access the Oracle database. In this article, we will focus on how to use ASP to process data in an Oracle database.

Before we begin to discuss this issue, we need to know a few things about the background, and Oracle Objects for OLE is one of them. Oracle Objects for OLE is a middleware developed by Oracle that allows client applications using Microsoft's OLE standard to access Oracle databases. There may be readers who say that we can also use ODBC to access Oracle's databases. Of course, you can use ODBC to access the Oracle database, but I think Oracle Objects for OLE is more secure than ODBC and fully supports PL/SQL. Pl/sql is an oracle extension of the SQL command set that developers can use to flow control and logical design of unstructured SQL command blocks. If the Oracle8i database is installed, I believe you are already using Oracle Objects for OLE. If you have not yet used Oracle Objects for OLE, you can download it from Oracle's Web site.

In addition, we need to understand Oracle's two objects and one interface for Visual Basic development: Orasession, Oradynaset objects, and Oradatabase interfaces. The Orasession object manages the Oradatabase, Oraconnection, and Oradynaset of the application, which is an object created by the ASP's CreateObject instead of Oracle Objects for OLE. The Oradatabase interface represents user dialogs to Oracle databases and provides methods for SQL, Pl/sql execution. Each of them has some attributes and methods. For example, the Oradynaset object has 10 methods, such as BOF, EOF, Bookmark, Connection, and AddNew, Update, Delete, Edit, Refresh, clone, etc.

Let's start with a topic that discusses how to use ASP to process data in an Oracle database.

Preparatory work

What kind of environment and tools do we need?

1 I use the oracle8i, IIS5.0, Windows2000 Professional Edition as the application development and operating environment.

2 Create a table named MYTABLE1 or similar in the Oracle database.

ID (Type:number) User Name (TYPE:VARCHAR2) Phone (TYPE:VARCHAR2) Email (type:varchar2) Colin Tong 999-999-8888 colinjava@hotmail.com John White 888-888-8888 johnw@yahoo.com Don wod 416-333-3344 donwod@te St.com

Access to and access to data

1) Instantiate oo4o Object, Orasession and interface oradatabase for connecting to ORACLE.
　　
Initializes Oracle Objects for OLE, Orasession objects, and Oradatabase interfaces to prepare for connecting Oracle databases.

First, create the Orasession object using CreateObject, and then create the Oradatabase object by opening a connection to Oracle, as follows:

<%

Set orasession = CreateObject ("Oracleinprocserver.xorasession")

Set oradatabase = Orasession.opendatabase ("", _

"Username/password", Cint (0))

%>

"Username" and "password" are the username and password of the relational database you are using.

2 Create the Oradynaset object to execute the SQL command. We can create a recordset using CreateDynaset or Dbcreatedynaset.

<%

' Execute SQL Set oradynaset = Oradatabase.dbcreatedynaset (_ "SELECT * FROM
Mytable1 ", CInt (0))

%>

3 Access the data and delete the 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 records

We will use the Oradynaset method to achieve the editing of data records.

1 Use SQL statements to create Oradynaset objects.

<%

' Creates the Oradynaset object for the record of the id= FID.

Set Oradynaset = Oradatabase.createdynaset (_

"SELECT * from MYTABLE1 where id=" & FID, CInt (0))

%>

The FID is the ID value of the record you want to insert into the update.

2 perform Oradynaset update or add data record.

<%

' Use the Edit method to update the domain of the Id=fid record.

' or insert a new record using AddNew

Oradynaset.edit

Oradynaset.fields ("Phone"). Value = Fphone

Oradynaset.update

' Delete the created dialog

Set orasession = Nothing

%>