구글와이드(336x280)_상단 2개

Databases IT 영문자료


Lesson 1
Tool check, sample application

Lesson 2
Relational database explained, defining and linking tables

Lesson 3
SQL, VBScript, ActiveX Data Objects, 2-D arrays

Lesson 4
Includes, finishing touches
Your First Database

by Jay Greenspan
Jay Greenspan [an error occurred while processing this directive]is a former Webmonkey producer who now splits his time between poker, technology and comedy. In his spare time, he also writes books.

Professional database folk would like you to think that building a Web-based relational database application is water-to-wine difficult. But it's not - especially now that Jay's here to show you how it's done.
Some of the most useful sites out there - the Internet Movie Database, Monster.com, and Amazon.com - have a relational database to thank for their success. Since you may not be in a position to drop $30K on the high-end RDBMSes (relational database management systems) that these sites employ, Jay starts the tutorial by explaining how you can use the tools you already have to create a smaller, yet still useful, database application.
After you make it past the equipment check, get ready for a breakneck tour of all the things you need to know to create your relational database. Take a crash course in defining and linking tables. Read the Cliffs Notes to linking your database to your Web server. Have a brief fling with 2-D arrays. Learn just enough SQL, VBScript, ActiveX Data Objects (ADO), and ASP stuff to scrape by. Put the pedal to the metal through HTML forms. And then learn how to put it all together. All in just four easy lessons.
Page 1 — Your First Database — Lesson 1

