This article will show how you can take some complex scripting logic out of your web pages, and put it into the database. Why would you want to do this? There are several advantages: the code will usually execute faster, the page code will be easier to read and maintain, and there will be a clearer separation between business logic and presentation logic. The concepts in the article will apply equally to all types of server languages.
The easiest way to move some of the logic out of a web application and into the database is to use an autonumber/identity field. This is one thing that most people are already using, but I mention it here in case you aren't. An identity field is a simple auto-incrementing field that is inserted into the database table automatically when you insert a record, and is often used as a primary key in a database table. In other words, if the highest value in the table is 49, the next record insert will automatically include 50 in the autonumber/identity field. Most databases have this functionality. It can save you from having to compute a primary key.
The next easy way you can remove complex logic from the web application and put it into the DB is to use database defaults. A default is nothing more than a simple expression or value that is inserted into the database if no value is given. The typical application of this is for the current date. How often have you seen something like this:
<input type="hidden" value="<cfoutput>#DateFormat(Now(),"MM/DD/YYYY")# #TimeFormat(Now(),"HH:MM:SS")#">
This hidden form field exists only to insert the current date into the database table when the user clicks the submit button.
In SQL Server, for example, you can specify a default value of getdate(). This is a built-in function that allows you to grab the current date/time from the system. You can add this into the table when you design it in the Enterprise Manager, or you can write some SQL code to add it after the fact:
ALTER TABLE MyTable ADD
CONSTRAINT MyTable_MyDateField_Default_Date
DEFAULT (getdate()) FOR MyDateField
MySQL unfortunately has no way to add this when using date fields, but if you use a Timestamp field the date will be set to the current date automatically. MS Access allows you to use the function NOW() when designing your database tables.
How often have you seen a line like this in a server-side script:
<img src="<% If myRecordset("image") <> "" Then
Response.Write(myRecordset("image")
Else
Response.Write("blank.gif")
End %>" width="100" height="100" />
The code is saying "if there is an image name in my database, use that, otherwise use a blank.gif file". The problem is, the HTML and server-side code is ugly. It would be much easier to do this:
<img src="<%= myRecordset("image") %>" width="100" height="100" />
Using a database like SQL Server or MySQL you can rewrite the SQL to supply the appropriate image name:
SELECT field1, field2, field3,
CASE WHEN image = '' THEN 'blank.gif' ELSE image END Image
FROM myTable
The conditional logic is now in the SQL statement, making the web page much easier to maintain.
If you have an Access database, you can do the same with an IIF keyword:
SELECT field1, field2, field3,
IIF(image = '','blank.gif', image) as NewImage
FROM myTable
(Using Access you have to create a new field name as an alias to reference your new database field.)
The same thing can be done in this situation: Suppose you have a field that may or may not be blank. If it is blank, you want to display another field from the query.
<td><% If myRecordset("FirstName") <> "" Then
Response.Write(myRecordset("FirstName")
Else
Response.Write(myRecordset("Username")
End %></td>
This query can save a similar amount of space on the page AND also save query time because you do not have to return both fields to your web page: only one field or the other depending on the condition. In other words, your query might have looked like this before:
SELECT FirstName, Username FROM MyTable
Now you can write it like this:
SELECT CASE WHEN FirstName = '' THEN Username
ELSE FirstName END DisplayName
FROM MyTable
In this instance, you are only passing one field to the page rather than two.
The Access version would look like this:
SELECT IIF(FirstName = '',Username,FirstName) As DisplayName
FROM MyTable
Views are a part of many database systems (Access, SQL Server, PostgreSQL, etc) and can help a lot in the quest to move functionality into your database. Views offer these advantages:
Take a look at this statement, written against the Northwind database:
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City,
Customers.Region, Customers.PostalCode, Customers.Country,
(FirstName + ' ' + LastName) AS Salesperson,
Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName,
"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,
"Order Details".Discount,
(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM Shippers INNER JOIN
(Products INNER JOIN
(
(Employees INNER JOIN
(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
ON Employees.EmployeeID = Orders.EmployeeID)
INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
ON Products.ProductID = "Order Details".ProductID)
ON Shippers.ShipperID = Orders.ShipVia
Is this really the kind of SQL you want to be passing to the database? Is this the kind of statement you want in your web page? Put the SQL into a view named vwInvoices, and on your web page you can use the following:
SELECT * FROM vwInvoices
Much cleaner, much more efficient, and much easier to maintain on your web page. You'll notice I used SELECT *, which I would never use against a database table. Here it is fine to use because the view returns only the fields that my page requires. There are no extra fields.
A view allows complex joins across database tables, databases, and even servers, to be hidden from the web page.
Take a look at the following:
SELECT OrderDate as OrderDate, OrderTotal as OrderTotal
FROM JacksEStore.dbo.Orders
UNION
SELECT orders_orderdate as OrderDate, orders_ordertotal AS OrderTotal
FROM JimsEStore.dbo.tbl_orders
UNION
SELECT oDate as OrderDate, oTotal as OrderTotal
FROM FranksEStore.dbo.current_orders
ORDER BY OrderDate DESC
The SQL statement joins 3 different databases that all have an Orders table. As you can see, each table has it's own structure and naming convention, yet the query is able to pull the data into one virtual table. If you were to save this hypothetical query into your database as vwCombinedOrders, you could call it from the web page like this:
SELECT * FROM vwCombinedOrders
This is true of SQL Server and other full-featured database servers. When you put your database access into views, you no longer have to give your web page access permissions to the database table. This gives your database an added layer of security against possible attacks, and also allows you to hide certain fields from the web page. For example, if you have a username and password field inside a Users table, your web page has access to this table if someone were to use SQL injection to pull information from the table. If your view does not access the username/password fields and there is no access to the table from the web application, there is no way for the hacker to gain access to this username/password information.
SQL Server 2000 offers indexed views, allowing you to speed up your database operations by placing complex joins and logic into a view, and then indexing the view. An index will speed up many types of database operations.
If your database table schema changes and you have to change the way you access the data, you can do this in the view and many times it will not affect the way your web page accesses the data. One example of this that I can think of is a search function. Let's say your web page searches a table of articles. Now, you want the search to also search through your blog entries. If your original query was in a view, you can now change the view to do a join across the blog table, and thereby allow searches of the blog. The web page can still access the view in the same way, making the change transparent to the web application.
Finally, stored procedures (SPs) offer huge benefits over regular queries. If you are afraid to begin using SPs, you shouldn't be. A stored procedure is no harder to write and implement than a regular query/recordset. You are using the same SQL language that you use to create recordsets. A stored procedure has all the benefits of views and more:
A simple SQL query might look like this:
SELECT ProductName, ProductPrice FROM Products
The same SQL statement in a stored procedure would look like this:
CREATE PROCEDURE spGetProducts AS
SELECT ProductName, ProductPrice FROM Products
There is no difference, other than the CREATE statement. Stored procs are easy to write. It's beyond the scope of this article to show you how to write stored procedures, but we have several other articles that address this topic:
Stored Prodcedure Basics, at http://www.communitymx.com/abstract.cfm?cid=A53FE
Moving To Stored Procedures, 3 part series at http://www.communitymx.com/abstract.cfm?cid=B3D70
I hope this article has shown you at least one or two new ways to look at how you build your web application. The goal is to simplify the coding on the web page, and move logic into the database where it will execute faster, be more easily maintained, and make your pages more easy to develop.