Using Oracle database techniques in ASP

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

%>

deleting data records

If you have really understood some of the methods we discussed above (Edit, update, and AddNew), perhaps some readers already know how to delete records in an Oracle database.

<%

' Delete all records that match the above criteria Oradynaset.delete

%>

Code to search for and update data records in Oracle8i

1) Search

<%

' retriverecproc.asp-Update data logging using ASP's Oracle Objects for OLE

%>

<% ' defines a variable as an OLE object

Dim orasession

Dim Oradatabase

Dim Oradynaset

' Create a Orasession object

Set orasession = CreateObject ("Oracleinprocserver.xorasession")

' Create a Oradatabase object by opening a connection to an Oracle database

' Be sure to use your username and password to access the Oracle database

Set oradatabase = Orasession.opendatabase ("", "User/password", _

Cint (0))

' Create Oradynaset object Execute SQL statement

Set Oradynaset = Oradatabase.dbcreatedynaset (_

"SELECT * from Mytable1", CInt (0))

%>

<body>

Using oo4o

<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

' Delete 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>

2) Update

<% ' updaterecproc.asp-Update data logging using ASP's Oracle Objects for OLE

%>

<%

' defines a variable as an OLE object.

Dim orasession

Dim Oradatabase

Dim Oradynaset

' Get the field value from the submitted table

FID = Request.Form ("ID")

Fusername = Request.Form ("UserName")

Fphone = Request.Form ("Phone")

FEMAIL = Request.Form ("Email")

' Create a Orasession object

Set orasession = CreateObject ("Oracleinprocserver.xorasession")

' Create a Oradatabase object by opening a connection to an Oracle database

Set oradatabase = Orasession.opendatabase ("", "User/password", _ Cint (0))

' Create the Oradynaset object for the record of the id= FID

Set Oradynaset = Oradatabase.createdynaset (_ "SELECT * from MYTABLE1 where id=" & FID, CInt (0))

' Update fields for Id=fid records using the 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

%>

<body>

The record (id=<%=fid%>) has been updated successfully!<br>

can view the result <a href= "retrieveallrec.asp" > Here</a>

<a href= "Javascript:window.history.go ( -1)" > Back previous page</a>

&bnsp;&bnsp;

<a href= "Javascript:window.history.go ( -2)" > Back home page</a>

<%

' Delete Orasession object

Set orasession = Nothing

%>

</body>

So far, we've discussed how to use Oracle Objects for OLE in ASP code to handle data in an Oracle database.

Using Stored Procedures

We have discussed how to access an Oracle database in ASP, and all SQL statements can be embedded in an ASP Web page. If you use stored procedures in your ASP, you will be more able to process your data more efficiently. I recommend that readers use pl/sql stored procedures in addition to embedding SQL statements in ASP. Creating a stored procedure in an Oracle database is beyond the scope of this article and is no longer covered here.

ASP and Oracle databases are two of the more popular technologies, there are quite a wide range of user groups, if they can organically combine the two, will be able to bring a lot of convenience to work, I hope this article can play a role, so that readers can better explore the combination of these two technologies to use the way.