ch15.htm

CGI programming is the hottest stuff to look out for in this book

used with large multitable databases, a connection is made; then
you choose the portion of the database you want to affect: the
table.
<P>
Finally, you can apply conditions to the statement-for example,
&quot;&#133;but only ripe ones.&quot; These conditions consist
of some field of data and the value it should or shouldn't be.
So, you might enter <TT><FONT FACE="Courier">SELECT strawberries
from BigBucket WHERE ripe = 'yes'</FONT></TT>.
<P>
SQL has lots of different variations, depending on the database
you're accessing. If you need lots of conditions and power, you
should familiarize yourself with SQL and whether your database
of choice supports it through either a driver or internal functionality.
<H3><A NAME="DDE">DDE</A></H3>
<P>
An unusual option available in Windows CGI is Dynamic Data Exchange
(DDE). DDE causes a conversation to take place between two concurrently
running applications (assuming they both support DDE). This way,
they can share information in real time, and one can even cause
the other program to do something. The reason this is unusual
is that it's unique to Windows; you won't see it on a UNIX platform
because DDE doesn't exist on UNIX. Some variants exist on those
other platforms, of course, but none of them are quite as open
or as easily accessible as DDE is to most Windows applications.
<P>
Using DDE as a database access method requires that the database
program be running all the time, but this approach does have some
advantages. Although the other program is taking up memory all
the time, starting a DDE conversation has very little impact on
the system. So, if you start up a tiny application that uses DDE
and connects to a DDE-capable database application running on
that machine, you can start performing database operations without
any real additional work.
<P>
The best times to use DDE over ODBC or SQL are when the application
supports neither, some other function needs to be performed (such
as running a macro that can't be run through SQL), or initial
load or access of the database takes an extremely long time. In
all these cases, DDE communications to the already-open database
may give you the performance or functionality edge that your application
needs.
<H2><A NAME="DatabaseTools"><FONT SIZE=5 COLOR=#FF0000>Database
Tools</FONT></A></H2>
<P>
Now that you know what you can store in a database and how information
will come to you, you're ready to see what kinds of tools are
out there for general use.  Depending on your comfort level with
various programming tools, you may be more inclined to drift towards
one particular solution. It's important, though, that you look
at all the options you have available before you decide on a particular
path. You may just see that there's an easier or faster way for
what you want to do.
<H3><A NAME="VisualBasic">Visual Basic</A></H3>
<P>
Visual Basic developers turn to Visual Basic (VB) time and time
again because it provides power and flexibility, making it usable
for almost any task. This fact certainly holds true with databases,
because VB has both internal functions to access database formats
directly and the internal functionality necessary to make short
work of processing that information.
<P>
Though the object-oriented nature of Visual Basic doesn't help
much when you're writing behind-the-scenes applications with no
visual components except text output, the ease with which you
can create and compile an application makes VB useful for any
task. For example, SQL queries are built into VB's implementation
of the Database Access Object (DAO) 2.5, enabling you to build
in powerful queries as you work.
<P>
As a Rapid Application Development (RAD) tool, Visual Basic has
more users than any other similar tool on the market; it is used
extensively in examples provided by server manufacturers, individual
consultants, and thousands of other people all over the place.
Because it has so many built-in functions and prebuilt modules
available for almost every task, using VB is more like stacking
building blocks together than building your own application from
scratch. And when you're in a hurry, that's good news.
<H3><A NAME="Delphi">Delphi</A></H3>
<P>
Borland's Delphi, or &quot;Object Pascal&quot; as some have called
it, is close in popularity to Visual Basic when it comes to developing
powerful Windows applications quickly. Delphi is built on the
same object-oriented principle, but it works just as well for
nonvisual applications that just need raw processing horsepower.
<P>
With modules and classes, you can plug other components into what
you're developing with as little pain as possible, and built-in
functions for Delphi (in the form of these modules) give you the
quick access you need to get the data.
<H3><A NAME="cc">C/C++</A></H3>
<P>
Starting a C program from scratch to perform database access isn't
exactly beginning programming. If you intend to read a text file
line by line and print the results, using C is not too bad; but
if you want to start creating dynamic sets of data from ODBC data
sources, you'll be programming for a while.
<P>
Anything and everything that you need to do for database access
you can do in C. Although freeware and shareware database access
code isn't as rampant for C, you can find examples with source
code that you can modify in almost any location on the Internet.
The problem is that most modules and code fragments aren't designed
to be neatly tied together, so you'll spend lots of time, effort,
and frustration doing it yourself, instead of using a tool designed
to do the job quicker, like Visual Basic or Delphi.
<P>
The benefits of a C program are all in speed and memory overhead.
If you want the best response time and the most efficient program,
you should use C. But you also need lots of expertise in using
it and a good headache medicine for the more complex operations.
<H3><A NAME="OtherTools">Other Tools</A></H3>
<P>
When you're dealing with Windows CGI and its lack of direct STDIN/STDOUT
capabilities, one interesting side effect is that almost any program
in existence can go out and retrieve data from a content file
and then do something with it. All the program has to be able
to do is read text files and environment variables and understand
what it is it's reading in. If a program can take advantage of
the <TT><FONT FACE="Courier">ReadPrivateProfileString()</FONT></TT>
function through a DLL call, that makes the task even easier.
If you really want to use a tool, but you can't make it read environment
variables, you can create a buffer program to help it along.
<P>
<I>Buffer programs</I> are nothing more than intermediary programs
that have some of the functionality you need but don't do all
the work. Say part of what you want to do involves using DDE communications
to talk to your database. You have a tool that makes the conversations
and other functions easy, but it can't read environment variables
without special help, and starting it up every time you need to
use it would be too slow. By using a buffer program to move the
information to where the other program needs it, such as a specific
file it's checking for in a loop, you can use the smaller tool
to take care of the basics and the bigger tool to do the real
work and return the data you're looking for.
<H2><A NAME="CreatingYourDatabase"><FONT SIZE=5 COLOR=#FF0000>Creating
Your Database</FONT></A></H2>
<P>
The time has come to build a database. The purpose of the database
itself is reasonably simple: You want to provide customers with
a way to search for a movie title, get more information on it,
and see how much it costs on videotape. You're creating sort of
an online video store for this example, but this database would
work for any kind of catalog or similar list.
<H3><A NAME="TheData">The Data</A></H3>
<P>
The data in the Movie Catalog consists of the following seven
parts:
<OL>
<LI>Title*
<LI>Director*
<LI>Year*
<LI>Category*
<LI>Price
<LI>Starring
<LI>Summary
</OL>
<P>
The items marked with an asterisk (*) are the fields that you
can let people search through. Sure, people might want to look
for a movie under $20 in the Price field, but that's just another
option that you could implement.
<P>
As you can see from this information, you don't need to make too
much initial data available. People might want to know other possible
information about the movie, but you're not creating an online
reference here (yet), just a guide to what you have. Because you
don't need to go &quot;in depth&quot; with your information, a
flat file database will work just fine. Because you might want
to do more later, however, you build the example so that you can
easily make it more powerful.
<H3><A NAME="TheTools">The Tools</A></H3>
<P>
The choice of tools to create the database for this endeavor is
Microsoft Access. Besides being a cool database tool in general,
Access has one or two other tricks that enable you to make this
example work with less hassle. In addition, because things are
built into tables, you can easily expand with more tables for
reviews, orders, and other features later on, and tie them together.
Remember, in some cases flat file databases are only a state of
mind and implementation. In this case, you're planning for the
future and shouldn't limit yourself much. Figure 15.1 shows some
of the data being used in this example.
<P>
<A HREF="f15-1.gif" ><B>Figure 15.1:</B> <I>Movie catalog data in a Microsoft Access table.</I></A>
<P>
To get at the data for this example, use Visual Basic for two
basic reasons: built-in data access and available modules. First,
and most important, VB can get data from a Microsoft Access database
(MDB) without any extra filters or drivers. By default, Visual
Basic applications can take advantage of Microsoft's DAO architecture
for accessing databases. Because Access and VB both fit into this
standard, an easy conversation occurs between them, saving you
lots of work and enabling you to use the more advanced Access
database functions later on if you want to. Second, but also important,
some freely available VB modules (precreated snippets of VB code)
make this whole task a breeze. After all, using databases isn't
supposed to make life harder.
<P>
In this case, use the Visual Basic module CGI32.BAS, written by
Robert Denny. Besides being an all-around smart guy and making
cool server software, Bob puts a lot of effort into making other
people's code-writing lives easier; CGI32.BAS is a prime example.
Instead of needing to dive back into the intricacies of the Windows
CGI format, you can rely on this prebuilt module to do the dirty
work. Using such modules is absolutely invaluable for both experienced
VB developers (to give a starting point for other code or just
to use) as well as for beginning VB developers (to not have to
worry about stuff you really don't care about yet and to save
yourself countless hours of effort).
<P>
<CENTER><TABLE BORDERCOLOR=#000000 BORDER=1 WIDTH=80%>
<TR><TD><B>Tip</B></TD></TR>
<TR><TD>
<BLOCKQUOTE>
You can find the CGI32.BAS module on any copy of the WebSite server software from O'Reilly and Associates at <TT><FONT FACE="Courier"><A 
HREF="http://software.ora.com/techsupport/software/cgi32.zip">http://software.ora.com/techsupport/software/cgi32.zip</A></FONT></TT>.
</BLOCKQUOTE>
<BLOCKQUOTE>
The functions in CGI32.BAS are looked at in detail in <A HREF="ch14.htm" >Chapter 14</A>, &quot;WinCGI: The Basics,&quot; which will help you better understand what it does for you.
</BLOCKQUOTE>

</TD></TR>
</TABLE></CENTER>
<P>
<H3><A NAME="Communications">Communications</A></H3>
<P>
Because Visual Basic can get the information from the WinCGI input
file, and it can automatically talk to an Access database, what
do you need to know about communications? Well, getting the information
there is only half the battle. To start, you need to be able to
format the users' questions so that the database understands them.
<P>
Data read by Visual Basic can all be stored in internal variables.
That's no trouble. You can, and do, check those variables to see
whether anything is in them. So far, so easy. Now you need to
talk to the database itself. To do that, SQL comes into play.
<P>
Benefits from Visual Basic and Access talking to each other come
in a number of forms, but SQL is one of the big ones. Without
outside interference, VB can direct SQL queries to the Access
database and store the results for ease of use. This way, you
get the full power of a relational database, where you can have
multiple tables and conditions, but you also can do the basic
operations that are currently needed.
<H3><A NAME="TheCode">The Code</A></H3>
<P>
Listing 15.1 shows the MOVIES.BAS file, a Visual Basic 4.0 file
created to search the Access 2.0 database. The code itself is
reasonably short because of the use of CGI32.BAS as an intermediary,
which saves your having to put in lots of calls to read the Windows
CGI. INI file that contains the data.
<P>
<CENTER><TABLE BORDERCOLOR=#000000 BORDER=1 WIDTH=80%>