Teach Yourself SQL in 21 Days, Second Edition
  
- Day 14 -
Dynamic Uses of SQL
Objectives
The purpose of today's lesson is to show you where to start to apply what you
have learned so far. Today's lesson covers, in very broad strokes, practical applications
of SQL. We focus on applications in the Microsoft Windows environment, but the principles
involved are just as applicable to other software platforms. Today you will learn
the following:
- How various commercial products--Personal Oracle7, open database connectivity
(ODBC), InterBase ISQL, Microsoft's Visual C++, and Borland's Delphi--relate to SQL
- How to set up your environment for SQL
- How to create a database using Oracle7, Microsoft Query, and InterBase ISQL
- How to use SQL inside applications written in Visual C++ and Delphi
After reading this material, you will know where to start applying your new SQL
skills.
A Quick Trip
This section examines several commercial products in the context of the Microsoft
Windows operating system and briefly describes how they relate to SQL. The principles,
if not the products themselves, apply across various software platforms.
ODBC
One of the underlying technologies in the Windows operating system is ODBC, which
enables Windows-based programs to access a database through a driver. Rather than
having a custom interface to each database, something you might very well have to
write yourself, you can connect to the database of your choice through a driver.
The concept of ODBC is very similar to the concept of Windows printer drivers, which
enables you to write your program without regard for the printer. Individual differences,
which DOS programming forced you to address, are conveniently handled by the printer
driver. The result is that you spend your time working on the tasks peculiar to your
program, not on writing printer drivers.
ODBC applies this idea to databases. The visual part of ODBC resides in the control
panel in Windows 3.1, 3.11, and Windows 95 and in its own program group in Windows
NT.
We cover ODBC in more detail when we discuss creating the database later today.
Personal Oracle7
Personal Oracle7 is the popular database's latest incursion into the personal
PC market. Don't be put off by the number of programs that Oracle7 installs--we built
all the examples used in the first several days using only the Oracle Database Manager
and SQL*Plus 3.3. SQL*Plus is shown in Figure 14.1.
Figure 14.1.
Oracle7's SQL*Plus.
INTERBASE SQL (ISQL)
The tool used in the other examples is Borland's ISQL. It is essentially the same
as Oracle7 except that Oracle7 is character oriented and ISQL is more Windows-like.
An ISQL screen is shown in Figure 14.2. You type your query in the top edit box,
and the result appears in the lower box. The Previous and Next buttons scroll you
through the list of all the queries you make during a session.
Figure 14.2.
InterBase's Interactive SQL.
Visual C++
Dozens of books have been written about Visual C++. For the examples in this book,
we used version 1.52. The procedures we used are applicable to the 32-bit version,
C++ 2.0. It is used here because of its simple interface with ODBC. It is not the
only compiler with the capability to connect to ODBC. If you use a different compiler,
this section provides a good point of departure.
Visual C++ installs quite a few tools. We use only two: the compiler and the resource
editor.
Delphi
The last tool we examine is Borland's Delphi, which is the subject of many new
books. Delphi provides a scalable interface to various databases.
Delphi has two programs that we use: the InterBase Server (Ibmgr) and the Windows
ISQL (Wisql).
Setting Up
Enough with the introductions--let's get to work. After you install your SQL engine
or your ODBC-compatible compiler, you must do a certain amount of stage setting before
the stars can do their stuff. With both Oracle7 and InterBase, you need to log on
and create an account for yourself. The procedures are essentially the same. The
hardest part is sorting through the hard copy and online documentation for the default
passwords. Both systems have a default system administrator account. (See Figure
14.3.)
Figure 14.3.
InterBase Security manager screen.
After logging on and creating an account, you are ready to create the database.
Creating the Database
This step is where all your SQL training starts to pay off. First, you have to
start up the database you want to use. Figure 14.4 shows Oracle7's stoplight visual
metaphor.
Figure 14.4.
Oracle7 Database Manager.
After you get the green light, you can open up the SQL*Plus 3.3 tool shown in
Figure 14.5.
Figure 14.5.
Oracle SQL*Plus.
At this point you can create your tables and enter your data using the CREATE
and INSERT keywords. Another common way of creating tables and entering
data is with a script file. A script file is usually a text file with the SQL commands
typed out in the proper order. Look at this excerpt from a script file delivered
with Oracle7:
------------------------------------------------------------
-- Script to build seed database for Personal Oracle
----------------------------------------------------------
-- NTES
Called from buildall.sql
-- MODIFICATIONS
-- rs 12/04/94 - Comment, clean up, resize, for production
------------------------------------------------------------
startup nomount pfile=%rdbms71%init.ora
-- Create database for Windows RDBMS
create database oracle
controlfile reuse
logfile '%oracle_home%dbswdblog1.ora' size 400K reuse,
'%oracle_home%dbswdblog2.ora' size 400K reuse
datafile '%oracle_home%dbswdbsys.ora' size 10M reuse
character set WE8ISO8859P1;
The syntax varies slightly with the implementation of SQL and the database you
are using, so be sure to check your documentation. Select File | Open to load this
script into your SQL engine.
Borland's InterBase loads data in a similar way. The following excerpt is from
one of the files to insert data:
/*
* Add countries.
*/
INSERT INTO country (country, currency) VALUES ('USA', 'Dollar');
INSERT INTO country (country, currency) VALUES ('England', 'Pound');
INSERT INTO country (country, currency) VALUES ('Canada', 'CdnDlr');
INSERT INTO country (country, currency) VALUES ('Switzerland', 'SFranc');
INSERT INTO country (country, currency) VALUES ('Japan', 'Yen');
INSERT INTO country (country, currency) VALUES ('Italy', 'Lira');
INSERT INTO country (country, currency) VALUES ('France', 'FFranc');
INSERT INTO country (country, currency) VALUES ('Germany', 'D-Mark');
INSERT INTO country (country, currency) VALUES ('Australia', 'ADollar');
INSERT INTO country (country, currency) VALUES ('Hong Kong', 'HKDollar');
INSERT INTO country (country, currency) VALUES ('Netherlands', 'Guilder');
INSERT INTO country (country, currency) VALUES ('Belgium', 'BFranc');
INSERT INTO country (country, currency) VALUES ('Austria', 'Schilling');
INSERT INTO country (country, currency) VALUES ('Fiji', 'fdollar');
ANALYSIS:
This example inserts a country name and the type currency used in that country
into the COUNTRY table. (Refer to Day 8, "Manipulating Data,"
for an introduction to the INSERT command.)
There is nothing magic here. Programmers always find ways to save keystrokes.
If you are playing along at home, enter the following tables:
INPUT:
/* Table: CUSTOMER, Owner: PERKINS */
CREATE TABLE CUSTOMER (NAME CHAR(10),
ADDRESS CHAR(10),
STATE CHAR(2),
ZIP CHAR(10),
PHONE CHAR(11),
REMARKS CHAR(10));
INPUT:
/* Table: ORDERS, Owner: PERKINS */
CREATE TABLE ORDERS (ORDEREDON DATE,
NAME CHAR(10),
PARTNUM INTEGER,
QUANTITY INTEGER,
REMARKS CHAR(10));
INPUT:
/* Table: PART, Owner: PERKINS */
CREATE TABLE PART (PARTNUM INTEGER,
DESCRIPTION CHAR(20),
PRICE NUMERIC(9, 2));
Now fill these tables with the following data:
INPUT/OUTPUT:
SELECT * FROM CUSTOMER
NAME ADDRESS STATE ZIP PHONE REMARKS
========== ========== ====== ====== ======== ==========
TRUE WHEEL 55O HUSKER NE 58702 555-4545 NONE
BIKE SPEC CPT SHRIVE LA 45678 555-1234 NONE
LE SHOPPE HOMETOWN KS 54678 555-1278 NONE
AAA BIKE 10 OLDTOWN NE 56784 555-3421 JOHN-MGR
JACKS BIKE 24 EGLIN FL 34567 555-2314 NONE
INPUT/OUTPUT:
SELECT * FROM ORDERS
ORDEREDON NAME PARTNUM QUANTITY REMARKS
=========== ========== =========== =========== =======
15-MAY-1996 TRUE WHEEL 23 6 PAID
19-MAY-1996 TRUE WHEEL 76 3 PAID
2-SEP-1996 TRUE WHEEL 10 1 PAID
30-JUN-1996 TRUE WHEEL 42 8 PAID
30-JUN-1996 BIKE SPEC 54 10 PAID
30-MAY-1996 BIKE SPEC 10 2 PAID
30-MAY-1996 BIKE SPEC 23 8 PAID
17-JAN-1996 BIKE SPEC 76 11 PAID
17-JAN-1996 LE SHOPPE 76 5 PAID
1-JUN-1996 LE SHOPPE 10 3 PAID
1-JUN-1996 AAA BIKE 10 1 PAID
1-JUL-1996 AAA BIKE 76 4 PAID
1-JUL-1996 AAA BIKE 46 14 PAID
11-JUL-1996 JACKS BIKE 76 14 PAID
INPUT/OUTPUT:
SELECT * FROM PART
PARTNUM DESCRIPTION PRICE
=========== ==================== ===========
54 PEDALS 54.25
42 SEATS 24.50
46 TIRES 15.25
23 MOUNTAIN BIKE 350.45
76 ROAD BIKE 530.00
10 TANDEM 1200.00
After you enter this data, the next step is to create an ODBC connection. Open
the Control Panel (if you are in Win 3.1, 3.11, or Windows 95) and double-click the
ODBC icon.
NOTE: Several flavors of SQL engines load
ODBC. Visual C++, Delphi, and Oracle7 load ODBC as part of their setup. Fortunately,
ODBC is becoming as common as printer drivers.
The initial ODBC screen is shown in Figure 14.6.
Figure 14.6.
ODBC's Data Sources selection.
This screen shows the current ODBC connections. You want to create a new connection.
Assuming you used InterBase and called the new database TYSSQL (give yourself 10
bonus points if you know what TYSSQL stands for), press the Add button and select
the InterBase Driver, as shown in Figure 14.7.
Figure 14.7.
Driver selection.
From this selection you move to the setup screen. Fill it in as shown in Figure
14.8.
Figure 14.8.
Driver setup.
You can use your own name or something short and easy to type, depending on the
account you set up for yourself. The only tricky bit here, at least for us, was figuring
out what InterBase wanted as a database name. Those of you coming from a PC or small
database background will have to get used to some odd-looking pathnames. These pathnames
tell the SQL engine where to look for the database in the galaxy of computers that
could be connected via LANs.
Using Microsoft Query to Perform a Join
Now that you have made an ODBC connection, we need to make a slight detour to
a rather useful tool called Microsoft Query. This program is loaded along with Visual
C++. We have used it to solve enough database and coding problems to pay for the
cost of the compiler several times over. Query normally installs itself in its own
program group. Find it and open it. It should look like Figure 14.9.
Figure 14.9.
Microsoft Query.
Select File | New Query. Your TYSSQL ODBC link does not appear, so click the Other
button to bring up the ODBC Data Sources dialog box, shown in Figure 14.10, and select
TYSSQL.
Figure 14.10.
Data Sources dialog box.
Click OK to return to the Select Data Source dialog box. Select TYSSQL and click
Use, as shown in Figure 14.11.
Figure 14.11.
Select Data Source dialog box.
Again, small database users aren't accustomed to logging on. Nevertheless, type
your password to move through the screen.
The Add Tables dialog box, shown in Figure 14.12, presents the tables associated
with the database to which you are connected. Select PART, ORDERS,
and CUSTOMER, and click Close.
Figure 14.12.
Selecting tables in Query.
Your screen should look like Figure 14.13. Double-click ADDRESS and NAME
from the CUSTOMER table. Then double-click ORDEREDON and PARTNUM
from ORDERS.
Figure 14.13.
Visual representation of a table in Query.
Now for some magic! Click the button marked SQL in the toolbar. Your screen should
now look like Figure 14.14.
Figure 14.14.
The query that Query built.
This tool has two functions. The first is to check the ODBC connection. If it
works here, it should work in the program. This step can help you determine whether
a problem is in the database or in the program. The second use is to generate and
check queries. Add the following line to the SQL box and click OK:
WHERE CUSTOMER.NAME = ORDERS.NAME AND PART.PARTNUM = ORDERS.PARTNUM
Figure 14.15 shows the remarkable result.
Figure 14.15.
Query's graphic representation of a join.
You have just performed a join! Not only that, but the fields you joined on have
been graphically connected in the table diagrams (note the zigzag lines between NAME
and PARTNUM).
Query is an important tool to have in your SQL arsenal on the Windows software
platform. It enables you examine and manipulate tables and queries. You can also
use it to create tables and manipulate data. If you work in Windows with ODBC and
SQL, either buy this tool yourself or have your company or client buy it for you.
It is not as interesting as a network version of DOOM, but it will save you time
and money. Now that you have established an ODBC link, you can use it in a program.
Using Visual C++ and SQL
NOTE: The source code for this example
is located in Appendix B, "Source Code Listings for the C++ Program Used on
Day 14."
Call up Visual C++ and select AppWizard, as shown in Figure 14.16. The name and
subdirectory for your project do not have to be identical.
Figure 14.16.
Initial project setup.
Click the Options button and fill out the screen as shown in Figure 14.17.
Figure 14.17.
The Options dialog box.
Click OK and then choose Database Options. Select Database Support, No File Support
as shown in Figure 14.18.
Figure 14.18.
The Database Options dialog box.
Click the Data Source button and make the choices shown in Figure 14.19.
Figure 14.19.
Selecting a data source.
Then select the CUSTOMER table from the Select a Table dialog box, shown
in Figure 14.20.
Figure 14.20.
Selecting a table.
Now you have selected the CUSTOMER table from the TYSSQL database. Go
back to the AppWizard basic screen by clicking OK twice. Then click OK again to display
the new application information (see Figure 14.21), showing the specifications of
a new skeleton application.
Figure 14.21.
AppWizard's new application information.
After the program is generated, you need to use the resource editor to design
your main screen. Select Tools | App Studio to launch App Studio. The form you design
will be simple--just enough to show some of the columns in your table as you scroll
through the rows. Your finished form should look something like Figure 14.22.
Figure 14.22.
Finished form in App Studio.
For simplicity we named the edit boxes IDC_NAME, IDC_ADDRESS,
IDC_STATE, and IDC_ZIP, although you can name them whatever you
choose. Press Ctrl+W to send the Class Wizard page to the Member Variables and set
the variables according to Figure 14.23.
Figure 14.23.
Adding member variables in Class Wizard.
NOTE: The program was nice enough to provide
links to the table to which you are connected. Links are one of the benefits of working
through Microsoft's wizards or Borland's experts.
Save your work; then press Alt+Tab to return to the compiler and compile the program.
If all went well, your output should look like Figure 14.24. If it doesn't, retrace
your steps and try again.
Figure 14.24.
A clean compile for the test program.
Now run your program. It should appear, after that pesky logon screen, and look
like Figure 14.25.
Figure 14.25.
The test program.
An impressive program, considering that you have written zero lines of code so
far. Use the arrow keys on the toolbar to move back and forth in the database. Notice
that the order of the data is the same as its input order. It is not alphabetical
(unless you typed it in that way). How can you change the order?
Your connection to the database is encapsulated in a class called Ctyssqlset,
which the AppWizard created for you. Look at the header file (tyssqset.h):
// tyssqset.h : interface of the CTyssqlSet class
//
////////////////////////////////////////////////////////////////////////
class CTyssqlSet : public CRecordset
{
DECLARE_DYNAMIC(CTyssqlSet)
public:
CTyssqlSet(CDatabase* pDatabase = NULL);
// Field/Param Data
//{{AFX_FIELD(CTyssqlSet, CRecordset)
Cstring m_NAME;
Cstring m_ADDRESS;
Cstring m_STATE;
Cstring m_ZIP;
Cstring m_PHONE;
Cstring m_REMARKS;
//}}AFX_FIELD
// Implementation
protected:
virtual CString GetDefaultConnect();// Default connection string
virtual CString GetDefaultSQL();// default SQL for Recordset
virtual void DoFieldExchange(CFieldExchange* pFX);// RFX support
};
ANALYSIS:
Note that member variables have been constructed for all the columns in the table.
Also notice the functions GetDefaultConnect and GetDefaultSQL;
here's their implementations from tyssqset.cpp:
CString CTyssqlSet::GetDefaultConnect()
{
return ODBC;DSN=TYSSQL;";
}
CString CTyssqlSet::GetDefaultSQL()
{
return "CUSTOMER";
}
GetDefaultConnect makes the ODBC connection. You shouldn't change it.
However, GetDefaultSQL enables you to do some interesting things. Change
it to this:
return "SELECT * FROM CUSTOMER ORDER BY NAME";
Recompile, and magically your table is sorted by name, as shown in Figure 14.26.
Figure 14.26.
Database order changed by SQL.
Without going into a tutorial on the Microsoft Foundation Class, let us just say
that you can manipulate CRecordSet and Cdatabase objects, join
and drop tables, update and insert rows, and generally have all the fun possible
in SQL. You have looked as far over the edge as you can, and we have pointed the
way to integrate SQL into C++ applications. Topics suggested for further study are
CRecordSet and Cdatabase (both in the C++ books online that should
come as part of the C++ software), ODBC API (the subject of several books), and the
APIs provided by Oracle and Sybase (which are both similar to the ODBC API).
Using Delphi and SQL
Another important database tool on the Windows software platform is Delphi. The
splash that comes up as the program is loading has a picture of the Oracle at Delphi,
surrounded by the letters SQL. In the C++ example you rewrote one line of code. Using
Delphi, you will join two tables without writing a single line of code!
NOTE: The code for this program is located
in Appendix C, "Source Code Listings for the Delphi Program Used on Day 14."
Double-click Delphi's icon to get it started. At rest the program looks like Figure
14.27.
Figure 14.27.
The Delphi programming environment.
Delphi requires you to register any ODBC connections you are going to use in your
programming. Select BDE (Borland Database Environment) from the Tools menu and then
fill out the dialog box shown in Figure 14.28.
Figure 14.28.
Registering your connections.
Click the Aliases tab shown at the bottom of Figure 14.28 and assign the name
TYSSQL, as shown in Figure 14.29.
Figure 14.29.
Adding a new alias.
Select File | New Form to make the following selections. Start by choosing the
Database Form from the Experts tab, as shown in Figure 14.30.
Figure 14.30.
The Experts page in the Browse gallery.
Then choose the master/detail form and TQuery objects, as shown in Figure
14.31.
Figure 14.31.
The Database Form Expert dialog box.
NOTE: Delphi enables you to work with
either a query or a table. If you need flexibility, we recommend the TQuery
object. If you need the whole table without modification, use the TTable
object.
Now select the TYSSQL data source you set up earlier, as shown in Figure 14.32.
Figure 14.32.
Choosing a data source.
Choose the PART table as the master, as shown in Figure 14.33.
Figure 14.33.
Choosing a table.
Choose all its fields, as shown in Figure 14.34.
Figure 14.34.
Adding all the fields.
Pick the Horizontal display mode, as shown in Figure 14.35.
Figure 14.35.
Display mode selection.
Then choose ORDERS, select all its fields, and select Grid for its display
mode, as shown in Figures 14.36, 14.37, and 14.38.
Figure 14.36.
Choosing the table for the detail part of the form.
Figure 14.37.
Selecting all the fields.
Figure 14.38.
Selecting the orientation.
Now the software enables you to make a join. Make the join on PARTNUM,
as shown in Figure 14.39.
Figure 14.39.
Making the join.
Now go ahead and generate the form. The result looks like Figure 14.40.
Figure 14.40.
The finished form.
Compile and run the program. As you select different parts, the order for them
should appear in the lower table, as shown in Figure 14.41.
Figure 14.41.
The finished program.
Close the project and click one or both of the query objects on the form. When
you click an object, the Object Inspector to the left of the screen in Figure 14.42
shows the various properties.
Figure 14.42.
The query in the TQuery object.
Try experimenting with the query to see what happens. Just think what you can
do when you start writing code!
Summary
Today you learned where to start applying SQL using the ordinary, everyday stuff
you find lying on your hard drive. The best way to build on what you have learned
is to go out and query. Query as much as you can.
Q&A
Q What is the difference between the ODBC API and the Oracle and Sybase APIs?
A On a function-by-function level, Oracle and Sybase are remarkably similar,
which is not a coincidence. Multiple corporate teamings and divorces have led to
libraries that were derived from somewhat of a common base. ODBC's API is more generic--it
isn't specific to any database. If you need to do something specific to a database
or tune the performance of a specific database, you might consider using that database's
API library in your code.
Q With all the available products, how do I know what to use?
A In a business environment, product selection is usually a compromise
between management and "techies." Management looks at the cost of a product;
techies will look at the features and how the product can make their lives easier.
In the best of all programming worlds, that compromise will get your job done quickly
and efficiently.
Workshop
The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, "Answers to Quizzes and Exercises."
Quiz
- 1. In which object does Microsoft Visual C++ place its SQL?
2. In which object does Delphi place its SQL?
3. What is ODBC?
4. What does Delphi do?
Exercises
- 1. Change the sort order in the C++ example from ascending to descending
on the State field.
2. Go out, find an application that needs SQL, and use it.
© Copyright, Macmillan Computer Publishing. All
rights reserved.
| 
