Using OO4O Automation with Active Server Pages (ASP)
This example uses Active Server Pages (ASP) in a Microsoft Internet
Information Server (IIS) to demonstrate the Connection Pooling feature of Oracle
Objects for OLE. The sample code executes a SQL SELECT query and returns the
result as an HTML table. The database connection used in this script is obtained
from a pool that is created when global.asa is executed.
To use Oracle Objects for OLE with OLE Automation
and IIS, you need to install IIS 3.0 or later, including all ASP extensions. On
the computer where IIS is running, an Oracle database must be accessible also.
Note: The sample code
for this example is available in the ORACLE_BASE\ORACLE_HOME\oo4o\iis\samples\asp\connpool directory.
Start SQLPLUS and log into the Oracle database as
“scott/tiger”. Create the
following PL/SQL procedures.
-- creates PL/SQL package to be used in ASP demos
create or replace package ASP_demo as
--cursor c1 is select * from emp;
type empCur is ref cursor;
PROCEDURE GetCursor(p_cursor1 in out empCur,
indeptno IN NUMBER,
p_errorcode OUT NUMBER);
END ASP_demo;
/
--------------------------------------------------------------
create or replace package body ASP_demo as
PROCEDURE GetCursor(p_cursor1 in out empCur,
indeptno IN NUMBER,
p_errorcode OUT NUMBER) is
BEGIN
p_errorcode:= 0;
open p_cursor1 for select * from emp where deptno =
indeptno;
EXCEPTION
When others then
p_errorcode:= SQLCODE;
END GetCursor;
----------------------------------------------------------------
END ASP_demo;
/
2. Create the Active Server Pages (ASP) sample code. The OO4O related code
is in bold.
'GLOBAL.ASA
<OBJECT RUNAT=Server SCOPE=Application ID=OraSession
PROGID="OracleInProcServer.XOraSession"></OBJECT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'Get an instance of the Connection Pooling object
and
'create a pool of OraDatabase
OraSession.CreateDatabasePool 1,40,200,"exampledb",
"scott/tiger", 0
End Sub
'OO4ODEMO.ASP
<html>
<head>
<title>Oracle Objects For OLE (OO4O)
</title>
</head>
<body BGCOLOR="#FFFFFF">
<font FACE="ARIAL,HELVETICA">
<h2 align="center">Oracle Objects
For OLE (OO4O) </h2>
<form ACTION="OO4ODEMO.asp"
METHOD="POST">
<%
SqlQuery = Request.Form("sqlquery")
%>
<p>This sample executes a SQL SELECT query and
returns the result as an HTML table. The database connection used in this script
is obtained from a pool that is created when the
<strong>global.asa</strong> is executed. </p>
<p>SQL Select Query: <input
SIZE="48" NAME="sqlquery"> </p>
<p><input TYPE="SUBMIT">
<input TYPE="RESET"> <input LANGUAGE="VBScript"
TYPE="button" VALUE="Show ASP Source"
ONCLICK="Window.location.href = "oo4oasp.htm""
NAME="ShowSrc"></p>
</form>
<%
If SqlQuery = "" Then
%>
<% Else %>
<table BORDER="1">
<%
Set OraDatabase =
OraSession.GetDatabaseFromPool(10)
Set OraDynaset =
OraDatabase.CreateDynaset(SqlQuery,0)
Set Columns = OraDynaset.Fields
%>
<tr>
<td><table BORDER="1">
<tr>
<% For i = 0 to Columns.Count - 1 %>
<td><b><% = Columns(i).Name %></b></td>
<% Next %>
</tr>
<% while NOT OraDynaset.EOF %>
<tr>
<% For col = 0 to Columns.Count - 1 %>
<td><% = Columns(col) %>
</td>
<% Next %>
</tr>
<% OraDynaset.MoveNext %>
<% WEnd %>
</table>
<p></font><%End If%> </p>
<hr>
</td>
</tr>
</table>
</body>
</html>
3. Create a virtual directory from Microsoft Internet Service Manager
whose access are both readable and executable, and place all .asp and .asa files
in that directory.
4. Create an html page from which to launch
oo4odemo.asp. Add a
link in the page:
<a href="/<your_path>/OO4ODEMO.ASP">This link
launches the demo!</a>
5. Load the page in a web browser and click on the link to the demo.
6. Enter a query, such as 'SELECT * FROM EMP', in
the SQL SELECT Query field and select the Submit Query button. Do not include a
';' at the end of the query.
Text description of the
illustration iissamp.gif