ch15.htm

好书《C++ Builder高级编程技术》

<HR>


</BLOCKQUOTE>

<P>In the preceding two sections, you have learned the 
basic facts about using LIBS.
The next step is to learn how to create your own databases and tables.
<H3><A NAME="Heading10"></A><FONT COLOR="#000077">Creating Databases</FONT></H3>
<P>Unlike local Paradox or dBASE files, InterBase tables are not 
stored in separate
files located within a directory. Instead, InterBase tables are stored in one large
file called a database. Therefore, you need to first go out and create a database,
and then you can create a series of tables inside this larger 
database.

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The single file system is, in my
	opinion, vastly superior to having a series of separate files. I'm sure you've noticed
	what happens after 
you have placed a few indexes on a typical Paradox table. The
	end result is that your table is associated with six or seven other files, some of
	which have to be present or you can't get at your data! A big Paradox database might
	consist of a 
hundred or more files, all of which have to be backed up, moved from
	place to place, and maintained. Life is much simpler when your whole database is
	stored in a single file! 
<HR>


</BLOCKQUOTE>

<P>The simplest way to create a database is with a 
CASE tool such as SDesigner or
Cadet. However, these tools do not ship with BCB, so you must instead choose between
the Database Desktop, BCB itself, and the WISQL program that ships with the Local
InterBase Server. Absent the presence of a CASE tool, 
I find that my weapon of choice
is WISQL, though this is certainly a debatable decision. (Cadet will be discussed
briefly, along with SDesigner, in Chapter 18, &quot;Working with CASE Tools: Cadet,
ER1, and SDesigner.&quot;)</P>
<P>WISQL stands for 
Windows Interactive Standard Query Language, or simply the Interactive
SQL tool. WISQL is fundamentally a tool for entering SQL statements, with a few other
simple features thrown in for good measure. One advantage of relying on WISQL is
that it 
allows you to work directly in the mother tongue of databases, which is SQL.
I find that defining databases directly in SQL helps me understand their structure,
though of course, there is little reason for resorting to these measures if you have
a 
copy of SDesigner or ERWin available.</P>
<P>You should also remember that WISQL bypasses the BDE altogether. You can therefore
use it to test your connections to InterBase even if you are not sure that you have
the BDE set up correctly. For example, 
if you're having trouble connecting to InterBase
and you're not sure where the problem lies, start by trying to connect with WISQL.
If that works but you can't connect from inside BCB, the problem might lie not with
your InterBase setup, but with the 
way you have deployed the BDE.

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>In addition to WISQL, the other
	important tool that ships with InterBase is the InterBase Server Manager, 
<TT>IBMGR.EXE</TT>.
	It enables you to test connections to servers and perform simple maintenance tasks
	such as backing up and restoring databases and setting passwords. You can use IBMGR
	to back up your data so that you can recover if disaster 
strikes. What little information
	you need for this easy-to-use tool is available in the InterBase documentation and
	in the short section on this tool that appears later in this chapter. 
<HR>


</BLOCKQUOTE>

<P>After starting WISQL, choose File | 
Create Database. A dialog like the one shown
in Figure 15.2 appears. Set the Location Info to Local Engine, because you are in
fact working with local InterBase. (Actually, there is no reason that you have to
use Local InterBase rather than the full 
server version when working through these
examples. However, I will reference LIBS throughout this chapter because it will
be the tool of choice for most readers.)<BR>
<BR>
<A NAME="Heading13"></A><A HREF="15ebu02.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/15/15ebu02.jpg">FIGURE 15.2.</A><FONT 
COLOR="#000077">
</FONT><I>The dialog used for creating databases inside WISQL.</I></P>
<P>In the Database field, enter the name of the table you want to create. If the
table is to be located inside a particular directory, include that directory in 
the
database name. For practice, you should create a database called <TT>INFO.GDB</TT>
that is located in a subdirectory called <TT>DATA</TT>. If it does not already exist
on your system, you should first go to DOS or the Windows Explorer and create 
the
<TT>DATA</TT> subdirectory. After you set up the subdirectory, enter the following
in the <TT>Database</TT> field:</P>
<PRE><FONT COLOR="#0066FF">E:\DATA\INFO.GDB

