Recently, the company I work for, KPMG LLP, decided to upgrade an older Web
application
to take advantage of some of the recent upgrades in software. The original
application
was an Active Server Page application that saved data to a low-level database.
Since this database only offered the basic data storing functionality, all of
the SQL code was embedded on the pages. The upgraded application would be
using
an Oracle database including heavy use of stored procedures.
All of the sample code in this article is from Active Server Pages. The
stored
procedure code is not included since it will vary according to the database
used.
We used Oracle for this project, but any database that supports the use of
stored
procedures will work as well.
Starting Point
The first step was to take a look at the original code to make sure that the
conversion to stored procedures would be beneficial. We did not want to
upgrade
just for the sake of upgrading. The following is an example of the type of
code
that was in the existing application where the Connection object was used
to run
SQL statements (note that extra linebreaks may have been introduced by the
layout of this Web page).
<%
sub saveData
dim custID, custFName, custLName, custAddr, custCity, custState, custZip
dim lsql
dim rs
dim contin
dim conn
contin = "Yes"
custID = Request.Form( "id" )
custFName = Request.Form( "fname" )
custLName = Request.Form( "lname" )
custAddr = Request.Form( "address" )
custCity = Request.Form( "city" )
custState = Request.Form( "state" )
custZip = Request.Form( "zip" )
if ( custID = "" ) then
Response.Write( "Customer ID must be entered<br>" )
contin = "No"
end if
if ( custFName = "" ) then
Response.Write( "Customer First Name must be entered<br>" )
contin = "No"
end if
if ( custLName = "" ) then
Response.Write( "Customer Last Name must be entered<br>" )
contin = "No"
end if
if ( contin = "Yes" ) then
lsql = "select * from customer where id = '" & custID
&
"'"
set rs = conn.Execute( lsql, -1, 1 )
if ( rs.EOF and rs.BOF ) then
lsql = "insert into customer ( id, fname, lname, address, city, state,
zip ) " & _
"values ( '" & custID & "', '"
& custFName & "', '" & custLName & _
"', '" & custAddr & "', '"
& custCity & "', '" & custState &
"', '" & _
custZip & "' )"
else
lsql = "update customer set fname = '" & custFName &
"', lname = '" & custLName & _
"', address = '" & custAddr & "',
city = '" & custCity & "', state = '"
& _
custState & "', zip = '" & custZip &
"' where id = '" & custID & "'"
end if
conn = getDBConnection()
set rs = conn.Execute( lsql, -1, 1 )
end if
end sub
%>
This subroutine does several things. First, it pulls all of the values from
an HTML form and validates the data by checking to make sure the Customer ID,
First Name and Last Name exist. If all three fields exist, it determines if
the
customer exists on the database by checking the Customer ID against the
customer
table. If the customer does exist, it updates the record. If the customer does
not exist, it inserts a new record with the values on the form. Once the
subroutine
is finished, the customer has the values that were entered.
This code and the various variations represented the majority of the code that
already existed. While there is nothing really wrong with this code, it did
fall
short in a number of areas.
First, there are always two calls to the database - the check to determine
if the customer existed and then either the insert or update. Calling the
database
is a very resource intensive function since each SQL statement has to be sent
to the database, parsed by the SQL compiler, executed and returned with a
recordset.
Network speed, connectivity issues, database complexities, etc. further
complicate
this problem
Second, there is no error checking. If any of the SQL statements fail, a very
ugly and useless (from the users point-of-view) message will appear on the
screen.
Extensive error check could be built into this subroutine, but would only add
to the former problem.
Third, there is very little validation. The first three fields - Customer ID,
First Name and Last Name - have to be there, but that is all. Even on this
small
example there are numerous things that could go wrong that are not being
checked.
For example, the customer could exist but the number was entered incorrectly.
Again, this could be fixed with the addition of extra coding at the expense of
additional calls to the database.
Fourth, this is way too much code for such a simple operation. This code
takes
7 fields and stores them to a single table. The ASP code is over twice the
size
of the HTML code even on this simple page. On some of the more complex pages,
multiple tables were updated. If we were to add the additional error checking
and validation, the code could easily double or triple in size.
Conversion
The first thing we decided to do was to remove all of the embedded SQL and
related code and put it into a stored procedure. The procedure code was
optimized.
All error checking and validation was put into the stored procedure that still
outperformed the ASP since the code was pre-compiled.
This method is by far the simplest way to handle a stored procedure. A string
is built with the stored procedure name and all of the variables that it uses.
This string is then executed much the same way as the SQL statement above was
executed. The only difference is that the number 4 is used as the part of the
Execute statement rather than the number 1. This is because the number 1
denotes
a SQL statement while the number 4 denotes a stored procedure.
As mentioned above, the database now handles all validation and error
checking.
In addition, there is now only a single call to the database. The size of the
ASP code has been greatly reduced. This is an acceptable alternative to
embedding
the SQL in the page. However, it is far from perfect.
First, there is no way to determine if the update successfully completed. As
long as the stored procedure does not crash, nothing will be returned. Did the
customer get updated or added? If not, why? This is actually worse than the
original
code that would at least let the user know if any of the main three fields
were
missing. This new code is an informational black hole where the data goes
in but
nothing comes out.
Second, the code is barely readable. Oracle in particular and most other
databases
in general require string values to be encased in single quotes. To do this in
the string above, the single quotes are inside of double quotes with commas
between
them - not something that is can be easily read or debugged.
Adding Parameters
The above code could be useful in certain situations, especially if few
variables
are being passed and nothing is being returned. Tasks such as starting a batch
process where the results are being written to a table on the database rather
than being returned are well suited.
However, for the current example, it is not that useful. Seven values
are being
passed in and a return is expected. Something better is needed. This something
is the Command object.
The Command object is like the Connection object on steroids. It can perform
many of the same features and more. The Command object requires four items.
First,
it needs the current connection (ActiveConnection). Second, it needs the name
of the stored procedure (CommandText). Third, it needs to be told that the
type
of command is a stored procedure (CommandType). Fourth, it needs a list of the
parameters that will be going to or coming from the stored procedures
(Parameters).
Parameters requires five items to be created. First, it requires the name of
the field in the stored procedure. Second, it requires the type of field with
the most common types being 200 for Varchar and 131 for Numbers. (For
additional
types see the DataTypeEnum section of the adovbs.inc file.) Third, parameters
requires the direction of the data either unknown(0), input (1), output(2),
inputoutput(3)
or returnvalue(4). Fourth, it requires the maximum size of the field.
Fifth, it
requires the value of the field that is going into the stored procedure
that is
generally supplied by calling subroutine.
<%
sub saveData
dim custID, custFName, custLName, custAddr, custCity, custState, custZip
dim pCustID, pCustFName, pCustLName, pCustAddr, pCustCity, pCustState,
pCustZip
dim pAction, pMessage
custID = Request.Form( "id" )
custFName = Request.Form( "fname" )
custLName = Request.Form( "lname" )
custAddr = Request.Form( "address" )
custCity = Request.Form( "city" )
custState = Request.Form( "state" )
custZip = Request.Form( "zip" )
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = getDBConnection()
oCmd.CommandText = "updateCustomer"
oCmd.CommandType = 4
Set pCustID = oCmd.CreateParameter("custID",200,1,30,custID)
oCmd.Parameters.Append pCustID
Set pCustFName =
oCmd.CreateParameter("custFName",200,1,30,custFName)
oCmd.Parameters.Append pCustFName
Set pCustLName =
oCmd.CreateParameter("custLName",200,1,30,custLName)
oCmd.Parameters.Append pCustLName
Set pCustAddr = oCmd.CreateParameter("custAddr",200,1,30,custAddr)
oCmd.Parameters.Append pCustAddr
Set pCustCity = oCmd.CreateParameter("custCity",200,1,30,custCity)
oCmd.Parameters.Append pCustCity
Set pCustState =
oCmd.CreateParameter("custState",200,1,30,custState)
oCmd.Parameters.Append pCustState
Set pCustZip = oCmd.CreateParameter("custZip",200,1,30,custZip)
oCmd.Parameters.Append pCustZip
Set pAction = oCmd.CreateParameter("ioAction",131,3,50,0)
oCmd.Parameters.Append pAction
Set pMessage =
oCmd.CreateParameter("ioMessage",200,3,50,"")
oCmd.Parameters.Append pMessage
oCmd.Execute
if ( oCmd("ioAction") <> 0 ) then
Response.Write( oCmd("ioMessage") )
end if
end sub
%>
The most obvious advantage of the above is the inputoutput values. The two
values that are being returned - ioAction and ioMessage - contain all of
the information
that is needed to determine whether the procedure completed successfully or
not.
If it did not, as denoted by a value in the ioAction field other than zero,
then
the message is printed to the screen. This could also be used to attempt to
correct
the errors or even to redirect the user to a different page.
In addition, this subroutine is much easier to read than the previous one.
Each of the parameters is created and appended separately which is much
simpler
than a single large string.
Quick Note
Of course, there are multiple ways to create parameters. The above creates
each parameter and then appends it to the Command object such as the two
statements
below.
Set pCustID = oCmd.CreateParameter("custID",200,1,30,custID)
oCmd.Parameters.Append pCustID
The first statement creates the parameter and names it pCustID. The second
statement appends pCustID to the Command parameters. These two statements can
be combined into a single statement without reducing readability.
This eliminates the entire step of naming the parameter. Since it is not
referenced
elsewhere, the name is not really needed. Speaking of not needing
elsewhere, the
final part of the parameter, custID, is also not really needed anywhere else.
This statement could be rewritten like this.
This only works for fields that are input variables (type 1). Any other
fields
- such as ioAction and ioMessage - will cause an exception.
Words Of Warning
No change is completely free of problems. We encountered a couple of problems
during the conversion. Here are some of the most difficult challenges that we
faced.
Stored procedures had to be designed differently for interacting with a
Web
site. Specifically, since the connection was not static, we could not
design the
procedures to wait until all transactions were finished before making changes.
We had to make the changes when submitted and maintain rigorous transaction
control.
The learning curve presented the major challenge. Working with stored
procedures
is significantly different from working with embedded SQL. Testing stored
procedures
is completely different since they exist on the database and cannot be
debugged
through the page itself. Standardized testing techniques and meaningful error
messages and return values significantly reduced the learning curve.
As our web site has become more complex, the stored procedures have
greatly decreased development time and increased the readability and reusability of
the Active Server Pages. They have also added a new dimension of scalability
and flexibility to our site. We will never go back.
About the Author
Al Hetzel is an Oracle DBA/Web
Developer in Dallas, Texas for KPMG LLP.
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.
