In the previous two parts of this series, we looked at the underlying concepts of SQL and many of the basic SQL query components. In this, the third and final installment of the series, we'll look at why you'd want to use SQL, and an example of it in action, via the Microsoft Visual InterDev 6.0 programming environment.
SQL in Action
SQL is a very utilitarian database environment. Normally, with every
database management system comes a new language for construction, querying,
reporting, and design. After a while, it becomes rather tiresome, especially
with the multiple upgrades to each system, to keep working with different
databases. With SQL, however, the language is standardized, thus any
SQL-compliant database will adhere to at least some base language
structures, affording the development staff greater flexibility in their
designs as they can build with relative ease and confidence.
To further expound upon this aspect of SQL, imagine you're the development
department of a large firm. You have three other departments: marketing,
finance, and technical services. Marketing has an Access 2.0 database,
finance is running an older FoxPro database, and technical services is
running a Paradox database, which in sum makes life a nightmare for your
development team should they ever wish to exchange data between systems.
The best plan for a workplace like this would be to upsize everyone to SQL
server databases, and work from there, decreasing development time and
increasing the amount of data interchange between departments. As an
alternative, you may not have to move everyone to SQL servers, but ensure
that whatever they are using is SQL-compliant, which would also make the
developers' lives easier.
Building an InterDev Database
Odds are that if you're reading this publication, you're a Web
developer/designer and you're probably scratching your head wondering,
"well, SQL is great for other programmers, but how does it fit into Web
development?" The answer is obvious - it makes your life easier when your
customers want you to integrate their databases with their Web sites.
Instead of having to learn every aspect of every database management system
out there, you learn SQL and hope that any customer's database is
SQL-compliant, allowing you to quickly hook their pages into the database
for dynamic content. What would you prefer as a developer/designer,
learning every database management language (or having to contract out to
someone who does) or learning one language to do most of the work?
There are plenty of Web development tools and languages that support SQL,
which makes your Web app development all the easier. These tools include
Visual InterDev 6.0, PHP, Cold Fusion, and Drumbeat 2000.
Whatever server platform you use, there is more than likely a toolkit for
As an example of SQL-to-Web integration, I'll give you a walk-through of a
recent demonstration project I worked on. This Web application uses Visual
InterDev 6.0 to connect a Web site to a small (about 10 megabyte) Microsoft
The object in this example was to create a simple query that would display
records with a listed price of under $80,000. The database file was in
Microsoft Access97 format, and contained 13,000 records. The database
records range in price from $10,000 to $4,000,000, and the price field is
The first step with any database in InterDev is to ensure it is registered
as an ODBC data source in Windows (95/98/NT). This is a fairly simple
procedure which I will cover somewhat quickly. First, open the Control
Panel from Windows and select ODBC (or 32-bit ODBC if you have the option).
Then, while on the User DSN tab, hit the Add button. The next steps are to
choose the type of database driver (mine is a Microsoft Access97 driver)
you need, give it a name and description, and choose the actual database
file. When you close out of the configuration, you should see the name of
the connection in the list connections in User DSN. Should you neglect to
register the database, don't fret, you'll get another opportunity when
you're building the data connection in InterDev.
Now the actual work with InterDev begins. After opening the application,
you can use the wizard to quickly build a base Web site. Once the Web site
is built, you need to hook into your database. From the project view window,
select your current site (referred to as "the project" in InterDev).
Right-click on the project title and choose Add Data Connection. The ODBC
data connection manager will appear, allowing you to choose any DSNs you've
already built, or, if you need to create a new one, you can do it here just
as you would in Control Panel.
The next step is to create an
".asp" page. An asp page is a specially
scripted document that contains both
HTML and database code, generally in the
form of SQL statements. To create an asp page, select your project again in
the project viewer, then right click and insert a new asp file (Active
Server Page). The page editor will now display in the center of the screen.
This is where you'll add your dataset controls to display dynamic database
content on the page. When the page appears, it looks more like a code
window than a standard HTML WYSIWYG window due to the application
development style of InterDev, which takes precedence over the HTML layout
style of page design tools.
Creating and embedding your SQL statement(s)
Creating and embedding your SQL statement(s) into the asp page is very
simple, and very visual, barring that you might have any complex functions
you'd need to use. Right-click on the project name and select Add Data
Command. Enter the name you wish to give the command (I've used "Basequery"),
choose SQL Statement and SQL Builder. After a brief wait, the SQL builder
window appears. You need to drag the data table you're going to use (mine
HOMEADV) into the central design pane. InterDev will display all the
fields available to you in the table. From this, you may click on those you
wish to include in your query. I've included
LPRICEA. As you select your fields, the SQL window (two panes below the
main) will display the SQL statement as it's being built. To customize the
statement (I set the
LPRICEA to less than 80000) you'll hand edit it in
this viewer. When you're done designing the query, remember to save it.
Then return to your asp page in the project viewer.
From the toolbar menu on the left, select Design-Time Controls, and
Recordset. Drag the recordset control into the area marked by the
"Add your content above" tag. The recordset control appears, which you'll
need to modify to insert your query. If the Connection pulldown within the
control doesn't show the name of the data connection you've added to the
project, click on it and select the correct connection. The database object
needs to be set to DE Commands and the Object Name must be set to that of
your query. Save the file, and then choose View in Browser. You should now
see a page displaying the data from your query. That's it! That's really
all there is to creating database-connected Web pages with Visual InterDev.
If you don't see a nicely formatted data-connected page, there may be a
problem with your configuration. Some things to check include:
- Did you install Internet Information Server or Personal Web Server when
- Did you install FrontPage extensions and Active Server Pages support?
- Do you have any security permissions set on the database? If so, you
need to set those permissions when creating the data connection in
As a closing note regarding InterDev, you should really run it under NT 4.0,
as IIS and Active Server Pages can be problematic under Windows 95/98, even
when you're just using it to develop sites and not deploy them. On another
note, Active Server Pages are no longer Windows-specific - there have been
ports for Unix and Linux developed, and a module for Apache has also been
in place for some time now.
That's all there is for this series on SQL. Hopefully by now you have a
basic understanding of SQL statements, structures, and its overall utility.
With so many Web application development tools integrating SQL, I'm sure
you'll find it quite useful when designing truly dynamic, database-driven
Microsoft SQL Server and Visual InterDev 6.0
Pricing varies by licensing
PHP Scripting Language
is the Information Services Manager for a real estate listing service in
Oregon. His experience covers Java, Linux, UNIX, NT, Win95/98, Win3.x, and