DECEMBER, 1996
CIO OUT LOUD
by Alain Trottier
As a departure from my usual bantering, here is a simple example of
connecting the Internet to an SQL database. It
requires NT 4.0, Internet Information Server and SQL Server. This
example is how I set up an employee lookup app. The
user searches the database with a search string such as "a%" and has a
list of matches returned in a new screen. The path
information presented here is of course useless to you so be careful
when one file calls another. When the user clicks on
a name all the information about that employee is then displayed in a
nice format. This will work for any collection of
people like customers, group members and so on. Its a simple app, but
illustrates why I think the most powerful Microsoft
technology to hit the streets this year was connecting the Internet to
an SQL Server data source through IIS.
STEP ONE
First create a database to host the employee data. This is simple,
standard SQL. Please replace all triple stars (***)
with correct comment tag in actual SQL script. There are many ways to
write these things so use your own preference. For
example I could have included the DEFAULT constraint in the
CREATE TABLE statement. I chose not to do it
that way because I wanted to make the defaults available for other
tables. You decide what works best.
*** Create 14 MB database and 4 MB transaction log space. ***
CREATE DATABASE help_system
ON help_device_data = 14 ***7168 pages***
LOG ON help_device_log = 4 ***2048 pages***
*** Create the help_author table to store information about people
involved in using
the database such as help document authors, customers, administrators
and the like.
***
CREATE TABLE person
(
person_id INT IDENTITY(1,1), ***counter***
person_lname VARCHAR(40) NOT NULL, ***last name***
person_fname VARCHAR(40) NULL, ***first name***
person_title VARCHAR(40) NULL,
person_org_name VARCHAR(50) NULL, ***name of organization***
person_street VARCHAR(50) NULL, ***street address***
person_city VARCHAR(20) NULL,
person_state VARCHAR(20) NULL,
person_country VARCHAR(30) NULL,
person_zip VARCHAR(15) NULL,
person_phone VARCHAR(15) NULL,
person_ext VARCHAR(10) NULL,
person_phone2 VARCHAR(15) NULL,
person_beeper VARCHAR(15) NULL,
person_fax VARCHAR(15) NULL,
person_email VARCHAR(40) NULL, ***email address***
person_email2 VARCHAR(40) NULL, ***second email address***
person_url VARCHAR(40) NULL, ***web address***
person_note VARCHAR(255) NULL ***information ***
)
*** Set and bind some defaults ***
CREATE DEFAULT phone_default as "800-999-1809"
SP_BINDEFAULT phone_default, 'person.person_phone'
CREATE DEFAULT note_default as "None"
SP_BINDEFAULT note_default, 'person.person_note'
CREATE DEFAULT phone_ext_default as "41366"
SP_BINDEFAULT phone_ext_default, 'person.person_ext'
CREATE DEFAULT email_default as "atrottier@platsoft.com"
SP_BINDEFAULT email_default, 'person.person_email'
CREATE DEFAULT url_default as "http://www.platsoft.com"
SP_BINDEFAULT url_default, 'person.person_url'
CREATE DEFAULT fax_default as "714-450-4493"
SP_BINDEFAULT fax_default, 'person.person_fax'
CREATE DEFAULT org_name_default as "Platinum Software Inc."
SP_BINDEFAULT org_name_default, 'person.person_org_name'
CREATE DEFAULT street_default as "195 Technology"
SP_BINDEFAULT street_default, 'person.person_street'
CREATE DEFAULT city_default as "Irvine"
SP_BINDEFAULT city_default, 'person.person_city'
CREATE DEFAULT state_default as "CA"
SP_BINDEFAULT state_default, 'person.person_state'
CREATE DEFAULT zip_default as "92718-2402"
SP_BINDEFAULT zip_default, 'person.person_zip'
CREATE DEFAULT country_default as "US"
SP_BINDEFAULT country_default, 'person.person_country'
STEP TWO
Populate the table with some sample data. Since the defaults cover most
of the fields you need only INSERT a few
of them.
STEP THREE
Create an HTML file (e.g., search_au.htm) that acts as the front end of
the app:
<html>
<head>
<title>Searching People</title>
</head>
<body>
<CENTER><H1>Employee Search
Page;</H1></CENTER>
<FORM METHOD="POST"
ACTION="/scripts/help_system/search_au.idc">
Search for what last name: <INPUT NAME="au__lname" VALUE="%" >
Use percent sign % as a wildcard (i.e., % by itself returns all names
and a% returns
all names beginning with the letter a).
<P>
<INPUT TYPE="SUBMIT" VALUE="Run Query">
</form>
</body>
</html>
STEP FOUR
Create this IDC (e.g., search_au.idc) file in the Scripts directory.
Name and location MUST match the path in POST tag in
the HTML file above:
Datasource: Web SQL
Username: sa
Template: search_au.htx
SQLStatement:
+SELECT person_lname, person_fname
+ FROM help_system.dbo.person
+ WHERE person_lname like '%au__lname%'
STEP FIVE
Create the html template file (e.g., search_au.htx) in the Scripts
directory. Name and location must match the path
specified in the calling IDC file.
<HTML>
<HEAD><TITLE>Employees as
links</TITLE></HEAD>
<BODY BGCOLOR="FFFFFF">
<CENTER><H1>Employees as links</H1></CENTER>
<hr>
<font size=2>
<CENTER>
<H2>Names of People Like:
<I><%idc.au__lname%></I></H2>
<TABLE BORDER>
<%begindetail%>
<%if CurrentRecord EQ 0 %>
<caption>Query results:</caption>
<TR><TH><B>Last
Name</B></TH><TH><B>First
Name<BR></B></TH></TR>
<%endif%>
<TR><TD><A
HREF="/scripts/help_system/people_info.idc?lname=<%person_lname%>"
><%person_lname%>
</A></TD>&l
t;TD
align="right"><%person_fname%></TD></TR>
<%enddetail%>
</TABLE>
</CENTER>
<P>
<%if CurrentRecord EQ 0 %>
<I><B>Sorry, no names of people like
</I><%idc.au__lname%>.</B>
<%else%>
<P>
<HR>
Click on an author name to see the titles published by that author
<%endif%>
</font>
</td>
</tr>
</table>
</BODY>
</HTML>
STEP SIX
Two more little files and you are done. When someone enters a search
parameter they will get back a list of names. When
they click on one of those hyperlinked names the following file queries
the database for all the information on that
person which is then displayed in the format specified by the associated
HTML template (e.g., people_info.htx)
Datasource: Web SQL
Username: sa
Template: people_info.htx
SQLStatement:
+SELECT *
+ from help_system.dbo.person
+ where person_lname = '%lname%'
STEP SEVEN
This file (e.g., people_info.htx) is the template for the information on
a given employee. It could use a polish so feel
free to improve upon my chicken scratch.This is the last file to
do.
<HTML>
<HEAD><TITLE>Titles published by
author</TITLE></HEAD>
<BODY BACKGROUND="/samples/images/backgrnd.gif">
<BODY BGCOLOR="FFFFFF">
<CENTER><H1>Platinum Support Information
Server</H1></CENTER>
<hr>
<font size=2>
<TABLE BORDER>
<CENTER><H2>Information about
<%idc.lname%></H2></CENTER>
<CENTER>
<TABLE BORDER>
<%begindetail%>
<TR><TH>First Name</TH><TH>Last
Name</TH><TH>Title</TH><TH>ID</TH></TR&
gt;
<TR><TD><I><%person_fname%></I></TD>
<TD>
<I><%person_lname%></I></TD><TD><I>&
lt;
%person_title%></I></TD><TD><I><%person_id
%>
</I></TD></TR>
<TR><TH>Organization</TH></TR>
<TR><TD><I><%person_org_name%></I></TD&
gt;</TR>
<!--<TR><TH>Street</TH><TH>City</TH><
;TH>State</TH></TR>-->
<TR><TH><CENTER>Address</CENTER></TH></
TR>
<TR><TD><I><%person_street%></I></TD>
;<TD><I>
<%person_city%></I></TD><TD><I><%person
_state%></I></TD></TR>
<TR><TD><I><%person_zip%></I></TD>&l
t;TD><I>
<%person_country%></I></TD></TR>
<TR><TH>Phone</TH><TH>Extension</TH><TH
>Email</TH><TH>FAX</TH></
TR>
<TR><TD><I><%person_phone%></I></TD>
<TD><I>
<%person_ext%></I></TD><TD><I><%person_
email%></I></TD>
<TD><I><%person_fax%></I></TD></TR><
BR>
<TR><TH>Notes:</TH></TR>
<TR><TD><I><%person_note%></I></TD>&
lt;/TR>
<%enddetail%>
</TABLE>
</font>
</td>
</tr>
</table>
</CENTER>
</BODY>
</HTML>
STEP EIGHT
Once you finish debugging you can improve a few things like add columns
to the employee table reflecting your company's
special needs such as pager numbers or room numbers. If you do that be
sure to propopate references to the fields in the
last file (people_info.htx). Tell your friends about your cool app and
HR just might show their appreciation at the year
end review!!
My new email address is trottier@deltanet.com. Please don't email this account (atrottier@alphamicro.com) anymore. It will be dead in a day or
two.
Click here to go
back to the December '96 Article Index