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.
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 Delphi 3.0 installed on your system, you can get access to these features automatically.
NOTE: The issue here is that BCB 1.0 ships with an older version of the BDE than Delphi 3.0, because the new version of the BDE was not available at BCB ship time. If you can get the most recent version of the BDE, BCB will work with it automatically, which means you can get at Access tables. You can sometimes download the BDE directly from the Borland Web site at www.borland.com. If you can't get the most recent version of the BDE, you can use ODBC to, as they say, access "Access."
It does not matter that BCB shipped with an earlier version of the BDE than the one you may be running on your system. The BDE was designed to handle this type of situation, and it is entirely regular. I use the most recent version of the BDE with my copy of BCB, and I never have any hint of trouble.
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 Delphi.
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.
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.
FIGURE 8.1.
TTable, TDataSource, and TDBGrid arranged
on a form.
Wire these three controls together by completing the following simple steps:
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:
When you are done, the Object Inspector should look as it does in Figure 8.2.
FIGURE 8.2.
The Object Inspector after connecting
to a table called Customer,
using an alias called BCDEMOS.
If you have completed all these steps properly, you should now be looking at the
data from the table you chose, as shown in Figure 8.3. To take this process
one step
further, you can compile and run the program and then begin browsing and editing
your data.
FIGURE 8.3.
Simple form displaying the contents of
CUSTOMER.DB.
If you want to simplify the task of browsing through the data in your application,
you can go back into design mode and add the TDBNavigator control to the
program. To hook this control into the loop, all
you need to do is set its DataSource
property to DataSource1. Now you can run the program and begin iterating
through the records with the navigator, as shown in Figure 8.4. In Figure 8.4, most
of the functionality of the
TDBNavigator has been turned off by manipulating
its VisibleButtons property. For instance, a navigator can automatically
enable you to edit, insert, delete, post, cancel, and refresh. All those capabilities
have been disabled and
hidden in the form shown here by manipulating the VisibleButtons
property of TDBNavigator.
FIGURE 8.4.
A simple database program with a
TDBNavigator control.
A program like the one described here is found on the CD-ROM that accompanies this
book as Simple.cpp. Programs like this are the bread and butter of the database
world. You might even find it
useful to create several programs of this type, just
be to be sure you can do it quickly and easily with little thought. BCB database
programmers work with the TTable, TDataSource, and TDBGrid
objects on a regular basis, and
the basics of their operation should be as easy as
tying your shoe.
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.
The Hungarian system enables you to automatically group all the objects shown in the Object Inspector according to type. If all the tables begin with tbl, they will appear together in the Object Inspector.
Despite this advantage, I have decided against Hungarian notation on the grounds that it tends to make even simple code appear a bit abstruse. Recondite code has a certain emotional appeal, but it is not the effect I want to strive for in a book that champions the virtues of clear, easy-to-read logic.
In general, I try to avoid systems that force me to use hard to read abbreviations, or that clutter up the beginnings of words. In particular, I find an identifier like dsCustomer unpleasant because it makes the type of the variable appear more important than the name of the variable. This system also makes it hard to read the identifier, because you have to mentally skip over the first syllable before getting to the key piece of information contained in the name. Of course, my least favorite naming convention abbreviates both the type and the variable name: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.
So, in this book, it will generally be CustomerTable, and not tblCustomer. If you prefer some other system, you should feel free to pursue your tastes. After all, many of the best programmers use Hungarian notation on a regular basis. In fact, in the world of C++, my tastes probably represent a minority opinion.
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.
You can create a BCB SQL statement by using a TQuery component in the following manner:
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.
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.
FIGURE 8.5.
Selecting the TDataModule component from the New Items dialog.
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 -TFormThe hierarchy for a TDataModule, on the other hand, looks like this:
-TComponent -TDataModuleClearly, TForms and TDataModules are two very different beasts, despite some apparent similarities between them.
The header file that contains the declaration for the TDataModule class is FORMS.HPP. If you have the Pascal source to the VCL, the TDataModule object is found in FORMS.PAS.
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.
Let me tack on one additional piece of information to this note. If you want to add a module to a project without using the project manager, and without editing a makefile, you can use the following syntax:#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.
FIGURE 8.6.
Form1 and DataModule2
arranged on the screen so
that you can easily view them both at the same time.
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.
FIGURE 8.7.
Some of the fields of the BioLife table arranged in visual controls
on a TForm object.
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.
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.
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.
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:
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.
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.
FIGURE 8.8.
Exploring the BioLife table in the Database Explorer.
Click the little plus symbol in front of the
BioLife.db node, and you will
see a list of properties for the BioLife table. The properties listed are
Fields, Indices, Validity Checks, Referential Constraints,
Security Specs, and
Family Members. You can expand each of these
nodes to view their properties. For instance, if you select the Fields node,
you will see a list of all the fields in the table. As you select each individual
field, you will see a
description of its primary characteristics.
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.
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.9.
The SQL Monitor Trace Options dialog is accessible from one of
the program's
speed buttons.
FIGURE 8.10. The SQL Monitor showing prepared and executed SQL statements.
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.)
FIGURE 8.11.
The Borland Database Configuration utility (BDECFG32.exe) provides
a list of available SQL Links drivers.
The BDE Configuration utility lists the actual files that contain the SQL Links drivers.
For instance, the SQL
Links driver for Oracle is called SQLORA32.dll. The
driver for InterBase is called SQLINT32.dll, and so on, as shown in Table
8.1.
Table 8.1. DLLs associated with various SQL drivers.
| Database | SQL Links driver names |
| DB2 | SQLDB232.dll |
| Informix | SQLINF32.dll |
| InterBase | SQLINT32.dll |
| MS SQL | SQLMSS32.dll |
| Oracle | SQLORA32.dll |
| Sybase | SQLSSC32.dll |
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 Delphi to connect to these databases.
There is a Sams Publishing book called the Database Developer's Guide with Delphi 2 (Ken Henderson, ISBN 0-672-30862-2), which goes into considerable depth on connecting to most of the major servers. The portion of the book you want to see is Chapter 24, "Delphi's Database Drivers Demystified." Almost anything this book says about Delphi databases will apply equally to BCB's databases. Delphi and BCB are sister and brother tools, and their approach to databases is almost identical.
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.
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.
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.
FIGURE 8.12.
The TCP/IP information from the Control Panel.
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 Delphi. I also have a network at home with machines on it that are never connected directly to the Net.
I want to stress, however, that if you are connected to the Internet, it is very simple to ask your IS department for an IP address for your computer. There are plenty of IP addresses in this world, and everyone can afford to have several for their own use.
To check whether you are connected properly, open up a DOS window and try to ping one of the machines in your network. Ping is a built-in application that ships with Windows 95 and Windows NT. If you installed TCP/IP as explained previously, ping will be set up on your machine.
To get started, you can try to ping yourself:
Ping 143.186.186.2
Here is a built-in address for referencing your own machine:
Ping 127.0.0.1
Or you can try to ping one of the other machines in your network:
Ping 143.186.186.3
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:
Ping compuserve.com
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 Chicago # # 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
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.
FIGURE 8.13.
The 32-Bit ODBC application.
On my system
one of the available drivers is called InterBase 4.X driver by Visigenic
(*.gdb). If I selected this button in the Add dialog, the dialog shown in
Figure 8.14 would appear.
FIGURE
8.14.
The dialog used for configuring an ODBC connection.
To set up a connection to the Employee.gdb table through the local InterBase
server, I fill out the fields of the dialog, as shown
in Figure 8.14. The database
field of the dialog might have a path in it that looks like this:
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 ODBC Drive: InterBase 4.x Driver by Visigenic 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.
FIGURE 8.15.
The dialog for creating a new ODBC. connection.
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.
©Copyright,
Macmillan Computer Publishing. All rights reserved.
