ASP database programming: Using Oracle Databases in ASP

Oracle is one of the most used databases in the world, and Active Server Web pages (ASP) is a powerful server-side scripting language that is widely used to create dynamic Web pages. Many ASP developers have been considering the ability to use ASP and Oracle databases when developing 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'll 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 background information, Oracle Objects for OLE is one of them. Oracle Objects for OLE is a middleware developed by Oracle that allows access to Oracle's databases using Microsoft's OLE standard client applications. Some readers may say that we can also use ODBC to access Oracle's database. 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 extension of Oracle to the set of commands that developers can use to flow control and logical design of unstructured SQL command blocks. If you are installing the Oracle8i database, 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 the Oracle Web site.
In addition, we need to understand the two objects and an interface that Oracle has developed for Visual Basic: 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 a user conversation to an Oracle database, and provides methods for SQL, PL/SQL execution. Each of them has some properties and methods. For example, the Oradynaset object has 10 methods, such as BOF, EOF, Bookmark, connection, and so on, AddNew, Update, Delete, Edit, Refresh, clone, and so on.
Let's start with the topic of 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 Oracle8i, IIS5.0, Windows2000 Pro as the application development and operating environment.
2) Create a table with the name 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[email protected]
111 John White 888-888-8888[email protected]
101 Don Wod 416-333-3344[email protected]
Access and access to data
1) Instantiate oo4o Object, Orasession and interface oradatabase for connecting to ORACLE.
1) Initialize the Oracle Objects for OLE, Orasession objects, and Oradatabase interfaces to prepare for connecting to the Oracle database.
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 user name 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
%>
Editing data records
We will use the Oradynaset method to achieve the editing of data records.
1) Use the SQL statement to create the Oradynaset object.
<%
' Creates a Oradynaset object for id= FID records.
Set Oradynaset = Oradatabase.createdynaset (_
"SELECT * from MYTABLE1 where id=" & FID, CInt (0))
%>
FID is the ID value of the record that you want to insert for more updates.
2) Perform oradynaset updates or add data records.
<%
' Use the Edit method to update the fields 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
%>
Delete 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 the Oracle database.
<%
' Delete all records that meet the above criteria
Oradynaset.delete
%>
Code to search for and update data records in Oracle8i
1) Search
<%
' Retriverecproc.asp-Update data records using ASP's Oracle Objects for OLE%>
<%
' Define a variable as an OLE object
Dim orasession
Dim Oradatabase
Dim oradynaset ' Create Orasession Object
Set orasession = CreateObject ("Oracleinprocserver.xorasession") ' Creates a Oradatabase object by opening a connection to the Oracle database
' Be sure to access the Oracle database with your own username and password
Set oradatabase = Orasession.opendatabase ("", "User/password", _
Cint (0))
' Create Oradynaset object Execute SQL statement
Set Oradynaset = Oradatabase.dbcreatedynaset (_
"SELECT * from Mytable1", CInt (0))
%>
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-updating data records 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 Orasession Object
Set orasession = CreateObject ("Oracleinprocserver.xorasession")
' Create a Oradatabase object by opening a connection to the Oracle database
Set oradatabase = Orasession.opendatabase ("", "User/password", _
Cint (0))
' Create a Oradynaset object for id= FID records
Set Oradynaset = Oradatabase.createdynaset (_
"SELECT * from MYTABLE1 where id=" & FID, CInt (0))
' Use the Edit method to update the fields of the Id=fid record
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
%>
The record (id= <%=fID%>) has been updated successfully! <br>
You can view the result <a Href= "retrieveallrec.asp" and 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>
<%
' Delete Orasession object
Set orasession = Nothing
%>
</body Jing? html>
At this point, we have discussed how to use Oracle Objects for OLE in ASP code to process data in an Oracle database.
Using Stored Procedures
We have discussed how to access the Oracle database in ASP, and all SQL statements can be embedded in ASP Web pages. If you use stored procedures in ASP, you will be more able to work with your data more efficiently. I recommend that readers, in addition to embedding SQL statements in ASP, should also use PL-SQL stored procedures. Creating a stored procedure in an Oracle database is beyond the scope of this article and is no longer covered here.
ASP and Oracle database is two more popular technology, there are quite a wide range of user groups, if you 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 use of these two technologies in conjunction with the way. [Shanghai Treatment Impotence Hospital]

ASP database programming: Using Oracle Databases in ASP