Tutorial: Writing SQL from inside ASP

2003, DeVry Institute of Technology

Problem

Help students understand how to generate database queries, written in the SQL language, from inside a language other than SQL.

Solution

A paper document which walks the students through the process of evolving a complex piece of software ("code") written in the Active Server Pages ("ASP") language. The tutorial starts with a description of the query we want to write, and changes it, phrase by phrase, into a segment of ASP code which generates that query.

The tutorial is presented as an interactive lecture: the students and I write this code together, in real time. I write a line or two of code on my laptop, which appears on the overhead screen. As a group, we discuss the code — how it shows data flowing from tier to tier, and the technical challenges of the code itself. Then I write a few more lines, we discuss some more, I write, we discuss, and so on, until we have reached the final state of the code presented in the tutorial. Then the students get the same code to work on their own machines. By the end, the students have experienced the data-flow process here as a process, which seems to help their understanding of both the concepts and the techniques.

Technical Background

The situation in question, "writing SQL from inside another language," can be complex. In particular, a single section of code can contain vocabulary from three or four different languages at a time — two or three programming languages, as well as English. Most students in my course, a web-programming course taught at DeVry, seem unfamiliar with that situation before I introduce it. After a year of teaching the course, I wrote this tutorial as a way of helping the students see how each language is introduced into a given section of code. Before the students and I work through the tutorial, the students have done the following.

To help students follow data as it flows from tier to tier — from the user interface to the middleware, then to the database, and back to the UI — we use the same variable name on all three tiers to represent a given piece of data. However, on each tier, we give that variable a prefix representing its tier. For example, a person's first name would be represented by these variables. I underline the differences for emphasis:

• In an HTML form, the person would type his or her first name into a field called uiFirstName.
• In the database, the person's first name would be stored in a column called dbFirstName.
• In the middleware, the first name would be held in a variable called midFirstName.

Before the following tutorial begins, the students have already copied data from the user interface into a middleware variable:

midFirstName = Request ("uiFirstName")

The tutorial below creates an SQL query from the midXXXXX variables. In other words, the code below copies data from variables like midFirstName into database columns with the corresponding names, like dbFirstName.

The Tutorial

Here's the tutorial:

Writing SQL from within ASP

Environment

I wrote this tutorial for the Web Application Development course I teach at the DeVry Institute of Technology.