Professional database folks are a lot like magicians: They don't want their secrets revealed. They, too, use all the standard ploys - smoke, mirrors, obfuscation, tedious conversation - to keep you from finding out the hidden truth behind Web-based database manipulation.
The reason for this secrecy is clear. Right now, only a select few can add show stoppers such as "Developed a Web-based database application" to their résumé. And the fewer who can make such claims, the more special they seem.
Lucky for you, ladies and gentlemen, I am the debunker of myths, the exposer of untruths, a man willing to risk the wrath of the chosen few database masters by telling you exactly what they don't want you to know. That's right, following this very sentence, I will reveal the shocking truth about databasing on the Web.
It's pretty easy.
Take a moment. Catch your breath. Have some water if you need it. When you're settled, I'll explain.
You already know HTML can be learned in minutes. The concepts that will get you started in Web databasing aren't that much more difficult. In only four lessons, I'll introduce you to everything you need to know to develop a functional Web-based application. Of course, I'm not going to teach you everything. People go to school for years to learn this stuff. But by the time you're done with this lesson, you'll know just enough to be dangerous. And, as we all know, the more dangerous you are, the sexier you appear. Make it through this tutorial, and you will be one sexy person.
Sound good? I thought so. Now that I've got you properly motivated, let's take a look at what we'll cover in this four-part tutorial:
In Lesson 1 (which, conveniently enough, is what you're reading right now), we'll take a quick look at the background of databasing. We'll also go over the purposes of a data-driven site. Then I'll show all the tools you'll need to get started. And we'll finish up with a glimpse at the site we're going to develop over the course of this tutorial.
In Lesson 2 I'll show how you need to think about data and how to hook your database to a Web server.
The real fun comes in Lesson 3, where we'll take a lightning-fast tour of the elements involved in creating data-driven active server pages (ASP). VBScript, SQL, the ActiveX Data Object, and HTML forms are just some of the highlights.
We'll finish up by putting it all together in Lesson 4. By the time you're done, you'll be building pages like the pros.
Ready? Let's get started by taking a look at the benefits and applications of data-driven sites.
Page 2 — It's All About Data

Aside from how sexy your new-found database knowledge will make you, you should also appreciate the importance of the database on the Web. There may come a day when the anticipated "fat pipe" (big bandwidth) makes it into every home in the world, making the Web a viable entertainment alternative to radio, TV, or the art gallery. But for now and the foreseeable future, the most successful uses of the Web involve the exchange and sorting of information.
Think about the most useful sites out there. The Internet Movie Database, Monster.com, and Amazon.com all offer ways to search, sort, and view massive amounts of data.
The thing that makes each of these sites work is a relational database. Since these sites have to handle a tremendous number of hits, they use big, expensive RDBMSes (relational database management systems). The names for the RDBMSes they use are familiar to anyone who follows the Nasdaq: Oracle, Sybase, Microsoft SQL Server, Informix. Go to school for a couple of years and dish out 30 grand for Oracle, and you'll be ready to create your own systems capable of doing a billion dollars' worth of transactions.
But that's a pretty tall order for small fries like you and me. Instead, let's shoot for a slightly more obtainable (though still sexy) goal: Rather than fork over 30 grand for a heavy-duty management system, we'll use tools that we already have to create a site capable of tracking a more modest amount of information. Don't worry - there's plenty you can do with a small database on your Web site:
  • Track the progress of projects where key players are in several locations.
  • Put up a dynamically generated Web page that allows folks to share common interests on topics like books or music.
  • Maintain control of contraband as it heads across unfriendly borders.
  • Keep track of the movements and whereabouts of your enemies. (If tied in with Mapquest, this could be really effective.)
  • Catalog every last Web site dedicated to Dawson's Creek. Find out who the creators are and add them to the enemies database.
  • Keep abreast of stalking laws. Make sure you're not violating them with any of the above databases.
I could go on, but you're probably ready to get started, which is cool with me. Let's take a look at the database tools we already have.
Page 3 — Tool Check

The plan here is to develop a small application locally - on your home or work computer. Once that's done, you'll have no trouble finding an ISP that can serve your app to the world at large.
You probably have everything you need to get this done already, but let's do a quick check to make sure.
If you're running Windows 95/98, check to see if you have Personal Web Server (PWS) installed. If it is, there will be an icon for it in the Control Panels. If not, you can download PWS from Microsoft's site. Or, if you have an NT Workstation, you'll need the NT Option Pack, also from Microsoft. From here on out, I'll assume you have one of these packages installed and have poked around it a little bit. Make sure you know which folder will be serving out your pages. The default will likely be something like C:\Internetpub or C:\wwwroot.
If you're working on a Mac, this probably isn't the tutorial for you. PWS and ASP will work only on a PC. Filemaker Pro offers a nice Web-database solution for the Mac. Or if you're heading into Unixland, I'd recommend that you get familiar with PHP, which is a great open-source scripting language.
Go into your Control Panel and double-click the Network item. In the Identification Tab, there will be a slot labeled Computer Name. Whatever you have in here will be the URL you'll use to access your server.
Network Interface
Since I indicated my computer name was Jay, I simply have to enter http://jay/ in a browser to access my default directory. Make sure you get something other than an error when you try this.
If you have any version of PWS installed, you have the ability to create active server pages (ASPs), which we'll be doing in the lessons to come. So, if you don't already know your way around ASP, check out Kevin's introductory article. You should also know your way around basic SQL statements, which Charles covers in his ColdFusion article.
If you're having problems getting PWS to work, you may have to make some changes to the system registry. Since I don't want to be responsible for crashing your system, I'll simply suggest that you research the problem through the ASP message board at 4GuysFromRolla.com or via Usenet groups at Deja.com.
When you use ASP, HTML and server-side code (in our case VBScript) are intermingled, so you need something that allows you to manipulate your pages easily. (If you don't know what I mean by intermingled, you haven't read Kevin's piece. Go back and do so now, and then join us when you're ready.) A good text editor is in order. I'm sure you have a favorite of your own, but if you're undecided and you're on a PC, I'd recommend HomeSite.
Finally, you're going to need a database. For the purposes of this demonstration, I'm using Microsoft Access. I decided on Access for one very simple reason: It's sitting on my hard drive, and it's likely to be on yours. This doesn't mean you should run away if you're using something else. FileMaker Pro 4.1, Paradox 7+, or any other current desktop database will work just fine for the purposes of this tutorial - just remember as you follow along that you need to figure out the appropriate program-specific commands. And I think you're up for it - you strike me as the smart-but-quiet type, the kind who reads every page of an informative article then sends complimentary email to the writer's boss. And damn if you aren't looking sexier already.
Lest you become disgusted with my Redmond-centric choices, let me point something out to you. It's not totally unheard of for someone to make the leap from coding in a home-based Win98/PWS/Access setup to a corporate NT Server/IIS/SQL Server environment. And there's always a well-paying job or two available for people with these skills.
It's funny how personal well-being can trump concerns about monopolistic practices, ain't it? Finally, you should be able to get through this tutorial even if you're a complete newbie. But if you have no programming experience, take a look at JoAnne's Intro to Programming. You're going to need to learn this stuff eventually - why not now?
Now that we're all up and running, let's take a look at the backend database we'll be creating.
Page 4 — Our Sample Application

So what are we going to create?
Every good application comes from a great idea, and I've got one. It's going to make me a ton of money. See, I live in San Francisco, where the hills are steep, the parking scarce, and the population wealthy. When combined, these factors make for an outstanding business opportunity for the person with the right skills.
And I am the man with the talent. My parallel parking abilities are unmatched. I can maneuver my standard transmission rig into the tightest imaginable space on a 15 percent downhill grade. The way I figure it, the elite of this burg would be willing to pay plenty to learn how to park like me.
I've been unable to get the VC (that's venture capital) I need to get this gig going. Investors always say, "If they're so rich, why wouldn't they pay for a garage?" But those stuffed shirts just don't understand the satisfaction that comes from a good park.
So I've decided to bankroll the business myself. Since I'm not exactly rolling in it (yet), I'm going to forgo the fancy office space and company car and instead build a simple, affordable Web site that will allow people to check out my services and make appointments. But a mere sign-in sheet isn't going to work here. I want to be able to track the cars that my clients drive, the pitch of the slope on which they need help, and the type of transmission they're using. Further, I want to have rates generated depending on these criteria.
I also need the ability to post my available appointments as the mood strikes me - and have those appointments show up on the pages as I enter them into the database.
To understand all I needed my database to do, I sketched out some sample pages. Here's one of the key pages. Notice the list of cars (which will need to be dynamically drawn from the database so I can add additional cars whenever I need to). Also, as I said before, it's important for me to know the type of transmission I'll be dealing with.
Sample Interface
After I've gathered all the necessary information, I want a screen like this to appear:
The rate I've quoted is calculated for each client based on that person's car, the type of transmission, and the slope of the hill on which they want lessons. The list of available appointments should also be drawn from the database, which will be populated by only those appointment slots I've entered. As these appointments are booked, they should no longer appear in this listing. Sound like fun? Sound like a sure winner? If you think so, or even if you don't, join me for Lesson 2, where we'll start building this application. We'll begin with the most important part: setting up your database.
And when you're done with that, trust me, you'll be fighting off the babes/hunks/company headhunters.

Page 1 — Your First Database — Lesson 2

