Active Server Pages (ASP) is a server-side scripting language widely used to build dynamic Web pages. The ASP Engine processes the script on the Web server at the back end and sends only HTML to any browser. Since the script is processed at the server, the requesting browser will be catered, even if it does not support a scripting language.
Oracle is one of the most used relational databases. Oracle Objects for OLE (OO4O) is an Oracle middleware that allows native access to Oracle from client applications using the Microsoft Object Linking and Embedding (OLE) standard. But performance-wise it is better than Open DataBase Connectivity (ODBC), a standard database access method developed by Microsoft Corporation, to access Oracle. OO4O is thread safe and provides full support for PL/SQL. Connection pooling is also available with later versions of OO4O.
This article describes practical Oracle Database connectivity from ASP using OO4O. It also explains the execution of pass through SQL, and Procedure and Package from ASP, which are commonly used in data-driven dynamic Web applications.
Installation of OO4O
OO4O can be freely downloaded from Oracle’s site. The creation of an oo4o directory by the installer under Oracle home confirms the successful installation. OO4O also works well with Oracle 7.0 or later.
All the examples are based on a fictitious Products table, which comprises the below-mentioned structure.
Table Products
Field Name
Data Type
PID
NUMBER
PNAME
Including an oo4oglobals.asp file in a project is a good practice. All oo4o constants are defined in this file (see below):
The following ASP code demonstrates the execution of pass through SQL using OO4O.
Step 1: Making the Connection
<%
Dim OraSession
Dim OraDatabase
Dim osRecordSet
Set OraSession = Server.CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("", "scott/tiger",cint(0))
%>
OLE Server - OraSession object is instantiated by the CreateObject method. DbOpenDatabase method of OraSession is used to open a connection. The DbOpenDatabaseMethod takes database name, username, password, and a mode parameter as inputs. The mode parameter cint(0) specifies the result return format (default or Visual Basic mode). This mode will set null values to columns that are not explicitly specified during AddNew and Edit methods of the oo4o object.
Step 2: Executing SQL
<%
Set osRecordSet = OraDatabase.DbCreateDynaset("select pid, pname from PRODUCTS", cint(0))
%>
DbCreateDynaset method of OraDatabase creates a Dynaset from the specified SQL statements. The parameter cint(0) specifies the mode as discussed in the connection open method.
A Dynaset created by the oo4o object will always point to an initial record of a database connection. The server script will manipulate the Dynaset result set to present the results to the browser. Movenext is an often-used method to navigate through Dynasets.
Step 4: Closing the Connection
<%Set OraSession = Nothing%>
Closing the connection frees up all the system resources being used.
Execution of Oracle Procedure Using OO4O
The following ASP code demonstrates the execution of a stored procedure within a database using OO4O.
Step 1: Making the Connection
<!--#INCLUDE FILE="oo4oglobals.asp"-->
<%
Dim OraSession
Dim OraDatabase
Dim osRecordSet
Dim nResult
nPID=10
sPNAME="HDD"
Set OraSession = Server.CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("", "scott/tiger",cint(0))
%>
Note: For demo purposes, the input values to the procedure are hard-coded.
In the example above, the Add method adds parameters to the collection. Name, value, and I/O type are taken as input. I/O type constants are defined in oo4oglobals.asp. DbExecuteSql executes the formatted PL/SQL statements and returns the number of rows processed.
Step 3: Retrieving Results
If there are any output parameters for a procedure, they can be retrieved after the procedure execution.
Step 4: Closing the Connection
<%
OraDatabase.Parameters.Remove "nPID"
OraDatabase.Parameters.Remove "sPNAME"
Set OraSession=Nothing
%>
Re-initializing the parameters to specific needs can promote the reuse of objects. Therefore, purely program logic, and not the resource availability, will influence the decision on closing or keeping a database session.
Procedure Used for Demo:
/******************************************************
Procedure: SP_INS_PRODUCT
Desc : Insert a product
Author : Selva Kumar
******************************************************/
CREATE OR REPLACE PROCEDURE SP_INS_PRODUCT(nPID IN NUMBER, sPNAME IN VARCHAR2)
AS BEGIN
INSERT INTO PRODUCTS(PID,PNAME) VALUES(nPID,sPNAME);
END;
Execution of Oracle Package Using OO4O
The following ASP code demonstrates the execution package using OO4O.
Step 1: Making the Connection
<!--#INCLUDE FILE="oo4oglobals.asp"-->
<%
Dim OraSession
Dim OraDatabase
Dim osRecordSet
Set OraSession = Server.CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("", "scott/tiger",cint(0))
%>
CreatePLSQLDynaset creates a Dynaset from PL/SQL cursor. This method takes SQL Statements, a cursor created by stored procedure, and a mode parameter as input.
/******************************************************
Package : RET_PRODUCTS
Procedure: SP_RET_PRODUCTS
Desc : Returning all products
Author : Selva Kumar
******************************************************/
CREATE OR REPLACE PACKAGE RET_PRODUCTS
AS
CURSOR C1 IS SELECT * FROM PRODUCTS;
TYPE tProducts is ref cursor return C1%rowtype;
PROCEDURE SP_RET_PRODUCTS
(iProducts in out tProducts);
END RET_PRODUCTS;
/
Package Body:
/******************************************************
Package : RET_PRODUCTS
Procedure: SP_RET_PRODUCTS
Desc : Returing all products
Author : Selva Kumar
******************************************************/
CREATE OR REPLACE PACKAGE BODY RET_PRODUCTS
AS
PROCEDURE SP_RET_PRODUCTS
(iProducts in out tProducts)
IS
BEGIN
OPEN iProducts for Select * from PRODUCTS;
end SP_RET_PRODUCTS;
END;
/
About the Author
Selva Kumar is a consultant at Xpedior Inc., in Chicago. Xpedior provides eBusiness solutions to Global 2000 and emerging Internet companies. The company combines technical expertise with strategic consulting and creative services, and enables clients to capitalize on the power and efficiency of the Internet. E-mail him at wwgselva@yahoo.com.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