</FONT></PRE>
<P>You can replace <TT>E:</TT> with the letter for the appropriate 
drive on your
system. The extension <TT>.GDB</TT> is traditional, though not mandatory. However,
I suggest always using this extension so that you can recognize your databases instantly
when you see them. Accidentally deleting even a recently backed 
up database can be
a tragedy.</P>
<P>You can set the user name to anything you want, although the traditional entry
is <TT>SYSDBA</TT>, and the traditional password is <TT>masterkey</TT>. When you
first start out with InterBase, sticking with this 
user name and password combination
is probably best. Even if you assign new passwords to your database, the <TT>SYSDBA</TT>/<TT>masterkey</TT>
combination will still work unless you explicitly remove it using the IBMGR.</P>
<P>After you have entered a 
user name and password, you can create the database by
clicking the OK button. If all goes well, you are then placed back inside WISQL proper.
At this stage, you can either quit WISQL or add a table to your database. If something
goes wrong, an error 
message will appear. Click the Details button to try to track
down the problem.</P>
<P>Assuming all goes well, the following SQL statement can be run inside WISQL if
you want to create a very simple table with two fields:</P>
<PRE><FONT 
COLOR="#0066FF">CREATE TABLE TEST1 (FIRST VARCHAR(20), LAST INTEGER);

</FONT></PRE>
<P>Enter this line in the <TT>SQL Statement</TT> field at the top of WISQL, and then
click the Run button. If all goes smoothly, your statement will be echoed in the

ISQL output window without being accompanied by an error dialog. The lack of an error
dialog signals that the table has been created successfully.</P>
<P>The preceding <TT>CREATE TABLE</TT> command creates a table with two fields. The
first is a 
character field containing 20 characters, and the second is an integer
field.

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The table-creation code shown here
	is used to describe or create a table 
in terms that WISQL understands. In fact, you
	can use this same code inside a <TT>TQuery</TT> object in a BCB program. 
<HR>


</BLOCKQUOTE>

<P>Throughout most of this chapter and the next, I work with WISQL rather than with
the DBD. In describing 
how to perform these actions in WISQL, I do not mean to imply
that you can't use the Database Desktop to create or alter InterBase tables. In fact,
the 32-bit version of DBD provides pretty good support for InterBase tables. Still,
I have found WISQL 
to be considerably more powerful than I suspected when I first
started using it. Once again, I should add that neither of these tools is as easy
to use as a good CASE tool.</P>
<P>After creating a database and table, you should choose File | Commit 
Work. This
command causes WISQL to actually carry out the commands you have issued. At this
stage, you should choose File | Disconnect from Database.</P>
<P>In this section, you have learned the basic steps required to use InterBase to
create a 
database and table. The steps involved are not particularly complicated,
although they can take a bit of getting used to if you're new to the world of SQL.
<H3><A NAME="Heading15"></A><FONT COLOR="#000077">Exploring a Database with WISQL</FONT></H3>

<P>WISQL provides a number of tools that can help you explore a database and its
contents. In the preceding section, you created a database with a single table. In
this section, you will learn how to connect to the database and table from inside

WISQL. You will also see how to examine the main features of the entities you have
created.</P>
<P>To connect to <TT>INFO.GDB</TT>, choose File | Connect to Database, which brings
up the dialog shown in Figure 15.3. Enter the drive and the database as 
<TT>e:\data\info.gdb</TT>,
where <TT>e:</TT> represents the appropriate drive on your machine. Enter the user
as <TT>SYSDBA</TT>, and the password as <TT>masterkey</TT>. If all goes well, you
should be able to connect to the database by clicking the 
OK button. Once again,
success is signaled by the lack of an error message.<BR>
<BR>
<A NAME="Heading16"></A><A HREF="15ebu03.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/15/15ebu03.jpg">FIGURE 15.3.</A><FONT COLOR="#000077">
</FONT><I>Connecting to the <TT>INFO.GDB</TT> database using 
WISQL.</I></P>
<P>Choose View | Metadata Information and set View Information On to Database, as
shown in Figure 15.4. After you click OK, the information displayed in the ISQL output
window should look something like this:</P>
<PRE><FONT 
COLOR="#0066FF">SHOW DB

Database: c:\data\info.gdb

        Owner: SYSDBA

PAGE_SIZE 1024

Number of DB pages allocated = 210

Sweep interval = 20000