Let me start with a confession: I am, and have always been, a hacker. Code that I author is neither elegant nor efficient. It is, however, functional. Once I get something that works, I'm generally satisfied with myself - I mean, if it works, that's good enough, right?
In contrast, I would never, ever take this cavalier attitude with a database, where a solid data structure is absolutely vital. If you start out with poorly arranged data, you could be in big trouble down the line, trouble from which you might never recover. Even experienced database administrators have been known to discover, halfway into a project, that their data design is flawed and they have no choice but to throw out all the code they've written so far and start from scratch. As you tackle your first database, be prepared to make some mistakes. In fact, plan on doing a couple of practice apps before you're ready to pitch some intricate, in-house data-tracking project to your boss. (Trust me on this one.)
You need the practice because relational database design is difficult conceptually and even harder practically. If you're new to the database world, perhaps a slightly more thorough introduction to the topic will make things easier. I recommend you pick up a copy of Database Design for Mere Mortals by Michael J. Hernandez. It's written for the layperson, and presents some difficult concepts clearly. And it will fill some gaps left by this tutorial. (We'll only be covering the concepts needed to get our small project off the ground).
That said, let's get on with the tutorial. I'm going to start with a stripped-down definition of a relational database.
Page 2 — The Relational Database

Here's your definition: A relational database stores data in one or more tables, and these tables can be joined in a variety of ways to efficiently access the information.
Assuming that definition still leaves you with a question or two, let me give you an example. Say my database contains two tables: The first is named Clients, the other Cars.
clientID fname lname email carID
85 John Doe jdoe@whatever.com 45
carID carname carrate
45 Buick LeSabre 1
Given the information above, we can determine the name of the car owned by John Doe and the rate for that car by joining these tables at the carID field. The joined table would look like this:
clientID fname lname email carID carName carrate
85 John Doe jdoe@whatever.com 45 Buick LeSabre 1
Just so you know, the language that allows us to join these tables is called SQL (structured query language), which we'll be covering in more detail in Lesson 3.
OK. You may wonder, why is this joining necessary? Why not simply put the carName and rate in the same table with the client information? Well, there just so happens to be an excellent explanation for that. To see what I mean, let's try it your way first:
clientID fname lname email carName carrate
85 John Doe jdoe@whatever.com Buick LeSabre 1
86 The Monkey webmonkey@wired.com Ford Pinto 1.2
87 Another Monkey anothermonkey@wired.com Buick LeSabre 1
There are two major problems here. First, look at the rate for the Buick LeSabre. Say I realized Buicks were harder to park than I first bargained for, so I decided to raise the rate I charged from 1 to 1.1. Thanks to the way this table is constructed, I'd have to go into the records for both client ID 85 and 87 to make the change. This may not sound like a big deal, but imagine how heinous a job it would be if this table had 25,000 records instead of just three. If there were two tables, however, one for clients and one for cars, I'd only have to change the rate information once. To apply the updated rate information to the appropriate records, I'd simply join the tables.
There's another problem here. Look at the record for client ID 86. Let's say I find out this guy's a deadbeat and I want him expunged from the database. If I do that, I'm going to lose information not only about this client, but about the car as well. Again, if we divide the information into two tables, this won't happen. Sure, there would be a record for a car that wasn't in the Clients table, but that's fine. What's more important is that we're prepared for the next client who comes along with a Pinto.
Now, are you ready for some abstruse language - the kind you can use to intimidate those who haven't read this article? I thought so.
This first problem, where the same information would have to be changed in more than one record, is what those in the know refer to as an "update anomaly." The second problem is, as you may have guessed, a "deletion anomaly."
Finally, there is a third type of anomaly. Let's say we add a row of data to the table:
88 Greatest Primate simian@wired.com Pinto 1
If we want to change the rate for Pintos, we have to go in and change both Client ID 86 and 88. It's the same problem we uncovered with the Buick update anomaly, but we may not have noticed it until we added this row. When you add data that reveals an anomaly, it is an "insertion anomaly." When we set up our data, we want to avoid these three kinds of anomalies at all costs. This may sound easy, and in the examples to come, it may even seem easy. But in the real world, when you have to deal with complex data, you'll soon come to understand what a royal pain this can be. Often you'll find yourself well into a project before you notice one of these buggers staring you right in the face.
Now that we have this "no anomaly" goal tucked away in the backs of our minds, let's go ahead and define our tables.
Page 3 — Defining Tables

Before we define our tables, I need to explain one more term: the Primary Key. The Primary Key, quite simply, is a column in a table that contains a unique value in every row. Every table in a database needs one of these - something that makes each row distinct. It's common practice to assign the Primary Key with an AutoNumber field (as records are added to a table, the database automatically inserts a unique numerical value for that field).
Now, back to defining our tables: Let's take a look at the data we want to include in our database (in case you've forgotten, we're building a site that will take appointments for my soon-to-be-profitable parallel parking business). The whole point of this endeavor is to keep track of my appointments. To do this efficiently, there's a bunch of ancillary information I need to keep track of - all the who, when, what, and how much data. To make sure I'm including everything, I'm going to start the table-definition process by building one big table that includes all the information I'll ever need to keep track of my appointments.
appointmentID clientFname clientLname clientEmail carName carSize carRate transmissonType transmissionRate slopeName slopeRate app1Time app1Status app1Cost app2Time app2Status app2Cost
1 Evany Thomas evany@wired.com Toyota Tercel small 1 standard 1 flat 1 4/1/99 3:00 pm reserved 120
2 Josh Allen josh@wired.com Jaguar XJ6 large 1.3 standard 1 moderate 1.2 4/2/99 3:00 pm open 100
3 Nadav Savio nadav@wired.com Maxima medium 1.2 automatic 1.2 friggin' steep 1.4 4/3/99 3:00 pm open 100 4/7/99 3:00 pm open 100
4 Nate Manchester nate@wired.com Maxima medium 1.2 standard 1 flat 1 4/4/99 3:00 pm reserved 110
Glancing at this table, I bet you can see there are a bunch of anomalies. Don't worry - this table is just a sketch, a jumping-off point if you will, which we'll use to make sure we create an anomaly-free data structure. We do this by going through the table above and eliminating each anomaly, one by one. This process is known as normalization, which is actually a fairly formal, well-defined system - one you should get to know well if you're serious about creating databases.
The first step is to remove repeating groups of information and move each group out into its own table. Why should you remove repeating groups of information? The answer, quite simply, is because I said so. It's a rule. You will look both ways before crossing the street, you will cook chicken thoroughly, and you will separate repeating groups of data. (It's part of the anomaly removal process. Trust me.)
You may have already noticed the repeating data in the appointments area: See how Nadav has two appointments? Think about what would happen if he had three, four, or even 10 appointments. I think you can see where this is going, so let's separate these columns out into another table. Now that we've removed the appointment data, we have two tables: one called Appointments, the other Clients. Notice I've given each a Primary Key.
clientID clientFname clientLname clientEmail carName carSize carRate transmissonType transmissionRate slopeName slopeRate
1 Evany Thomas evany@wired.com Toyota Tercel small 1 standard 1 flat 1
2 Josh Allen josha@wired.com Jaguar XJ6 large 1.3 standard 1 moderate 1.2
3 Nadav Savio nadav@wired.com Maxima medium 1.2 automatic 1.2 friggin' steep 1.4
4 Nate Manchester nate@wired.com Maxima medium 1.2 standard 1 flat 1
appID appTime appStatus appCost
1 4/1/99 3:00 pm reserved 120
2 4/2/99 3:00 pm open 100
3 4/2/99 reserved 100
4 4/3/99 3:00 pm open 100
5 4/4/99 3:00 pm reserved 110
"Hold the phone!" I can here you screaming. "Hold that phone just one gosh-darn-stinking minute. This isn't going to work at all. There are still anomalies all over the place." Well, you're very, very clever. We do, in fact, need to continue with project Anomaly Eradication. The next step in organizing this data is to look at the information in terms of dependencies. I don't particularly relish the word dependency, but it is descriptive. Essentially, it means separating out information that is not dependant on the table's Primary Key.
Look at the Clients table above. You'll notice Toyota Tercel has little to do with any one person's email address. Let's separate car information out into another table called Cars. Once we give this table its AutoNumber Primary Key, it'll look something like this:
carID carName carSize carRate
1 Toyota Tercel small 1
2 Jaguar XJ6 large 1.3
3 Maxima medium 1.2
If you look at the table we started with (at the top of this page), you'll see that the transmission and slope information should be treated the same way as the car data. So we'll have tables labeled Slope and Transmission, and each will have an AutoNumber Primary Key. And we should be good to go, right?
Wrong. There's still a sneaky anomaly in the Cars table. Think what would happen if we were to add another medium-sized car, say a Honda Civic Sedan. We'd have to enter the rate, which would be 1.2, just like the Maxima. Then what would happen if we wanted to update the rate associated with medium-sized cars? Hmmmm.
You see the anomaly, don't ya? Let's separate the car size and rate information. OK. This has been one rough page. Read it again if you need to. Then do some stretching, limber up, get a can of your favorite cola, and come back when you're ready to fine-tune our new Car-size and Car-rate tables.
Page 4 — Making Those Tables Relate

