Web based School

- Chapter 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. ToChapter 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 guides have been written about Visual C++. For the examples in this guide, 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 guides. 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%\dbs\wdblog1.ora' size 400K reuse,
            '%oracle_home%\dbs\wdblog2.ora' size 400K reuse
    datafile '%oracle_home%\dbs\wdbsys.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 Chapter 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 Chapter 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++ guides online that should come as part of the C++ software), ODBC API (the subject of several guides), 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 Chapter 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

ToChapter you learned where to start applying SQL using the ordinary, everyChapter 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."