</FONT></PRE>
<P><FONT COLOR="#0066FF"><BR>
<A NAME="Heading17"></A></FONT><A 
HREF="15ebu04.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/15/15ebu04.jpg">FIGURE 15.4.</A><FONT
COLOR="#000077"> </FONT><I>Preparing to view information on the <TT>INFO.GDB</TT>
database.</I></P>
<P>To see the tables available in a database, choose View | Metadata Information,
and set View 
Information On to Table. You can leave the edit control labeled Object
Name blank. If you fill it in with a table name, you will get detailed information
on a specific table--but in this case we want general information on all tables.
Click the OK 
button and view the information, which should look like the following,
in the ISQL output window:</P>
<PRE><FONT COLOR="#0066FF">SHOW TABLES

      TEST1

</FONT></PRE>
<P>Browsing through the Metadata Information menu choice, you can see that 
InterBase
supports triggers, stored procedures, views, and a host of other advanced server
features.</P>
<P>The Extract menu choice enables you to find out more detailed information about
the database and its tables. For example, if you choose Extract 
| SQL Metadata for
a Database, you get output similar to the following:</P>
<PRE><FONT COLOR="#0066FF">/* Extract Database e:\data\info.gdb */

CREATE DATABASE &quot;e:\data\info.gdb&quot; PAGE_SIZE 1024

;

/* Table: TEST1, Owner: SYSDBA */

CREATE 
TABLE TEST1 (FIRST VARCHAR(20),

        LAST INTEGER);

/* Grant permissions for this database */

</FONT></PRE>
<P>If you choose Extract | SQL Metadata for Table, you get the following output:</P>
<PRE><FONT COLOR="#0066FF">/* Extract Table TEST1 */


/* Table: TEST1, Owner: SYSDBA */

CREATE TABLE TEST1 (FIRST VARCHAR(20),

        LAST INTEGER);

</FONT></PRE>
<P>You should note that WISQL often asks whether you want to save the output from
a command to a text file, and the File menu gives you 
some further options for saving
information to files. You can take advantage of these options when necessary, but
90 percent of the time, I pass them by with barely a nod. (Some CASE tools use the
output from Extract | SQL Metadata to reverse-engineer 
a database. If your CASE tool
asks you for a script file, you can produce one this way.)

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The WISQL program accepts most SQL
	statements. For example, you 
can perform <TT>Insert</TT>, <TT>Select</TT>, <TT>Update</TT>,
	and <TT>Delete</TT> statements from inside WISQL. Just enter the statement you want
	to perform in the SQL Statement area, and then click the Run button. <BR>
	<BR>
	WISQL also comes 
equipped with a handy online reference to SQL. If you have questions
	about how to format an <TT>Alter</TT>, <TT>Drop</TT>, <TT>Insert</TT>, <TT>Create</TT>
	<TT>Index</TT>, or other SQL statement, you can look it up in the help for WISQL.
	(For 
better or worse, this is my number-one reference for SQL statements. Another
	book I have found useful is called The Practical SQL Handbook, by Bowman, Emerson,
	and Darnovsky, Addison Wesley, ISBN 0-201-62623-3.) 
<HR>


</BLOCKQUOTE>

<P>After 
reading the preceding three sections, you should have a fair understanding
of how WISQL works and how you can use it to manage a database. The information provided
in this chapter is nothing more than an introduction to a complex and very 
sophisticated
topic. However, you now know enough to begin using the local InterBase. This accomplishment
is not insignificant. Tools such as InterBase, Oracle, and Sybase lie at the heart
of the client/server activity that is currently so volatile 
and lucrative. If you
become proficient at talking to servers such as InterBase, you might find yourself
at an important turning point in your career.
<H3><A NAME="Heading19"></A><FONT COLOR="#000077">Transactions</FONT></H3>
<P>Now you can break out 
of the abstract theory rut and start writing some code that
actually does something. In this section, you will look at transactions, followed
by a discussion of cached updates and many-to-many relationships. In the next chapter,
you will see another 
&quot;real-world&quot; database, when you take a look at a sample
program that tracks the albums, tapes, and CDs in a music collection.</P>
<P>The <TT>TRANSACT</TT> program, found on the CD that accompanies this book, gives
a brief introduction to 
transactions. To use transactions, you must have a <TT>TDataBase</TT>
component on your form. Transactions work not only with real servers such as Sybase,
Informix, InterBase, or the local InterBase, but also with the 32-bit BDE drivers
for Paradox or 
dBASE files. In other words, transactions can be part of most of the