If we separate out car size and car rate from the Cars table, we'll be left with two tables that look like this:
carID carName
1 Toyota Camry
sizeID sizeName sizeRate
1 medium 1.2
Do you see anything missing here? You should. We need a way of joining these tables - a way to link a size and rate to a specific car. We need to add a column (sizeID) that will allow for this join. In fact, we need to go through every one of the tables we created on the previous page and add fields that let us establish these relationships.
Now maybe you understand why they call these things relational databases.
Before we move on, let's take a second to jot down all this table information. (Hopefully, it'll make it a bit easier to keep track of these different tables.)
Just one quick note about this change. I've made an executive decision: The Email field will serve as the Primary Key for the Clients table. Yes, I know it's possible for more than one person to use the same email address. For the sake of this database, however, I'm assuming that each person who comes to my site will have his or her own email address.
I reviewed this data structure some months after first publishing this tutorial and saw that there was some room for improvement. The carID, slopeID, and tranID fields should probably be in the Appointments table, not the Clients table. That way, one client would be free to obtain lessons in any number of cars or slopes. This error won't affect the rest of the tutorial, but it's definitely something to be aware of. (See, good data structures are hard!)
If you're starting to feel a bit disconnected from the data right now, don't worry. Just keep moving forward. It should click for you down the line. Now that we have our structure, we need to go to our RDBMS (in my case, MS Access) and create the tables.
Page 5 — Working with Access

