Using Oracle Databases in ASP

Source: Internet
Author: User
Tags insert odbc ole oracle database
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 (ty PE:VARCHAR2) phone (TYPE:VARCHAR2)
100 colin Tong 999-999-8888
111 john White
don W OD 416-333-3344 donwod@test.com

Access to and access to data

1) Instantiate oo4o Object, Orasession and interface oradatabase for connecting to ORACLE.

1 Initialize Oracle Objects for OLE, Orasession object, and Oradatabase interface to prepare for connection to 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 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.

<%
'创建ID= fID的记录的OraDynaset对象。
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.

<%
'删除所有符合上面条件的记录
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%>
<%
' Define a variable as an OLE object
Dim orasession
Dim Oradatabase
Dim oradynaset ' Create Orasession Object
Set orasession = CreateObject ("Oracleinprocserver.xorasession") ' creates 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))
%>
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
%>
The record (id= <%=fID%>) has been updated successfully! <br>
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>
<%
' Delete Orasession object
Set orasession = Nothing
%>
</body Jing html>?

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

   Use 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.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.