|
Database Basics and Database Tools
This
chapter provides an introduction to BCB database programming. I start by
showing how to create aliases and simple database applications, and then
move on to a discussion of various conventions that I use when programming
databases. Next, I discuss key database tools that ship with BCB, and close
with a discussion of getting ODBC and TCP/IP set up on your system.
In subsequent
chapters I move on to a discussion of advanced client/server issues. I am
covering such basic material as you find in this chapter in a book on
intermediate-to-advanced programming because there are so many C++
programmers who are new to the database world. If you have much experience
in C++ desktop and systems programming, but little database experience, you
should take the time to work through this chapter. BCB makes this subject
easy to master, and once you have database tools at your fingertips, you
will be surprised how many uses you can find for them. This is especially
true in the data-centric, Internet-based world most programmers live in
these days.
More advanced
database programmers should also read this chapter, because I include an
overview of the BCB database architecture. All serious developers need to
be concerned with matters covered in this chapter such as Object
Repositories, business rules, the proper use of TDataModule, and so on.
More specifically,
the subjects covered in this chapter include introductions to the
following:
After you have
covered the basics in this chapter, the next step is to move onto the
in-depth discussions of these topics found in the next few chapters. In
particular, the next chapter covers the TTable object in depth, and the
following chapter covers the SQL-based TQuery object.
Here is an overview
of what lies ahead:
Subsequent chapters
in this section of the book cover intermediate and advanced database
topics. For instance, you will find more material on the database tools in
general and on CASE tools in particular, in Chapters 17 and 18.
The logic behind
arranging things this way is to enable you to first get an introduction to
BCB and databases, and to then dig into the subject once you understand the
issues involved. This way I can keep the simplest subject matter out of the
latter chapters so they can focus on relatively serious programming issues.
If you are
interested primarily in design issues, you can safely skim through this
chapter, looking only for the parts that interest you. If you have not
built a BCB database program before, you should read this entire chapter
through from beginning to end.
On Setting Up the Database Tools
BCB comes ready to
run Paradox, dBASE, and local InterBase tables. If you have the client/server version of the product, it comes with
the full InterBase server. The client/server
product also lets you access any DB2, Informix, MS SQL Server, Sybase, and
Oracle you have available. The very first version of BCB does not ship with
built-in support for Access or FoxPro, but if you have
NOTE: The issue here is that BCB 1.0 ships with
an older version of the BDE than
The primary way to
access data from BCB involves the BDE, or Borland Database Engine. This engine,
which is explained in more depth near the end of this chapter, is the
backbone of the BCB database tools.
Paradox, dBASE, and Access tables can be reached directly from
the BDE. To access the other databases, you also need SQL Links, which is a
product that ships with the client/server version of
One simple way to
find out what database access you have from your system involves dropping
down a TDatabase object on a form and examining
its DriverName property in the Object
Inspector. This property contains a list of the installed drivers on your
system. For instance, all the databases listed earlier in this section
appear in this Property Editor on my system. (Paradox and dBASE tables are accessed through the driver labeled
"Standard.")
If you are having
trouble accessing some or all databases from your system, the best remedy is usually to do a complete uninstall and a complete
reinstall. The local database systems ought to work automatically out of
the box without any effort on your part. For instance, I have probably
installed BCB 50 times on a variety of systems, and I have always been able
to reach Paradox or dBASE tables immediately
after installation.
Users of the BDE
should be aware that BCB ships with a tool called the BDE Configuration utility.
This application can be accessed from the Start menu. You can use this tool
to make sure the BDE itself is running correctly. As a rule, if the BDE is
not running, you will not be able to access databases from inside of BCB.
Database Basics
To create a simple
database application, start by placing a TTable, a TDataSource, and a TDBGrid component on a form, as shown
in Figure 8.1. Wire these three
controls together by completing the following simple steps:
1. Connect the DataSource property of the TDBGrid to DataSource1. After completion of
these steps, the three components are hooked together and can communicate
with one another.
Connecting the TTable object to a table that resides
on disk is a three-step process:
1. Set the DatabaseName property either to a valid
alias or, in the case of Paradox or dBASE, to the
subdirectory where your data resides. For the example currently under
discussion, you can set the DatabaseName property to the BCDEMOS alias, which is created by default during BCB's installation. Alternatively, you could type c:\CBuilder\demos\data into the DatabaseName Property Editor, where you might need to change some aspects of this path
depending on where you choose to install BCB. When you are done,
the Object Inspector should look as it does in Figure 8.2.
Naming and Architectural Conventions
In this section I
lay out a number of conventions that I generally abide by in programming
projects. Before beginning, I should emphasize that these are merely
conventions. There are no hard and fast rules in this area, and you should
feel free to follow my suggestions only to the degree that they suit your
taste. In fact, you will find that I myself do not follow these conventions
one hundred percent of the time, though I generally conform to them when I
am not feeling too rushed. I also have included legacy code in this book in
which I did not adopt the techniques that I currently believe are best. In
some cases, I have updated the legacy code, but some sample programs still
use old conventions.
When arranging
tables on a data module, I usually follow some simple naming conventions. If
I attach a TTable object to a table called Customer, I will call the TTable object CustomerTable. The data source attached to
that table will generally be called CustomerSource.
NOTE: An alternative technique, called Hungarian
notation, would name all TTable objects tblXXX, where the XXX is the name of the table
you want to use: tblCustomer, tblBioLife, and so on. You could then
prefix ds before the table name to
designate the name of the data source: dsCustomer, dsBioLife, and so on. This was the
technique I used in the past, but which I no longer believe to be best.
DDSURFACEDESC ddsd;
HBITMAP hbm;
RGBQUAD * prgb;
Incredibly, these samples
are taken from source code distributed by a major software company in order
to promote a new API. I am at least tempted to believe that the people who
came up with these variable names were trying to be funny, or perhaps just
to pull someone's leg. At any rate, these are classic examples of naming
conventions that I try to avoid. In simple projects
that have only one data module, I will usually call the files associated
with the data module DMod1.cpp and DMod1.h. The TDataModule object itself I usually rename to TDMod. In more complex projects that have
multiple data modules I might rename the data module to something more
meaningful such as TDModAddress, and I might then save the file
under the name DModAddress1.cpp.
Please note that my
convention is to name the file in which a data module or form is stored as
the same name as the data module or form, except that I append a 1 to it. Thus the file in which a data
module called TDMod is stored will be called DMod1. This prevents name conflicts
between the object name and the filename. If I have a form called TAddress, I will save it in a file
called Address1.cpp. The one exception to the
previous rule is that I tend to name the main module of a project Main.cpp, and I then usually keep the
main form default name of Form1.
Please understand
that I have included this section more as a courtesy than out of a desire
to attempt to force my tastes on someone else. I want you to know and
understand the conventions I use, but you should feel free to use the
techniques that you feel work best.
Enough on naming
conventions. It's time now to move on to a related, but slightly different
matter regarding the proper use of data modules.
Using the TQuery Object
You can create a BCB
SQL statement by using a TQuery component in the following manner:
1. Drop down TQuery, TDataSource, and TDBGrid objects on a form and wire them
together. If you're working
with local data, you can substitute a fully qualified subdirectory path for
an alias. When using the latter method, it's best if you don't include the
actual name of a table, but only the subdirectory in which one or more
tables exist. In my opinion, however, it is almost always better to work
with an alias rather than specify the path directly in the DatabaseName property.
That's all I'm going
to say about TQuery for now. Later in this chapter I discuss the SQL monitor tool that comes
with BCB. In subsequent chapters I begin using SQL statements more heavily.
I find it easier to use TTable than TQuery for many basic database
operations. However, as I discuss matters of increasing complexity
throughout the database section of this book, I will rely more and more on
SQL.
The Data Module
Earlier in this
chapter, you placed a TTable and TDataSource component on the same form with
your visual components. When you ran the program, the icons representing
these components disappeared. However, they are visible at design time and
have a tendency to clutter up the form. Partially out of a desire to
eliminate this clutter, BCB features a component called a TDataModule, which can be used to store nonvisual controls such as TTable and TDataSource. A program on disk called SimpleTable shows how to use the TDataModule component.
To get started
working with TDataModules, first begin a new application. Next, choose File | New and select
the Data Module component from the New page of the New Items dialog, as
shown in Figure 8.5. You can also choose to create a data module directly
from the New Data Module option on the File menu. You should, however, get
used to using the Object Repository as it plays a big role in BCB
programming.
NOTE: A TDataModule is not the same thing as a
form. For instance, if you look in its ancestry you will see that it is a
direct descendant of TComponent. When you first see a TDataModule object, there is a tendency to
view it as merely a special kind of form, which is, to some degree, true,
at least in a very nontechnical sense. However,
the hierarchy for a TForm component looks like this:
-TComponent
-TControl
-TWinControl
-TScrollingWinControl
-TForm
The hierarchy for a TDataModule, on the other hand, looks like
this:
-TComponent
-TDataModule
Clearly, TForms and TDataModules are two very different beasts,
despite some apparent similarities between them. After adding a TDataModule object to your application,
take a moment to save your code. You might save Unit1 as MAIN.CPP and Unit2 as DMOD1.CPP. Click Form1, open the File menu, and choose the Include Unit Header expert from
the menu. In the Include Unit dialog, select DMod1 and press OK. This is a simple way of automatically inserting an #include directive at the top of Unit1. In particular, the following changes are
made to your code:
#include <vcl\vcl.h>
#pragma hdrstop
#include "Main.h"
#include "DMod1.h" // This directive
references the data module
#pragma resource
"*.dfm"
You can, of course,
type in the #include directive without using the
small expert found on the File menu. There is no particular advantage in
using the expert other than its ease of use. Remember, however, that if you
want to include a unit in your project, it is generally not enough to
simply add a header file to a unit. You must also be sure the unit has been
explicitly added to your project. In the case discussed here, there is no
need to explicitly add Unit2 (a.k.a. DMod1) to the project, because it was done
automatically when you first created the unit.
NOTE: This is a time when some programmers may
need to force themselves to abandon the old "command-line"
attitude, and to instead embrace a visual tool that can help make you more
productive. As always, the choice is yours, but if the visual tools are
easier to use, and if they make you more productive, you should consider
using them. Command-line programming has an honorable place in this world,
but it is generally not part of the attitude that makes for good RAD
programmers.
#pragma link "dmod1.obj"
This syntax can be
very useful when adding components to the component palette. See the FTP2.cpp module in the Utils directory from the CD-ROM that ships with
this book for an example of using this approach.
Arrange Form1 and DataModule2 on the screen so you can view them both at
the same time. Drop a TTable and TDataSource component on the data module,
as shown in Figure 8.6.
NOTE: For various reasons I snapped my screen
shots for this book at 640x480 resolution. Needless to say, I don't usually
run BCB at that resolution. 1024x768 is probably a more reasonable size
when working with an environment like this, though even higher resolutions
would be better. 800x600 is tolerable, but I still feel the pinch when
working at that low a resolution.
Wire the TDataSource to the TTable object, and set the DatabaseName of the TTable object to BCDEMOS and the TableName to BioLife. Set the Active property of the TTable object to True.
Right-click the TTable object and bring up the Fields Editor. Right-click the Fields Editor, and bring up
the AddFields dialog. Make sure all the fields in
the dialog are selected, which is the default behavior for the tool. Click
the OK button.
The Fields Editor
now contains a list of all the fields in the BioLife table. To add these fields to
your form, simply click one or more fields, hold the left mouse button
down, and drag the fields onto the form. For instance, select the Graphics
field, making sure that it is the only one highlighted. Now drag it onto
the form. When you let go of the left mouse button, the Graphics field will
automatically display itself in a TDBImage component. Drag over several
other fields, and create a form that looks something like the image in
Figure 8.7. If you want to drag
multiple fields over at the same time, perform a multiselect operation in the Fields Editor, just as you would in a list box. Now drag
all the fields over to the main form at once. Most likely they will scroll
on past the bottom of your form when you insert them, but you can fix this
easily enough by aligning them as you like with the mouse.
The TDBImage component, where the picture of
the fish is displayed, may be in some disarray when you first insert the
components on the form. To straighten things out, select the TDBImage component, go to the Object
Inspector, and set the Stretch property to True.
A sample program
illustrating these principles ships on the CD-ROM that accompanies this
book. It is called SimpleDataModule.
The Purpose of TDataModule
Now that you know
how to use the TDataModule, let me add a few words on its significance. This component's
primary purpose is to provide a place where you can define the means for
accessing a set of tables. However, it is also a place to put business
rules, and a place to create reusable means of accessing data.
Client/server
database programmers often want to put all the rules for accessing data on
the server side. Indeed, BCB supports this paradigm, and you can use stored
procedures, views, and other advanced database technologies to whatever
degree you want when accessing SQL databases. However, you also have the
ability to define a set of rules that live on the client side, inside a TDataModule. You can then use the Object
Repository from the File | New menu choice to store this form in a place
where it can be reused by multiple programmers. To put a form in the Object
Repository, right-click it and chose Add to Repository from the menu. I
discuss the Object Repository in more depth later in this chapter.
There is no simple
way to decide when it is best to put rules on the server side or when it is
best to put them inside a TDataModule. Often the best solution is to use a
combination of the two techniques. Put things on the server side when that
is simplest, and store things on the client side when you think the power
of C++ and its strong debuggers will be useful to you.
There is
considerable complexity in the whole set of related subjects that involve
designing databases, creating business rules, and creating metadata such as
stored procedures on a server. Many of these topics will be explored in
considerable depth in the next few chapters. However, a thorough
examination of these topics requires considerably more scope than I have in
this book.
Conventions Regarding the Use of TDataModules
When working with
database code, I often prefer to put my TTable, TQuery, and TDatasource objects in a TDataModule, rather than placing them on a
form. In other words, I think there are architectual reasons for using TDataModules rather than placing tables directly on a
form.
There are several
interrelated advantages to this scheme, most of which have to do with
proper design issues. In particular, the scheme outlined previously enables
me to do the following:
I will often use the
constructor of a TDataModule or its OnCreate event to open up the tables or
data modules used in a project:
void __fastcall TDModBioLife::DModBioLifeCreate(TObject *Sender)
{
BioLifeTable->Open();
}
This is the proper
and ideal way to do things. I want to stress, however, that it is also
correct from an OOP standpoint to access the same table from inside your
main form through the scoping operator:
void __fastcall TForm1::Button1OnClick(Tobject *Sender)
{
DMod->BioLifeTable->Open();
}
You can use either
technique, depending on your needs. Most of the time, you will use the
second technique shown here, the one that uses scoping operators. There are, however, some good arguments in favor of
using the first method. In particular, the first technique is pure from an
OOP point of view in that it completely hides the details of what goes on
in the TDMod object.
One problem with the
rigorous technique illustrated by the first example is that it can add
complexity to simple programs. If you only need to access one table, and
only need one TDataSource object, even the simple act of creating a data module can seem like
overkill. Going even further and insisting that the code for manipulating
the table also reside in the TDataModule can then seem almost absurdly roundabout
and abstruse.
In the type of
simple database projects that you will see in this chapter, it is possible
to forgo the use of data modules altogether and to instead place the TTable and TDataSource objects directly on your main
form. However, I will generally use a TDataModule even in such simple cases
simply because it is the best way to architect an application. The point is
to get in the habit of doing things the right way, because ultimately, in
large scale projects, decisions such as this do matter.
NOTE: Data modules can be used not only for data
controls, but for all nonvisual controls. You
can, for instance, place a TMenu object on a data module, and then add the
data module's header file to your main form, thereby accessing the TMenu object through the Object
Inspector. The problem with this technique, of course, is that the methods
you want to access from the menu are not always going to be located in the
data module. Another issue is that your form and the data module will then
be bound together, at least to some degree.
Remember that one of
the key features of data modules is that they provide a place to store a
set of business rules. You can create tables and queries, link them
together, and use code to define rules regarding the way they work. To
replicate these rules in multiple projects, simply reuse the data module
that contains them.
The Object Repository
In the last section,
I said that data modules can help promote reuse of code. BCB has a specific
mechanism called an Object Repository that can help with this process. In
particular, Object Repositories are a place where you can store data
modules and forms so that they can be reused in multiple applications. If
you define a set of business rules in a data module, you can save it to the
Object Repository and reuse it in multiple projects. This helps you
propagate the rules and promote conformity to them across a wide range of
projects.
The simplest way to
introduce you to the Object Repository is to just lead you step-by-step
through the process of using it. After you have seen how it works, I will
take a moment to explain its significance.
Save the program you
created in the last section as follows:
1. Save Unit1 as Main.cpp. Right-click the data
module and select Add To Repository. Fill in the Add To Repository dialog
by setting the Title, Description, and Author fields as you see fit. In the
Page drop-down combo, select Data modules. Use the Browse button to select
an icon from the ..BCB\images\icon subdirectory, or from any place
else where you might have some icons stored.
Start a new project.
Choose File | New. This time, instead of choosing the Data module component
from the New page, select the Data modules page and choose the DModBioLife component that you just
finished creating. When it appears on the screen, you will see that it
contains a TTable and TDataSource component. The components are
wired together, and the TTable object is set to the BioLife table with its Active property set to True.
To access this table
from Form1, you must first employ the
Include Unit Header menu option from the File menu to add Unit2 to the uses clause in Unit1. Go to the DataControls page of the Component Palette, and drop down a TDBGrid object on Form1. In the Object Inspector, drop down the DataSource property of the TDBGrid object, and you will see the TDataSource object from the DModBioLife module listed. Select this item, and the grid will automatically fill up with data.
If you drop down a TDBEdit control instead of a TDBGrid control, you proceed the same
way, except that you will need to fill in not only the DataSource property in the Object
Inspector, but also the DataField property. There is no need to type
information into the DataField property, because it will automatically
contain a list of the available fields in the BioLife table.
The true
significance of the Object Repository is only hinted at by this example. The
importance of this tool is made more obvious if you have six or seven
tables dropped onto a data module. You might then define several
relationships between the tables and add other related code. For instance,
you might have some one-to-many relationships established, possibly a
many-to-many relationship established, and you might have several filters,
lookups, and several calculated fields defined.
Altogether, a data
module of this type might encapsulate several sets of business rules
defining exactly how tables should be accessed and how they relate to each
other. The ability to save all this work in the repository, and to then
automatically reuse it in multiple projects, is extremely valuable. I am,
however, getting ahead of myself. Discussions of filters, lookups,
calculated fields, and other database issues occur in various places over
the next few chapters.
The Database Explorer
In addition to the
data module, another key tool to use when working with databases is the
Database Explorer. You can access the Database Explorer by choosing the Database
| Explore menu item. The Explorer is a stand-alone executable, so you can
also access it from the Windows Start button on the taskbar. You can use
the Explorer even if BCB is not running, but BCB and the Explorer work best
in conjunction with one another.
Once you have loaded
the Explorer, make sure you have selected the Databases page and not the
Dictionary page. Click the BCDEMOS node to expose the Tables node. Now click the little plus sign
before the Tables node. A list of all the tables
in the database will appear. Select the BioLife table and choose the Data page
to view the contents of the table, as shown in Fig- ure 8.8.
NOTE: The Database Explorer provides a means for
viewing the text of stored procedures and triggers. You cannot edit these
values, but you can view their code.
The Database
Explorer is a fairly complex tool with a number of powerful traits, many of
which will undoubtedly be expanded in future versions of the product. In
particular, you should note that it contains a DataDictionary that enables you to define a
new alias or modify existing aliases.
At this stage, I
want to show you only one key trait of the Database Explorer. Arrange your
screen so you can view both the Explorer and Form1 at the same time. Select the BioLife table in the Explorer with the
mouse, and then drag and drop it onto Form1. If you want, you can experiment further by expanding the BioLife node in the Explorer and
dragging and dropping individual fields of the table onto Form1, just as you did when using the Fields
Editor.
If you start a new application, and then drag and drop the BioLife table onto Form1 from the Explorer, you will find that the TTable and TDataSource objects are placed on Form1. If you want to move them off the form,
you can add a TDataModule object to the project, and then select both the TTable and TDataSource objects and choose Edit | Cut
from the menu. Now select the TDataModule object and choose Edit | Paste. Make sure Form1 contains a reference (#include) to the unit that contains the TDataModule, and then hook up the grid to
the TDataSource object in the TDataModule. This sounds like a fairly
complicated process when written out, but you can perform this task in just
a few seconds using BCB's visual tools.
Once again, the last
few paragraphs have done nothing more than introduce you to the Database
Explorer. This is a complex tool that will prove useful to you in many
different ways, some of which might not even have been apparent to its
creator. For now, the key point to grasp is that it gives you an overview
of all the data in a database and enables you to drag and drop fields and
tables onto your forms.
Working with the SQL Monitor
The SQL Monitor is
an advanced tool that enables you to see exactly what SQL statements are
being generated by your application when you are running queries against
SQL databases such as InterBase. The SQL Monitor
only works when you are using an ODBC connection or SQL links to access
real databases such as InterBase, Oracle, Sybase,
or Informix. In other words, it is not useful when you are using the TTable object, or when you are
accessing Paradox or dBASE tables.
There is no trick to
using the SQL Monitor. If it ships with your version of BCB, you can simply
select the SQL monitor from the Database menu, run your program, and then
browse through the SQL Monitor to see the specific statements generated by
your program.
Here is code
produced from a simple SQL request for all the rows from the Customer table from the IBLOCAL alias. Note that in this case I am using an InterBase table. InterBase is an ANSI 92 SQL-compatible
database server:
1 18:49:40 SQL Prepare:
INTRBASE - select * from Customer
2 18:49:40 SQL Vendor: INTRBASE
- isc_dsql_allocate_statement
3 18:49:40 SQL Vendor: INTRBASE
- isc_start_transaction
4 18:49:40 SQL Vendor: INTRBASE - isc_dsql_prepare
5 18:49:40 SQL Vendor: INTRBASE - isc_dsql_sql_info
6 18:49:40 SQL Vendor: INTRBASE - isc_vax_integer
7 18:49:40 SQL Transact:
INTRBASE - XACT (UNKNOWN)
8 18:49:40 SQL Vendor: INTRBASE
- isc_commit_retaining
9 18:49:40 SQL Execute: INTRBASE - select * from
Customer
10 18:49:40 SQL Vendor:
INTRBASE - isc_dsql_execute
11 18:49:40 SQL Stmt: INTRBASE -
Fetch
12 18:49:40 SQL Vendor: INTRBASE
- isc_dsql_fetch
13 18:49:40 SQL Stmt: INTRBASE -
Fetch
14 18:49:40 SQL Vendor:
INTRBASE - isc_dsql_fetch
15 18:49:40 SQL Stmt: INTRBASE -
Fetch
16 18:49:40 SQL Vendor: INTRBASE
- isc_dsql_fetch
17 18:49:40 SQL Stmt: INTRBASE -
Fetch
18 18:49:40 SQL Vendor:
INTRBASE - isc_dsql_fetch
19 18:49:40 SQL Stmt: INTRBASE -
Fetch
20 18:49:40 SQL Vendor: INTRBASE
- isc_dsql_fetch
21 18:49:40 SQL Stmt: INTRBASE -
Fetch
22 18:49:40 SQL Vendor:
INTRBASE - isc_dsql_fetch
23 18:49:40 SQL Stmt: INTRBASE -
Fetch
24 18:49:40 SQL Vendor: INTRBASE
- isc_dsql_fetch
25 18:49:40 SQL Stmt: INTRBASE -
Fetch
26 18:49:40 SQL Vendor:
INTRBASE - isc_dsql_fetch
27 18:49:40 SQL Stmt: INTRBASE -
Fetch
28 18:49:40 SQL Vendor: INTRBASE
- isc_dsql_fetch
29 18:49:40 SQL Stmt: INTRBASE -
Fetch
30 18:49:40 SQL Vendor:
INTRBASE - isc_dsql_fetch
31 18:49:40 SQL Stmt: INTRBASE -
Fetch
32 18:49:40 SQL Vendor: INTRBASE
- isc_dsql_fetch
All this information
can be a bit overwhelming at times. To simplify the output from the SQL
Monitor, select Options | Trace Options from the SQL Monitor menu. The
dialog shown in Figure 8.9 is launched. You can then select just the first
two, or perhaps only the second option. The output from the same test run
previously then looks like this:
2 04:54:44 Log started for:
Project1
3 04:55:02 SQL Prepare:
INTRBASE - select * from customer
4 04:55:03 SQL Execute:
INTRBASE - select * from customer
Now you see only the prepare and execute statements, which is probably
all the information you needed. A screen shot of the SQL Monitor with this
simple information in it is shown in Figure 8.10.
Once again, the SQL
Monitor is only for use with powerful databases such as InterBase or Oracle. I will present in-depth discussions of InterBase later in this section of the book.
FIGURE
8.10. The SQL Monitor showing prepared
and executed SQL statements.
Understanding the BDE and Aliases
The BDE is the
Borland Database Engine, which used to be called IDAPI. This engine is the
gateway to all the databases accessed from BCB, except under certain
unusual circumstances.
The BDE gives you
direct access to Paradox and dBASE tables. If you
own a copy of Paradox or dBASE, you already have
the BDE installed on your system. The brains behind these
two products is the BDE. Paradox and dBASE are merely wrappers around the BDE, in much the same way that the BCB IDE
is a wrapper around a C++ compiler.
Besides giving you
access to Paradox and dBASE, the BDE also uses
Borland's SQL Links technology to give you fast access to client/server
databases. In particular, BCB ships with SQL Links drivers for InterBase, Oracle, Sybase, MS SQL Server, DB2, and
Informix.
The list of
available client/server databases changes depending on the version of the
BDE you are using. To check the current list, open up the BDE Configuration
application that ships with BCB and look at the available list of drivers. (See
Figure 8.11.)
Each vendor will
have a different set of local drivers and tools for you to use. For
instance, if you want to connect to Oracle, you need to install the Oracle
SQL Net tools.
Your copy of BCB
comes with a local version of InterBase. If you
look at the right-hand portion of the taskbar at the bottom of your copy of
Windows 95 or Windows NT, you should see the green and gray icon for the InterBase Server Properties applet. Other InterBase applets include the InterBase Server Manager (IBMGR32.exe) and InterBase Windows SQL (WISQL32.exe). (When reading these cryptic and inadvertently humorous 8/3 executable
names, you should break the abbreviations up like this: IB-MGR-32.exe, not like this: IBM-GR-32.exe.)
If you are running
BCB, your connection to InterBase will be set up
automatically during program install. However, if you want to connect to
Oracle, Sybase, or some other SQL server, you can be in for a rather
complicated ordeal. The difficult part of these installs is almost always
setting up the third-party tools. Borland's half of the equation is usually
automatic, and occurs without effort on your part during BCB's install.
To get connected to
Oracle or Sybase, the first thing to do is close BCB and all the Borland
tools, and consult the manuals for your server. They will show you how to
get the server set up, how to test the connection, and how to run some
tools for managing your database. Once this part of the procedure is over,
you can launch BCB, and you should be able to connect right away after
establishing an alias. Aliases are described in the next section of this
chapter, and in- depth in several of the upcoming chapters, including
Chapter 15, "Working with the Local InterBase Server." They are also discussed in the readme file from the CD that accompanies this book. Of course, you have to have
the client/server version of
There is a Sams Publishing book called the Database Developer's
Guide with
I will, however, go
to some lengths to ensure you are properly connected to InterBase,
when I introduce that topic in Chapter 15. Until that time, there is no
need for you to be connected to InterBase while
reading this book. I should add, however, that connecting to InterBase is a simple task that should have been done
for you automatically during the install of BCB. To check whether you are
properly connected, try using the IBLOCAL alias set up by the install program for connecting to an InterBase table called Employee.gdb.
Aliases
You can create aliases
inside any one of three tools:
Both Paradox and InterBase aliases are created automatically when you
install BCB. You can study these aliases as guides when creating your own
aliases. Also see the readme files on the CD that
accompanies this book, and the section on creating ODBC aliases near the
end of this chapter.
Various installation
programs, such as Wise from Great Lakes Business Software (www.glbs.com, Tel (313) 981-4970, Fax (313)
981-9746) and InstallShield, can create aliases
for you automatically. If you need to add aliases to a client machine
during installation, you should let one of these programs handle it for
you. They will also automate the installation of the BDE. InstallShield Express ships in the ISX directory found
on some versions of the BCB CD.
Some Notes on Installing TCP/IP
In this section I
briefly discuss the process of setting up TCP/IP on a Windows 95 machine. The
process should be nearly identical on a Windows NT 4.0 machine, though the
dialogs might have a slightly different name or appearance.
TCP/IP is the
protocol of choice when connecting to client/server databases. It ships
automatically with the 32-bit Windows products. To see if it is installed
on your system, open the Control Panel and launch the Network applet. If
you have TCP/IP installed, it will show up on the Configuration page of
this applet, as shown in Figure 8.12. If TCP is not
installed, you should push the Add button on the Configuration page and
bring up the Select Network Component Type dialog. Select Protocol from the
list of drivers, and again choose the Add button. In the Select Network
Protocol dialog, choose Microsoft in the left-hand list box, and TCP/IP in
the right-hand list box. Windows will then install the necessary software,
which may require the use of your Windows Install CD-ROM.
You will probably
also have to specify an IP address, subnet mask, gateway, and DNS server. This
information can be garnered from your network administrator. If you are
working on a small local network with Windows machines that you have set up
in your office or home, you can ignore the DNS server, and can make up your
own IP address, subnet mask, and gateway. For instance, the following
numbers would do, as long as you are not connected to the real Internet,
and are only talking to the machines in your home or office:
IP Address: 143.186.186.2
Subnet mask: 255.255.255.0
Gateway: 143.186.186.1
The other machines
on your network should have the same subnet and gateway, but the IP address
should be unique. For instance, the next machine should have an IP address
of 143.186.186.3, and then 143.186.186.4, and so on. Remember, don't make up your own numbers if you are connected to the real Internet! If
you have an Internet connection, contact your network administrator or
Internet service provider (ISP).
NOTE: If you are appalled by my suggestion that
people make up their own IP addresses, you should remember that many people
connect their machines without being on the Internet. I do this all the
time with two laptops when I am on the road showing BCB and
To check whether you
are connected properly, open up a DOS window and try to ping one of the
machines in your network.
To get started, you
can try to ping yourself:
Here is a built-in
address for referencing your own machine:
Or you can try to
ping one of the other machines in your network:
Here is the result
of successful session:
c:\4dos>ping 143.186.186.2
Pinging 143.186.186.2 with 32 bytes of data:
Reply from 143.186.186.2: bytes=32 time=55ms
TTL=32
Reply from 143.186.186.2: bytes=32 time=1ms TTL=32
Reply from 143.186.186.2: bytes=32 time=1ms TTL=32
Reply from 143.186.186.2: bytes=32 time=1ms TTL=32
c:\4dos>
Here is the result
of a failed session:
c:\4dos>ping 143.186.186.3
Pinging 143.186.186.3 with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.
c:\4dos>
Failed sessions
usually occur because your machine is not configured properly or else the
wires connecting you to the network are not set up correctly. (For
instance, you might have forgotten to plug into the network!)
If you are attached
to the Internet and have a DNS server, you can try to ping one of the big
servers on the Net:
Here is successful
session:
c:\>ping compuserve.com
Pinging compuserve.com [149.174.207.12] with 32
bytes of data:
Reply from 149.174.207.12: bytes=32 time=298ms
TTL=239
Reply from 149.174.207.12: bytes=32 time=280ms
TTL=239
Reply from 149.174.207.12: bytes=32 time=333ms
TTL=239
Reply from 149.174.207.12: bytes=32 time=332ms
TTL=239
c:\>
Pinging compuserve.com is the same thing as pinging 149.174.207.12. In fact, it's the job of the
DNS server (the Domain Name Server) to resolve a human-readable name such
as compuserve.com into an IP address.
If you want to
create a human-readable IP address on a local office or home network, you
can edit the HOSTS files that ship with Windows 95
or Windows NT. Under Windows 95, you will find a sample HOSTS file called Hosts.sam in your Windows directory. Here
is what this file looks like:
# Copyright (c) 1994 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft
TCP/IP for
#
# This file contains the mappings of IP addresses
to host names. Each
# entry should be kept on
an individual line. The IP address should
# be placed in the first
column followed by the corresponding host name.
# The IP address and the host name should be
separated by at least one
# space.
#
# Additionally, comments (such as these) may be
inserted on individual
# lines or following the
machine name denoted by a `#' symbol.
#
# For example:
#
# 102.54.94.97 rhino.acme.com # source server
# 38.25.63.10 x.acme.com # x
client host
127.0.0.1 localhost
You can rename this
file to HOSTS. with no extension, and then add
your own list of IP address to it:
143.186.186.3 MarysPC
143.186.186.4 MikesPC
After doing this,
you can ping the other machines with a human-readable name:
ping maryspc
Connecting to ODBC
ODBC is a popular
means of connecting to databases. For many developers, ODBC plays the same
role in their development that the BDE plays in the life of Borland
developers. ODBC is so popular that Borland has added a high-performance
ODBC socket to the BDE that enables you to access data through ODBC.
NOTE: It's important to understand that ODBC is
not a database, but only a means of accessing a database. ODBC is a
standard for creating drivers; it is a not a type of data. For instance,
Borland developers commonly use SQL Links to connect to Oracle or Sybase. If
you wanted, you could also use ODBC drivers in lieu of SQL Links, though
this is usually not a wise thing to do because ODBC is often slow, while
SQL Links are usually fast.
The main appeal of
ODBC is its popularity. There are ODBC drivers for connecting to nearly every
database imaginable. If Borland does not ship with drivers for the data you
want to access, the logical thing to do would be to search for ODBC drivers
and then use them from BCB through the ODBC socket layer currently under
discussion in this section of the chapter.
Some copies of BCB
ship with an ODBC driver for InterBase. Because
this driver is readily available, I will use it as the model for this
discussion.
To get started using
ODBC, you should close down your Borland tools, open up the Windows Control
Panel, and start one of its applets called, quite poetically, "32 Bit
ODBC." This applet can be used to manage the ODBC connections on your
machine.
As shown in Figure
8.13, on the bottom-right corner of the applet is a button with the word
Driver on it, and another one right above it with the word Add on it. If
you click either button you can see a list of the drivers available on your
system.
c:\program files\borland\intrbase\examples\employee.gdb
The rest of the
fields should look like this:
Data Source Name: InterBase Test
Description: Test of InterBase
Driver: Local
DataBase: c:\program files\borland\intrbase\examples\employee.gdb
User Name: SYSDBA
Password: masterkey
You should enter SYSDBA as the user name, and enter masterkey as the password. After entering
all this information, you should be able to connect to the table by
pressing the Test Connect button. However, you may not be able to run this
test until you first press OK in the dialog, and then bring the dialog back
up by pressing the Settings button from the main screen of the 32-Bit ODBC
applet.
If all is working
correctly, you can now bring up the BDE Configuration utility that ships
with Borland C++Builder. On the drivers page pick
New ODBC Connection to bring up the dialog for creating a new BDE
connection, as shown in Figure 8.15. Fill in the fields as follows:
SQL_Link_Driver: ODBC_Test2
Default
Default Data Source Name: InterBase Test.
After creating the
driver for the ODBC test, switch to the Alias page of the BDE Configuration
Utility. Select New Alias and type in ODBCTest1. Set the Alias type to ODBC_Test2. Click
OK, save your work from the File menu, and exit BDECFG.exe.
If BCB is already
running, close it down. Now start up BCB and drop a TTable object on the main form. Select ODBCTest1 from the list of available
aliases in the DatabaseName property. Now proceed as you normally would, selecting a TableName and attaching a TDataSource and TDBGrid object to the table. When
prompted for a password, enter SYSDBA as
the Username and masterkey as the password. If you want, you can relaunch the BDE configuration program and set the user
name for this alias permanently to SYSDBA.
In the summary
presented here, I have hardcoded in the names of
the various drivers and aliases you create. You can, of course, name the
alias anything you want, just as you can enter whatever names you want in
the Data Source Name and Description fields of the ODBC Configuration
dialog in the 32-Bit ODBC applet from the Control Panel.
Summary
In this chapter you have learned some of the fundamental facts you need to know to start accessing databases from BCB. In particular, you have learned about the following:
Now that the
groundwork has been laid, the next few chapters start digging into the
objects that BCB uses to access databases. As a rule, this is not a
particularly difficult subject, but there is a good deal of information
that needs to be covered.
Once you have the
facts that you need at your fingertips, you can start building real
databases with BCB. C++Builder is one of the
premier tools in the industry for accessing data, so you should be prepared
to find yourself quickly writing powerful database applications that can be
used by hundreds of people at one time.
VMS Desenvolvimentos
Diversas Dicas, Apostilas, Arquivos Fontes,
Tutoriais, Vídeo Aula, Download de Arquivos Relacionado a Programação em C++ Builder.
|