When you open Access (or whatever database you're using), you'll be asked what kind of database you want to start with. Select Blank Database. Then, when it asks you where you want to save your database, choose a folder that has no read/write restrictions. If you're using Windows NT, folders within the C:\winnt\profiles directory may have restrictions you're not aware of. I suggest creating your database in the folder that serves out your Web pages.
Next, go over to the Table tab and click New. We'll use the Design mode to transfer the information from our chart on the previous page (along with a bit more detail) into this area. I have no intention of holding your hand through this process. You're big boys and girls, and you can play with the icons and drop-down menus to figure out what's going on. I will, however, give you the following advice. First, make sure you have your Primary Key defined. Next, be as restrictive as you can. For instance, the Size property of the email field defaults to 50. Since few email addresses will be that long, and leaving it at that length would take up more disk space than necessary, you might want to reduce the maximum length. Also, don't use spaces in your field names - it'll be a big bother down the line.
Appointments Table
Now that we've created our tables, we're going to establish the relationships between the tables. But first, I have another definition for you. You remember the Cars and the Clients tables, right? Let's say the Clients table contains the following information.
Clients Table
Take a gander at the carID field. Notice the value in both records is 2. By looking at the Cars table below, we can see that both Jay Greenspan and A. Monkey drive Jaguars.
Cars Table
This bit of info tells us there is a specific type of relationship between carID in the two tables. Since one carID from the Cars table can exist in many records in the Clients table, this is called a one-to-many relationship.
The one-to-many is the most common type of relationship in relational databases, and it is the only type we'll use in this database. There are a couple other types you should be aware of: one-to-one and many-to-many. Read up on them when you can. We know we have a number of One-to-Many relationships, and we should let Access know about them. Choose Relationships from the Tools menus. Then insert all the tables onto the screen. You should see little boxes that represent the tables in your database. Create the relationship by clicking on the field on the One side of the relationship and drag it to the corresponding field on the Many side. In the screen that appears, click on the Create button, and you're set. When you're done, there should be a line connecting these fields, like so:
Example of Connecting Fields
By the time we're done drawing all these relationships, you'll see that every table is connected to something, and that looks pretty cool. If you don't believe me, download the database and take a look for yourself. This would be a great opportunity to peruse the tables and see how the data is defined and, more importantly, how the tables work together.
Now that we have a database, we need to make it accessible to our Web server. Let's do that, shall we?
Page 6 — Connecting to the Web Server

You did go all the way through Lesson 1, right? You didn't just jump to this page for quickie guidelines to connecting your database to the Web server, did you? Well, I can't stop you from getting ahead of yourself, but please, for your own safety, at least look at Lesson 1, page three, where I covered the installation of Personal Web Server for Windows 95 and 98 and NT Workstation. Don't proceed unless you have one of those installed and you know the location of your default Web serving directory.
Molto bene.
You've downloaded the database from this lesson, page five. Now go into your Control Panels and double-click on the ODBC icon. For those of you with older versions of Windows 95, you may have two ODBC items. Select the one with 32 in the middle of the icon.
In the window that appears, click on the Systems DSN tab. From there, click the Add button.
ODBC Interface
On the next screen, select the type of database you're using (in this case, MS Access), then click Finish. The next screen is where we'll actually make the connection. Where it asks you for the Data Source Name, enter the word Parking, then click the Select button and choose the path to your database. Click OK. If the database you're using doesn't show up in this screen, you're going to need to visit the Web site of your database provider and download the ODBC driver.
ODBC Set-up
That's it, bub. Now you're ready to start pulling info from this database, which is what we'll be covering in Lesson 3.
But before you dub yourself a chick/dude magnet, you need to do your homework. To prepare for the next lesson, I'd like you to familiarize yourself with the look, feel, and functionality of our database's front end. Go ahead and download the files that will supply our front end, throw them into a directory that's available to your Web server, and open them up in your browser. Also, drag them into your text editor and look at the code.
If you're just too busy to do all that, at least take a look at some sample pages, and then click on the VIEW THE CODE link to get a look at the code that generated these files. (FYI: These sample pages aren't tied to an actual database, so any information you enter in the forms won't actually enter the database or populate other pages.)
When you finish this assignment, come back for Lesson 3, where we'll go over the jumble of text and code that makes up your database's front end.
Now get cracking!

Lesson 3
1 Your First Database — Lesson 3
2 The Wily Two-dimensional Array
3 VBScript with ASP
4 Working with Forms
5 More on SQL
6 The Wrap-Up
Page 1 — Your First Database — Lesson 3

Be prepared: This lesson is going to be quite a whirlwind tour. We'll be touching briefly, and in little detail, on all the topics you need to make slick, data-driven pages. So sit back and get ready for the onslaught of info.
Assuming you sat through Lesson 2, you should now have a data source name that is defined as Parking and a table within Parking called Cars. If I want to print out a list of all the cars in this table in alphabetical order, all I need is the following code.

SQL="SELECT carName FROM Cars ORDER BY carName"

set conn = server.createobject("ADODB.Connection")

conn.open "parking"

set cars=conn.execute(SQL)


<% do while not cars.eof %>

<%= cars(0) %> <br>



<% cars.close %>  

You probably recognize <br> as a standard HTML tag, but the other stuff may look totally new. There are, however, some things you can pick up from careful examination of this code. For instance, the line that starts <% do while looks like it begins the kind of loop you'd find in any programming language.
But I'm not here to make you guess. I only get paid if you learn. So let's decipher the code together. The first thing you should know is that there's more than one thing going on here. In addition to the small amount of HTML you noticed already, you'll find SQL (structured query language), VBScript (Visual Basic Scripting Edition), and Microsoft's ADO (ActiveX Data Objects) rearing their lovely heads. In the pages ahead, I'll deal with each of them. And since you've been such a great audience, I'm going to throw in a quick primer on the use of HTML forms in combination with all the other stuff.
Before I begin, let's take a look at the first couple of lines of this script. If you've read Kevin's introduction to ASP, you know ASP (active server pages) is simply a collection of five objects. One of these, the Server object, is called in the code set conn =server.createobject("ADODB.Connection"). This line creates a new Connection object with the variable name conn, which opens a connection to the server. The next line, conn.open "parking", uses the open method of the Connection object to establish a tie to the parking database.
In the following line, set cars=conn.execute(SQL), the conn object is being used to execute an SQL statement (which is stored in a variable created in the first line) against the database, which in turn loads an ADO recordset into the variable Cars.
Confused? Read on - this will make perfect sense soon.
Page 2 — The Wily Two-dimensional Array

You already know simple SQL from reading Charles' ColdFusion article, right? If not, just give this one page a read.
OK, say we throw the following SQL statement at our database: SELECT carID, carName FROM Cars ORDER BY carID. The server will return what's known as an ADO recordset to the ASP document. What is a recordset? It's a two-dimensional array. What's a two-dimensional array? It's essentially a table. Below I have a visual representation of the recordset that would be retrieved. Let's say I've loaded this recordset into a variable called Cars.
»»»»»» beginning of file (bof)
1 Porsche 911
2 Mercedes B11
3 Jaguar XJ6
end of file (eof)
The »»»»»» you see above indicates an imaginary starting point, which I'll refer to as a cursor. There are quite a few cursor types available for ADO recordsets, and I suggest you look at a complete list when you get the chance. But keep in mind that, at this point, support for many of these cursors is sketchy at best. There are two basic things we're going to do with our cursor. The first is to ask questions about its location. The position of the cursor indicates we're at the start of this recordset, or the bof. So, what's the answer to the question, "Is cars.bof?" It's "true." Make sense?
The second thing we're going to do with our cursor is give it orders, like, "move to the next record," for example. With that in mind, you can probably see how the following code snippet would scroll through the cars recordset.
<% do while not cars.eof %>

<%= cars(1) %> <br>



When the cursor reaches the eof position, the loop stops.
The only thing in this code that might be unclear is the cars(1) part. As I mentioned earlier, the recordset is a two-dimensional array. In an array, you refer to elements by their numerical position, starting with 0 (it may seem weird, but Perl, JavaScript, and C all deal with arrays in this manner, so you should get accustomed to it).
In our SQL statement, SELECT carID, carName FROM Cars ORDER BY carID, cars(1) will contain the second item (carName), the text from the carName field.
Now that you understand, let's take a pass at VBScript.
Page 3 — VBScript with ASP

As Kevin mentioned in his introduction to ASP, you don't have to use VBScript with ASP pages. You can also use JScript, which is pretty much identical to JavaScript - it's Microsoft's version - and ActiveState offers Perl scripting, which is a nice option for you Perl coders.
But I've decided to use VBScript with ASP for two reasons. First, it is the language most commonly used with ASP, and second, it is quite possibly the easiest programming language ever created. If you're dubious, I think I can bring you around. You already know what this code does.
<% do while not cars.eof 




Now try - think really hard - and figure out the meaning of this statement.

If cars(0) = 'Jaguar' then

        Response.write "Jags rule!"

Elseif cars(0) = 'Mercedes' then

        Response.write "I love my poshmobile!"


        Response.write "My car ain't worth nuthin'!"

End if


This is just a basic if-else statement. In other words, if the car name is Jaguar, the appropriate pro-Jag text will print, but if the car is a Mercedes, it will print a different message, and in all other circumstances, a third message will print.
Let's get totally fancy and put these two pieces together.
<% do while not cars.eof 

        If cars(0) = 'Jaguar' then

                Response.write "Jags rule!"

        Elseif cars(0) = 'Mercedes' then

                Response.write "I love my poshmobile!"


                Response.write "My car ain't worth nuthin'!"

        End if



We're still checking for Jaguars and Mercedes (so we can print out the appropriate message), but now we're checking each and every row in the ADO recordset.
Believe it or not, that, along with some knowledge of VBScript operators, combined with what you already know about ADO and two-dimensional arrays, is enough to get you through quite a lot of coding for your database. In fact, we'll be building the actual pages of our application using these very constructs in the next lesson.
But before we do that, let's practice our VBScript a bit, shall we?
One thing you need to know about VBScript is that it deals with variable types strangely. There is really only one kind of variable in VBScript, and it's called a variant. This does not mean, however, that all variants are the same. There is a numeric variant, a date variant, a text-string variant, as well as some others. The theory with variant is that VBScript is smart enough to figure out what kind of variable you want and will assign it automatically. And this can cause problems. It's something you'll want to be aware of down the road.
Also, it's not necessary to declare variables in VBScript. Of course, good coders always declare their variables, and so should you (using the Dim statement). But the following commands would execute without a problem even if there were no preceding Dim statements.



foobar=foo + bar



If you want to learn a bunch more about VBScript, you can start by taking the Microsoft tutorial, or browse through the language reference info.
Obviously, the onus is on you to go out and learn more VBScript. But I think you're up to it. Once you're comfy with VBScript, you're ready to add forms to the mix.
Page 4 — Working with Forms

Usually, when you create SQL statements, you make use of variables within the statement. For instance, if someone used the site and entered a first name, last name, and email address into the database, you'd want to retrieve this information with a query. Armed with that information, you would then send the following query to the database:
SELECT fname, lname, FROM Clients WHERE email = variableholdingemail
But how are you going to get that variable? You're going to use HTML forms and a couple lines of VBScript.
For the handful of you who've never created a form in HTML, rest assured - they're super easy. Take a look at the HTML that creates a simple input text box.
Here's the code:
<form action="nextpage.html" method="GET" name="myform">

        <input type="Text" name="thebox" align="TOP" size="25"><br>

        <input type="Submit" value="Take A Look" align="MIDDLE">


And here's the form:

Enter whatever text you want into the box and press the button. When a new page appears, take a close look at the URL in your browser. Once you're done with the examination, come on back here.
Welcome back. I'm assuming you noticed two things. First, the URL of the page that loaded matches the action attribute of the opening form tag. Second, the last part of the URL began with a question mark, which was followed by thebox=whatever you typed. Being the astute observer I know you are, you see that Thebox matches the Name attribute of the input tag.
The stuff that follows the question mark is known as the querystring. And the text appears there in name=value pairs. Additional name=value pairs will be separated by an ampersand.
You've seen the textbox in action. Let's take a look at a couple other form elements that will come in handy as you build your data-driven site: hidden and checkbox.
Here's the code:
<form action="nextpage.html" method="get" name="form2">

        <input type="hidden" name="hiddenvalue" value="hiddenstuff"><br>

        <input type="Checkbox" name="check1" value="yup"> Checkbox here


        <input type="Submit" value="Take A Look" align="MIDDLE">


And here's the form:

Checkbox here
Notice that after you click Take A Look, the name and value of the hidden element will be passed to the querystring, whether you've checked the box or not. If the checkbox is clicked, however, the value of the checkbox is passed on to the querystring.
Let's look at radio buttons and the dropdown box. With radio buttons, only one of the values can be selected. You can add as many buttons to a list of potential values as you like, just make sure the name attribute matches in all of them. If you want one of the values to be pre-selected, insert Checked into the tag. With a dropdown box, the text that appears to the user has nothing to do with the value passed to the querystring. As always, it's the value attribute that gets passed.
The code:
<form action="nextpage.html" method="get" name="form3">

        <input type="radio" name="radios" value="radio1" checked>

<b>This is radio1</b><br>

        <input type="radio" name="radios" value="radio2"> <b>This is


        <select name="dropdown">

                <option value="1">drop-down list 1</option>

                <option value="2">drop-down list 2</option>


        <input type="Submit" value="Take A Look" align="MIDDLE">


The form: This is radio1
This is radio2

One more word on forms: In the opening form tag, you have two choices for the method attribute - Get, which we've been using in the above examples, and Post. When the method is Get, the name=value pairs will be passed to the querystring, as you've seen. If, however, you choose Post, the information will be sent to the server from within the HTTP header. You can still access the information (as I'm going to show on the next page), but your user won't be able to see the value. More important, the user won't be able to bookmark the page.
And so ends the crash course in forms. Now back to SQL.
Page 5 — More on SQL

We're getting to the end of our tour. But before we go and create some fabulous pages, we need to look at a bit more SQL.
You now know how to pass information to the querystring using forms. But what good does that do you unless you can get it out of the querystring and onto the ASP page?
No problem. Just throw this line on the page.

The only real problem with the querystring, that I'm aware of, is that the user can see some of what's going on behind the scenes. So there may be times whey you want to use the Post method. In that case, do this:

Let's tackle the single-table Select statement, which clearly won't cause someone like you any problems. You'll just look at it and laugh at the simplicity. Well, stop laughing. It's just ASCII, and you look weird.
Here are a couple tables we went over in Lesson 2 called Clients and Cars.
clientID fname lname email carID
85 John Doe jdoe@whatever.com 45
carID carname carrate
45 Buick LeSabre 60
Here we have two tables that, for obvious reasons, we'd like to join on the carID field. First, let's retrieve an ADO recordset that looks like this:

clientID fname lname email carID carName carrate
85 John Doe jdoe@whatever.com 45 Buick LeSabre 60
What we need to do is join these tables based on an equality of the carID field. This is what's known as an equi-join, reasonably enough. Our SQL statement needs to reflect that. Below I'm going to build the proper SQL statement for this join, while loading the statement into a variable called SQL. Note that the ampersand (&) means concatenate (i.e., tack on to the end of).

SQL="SELECT fname, lname, email, carID, carName, carrate " 

SQL=SQL & "FROM Clients, Cars "

SQL=SQL & "WHERE email = 'jdoe@whatever.com' "

SQL=SQL & "AND Clients.carID = Cars.carID"

Most of this statement should be familiar. Note that, in the second line, we mention the two tables from which we're calling information, Clients and Cars. In the final line, Clients.carID = Cars.carID states the equality we're looking for: The carID field in the Clients table has to be equal to the carID field in the Cars table.
Not too tough. Using this technique, you'll be able to join a bunch of tables. I recently created a database that required a five-table join. It wasn't difficult, but the text does get bulky, and trying to find a typo can be a major pain.
The equi-join is all we're going to use in our parallel parking Web site. But be aware that many other types of joins are possible. The more you get into databasing, the more you'll find the need for these techniques. Again, for more information, pick up Michael J. Hernandez's book.
Aside from SELECT, there are two other SQL statements you'll need to learn. The first is INSERT. This, as you can probably guess, puts a brand-new record into a table. The syntax is:
INSERT INTO TableName(column_name1, column_name2, column_name3) VALUES

(value1, value2, value3)

If you're making an insertion into a text field, the value will need to be surrounded by single quotes. Numeric fields don't need them.
These statements will always be assembled with variables, which are usually grabbed from the querystring. Let's assume a form was used to gather the values for fname, lname, position, email, and carID. The phrase would look like this:





SQL="INSERT INTO Clients (fname, lname, email, carID) VALUES ("     

SQL=SQL & "'" & fname & "', "

SQL=SQL & "'" & lname & "', "

SQL=SQL & "'" & position & "', "

SQL=SQL & "'" & email & "', "

SQL=SQL & carID & ")"

Take a look at how this single expression was built, step by step. Just so you know, I could have included everything in one really long line with a bunch of ampersands, but that would be a bear to troubleshoot. Trust me, this sort of construction is the way to go.
So far, we know how to query the information and how to insert new records. But we don't know how to update a record that's already in the database. And we're going to need to do that. Let's take a look at the fabulous UPDATE statement.
Here, the general syntax is UPDATE TableName SET column1=value1, column2=value2 WHERE column3=value3. Using variables to build an UPDATE statement for the Clients table would look like this:
SQL="UPDATE Clients SET staffID=" & staffID & ", "

SQL=SQL & "fname='" & fname & "', "

SQL=SQL & "lname='" & lname & "', "

SQL=SQL & "position='" & position & "'"

SQL=SQL & "carID=" & carID & " "

SQL=SQL &  WHERE email='" & email & "'"

Keep in mind that the column name in the WHERE portion of the statement should be the primary key of the table. If you don't understand why, you should go back and reread Lesson 2.

Page 6 — The Wrap-Up

Think back to the first page of today's lesson. I presented a piece of code that didn't make much sense. To show you how much you've learned, I'd like you to take a look at the following code. By keeping track of the comments, you should be able to understand everything that's going on. Normally, you insert a comment into ASP by placing text after a single quote ('). For clarity, I'm changing the color to help you distinguish my comments from the code.
Let's say the page preceding this code presented a text-box form that asked the user to enter an email address.

'Get the variable from the querystring.


'Build the SQL statement, making sure to surround the text

'field "email" with single quotes

SQL="SELECT fname, lname FROM Clients "

SQL=SQL & "WHERE email='" &  email &  "'"

'Create connection named "conn"

'Load the resulting recordset into the variable myclient

set conn = server.createobject("ADODB.Connection")

conn.open "parking"

set myclient=conn.execute(SQL)

'Test to see if there are any matching records. If not,

give a message.

If myclient.bof and myclient.eof then

    Response.Write ("You are not yet a member. Please sign in.")

'Otherwise give another message.


    Response.Write ("Welcome back!" & myclient(0) & " " & myclient(1) &

". I think I love you."




You've got this stuff down cold and you know it. To put all this hard-learned knowledge to work, join me for Lesson 4, where we'll go on to build the ASP pages that will make my killer app a whopping success and me a billionaire.
Y'all come back now, y'hear?

Lesson 4
1 Your First Database — Lesson 4
2 Using Includes
3 Deciphering More of the Code
4 The Home Stretch
Page 1 — Your First Database — Lesson 4

So far, we've covered a lot of theory but have put little of it into practice. It's time to watch interactive ASP pages at work.
Cheaters beware: If you have come to this final lesson without reading all the other lessons, the stuff that follows isn't going to make much sense. Plus, you're missing out on some tips that will make you a really sexy person. I've already received desperate email from spouses of people who have taken the tutorial, complaining of marriages strained over all the ogling, catcalls, and pinching their honey now receives. But that's not my responsibility. I'm just here to teach.
If you've been with us since the beginning, you've already installed your Personal Web Server, downloaded and hooked up our database, and downloaded our sample files. If you didn't manage to install PWS, you can at least get an idea of what it looks like. Of course, these samples aren't hooked to our database, so the information in them is static (if you want to actually play around with the code, you need to download those sample files).
Go ahead and open the database you downloaded. As you go through the sections of this lesson, enter and delete information from the database and see how it affects your output. It will be fun. And informative. We'll start by taking a look at the meat of the code for info.asp.
Page 2 — Using Includes

When served, info.asp will look like this. If you look at the
code for this page, you'll notice there's next to nothing there.
<table width="500">

<form action="info2.asp" method="get">


     <td width="500" align="center">

<p><!--#include file="carsdown.inc"--> With a <!--#include file="trandown.inc"--> transmission<br>


    <p><input type="Submit" value="Let Jay Know" align="LEFT">      




If you're a savvy Web developer, however, you'll recognize
<!--#include file="carsdown.inc"--> as an include. This line
simply tells the Web server to import the contents of the file
named carsdown.inc as soon as this document is called. Commonly,
includes are used for headers, footers, and the like.
I've decided to use an include in this instance for two reasons.
First, it cleans up what would otherwise be some messy code.
Plus, I think I'll need a listing of cars on another page. And who
wants to retype? That said, let's look at the code for carsdown.inc.

SQLCAR="SELECT carName, carID FROM Cars"

set conncar = server.createobject("ADODB.Connection")

conncar.open "parking"

set cars=conncar.execute(SQLCAR)


<select name="carID">

        <% do while not cars.eof %>

                <Option value = "<%= cars(1) %>"> <%= cars(0) %></Option>




<% cars.close %>  

Nothing new here, really. All we've done is take several techniques
we went over in Lesson 3 and combine them in one HTML form. We've
created a dropdown box that is generated dynamically from the database.
As cars are added to the database, they will appear in this list.
I'd like you to note one important thing here. The value attribute of
each option will be the value of the carID field. Why? Because we're
after the table's primary key. That, as you might remember, is the basis
for the relationships to other tables.
If you're following along and serving pages at home, enter some values in
these boxes and click the button. Notice how the values are passed to the
Time to take on info2.asp.
Page 3 — Deciphering More of the Code

The next page of our app's interface, info2.asp (seen here), doesn't present anything new. I've just decided to spread out the info-gathering process over two pages. On the following page (infofinal.asp), however, things start to get interesting.
Go ahead and open up this page in a text editor. (Didn't download it? Tsk, tsk! Well, you can look at it here.)
I'm not going to start talking about this page from the top because I think it will make more sense if we start here:
      cost = 100 * grademultiple * transmultiple * carszmultiple

Remember how I wanted to calculate the price of each lesson individually, based on the size of the car, transmission type, and grade type? For that reason, I included a field for a multiple in each of the appropriate database tables. Knowing that's the goal, the preceding database calls should make sense. Let's look at one in detail just to make sure you've got it:
SQLCARRATE="SELECT carName, szMultiple FROM Cars, Size "

SQLCARRATE=SQLCARRATE & "WHERE Cars.szID = Size.szID and carID=" &

set conn = server.createobject("ADODB.Connection")

conn.open "parking"

set carrate=conn.execute(SQLCARRATE)




I want you to take note of a couple of things in this code. First, the WHERE portion of our SQL statement achieves an equi-join on the carID field. This allows us to get the name of the car and the rate associated with the car's size in a single database call. I also want to point out that I loaded the retrieved information from the ADO recordset into regular VBScript variables. Technically, this isn't necessary. But I am making three SQL queries with one connection, and I thought these variable names made the code a bit easier to read.
We should also take a moment to look at app.inc, which pulls the available appointments from the database. Here's the code:

SQLAPP="SELECT appID, appTime FROM Appointments WHERE appStatus = 'open' ORDER BY appTime"

set connapp = server.createobject("ADODB.Connection")

connapp.open "parking"

set apps=connapp.execute(SQLAPP)


<font face="arial" size="5">

<% do while not apps.eof



<a href="book.asp?carID=<%= carID %>&tranID=<%= tranID %>&gradeID=<%= gradeID

%>&appID=<%= apps(0) %>&appRate=<%= cost %>"><%= apps(1) %></a><br>




<% connapp.close %>       


There's one particular line here that's begging for your attention. This link may look awful, but if you take a look at the HTML it creates, I think you'll see the purpose. When rendered, this code simply creates a link with the necessary name=value pairs in the querystring. Here's an example of the text it would create: book.asp?carID=2&tranID=2&gradeID=2&appID=8&appRate=156. Prior to this example, we've only used forms to pass information to the querystring. But this method works just fine.
Once we've managed to pass this information, we're ready to look at our last page.
Page 4 — The Home Stretch

While the book.asp page looks nice, there's really nothing new here for you to learn.
The page we really need to look at is update.asp, which you can't even see through your browser, since it has no HTML. Rather, it's comprised of VBScript. The script does some processing and then sends the user to another page with the Response.Redirect command. Go ahead and open it up in your text editor or take a look at the code here.
You can see the UPDATE and INSERT statement that we talked about in Lesson 2. All the information we had gathered from previous pages was passed to the querystring for this page. Then the SQL statements were built from the values pulled from the querystring.
As you might guess, the code ourdate=FormatDateTime(now,vbLongDateTime) loads the current date into the variable ourdate. We're loading this into the database, so we know exactly when the appointment was made. If the person doesn't confirm, I can go into the database later and change the appointment status to Open and remove the email address that was inserted into the Appointments table.
Finally, Response.redirect(URL) tells the ASP server to send the browser to a simple Thank You page. When you see what the resulting page (including the URL) looks like, it should be pretty clear what's going on.
And with that, the last page of my killer app, we come to the end of this tutorial. I've given you the tools you need to create a relational database-driven Web site of your own. Now all that stands between you and the realization of your next bright idea is some hard work and a great deal of patience. As you roll up your sleeves and start building tables and hammering out code, I'm off to practice curbing my wheels. Buena suerte, amigo.


바보들의 영문법 카페(클릭!!)

오늘의 메모....

시사평론-정론직필 다음 카페

바보들의 영문법 다음 카페

티스토리 내 블로그

내 블로그에 있는 모든 글들과 자료에 대한 펌과 링크는 무제한 허용됩니다.
(단, 내 블로그에 덧글쓰기가 차단된 자들에게는 펌, 트랙백, 핑백 등이 일체 허용되지 않음.)

그리고 내 블로그 최근글 목록을 제목별로 보시려면....
바로 아래에 있는 이전글 목록의 최근달을 클릭하시면 됩니다.
그러면 제목을 보고 편하게 글을 골라 보실 수 있습니다.

그리고 내 블로그내 글을 검색하시려면 아래 검색버튼을 이용하시면 됩니다.



free counters