ASP (Active Server Pages) is one of the tools proposed by Microsoft for developing Internet applications. The connection between ASP and the database is generally achieved through ADO (Active Data Object), as "Computer World" reported on March 20, 2000 As introduced in the article "Using ASP to Operate SQL Server Database", ADO can fully support Microsoft SQL Server, but there are some difficulties with ORACLE database services that are more widely used and have more complex mechanisms. If you want to make some simple query functions, ADO is sufficient. If you want to better utilize the unique functions of ORACLE database, such as STORED PROCEDURE, STORED FUNCTION, etc., you must use another powerful tool to access ORACLE database services using ASP---Oracle Object for OLE. ORACLE Object Server (The Oracle Object Server). This article explains from an example how to use the controls provided by the ORACLE object server in ASP to achieve access to the ORACLE database, and discusses how to maintain the integrity constraints of the network database.
----Oracle Object for OLE is a much-needed product developed by ORACLE for client access to databases. It is based on Windows 95/98/NT and can be accessed by all OLE-compatible applications and programming languages. ORACLE database, such as ASP, Visual Basic excess97, etc., Oracle Object for OLE includes ORACLE Object Server (The Oracle Object Server), ORCLE Data Control (The Oracle Data Control) and ORACLE Object Class Library (The Oracle Objects for OLE C++ Class Library) and other three products.
---- Compared with ADO, ORACLE object server is a product dedicated to the development of ORACLE database applications. It has the following advantages:
---- (1) The connection with the database server operates efficiently. Because ADO connects to the database server through ODBC, and the ORACLE object server connects to the database server through ORACLE's SQL*NET.
---- (2) Can better utilize the unique functions of ORACLE database. Such as STORED PROCEDURE, STORED FUNCTION, PACKAGE or Multiple Cursor.
---- This example is a small program for querying and entering address books. Users can query the address book table through the browser, or enter their own information for others to query.
----The structure of the address book table is as follows:
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL CHAR(8)
ADDRESS CHAR(20)
PHONE CHAR(20)
EMAIL VARCHAR2(30)
---- First: configure the environment:
----Install the following software on the WEB server and configure it:
---- (1)Basic WEB server (WEB SERVER) and ASP and other software
---- (2)Oracle Object for OLE2.x
---- (3) SQL*Net Client or Oracle Net8 Client 8. After installation, use Sql*Net Easy Configuration to create a database alias (Database Alias), which is the alias used by ASP to connect to the database.
---- two. Function realization
---- (1) Query: Use ASP to get the data from the database address book table (TSB1) and display the data on the screen. The key to querying is to determine whether the connection to the database is successful. The ORACLE object server itself has an error control mechanism that can report database connection errors. The specific procedures are as follows:
<HTML>
<tile>query program</title>
<BODY>
<%
on error resume next
'Connect to database
set orasession=createobject
("OracleInprocServer.XOraSession")
set oradatabase=orasession.
dbopendatabase("axp03","scott/tiger",0)
'Error handling
if err.number >0 then
response.write"< h4 >asp error control< /h4 >"
response.write"asp error source:"&err.source &"< br >"
response.write"asp error code:"&err.number&"< br >"
response.write"asp error description:"&err.description&"< br >"
err.clear
response.write"< h4 >ORACLE OLE Error Control< /h4 >"
response.write"ORACLE error code:
"&OraSession.LastServerErr&"< br >"
response.write"ORACLE error description:
"&OraSession.LastServerErrText&"< br >"
else
'Query
sql="select * from tsb1"
set oradynaset=oradatabase.dbcreatedynaset(sql,0)
response.write"< h3 >result< /h3 >"
response.write"< table border=5 >< tr >"
for i=0 to oradynaset.fields.count-1
response.write"<td>"
response.write oradynaset.fields(i).name & " "
response.write"< /td >"
next
response.write"< /tr >"
do until oradynaset.eof
response.write"<tr>"
for i=0 to oradynaset.fields.count-1
response.write"<td>"
response.write oradynaset.fields(i).value
response.write"< /td >"
next
response.write"< /tr >"
oradynaset.dbmovenext
loop
response.write"< /table >"
'Close the database
oradatabase.close
set orasession=nothing
end if
%>
< /body >
< /html >
----The following is the query result image of the program:
Address book
NAME ADDRESS PHONE EMAIL
Zhang Ling No. 5 Hubei Road, Ma'anshan City 0555-2887765 [email protected]
Lin Xiang No. 9 Hunan Road, Ma'anshan City 0555-2223856 [email protected]
No. 504, Building 4, Deng Guohong Mining Road 0555-2474748 [email protected]
Room 507, Building 7, Qingdao Ocean University, Licheng 0464-32456678 [email protected]
No. 807, Building 7, Wang Guozai Mining Community 0555-3445454 [email protected]
No. 405, Building 17, Hongqi South Road, Ding Gang 0555-2883721 [email protected]
No. 607, Building 7, Baojiangxi Road 0554-4566777 [email protected]
---- (2) Insert your own information: Use ASP to insert the data provided by the user on the browser into the database address book table (TSB1). The ORACLE database has a set of integrity constraint mechanisms, so the insertion process is not only simple Insert the data into the table efficiently, and check the integrity constraints. Data that does not meet the requirements will be refused to be inserted. If processing is not considered in the program, the program will be interrupted due to an error reported by the database server during runtime. The ERR object in ASP can report the error source, error code, error description, etc., and can lead the program to error processing.
---- The following are programs, one is insert.htm, which is an input interface for users to input their own information, and the other is insert.asp, which is responsible for inserting user input information into the database and performing error checking.
Insert.htm:
<html>
<head>
< meta http-equiv="Content-Language"
content="zh-cn" >
< meta http-equiv="Content-Type"
content="text/html; charset=gb2312" >
< meta name="GENERATOR" content=
"Microsoft FrontPage 4.0" >
< meta name="ProgId" content=
"FrontPage.Editor.Document" >
<title>Please leave your information</title>
</ /head >
<body>
< p align="center" >Please enter your information</ /p >
< form method="post" action="insert.asp" >
Name:
< input type="text" name="name" size="8" > < br >
address:
< input type="text" name="address" size="20" > < br >
Telephone:
< input type="text" name="phone" size="20" > < br >
E-MAIL: < input type="text" name="email" size="20" > < br >
< input type="submit" value="OK" >
< input type="reset" value="Cancel" >< br >
< /form>
< /body >
< /html >
----The following is the user input interface:
Please enter your information name:
address:
Telephone:
E-MAIL：
Insert.asp:
<HTML>
<BODY>
<%
'Connect to database
set orasession=createobject
("OracleInprocServer.XOraSession")
set oradatabase=orasession.
dbopendatabase("axp03","scott/tiger",0)
'Transform user-inserted information into SQL statements
sql="insert into tsb1 values
(" &chr(39)&request("name")&chr(39) &","_
&chr(39)&request("ADDRESS")&chr(39)&","_
&chr(39) & request("phone")&chr(39) &","_
&chr(39)&request("EMAIL")&chr(39)&")"
'Run SQL insert statement
oradatabase.dbEXECUTESQL(sql)
'Check for integrity constraint violation errors
IF ERR.NUMBER >0 THEN
response.write"< h4 >asp error control< /h4 >"
response.write"asp error source:"&err.source &"< br >"
response.write"asp error code:"&err.number&"< br >"
response.write"asp error description:"&err.description&"< br >"
ELSE
'Insertion completed, return
response.write "Insertion completed, < a href=insert.htm > returns... < /a >"
end if
%>
< /body >
< /html >
----The above are some of my experiences in using ASP tools to move original ORACLE database information to Internet applications. The example is very simple and is intended to illustrate the principle. I also used STORED PROCEDURE, STORED FUNCTION, and PACKAGE in the ASP program. Wait for ORACLE's unique functions to speed up and improve efficiency. I hope it can inspire everyone, please write to communicate with each other.