Copyright (c) 1999 by Charlie Calvert
Delphi ships with the Local InterBase Server, which is sometimes simply called LIBS. This tool provides all the capabilities of the full InterBase server, but it runs on a local machine. You do not need to be connected to a network to be able to run the Local InterBase Server.
The client software you get with Delphi will talk to either LIBS or the standard version of the InterBase server. From your point of view as a programmer, you will find no difference between talking to LIBS and talking to an InterBase server across a network. The only way to tell which server you're connected to is by examining the path in your current alias. In short, LIBS is the perfect tool for learning or practicing real client/server database programming even if you're not connected to a LAN.
The goal of this chapter is to provide you with a useful introduction to LIBS and also a brief overview of transactions. In particular, you will learn how to do the following:
Everything you read about the local InterBase in this chapter applies equally to the full-server version of InterBase. As a result, this chapter will also be of interest to people who use InterBase on a network.
If you work with another database such as Oracle, you might still be interested in the material found in this chapter.
In particular, this chapter shows how you can use a local system to create a database that is fully compatible with the network version of InterBase. To convert a LIBS database to a real client/server application on a network, you just have to copy your database onto another machine:
copy MyDatabase.gdb p:\remote\nt\drive
You just copy the one file onto the network. No other steps are necessary, other than changing the path in your alias. Of course, you will also need a real copy of the InterBase server.
Note that the Client/Server version of Delphi ships with five licenses for the full InterBase server. The real InterBase server runs on most platforms, including Windows 95/98, Windows NT, and a wide range of UNIX platforms.
Many readers of this book will come from the world of "big iron," where the only kinds of databases that exist are servers such as Oracle, Sybase, InterBase, AS400, or DB2. Other readers come from the world of PCs, where tools such as dBASE, Paradox, Access, or FoxPro are considered to be the standard database tools. Overemphasizing the huge gap that exists between these two worlds is almost impossible.
Readers who are familiar with "big iron" and large network-based servers are likely to find the Local InterBase Server very familiar. Readers who come from the world of PCs are likely to find InterBase very strange indeed, especially at first.
InterBase is meant to handle huge numbers of records, which are stored on servers. It does not come equipped with many of the amenities of a tool such as dBASE or Paradox. In fact, InterBase supplies users with only the most minimal interface and instead expects you to create programs with a client-side tool such as Delphi. However, you will find that InterBase is not a particularly difficult challenge after you get some of the basics under your belt.
You probably work inside a corporation or at a small company. However, if you are a student or someone who wants to enter the computer programming world, you should pay special attention to the material in this and other chapters on InterBase.
Perhaps 80 percent of the applications built in America today use databases in one form or another. Indeed, most of these applications revolve around, and are focused on, manipulating databases. Furthermore, client/server databases such as InterBase, Oracle, or MS SQL Server form the core of this application development.
If you want to enter the programming world, getting a good knowledge of databases is one of the best ways to get started. Right now, there is virtually an endless need for good database programmers.
One note of caution should perhaps be added here. I happen to enjoy database programming. However, it is not the most romantic end of the computer business. If you're primarily interested in systems programming or game programming, then you should hold out for jobs in those fields rather than focus your career in an area of only minor interest to you.
Databases, however, offer the greatest opportunity for employment. In particular, client/server database programmers are almost always in demand. Because LIBS ships with your copy of Delphi, you have a great chance to learn the ins and outs of this lucrative field.
Owners of Delphi 4.0 Professional and Client/Server will have LIBS installed automatically when they install Delphi. Client/Server users will also get a five user licences to a full blown network based version of the product. In most cases, InterBase will run smoothly without any need for you to worry about setup. However, you should take several key steps to ensure that all is as it should be.
If you own the Client/Server version of Delphi, turn to the online help by searching in the index for the following key: InterBase: installing. There you will learn that the Client/Server version of Delphi comes with a five-user license for InterBase Server, and both the Professional and Client/Server versions of Delphi come with the Local InterBase Server. As a result, if you have the Client/Server version of Delphi, take a few moments to install the full network based version of InterBase server, and use that instead of LIBS. As I stated elsewhere in this chapter, you will find no difference in the way LIBS works and the way the full InterBase server works; one just has more power than the other.
When you install the true version of the InterBase server, you will find a file called IBKey in the IB5 directory on the CD-ROM. This file contains the certificate information needed to get past the copyright screens you encounter during the installation. To install the InterBase server, you must go to the CD-ROM and explicitly install it. It won't be loaded automatically for you when you install Delphi.
After you install the full InterBase server and client, it probably won't be configured correctly until you enter all the keys to activate it. To do so, choose Start, Programs, InterBase, InterBase License Registration. After the License Registration application loads, enter all the keys you need, as defined in IBKey.txt from the CD-ROM.
Here is an example of what one of the keys looks like:
Certificate ID: XX-XX-DEC-XXXXX
Certificate Key: XX-X-X-X
The simplest way to enter these keys is to cut and paste between the list on the CD and the InterBase License Registration program. After you are finished, you will probably have entered five sets of keys.
Getting everything set up properly is very important. InterBase does not always give you sensible error messages when you don't have the keys installed properly. Instead, you might get an error message that leads you to look in some completely different area to fix the problem. You simply must have the product registered properly before you try to use it; otherwise, the product's behavior is completely undefined.
You can drive yourself mad by having the product partially registered. For example, you might be able to get data from the product, update records, and so on, and think all is fine. Then you try to change some metadata. If the product is not registered to support this feature, your attempts will fail and the error messages you get back might not even hint at the fact that the product is not correctly registered. They might point you off in some other direction altogether. Follow my suggestion: The simplest thing is to fully register the product right from the start and then run simple tests to ensure that it is working.
After you register the product, find out if LIBS or the real InterBase server is running. By default, it will load into memory every time you boot up the system. If you're running Windows 95, Windows 98, or NT 4, you should see the InterBase Guardian as a little splash of green on the system tray to the right of the toolbar. On Windows NT 3.51, an icon appears at the bottom of your screen. Whatever shape it takes on your system, just click this green object, and you will see a report on the Local InterBase Server configuration.
The InterBase Guardian is actually a helper program designed to keep the server running. If, for some reason, your server is blown out of the water, then the Guardian will start it up again automatically. The Guardian will do nothing if you intentionally shut down the server. On NT, both the Guardian and the server are listed in the Services applet in the Control Panel. You can read more about the Guardian in the Operations Guide for InterBase.
You must know where your copy of LIBS is installed. Most likely, it is in the ..\PROGRAM FILES\INTERBASE CORP\INTERBASE subdirectory on the boot drive of your computer. Alternatively, it could be in the ..\PROGRAM FILES\BORLAND\INTRBASE subdirectory, which is where this file was put in previous versions of Delphi. For example, my copy of the local InterBase is in C:\PROGRAM FILES\INTERBASE CORP\INTERBASE. To find out for sure, right-click the InterBase icon on your taskbar and choose Properties or, if you started InterBase as a service, examine the properties of your InterBase icons on the Windows' Start menu.
To find this same information in the Registry, run REGEDIT.EXE, and open HKEY_LOCAL_MACHINE/SOFTWARE/INTERBASE CORP/INTERBASE. Several nodes report on the location of your server and other related information. (On NT 3.51 machines, the program is called REGEDIT32.EXE.)
In the INTERBASE subdirectory, you will find a copy of a file called INTERBAS.MSG. You should also be able to locate a copy of GDS32.DLL somewhere on your system, most likely in the ..\WINDOWS\SYSTEM subdirectory or the WINNT\SYSTEM32 subdirectory, but possibly in either your BDE or INTRBASE subdirectory.
A common problem occurs when InterBase users end up with more than one copy of GDS32.DLL. If you work with the networked version of InterBase, you probably already have a copy of the InterBase Client on your system. If this is the case, you should make sure that you don't have two sets of the file GDS32.DLL on your path. On my system, I use the copy of GDS32.DLL that comes with the InterBase server, instead of the one for LIBS. These tools communicate with both LIBS and the full networked version of InterBase. This setup works fine for me. However, the point is not which version you use, but only that you know which version is on your path and that you have only one version on your system at a time.
Borland almost always puts the version number of a product as the time at which the files were modified. InterBase is on version 5 at the time I write. Therefore, your version of GDS32.DLL should be made at 5:00 a.m. or later, and should have a date of 12-01-97 or later. Make sure you are not running on older version of GDS32.DLL that might have gotten copied over the new version installed by Delphi.
InterBase once was a Borland product. The creators of this product have since split off into a wholly-owned subsidiary of Borland called the InterBase Corporation. When this split happened, the developers and staff of InterBase began to enjoy a certain degree of autonomy.
To find out which version of InterBase you are currently using, run the InterBase Communications Diagnostics Tool that ships with Delphi.
Use the Browse button to find the EMPLOYEE.GDB file, which is probably located in the ..PROGRAM FILES\INTERBASE CORP\INTERBASE\EXAMPLES subdirectory. Enter SYSDBA, all uppercase, as the username, and masterkey as the password, all lowercase. (This example assumes that you have not changed the password from its default value.) You should get the following readout, or something like it:
Path Name := C:\WINDOWS\SYSTEM\gds32.dll
Size := 321536 Bytes
File Time := 05:00:00
File Date := 12/01/1997
Version := 5.0.0.627
This module has passed the version check.
Attempting to attach to
[ic:ccc]c:\program files\interbase corp\interbase\examples\employee.gdb
Attaching ...Passed!
Detaching ...Passed!
InterBase versions for this connection:
InterBase/x86/Windows NT (access method), version "WI-V5.0.0.627"
on disk structure version 9.0
InterBase Communication Test Passed
The key piece of information you're getting here is the location and version number of GDS32.DLL.
Here is how part of this output would look if you are connecting to the previous version of the server:
InterBase versions for this connection: InterBase/x86/Windows NT (access method), version "WI-V4.2.1.328" on disk structure version 8.0
This report comes from InterBase version 4.2, not from 5. If possible, you should make sure your system is upgraded to version 5. Of course, you may have a later version of the product on your system. The point here is merely that you should be able to find a version number and be able to read it.
If you want to connect to the full server version of InterBase, you will find that the procedure I have just outlined works fine, except that you must have a network protocol such as TCP/IP loaded first. This task is usually handled automatically by either Windows 95/98 or Windows NT. Setting up an InterBase connection is usually a fairly straightforward process when compared to setting up other servers.
The most obvious thing that can go wrong with an InterBase connection is simply that it is not being started automatically when you start Windows. If you are having trouble, try simply pointing the Explorer to the InterBase/bin subdirectory and clicking the IBServer.exe icon. The trouble could be that all is set up correctly, but for some reason the server is not currently running on your machine.
In the preceding section, you learned how to run a diagnostic tool to be sure you are connected to InterBase. This section deals with the issue of making sure that the BDE is able to connect to InterBase through the native SQL Links driver. In other words, the previous section dealt with making sure that InterBase was running correctly on your machine; this section deals with making sure Delphi is connected to InterBase. You should also check the readme file on the CD-ROM that accompanies this book for general information about setting up aliases for the programs.
After you have InterBase set up, take a few minutes to make sure the connection to the BDE is working correctly. In particular, make sure an alias points to one of the sample tables that ships with LIBS. For example, after a normal full installation of Delphi, you should have an alias called IBLOCAL that points to the EMPLOYEE.GDB file.
Next, you'll learn how to set up an alias identical to the IBLOCAL alias, except you can give it a different name. To begin, open the SQL Explorer and turn to the Databases page. (The SQL Explorer once was called the Database Explorer in previous versions of the product.) Select the first node in the tree, the one that's called Databases. Choose Object, New, and then select InterBase as the Database Driver Name in the New Database Alias page dialog box. Click OK.
Name the new alias TESTGDB, or give it whatever name you prefer. The ServerName property for this alias should be set to
C:\Program Files\InterBase Corp\InterBase\Examples\employee.gdb
You can adjust the drive letter and path to reflect the way you have set up the files on your machine.
When you are running against a server on a remote machine, then you should refernce that server in your server name: Spider:/Program File\InterBase Corp\InterBase\Examples\Employee.gdb
Instead of trying to type this information directly, use the Browse button to search across your hard drive with the File Open dialog box. To get to this dialog box, look for the ellipses ([el]) button on the far right of the editor control that lets you type in the server name.
Set the username to SYSDBA, and the default password you will use is masterkey. (If someone has changed the password on your system, use the new password. You can change the default password in the InterBase Server Manager, as described later in Security and the InterBase Server Manager section of this chapter.) All the other settings in the Database Explorer can have their default values, as shown in Figure 11.1. After you have everything set up correctly, choose Object, Apply.
Figure 11.1: A sample InterBase alias as it appears in the Database Explorer.
After you have set up and saved your alias, you can connect to the TESTGDB alias exactly as you would with any other set of data. From inside the Explorer, just click the plus symbol before the TESTGDB node. A dialog box will pop up prompting you for a password. Make sure the username is set to SYSDBA, and then enter masterkey as the password. Everything else will then be the same as when you're working with a Paradox table, except that you will find many new features such as stored procedures and triggers. Most of these new features are described in this chapter and the next.
To connect to the database from Delphi, first drop a table onto a form, and set its DatabaseName property to TESTGDB. When you try to drop down the list of TableNames, you will be prompted for a password. Enter masterkey at this point, all in lowercase. Now drop down the list again and select a table. After taking these steps, you can set the Active property for Table1 to True. If this call succeeds, everything is set up correctly, and you can begin using the InterBase to create Delphi database programs. If you can't set Active to True, go over the steps outlined previously, and see whether you can correct the problem.
I usually use SYSDBA and masterkey as the username and password combination for the InterBase databases in this book. However, I sometimes work with USER1 and USER1 instead, simply because typing USER1 is easier than typing masterkey. One way to change the sign-on criteria for InterBase is via the InterBase Server Manager. The Server Manager is discussed later in this chapter, in the section called "Security and the Server Manager."
In the preceding two sections, you learned the basic facts about using LIBS. The next step is to learn how to create your own databases and tables.
Unlike local Paradox or dBASE files, InterBase tables are not stored in separate files located within a directory. Instead, InterBase tables are stored in one large file called a database. Therefore, you must first go out and create a database, and then you can create a series of tables inside this larger database.
The single-file system is, in my opinion, vastly superior to having a series of separate files. I'm sure you've noticed what happens after you have placed a few indexes on a typical Paradox table. The end result is that your table is associated with six or seven other files, some of which must be present or you can't get at your data. These files have names like Address.XGO and Address.XG1. A big Paradox database might consist of a hundred or more files, all of which must be backed up, moved from place to place, and maintained. Life is much simpler when your whole database is stored in a single file!.
The simplest way to create a database is with a third-party CASE tool such as SDesigner or Cadet. However, these tools do not ship with Delphi, so you must instead choose from the Database Desktop, Delphi itself, or the WISQL program that ships with the Local InterBase Server. Without a CASE tool, I find that my weapon of choice is WISQL, though this decision is certainly debatable. You can open this program by choosing Start, Programs, InterBase, InterBase Windows ISQL.
WISQL stands for Windows Interactive Standard Query Language, or simply the Interactive SQL tool. WISQL is fundamentally a tool for entering SQL statements, with a few other simple features thrown in for good measure. One advantage of relying on WISQL is that it enables you to work directly in the mother tongue of databases, which is SQL. I find that defining databases directly in SQL helps me understand their structure, though of course there is little reason for resorting to these measures if you have a copy of SDesigner or ERWin available. (Cadet is a much less expensive tool that might still be available as shareware when you read this chapter.)
Also remember that WISQL bypasses the BDE altogether. You can therefore use it to test your connections to InterBase even if you are not sure whether you have the BDE set up correctly. For example, if you're having trouble connecting to InterBase and you're not sure where the problem lies, start by trying to connect with WISQL. If that works, but you can't connect from inside Delphi, the problem might lie not with your InterBase setup, but with the way you have deployed the BDE.
After you start WISQL, choose File, Create Database. A dialog box like the one shown in Figure 11.2 appears. Set the Location Info to Local Engine because you are, in fact, working with Local InterBase. (Actually, I can't think of any reason why you must use Local InterBase rather than the full server version when you're working through these examples. However, I will reference LIBS throughout this chapter because it is the tool of choice for most readers.)
Figure 11.2: The dialog box used to create databases inside WISQL.
In the Database field, enter the name of the database you want to create. If it is to be located inside a particular directory, include that directory in the database name. For practice, create a database called INFO.GDB that is located in a subdirectory called DATA. If it does not already exist on your system, first go to DOS or the Windows Explorer and create the DATA subdirectory. After you set up the subdirectory, enter the following in the Database field, where you can replace D with the appropriate drive on your system:
D:\DATA\INFO.GDB
The extension .gdb is traditional, though not mandatory. However, I suggest always using this extension so that you can recognize your databases instantly when you see them. Accidentally deleting even a recently backed up database can be a tragedy.
You can set the username to anything you want, although the traditional entry is SYSDBA and the traditional password is masterkey. When you first start out with InterBase, sticking with this username and password combination is probably best. Even if you assign new passwords to your database, the SYSDBA/masterkey combination will still work unless you explicitly remove it using the InterBase Server Manager (IBMGR.EXE). Of course, when you have sensitive data to protect, you want to be more careful about how you set up your password.
After you enter a username and password, you can create the database by clicking OK. If all goes well, you are then placed back inside WISQL. At this stage, you can either quit WISQL or add a table to your database. If something goes wrong, an error message will appear. Click the Details button to try to track down the problem.
Assuming all goes well, you can run the following SQL statement inside WISQL if you want to create a very simple table with two fields:
CREATE TABLE TEST1 (FIRST VARCHAR(20), LAST INTEGER);
Enter this line in the SQL Statement field at the top of WISQL, and then click Run (the Run button has a lightning bolt on it). You can also select Query, Execute from the menu or press Ctrl+Enter to run the query. If all goes smoothly, your statement will be echoed in the ISQL output window without being accompanied by an error dialog box. The lack of an error dialog box signals that the table has been created successfully.
The preceding CREATE TABLE command creates a table with two fields. The first is a character field that contains 20 characters, and the second is an integer field. After you create a database and table, choose File, Commit Work. This command causes WISQL to actually carry out the commands you have issued. Then choose File, Disconnect from Database.
The table-creation code shown here is used to describe or create a table in terms that WISQL understands. In fact, you can use this same code inside a TQuery object in a Delphi program. Throughout most of this chapter and the next, I work with WISQL rather than with the DBD. In describing how to perform these actions in WISQL, I do not mean to imply that you can't use the Database Desktop to create or alter InterBase tables. In fact, the 32-bit version of DBD provides pretty good support for InterBase tables. Still, I have found WISQL to be considerably more powerful than I suspected when I first started using it. Once again, I should add that neither of these tools is as easy to use as a good CASE tool.
In this section, you learned the basic steps required to use InterBase to create a database and table. The steps involved are not particularly complicated, although they can take a bit of getting used to if you're new to the world of SQL.
WISQL provides several tools that can help you explore a database and its contents. In the preceding section, you created a database with a single table. In this section, you will learn how to connect to the database and table from inside WISQL. You also will see how to examine the main features of the entities you have created.
To connect to INFO.GDB, choose File, Connect to Database, which brings up the dialog box shown in Figure 11.3. Enter the drive and the database as e:\data\info.gdb, where e: represents the appropriate drive on your machine. Enter the user as SYSDBA and the password as masterkey. If all goes well, you should be able to connect to the database by clicking OK. Once again, success is signaled by the lack of an error message.
Figure 11.3: Connecting to the INFO.GDB database using WISQL.
Choose Metadata, Show, and select Database from the options, as shown in Figure 11.4. After you click OK, the information displayed in the ISQL output window should look something like this:
SHOW DB
Database: c:\data\info.gdb
Owner: SYSDBA
PAGE_SIZE 1024
Number of DB pages allocated := 210
Sweep interval := 20000
Figure 11.4: Preparing to view information on the INFO.GDB database.
To see the tables available in a database, choose Metadata, Show, and select Table from the list of options. You can leave the edit control labeled Object Name blank. If you fill it in with a table name, you will get detailed information on a specific table; in this case, though, you want general information on all tables. Click OK and view the information, which should look like the following in the ISQL output window:
SHOW TABLES
TEST1
Browsing through the View Information dialog from the Metadata | Show menu choice, you can see that InterBase supports triggers, stored procedures, views, and a host of other advanced server features.
By choosing MetaData, Extract Database, you can find out more detailed information about the database and its tables. For example, if you choose Extract, SQL Metadata for a Database, you get output similar to the following:
/* Extract Database e:\data\info.gdb */
CREATE DATABASE "e:\data\info.gdb" PAGE_SIZE 1024
;
/* Table: TEST1, Owner: SYSDBA */
CREATE TABLE TEST1 (FIRST VARCHAR(20),
LAST INTEGER);
/* Grant permissions for this database */
If you choose Metadata, Extract Table, you get the following output:
/* Extract Table TEST1 */
/* Table: TEST1, Owner: SYSDBA */
CREATE TABLE TEST1 (FIRST VARCHAR(20),
LAST INTEGER);
Note that WISQL often asks whether you want to save the output from a command to a text file, and the File menu gives you some further options for saving information to files. You can take advantage of these options when necessary, but 90 percent of the time I pass them by with barely a nod. (Some CASE tools use the output from your choosing Metadata, Extract Database to reverse-engineer a database. If your CASE tool asks you for a script file, you can produce one this way.)
The WISQL program accepts most SQL statements. For example, you can perform Insert, Select, Update, and Delete statements from inside WISQL. Just enter the statement you want to perform in the SQL Statement area, and then click Run. Earlier versions of WISQL also came equipped with a handy online reference to SQL. If you had questions about how to format an Alter, Drop, Insert, Create Index, or other SQL statement, you could look it up in the help for WISQL. (A book I have found useful for checking SQL syntax is called The Practical SQL Handbook, by Bowman, Emerson, and Darnovsky, Addison Wesley, ISBN 0-201-62623-3.)
After reading the previous sections, you should have a fair understanding of how WISQL works and how you can use it to manage a database. The information provided in this chapter is simply an introduction to a complex and very sophisticated topic. However, you now know enough to begin using the Local InterBase. This accomplishment is not insignificant. Tools such as InterBase, Oracle, and Sybase lie at the heart of the client/server activity that is currently so volatile and lucrative. If you become proficient at talking to servers such as InterBase, you might find yourself at an important turning point in your career.
Now you can break out of the abstract theory rut and start writing some code that actually does something. In this section, you will look at transactions, followed by a discussion of cached updates and many-to-many relationships. In the next chapter, you will see another "real-world" database when you look at a sample program that tracks the albums, tapes, and CDs in a music collection.
The TRANSACT program, found on the CD-ROM that accompanies this book, gives a brief introduction to transactions. To use transactions, you must have a TDataBase component on your form. Transactions work not only with real servers such as Sybase, Informix, InterBase, or the Local InterBase, but also with the 32-bit BDE drivers for Paradox or dBASE files. In other words, transactions can be part of most of the database work you will do with Delphi. Using transactions is, however, a technique most frequently associated with client/server databases.
To begin, drop a TDatabase component on a TDataModule. Name the TDataModule DMod, and save it in a file called DMod1, per the usual standards employed in this book. Set the AliasName property of the TDataBase object to a valid alias such as IBLOCAL. Create your own string, such as TransactionDemo, to fill in the DatabaseName property of the TDatabase object. In other words, when you're using a TDatabase component, you make up the DatabaseName rather than pick it from a list of available aliases.
Drop a TQuery object on the datamodule and hook it up to the EMPLOYEE.GDB file that ships with Delphi. In particular, set the DatabaseName property of the TQuery object to TransactionDemo, not to IBLOCAL. In other words, set the DatabaseName property to the string you made up when filling in the DatabaseName property of the TDatabase component. You will find that TransactionDemo, or whatever string you chose, has been added to the list of aliases you can view from the Query1.DatabaseName Property Editor. Now rename Query1 to EmployeeQuery and attach a TDataSource object called EmployeeSource to it. Set the EmployeeQuery.SQL property to the following string:
select * from employee
Then set the Active property to True and set RequestLive to True.
Add a TTable object to the project, hook it up to the SALARY_HISTORY table, and call it SalaryHistoryTable. Attach a data source called SalaryHistorySource to it. Relate the SalaryHistoryTable to the EmployQuery table via the EMP_NO fields of both tables. In particular, you should set the MasterSource property for the SalaryHistoryTable to EmployeeSource. Then click the MasterFields property of the TTable object, and relate the EMP_NO fields of both tables. This way, you can establish a one-to-many relationship between the EmployeeQuery and the SalaryHistoryTable.
After you're connected to the database, you can add two grids to your main form so that you can view the data. Hook up one grid to one table via a TDataSource component and the second grid to the second table via a TDataSource component. Remember that you should choose File, Include Unit to link the TDataModule to the main form.
On the surface of the main form, add four buttons and give them the following captions:
Begin Transaction Rollback Commit Refresh
The code associated with these buttons should look like this:
procedure TForm1.BeginTransactionClick(Sender: TObject);
begin
DMod.TransDemo.StartTransaction;
end;
procedure TForm1.RollbackClick(Sender: TObject);
begin
DMod.TransDemo.Rollback;
RefreshClick(nil);
end;
procedure TForm1.CommitClick(Sender: TObject);
begin
DMod.TransDemo.Commit;
end;
{ Because of the indexing, we can't call Refresh explicitly }
procedure TForm1.RefreshClick(Sender: TObject);
var
Bookmark: TBookmark;
begin
Bookmark := DMod.EmployeeQuery.GetBookmark;
DMod.EmployeeQuery.Close;
DMod.EmployeeQuery.Open;
DMod.EmployeeQuery.GotoBookmark(Bookmark);
DMod.EmployeeQuery.FreeBookmark(Bookmark);
end;
At this point, run the program, click Start Transaction, and edit a record of the SalaryHistoryTable. When you do so, be sure to fill in all the fields of the table except for the first and last, which are called EMP_NO and NEW_SALARY. Be sure not to touch either of those fields because they will be filled in for you automatically. In particular, you might enter the following values:
CHANGE_DATE: 12/12/12 UPDATER_ID: admin2 OLD_SALARY: 105900 PERCENT_CHANGE:
These values are not randomly chosen. For example, you must enter admin2, or some other valid UPDATER_ID, in the UPDATER_ID field. You can, of course, enter whatever values you want for the date, old salary, and percent change fields. Still, you must be careful when working with the Employee tables. This database has referential integrity with a vengeance.
After entering the preceding values, you can post the record by moving off it. When you do, the NEW_SALARY field will be filled in automatically by something called a trigger. Go ahead and experiment with these tables if you want. For example, you might leave some of the fields blank or enter invalid data in the UPDATER_ID field, just to see how complex the rules that govern this database are. This data is locked up tighter than Fort Knox, and you can't change it unless you are very careful about what you're doing. (It's worth noting, however, that the developers of this database probably never planned to have anyone use these two tables exactly as I do here. Defining rules that limit how you work with a database is easy, but finding ways to break them is easier still. For all of its rigor, database programming is still not an exact science.)
If you started your session by clicking the Start Transaction button, you can now click RollBack and then Refresh. You will find that all your work is undone, as if none of the editing occurred. If you edit three or four records and then click Commit, you will find that your work is preserved.
Although you are safe in this particular case, in some instances like this you can't call Refresh directly because the table you're using is not uniquely indexed. In lieu of this call, you can close the table and then reopen it. You could use bookmarks to preserve your location in the table during this operation, or if you're working with a relatively small dataset you can just let the user fend for himself or herself.
Note that when you run the TRANSACT program included on the CD-ROM, you don't have to specify a password because the LoginPrompt property of the TDatabase object is set to False, and the Params property contains the following string:
password:=masterkey
Now that you have seen transactions in action, you probably want a brief explanation of what they are all about. Here are some reasons to use transactions:
Usually, you can simply leave this field set to tiReadCommitted. However, you need to understand that you have several options regarding how the data in your database is affected by a transaction. The whole subject of how one user of a database might alter records in a table while they are being used by another user is quite complicated, and it poses several paradoxes for which no simple solution exists. The preceding TransIsolation levels enable you to choose your poison when dealing with this nasty subject.
You must consider other issues when you're working with transactions, but I have tried to cover some of the most important here. In general, I find that transactions are extremely easy to use. However, they become more complex when you consider the delicate subject of concurrency problems, which are frequently addressed through setting the TransIsolation levels of your transactions.
Cached updates are like the transactions just described, except that they enable you to edit a series of records without causing any network traffic. When you are ready to commit your work, cached updates enable you to do so on a record-by-record basis, where any records that violate system integrity can be repaired or rolled back on a case-by-case basis.
Some users have reported remarkable increases in performance on some operations when they use cached updates.
The key feature of cached updates is that they let you work with data without allowing any network traffic to occur until you are ready for it to begin. A relatively complex mechanism also enables you to keep track of the status of each record on a field-by-field basis. In particular, when cached updates are turned on, you can query your records one at a time and ask them whether they have been updated. Furthermore, if they have been updated, you can ask the current value of each field in the updated record, and you can also retrieve the old, or original, value of the field.
You can do three things with the records in a dataset after the CachedUpdates property for the dataset has been set to True:
An excellent sample program in the Delphi4\Examples\CachedUP subdirectory shows how to use cached updates. This program is a bit complex in its particulars, however, and can therefore be hard to understand. So, instead of trying to go it one better, I will create a sample program that takes the basic elements of cached updates and presents them in the simplest possible terms.
The CachedUpdates program, shown in Figure 11.5, has one form. On the form is a copy of the Orders table. Recall that the Orders table is related to both the Customer table and the Items table. As a result, changing either the OrderNo or CustNo fields without violating system integrity in one way or another is difficult. When working with this program, you should change these fields to values like 1 or 2, which will almost surely be invalid. You can then watch what happens when you try to commit the records you have changed.
Figure 11.5: The CachedUpdates program
The code for the CachedUpdates program is shown in Listing 11.1. Go ahead and get this program up and running, and then come back for a discussion of how it works. When you're implementing the code shown here, the key point to remember is that none of it will work unless the CachedUpdates property of the OrdersTable is set to True.
Listing 11.1[em]The Form for the CachedUpdates Program
//////////////////////////////////////
// File: Main.pas
// Project: CacheUp
// Copyright (c) 1998 by Charlie Calvert
//
unit main;
{ Copyright 1996 by Charlie Calvert
Working with cached updates.
The simplest way to see the program in action is
to change the OrderNo of several records to small
integer values such as 1, 2, or 3. Then press
apply to see the errors this generates. The errors
occur because these small integer values violate
database integrity. To revert back to the old
values press cancel. To change one value back at
a time, first select the value, then press Revert. }
interface
uses
Windows, Messages, SysUtils,
Classes, Graphics, Controls,
Forms, Dialogs, DB,
Grids, DBGrids, DBTables,
StdCtrls, Buttons, TypInfo,
ExtCtrls;
type
TForm1 = class(TForm)
Table1: TTable;
DBGrid1: TDBGrid;
DataSource1: TDataSource;
bApply: TBitBtn;
bRevert: TBitBtn;
BitBtn1: TBitBtn;
Panel1: TPanel;
Edit1: TEdit;
Edit2: TEdit;
Label1: TLabel;
Label2: TLabel;
ListBox1: TListBox;
BitBtn2: TBitBtn;
Label3: TLabel;
Bevel1: TBevel;
Bevel2: TBevel;
procedure bApplyClick(Sender: TObject);
procedure bRevertClick(Sender: TObject);
procedure Table1UpdateError(DataSet: TDataSet; E: EDatabaseError;
UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
procedure BitBtn1Click(Sender: TObject);
procedure DataSource1DataChange(Sender: TObject; Field: TField);
procedure BitBtn2Click(Sender: TObject);
end;
var
Form1: TForm1;
implementation
{$R *.DFM}
procedure TForm1.bApplyClick(Sender: TObject);
begin
Table1.ApplyUpdates;
end;
procedure TForm1.bRevertClick(Sender: TObject);
begin
Table1.RevertRecord;
end;
procedure TForm1.Table1UpdateError(DataSet: TDataSet; E: EDatabaseError;
UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
var
S1, S2: string;
begin
S1 := GetEnumName(TypeInfo(TUpdateKind), Ord(UpdateKind)) + ': ' + E.Message;
S2 := DataSet.Fields[0].OldValue;
S2 := S2 + ': ' + S1;
ListBox1.Items.Add(S2);
UpdateAction := uaSkip;
end;
procedure TForm1.BitBtn1Click(Sender: TObject);
begin
Table1.CancelUpdates;
end;
procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField);
begin
Panel1.Caption := GetEnumName(TypeInfo(TUpdateStatus), Ord(Table1.UpdateStatus));
if Table1.UpDateStatus = usModified then begin
Edit1.Text := Table1.Fields[0].OldValue;
Edit2.Text := Table1.Fields[0].NewValue;
end else begin
Edit1.Text := 'Unmodified';
Edit2.Text := 'Unmodified';
end;
end;
procedure TForm1.BitBtn2Click(Sender: TObject);
begin
Close;
end;
end.
The first thing to notice about the CachedUpdates program is that it tracks which records have been modified. For example, change the OrderNo field of the first two records to the values 1 and 2. If you now select one of these records, you will see that the small panel in the lower-left corner of the screen gets set to Modified. This means that the update status for this field has been set to modified.
Here is the TUpdateStatus type:
TUpdateStatus = (usUnmodified, usModified, usInserted, usDeleted);
Any particular record in a database is going to be set to one of these values.
Here is the code that sets the value in the TPanel object:
procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField);
begin
Panel1.Caption := GetEnumName(TypeInfo(TUpdateStatus), Ord(Table1.UpdateStatus));
if Table1.UpDateStatus = usModified then begin
Edit1.Text := Table1.Fields[0].OldValue;
Edit2.Text := Table1.Fields[0].NewValue;
end else begin
Edit1.Text := 'Unmodified';
Edit2.Text := 'Unmodified';
end;
end;
The relevant line in this case is the second in the body of the function. In particular, notice that it reports on the value of OrdersTable.UpdateStatus. This value will change to reflect the update status of the currently selected record.
At the same time the CachedUpdates program reports that a record has been modified, it also reports on the old and new value of the OrderNo field for that record. In particular, if you change the first record's OrderNo field to 1, it will report that the old value for the field was 1003 and the new value is 1. (This assumes that you have the original data as it shipped with Delphi. Remember that if you end up ruining one of these tables performing these kinds of experiments, you can always copy the table over again from the CD. If you copy them directly using the Windows Explorer, remember that they will probably have the Read Only flag turned on. You can use the Windows Explorer to remove these flags.)
The following code reports on the old and new value of the OrderNo field:
Edit1.Text := Table1.Fields[0].OldValue; Edit2.Text := Table1.Fields[0].NewValue;
As you can see, this information is easy enough to come by; you just have to know where to look.
If you enter the values 1 and 2 into the OrderNo fields for the first two records, you will encounter errors when you try to commit the data. In particular, if you try to apply the data, the built-in referential integrity will complain that you cannot link the Orders and Items table on the new OrderNo you have created. As a result, committing the records is not possible. The code then rolls back the erroneous records to their original state.
When you are viewing these kinds of errors, choose Tools, Environment Options, Debugger and then turn off Integrated Debugging. Or, if you want, you can keep debugging on but let the User Program handle Delphi exceptions and Object Pascal exceptions. You can tell Delphi to let the User Program handle the exception by selecting Tools, Environment Options, Debugger and looking for the User Program option in the Exceptions section of the dialog box toward the bottom of the page. You first select either OBJECT PASCALExceptions or Delphi Exceptions in the list box and then select the User Program radio button. The issue here is that you want the exception to occur, but you don't want to be taken to the line in your program where the exception surfaced. You don't need to view the actual source code because these exceptions are not the result of errors in your code. In fact, these exceptions are of the kind you want and must produce and which appear to the user in an orderly fashion via the program's list box.
Referential Integrity is a means of enforcing the rules in a database. Some tables must obey rules, and the BDE will not let users enter invalid data that violates these rules. Here is the code that reports on the errors in the OrderNo field and rolls back the data to its original state:
procedure TForm1.Table1UpdateError(DataSet: TDataSet; E: EDatabaseError; UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction); var S1, S2: string; begin S1 := GetEnumName(TypeInfo(TUpdateKind), Ord(UpdateKind)) + ': ' + E.Message; S2 := DataSet.Fields[0].OldValue; S2 := S2 + ': ' + S1; ListBox1.Items.Add(S2); UpdateAction := uaSkip; end;
This particular routine is an event handler for the OnUpdateError event for the Table1 object. To create the routine, click once on the Table1 object, select its Events page in the Object Inspector, and then double-click the OnUpdateError entry.
The OrdersTableUpdateError method will be called only if an error occurs in attempting to update records. It will be called at the time the error is detected and before Delphi tries to commit the next record.
OrdersTableUpdateError gets passed four parameters. The most important is the last, which is a var parameter. You can set this parameter to one of the following values:
TUpdateAction = (uaFail, uaAbort, uaSkip, uaRetry, uaApplied);
If you set the UpdateAction variable to uaAbort, the entire attempt to commit the updated data will be aborted. None of your changes will take place, and you will return to edit mode as if you had never attempted to commit the data. The changes you have made so far will not be undone, but neither will they be committed. You are aborting the attempt to commit the data, but you are not rolling it back to its previous state.
If you choose uaSkip, the data for the whole table will still be committed, but the record that is currently in error will be left alone. That is, it will be left at the invalid value assigned to it by the user.
If you set UpdateAction to uaRetry, that means you have attempted to update the information in the current record and that you want to retry committing it. The record you should update is the current record in the dataset passed as the first parameter to OrdersTableUpdateError.
In the OrdersTableUpdateError method, I always choose uaSkip as the value to assign to UpdateAction. Of course, you could pop up a dialog box and show the user the old value and the new value of the current record. The user would then have a chance to retry committing the data. Once again, you retrieve the data containing the current "problem child" record from the dataset passed in the first parameter of OrdersTableUpdateError. I show an example of accessing this data when I retrieve the old value of the OrderNo field for the record:
S2 := DataSet.Fields[0].OldValue; S2 := S2 + ': ' + S1; ListBox1.Items.Add(S2);
The OldValue field is declared as a Variant in the source code to DB.PAS, which is the place where the TDataSet declaration is located:
function GetOldValue: Variant; property OldValue: Variant read GetOldValue;
Two other values are passed to the TableUpdateError method. The first is an exception reporting on the current error, and the second is a variable of type TUpdateKind:
TUpdateKind = (ukModify, ukInsert, ukDelete);
The variable of type TUpdateKind just tells you how the current record was changed. Was it updated, inserted, or deleted? The exception information is passed to you primarily so that you can get at the message associated with the current error:
E.Message;
If you handle the function by setting UpdateAction to a particular value, say uaSkip, then Delphi will not pop up a dialog box reporting the error to the user. Instead, it assumes that you are handling the error explicitly, and it leaves it up to you to report the error or not as you see fit. In this case, I just dump the error into the program's list box, along with some other information.
At this point, you should go back and run the Cache program that ships with Delphi. It covers all the same ground covered in the preceding few pages, but it does so in a slightly different form. In particular, it shows how to pop up a dialog box so that you can handle each OnUpdateError event in an intelligent and sensible manner.
In general, cached updates give you a great deal of power you can tap into when updating the data in a dataset. If necessary, go back and play with the CachedUpdates program until it starts to make sense to you. This subject isn't prohibitively difficult, but absorbing the basic principles involved takes some thought.
To save space in this book, the section called Many-To-Many has been moved to the CD. You will find this section and the accompanying code on the CD that accompanies this book in the Chap11 directory.
Before beginning the discussion of the Music program, covering a few basic issues regarding security might be a good idea. I included enough information so far to make any Delphi programmer dangerous, so I might as well also equip you with some of the tools you need to defend your work against prying eyes. If you have the skill to create programs that others can use, then you must know how to manage those clients.
When you are working with passwords, making a distinction between user security for an entire server and access rights for a particular table is important. If you open the InterBase Server Manager, log on, and select Tasks, User Security, you will find menu options that let you create new users for the system. By default, these users have access to very little. All you do is let them in the front door. You haven't yet given them a pass to visit any particular rooms in the house. As this discussion matures, I'll discuss how to grant particular rights to a user after he or she has been admitted into the "building."
If you are interested in setting up real security for your database, the first thing you should do is change the SYSDBA password. To change the password, sign on to the InterBase Server Manager as SYSDBA using the password masterkey. Select Tasks and then User Security from the menu. Select the username SYSDBA, and choose Modify User. Now enter a new password. After you do this much, the system is truly under your control. No one else can get at your data unless you decide that user should have the right to do so. Even then, you can severely proscribe that user's activities with a remarkable degree of detail. (If you are a control freak, this is paradise!)
After you establish your sovereignty, the next step is to go out and recruit the peons who will inhabit your domain. After you find a new user, select Tasks, User Security and choose Add User. Then give him or her a password. The person who creates users is the one who signs on as SYSDBA. SYSDBA has all power, which is the reason that changing the SYSDBA password is important if you are really serious about security.
If you create a new user, this newcomer has no rights on the system by default. To give a user rights, you must use the SQL grant command, which is discussed in the next section.
After you create a user in the InterBase Server Manager, you grant him or her rights to access a table. To do so, open WISQL or the SQL Explorer and connect to the database you want to work with. To grant rights, you can enter SQL statements into SQL and then execute them by pressing Ctrl+Enter. The actual statements you can use are discussed over the course of the next few paragraphs.
SQL databases give you extraordinary control over exactly how much access a user can have to a table. For example, you can give a user only the right to query one or more tables in your database:
grant select on Test1 to user1
Conversely, you may, if you want, give a user complete control over a table, including the right to grant other people access to the table:
grant all on album to Sue with grant option
The with grant option clause shown here specifies that Sue not only has her way with the Album table, but also can give access to the table to others.
You can give a user six distinct types of privileges:
Using these keys to the kingdom, you can quickly start handing out passes to particular rooms in the palace. For example, you can write
grant insert on Test1 to Sue grant delete on Test1 to Mary with grant option grant select on Test1 to Tom, Mary, Sue, User1 grant select, insert, delete, update on Test1 to Mary grant delete, insert, update, references on country to public with grant option;
The last statement in this list comes from the Employee.gdb example that ships with Delphi. Notice that it grants rights to the public, which means all users have absurdly liberal rights on the table.
The opposite of the grant command is revoke. Revoke removes privileges given with grant. Here is an example of using revoke:
revoke select on Test1 from Sue
This brief overview of the Server Manager and some related issues involving the grant command should give you a sense of how to limit access to your database. None of this material is particularly difficult, but SQL databases can be frustrating if you don't know how to control them.
Another important feature of the InterBase Server Manager is backing up tables. This task can be especially important if you must move a table from Windows 95/98 or Windows NT to UNIX. The highly compressed backup format for InterBase tables is completely version independent, so you can back up an NT table and then restore it on a UNIX system.
To get started backing up a database, sign on to the InterBase Server Manager. To sign on, all you must do is specify the masterkey password; everything else is automatic when signing on to the local version of InterBase. Of course, if you changed the SYSDBA password from masterkey to something else, then you must use the new password you created.
Go to the Tasks menu and select Backup. Enter the path to the local database you want to back up. For example, you might type c:\data\info.gdb in the edit control labeled Database Path. This means you want to back up the database called info.gdb.
Enter the name of the backup table you want to create in the Backup File or Device field. For example, you might type c:\data\info.gbk. Use the GDB extension for live tables and GBK for backed-up tables. These are just conventions, but they are good ones.
Select Transportable Format from the Options group box, and set any other flags you want to use. Click OK, and then be prepared for a short delay while InterBase contemplates certain knotty passeges from the works of the philosopher Immanuel Kant.. If all goes well, the results of your work might look something like this:
Backup started on Tue Dec 24 15:26:42 1996...
gbak: gbak version WI-V4.1.0.194
gbak: Version(s) for database "e:\data\info.gdb"
InterBase/x86/Windows NT (access method), version "WI-V4.2.1.328"
on disk structure version 8.0
Request completed on Tue Dec 24 15:26:45 1996
You can now close the InterBase Server Manager and copy your backed-up file to a floppy disk, zip drive, or other storage medium. Remember, the great thing about these files is that they are small, highly compressed, and can be moved from one operating system to another.
InterBase runs on a wide variety of UNIX platforms.
This chapter gave you a basic introduction to the Local InterBase and to several related subjects. In particular, you learned how to create and open InterBase databases, how to set up aliases, and how to perform fundamental database tasks such as transactions.
I should stress that InterBase is a very complex and powerful product, and what you read in this chapter should serve as little more than a brief introduction that will whet your appetite. In the next chapter, you will look at stored procedures, triggers, InterBase calls, and a few other tricks that should help you grasp the extent of the power in both the local and server-based versions of InterBase.
Delphi protects you from the details of how a server handles basic database chores. However, Delphi also enables you to tap into the power associated with a particular server. This was one of the most delicate balances that the developers had to consider when they created Delphi: How can you make a database tool as generic as possible, without cutting off a programmer's access to the special capabilities of a particular server? The same type of question drove the developers' successful quest to make Delphi's language as simple and elegant as possible without cutting off access to the full power of the Windows API.
Now you can forage on to the next chapter. By this time, we are deep into the subject of databases. In fact, the stage is now set to open a view onto the most powerful tools in a database programmer's arsenal. After you master the stored procedures, generators, and triggers shown in the next chapter, you will be entering into the world of real client/server programming as it is done on the professional level. These tools drive the big databases used by corporations, governments, and educational institutions around the world.