by Al Hetzel

Smarter ASP

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.
August 9, 2000

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

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.

<%

sub saveData

dim custID, custFName, custLName, custAddr, custCity, custState, custZip
dim lsql
dim rs
dim conn

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" )

 lsql = "updateCustomer( '" & custID & "', '" & custFName & "', '" & custLName & _
"', '" & custAddr & "', '" & custCity & "', '" & custState & "', '" & _
custZip & "' )"

%>

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.

oCmd.Parameters.Append = oCmd.CreateParameter("custID",200,1,30,custID)

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.

oCmd.Parameters.Append = oCmd.CreateParameter("custID",200,1,30, Request.Form( "id" ))

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.

Al Hetzel is an Oracle DBA/Web Developer in Dallas, Texas for KPMG LLP.