The examples built earlier in this chapter were created with the new dbExpress database library. Using this server-independent approach allows you to switch the database server used by your application, although in practice doing so is often far from simple. If the application you are building will invariably use a given database, you can write programs that are tied directly to the API of the specific database server. This approach will make your programs intrinsically non-portable to other SQL servers.
Of course, you won't generally use these APIs directly, but rather base your development on dataset components that wrap these APIs and fit into Delphi and the architecture of its class library. An example of such a family of components is InterBase Express (IBX). Applications built using these components should work better and faster (even if only marginally), giving you more control over the specific features of the server. For example, IBX provides a set of administrative components specifically built for InterBase 6.
I'll examine the IBX components because they are tied to InterBase (the database server discussed in this chapter) and because they are the only set of components available in the standard Delphi installation. Other similar sets of components (for InterBase, Oracle, and other database servers) are equally powerful and well regarded in the Delphi programmers' community. A good example (and an alternative to IBX) is InterBase Objects (www.ibobjects.com).
The IBX components include custom dataset components and a few others. The dataset components inherit from the base TDataSet class, can use all the common Delphi data-aware controls, and provide a field editor and all the usual design-time features. You can choose among multiple dataset components. Three IBX datasets have a role and a set of properties similar to the table/query/storedproc components in the dbExpress family:
IBTable resembles the Table component and allows you to access a single table or view.
IBQuery resembles the Query component and allows you to execute a SQL query, returning a result set. The IBQuery component can be used together with the IBUpdateSQL component to obtain a live (or editable) dataset.
IBStoredProc resembles the StoredProc component and allows you to execute a stored procedure.
These components, like the related dbExpress ones, are intended for compatibility with older BDE components you might have used in your applications. For new applications, you should generally use the IBDataSet component, which allows you to work with a live result set obtained by executing a select query. It basically merges IBQuery with IBUpdateSQL in a single component. The three components in the previous list are provided mainly for compatibility with existing Delphi BDE applications.
Many other components in InterBase Express don't belong to the dataset category, but are still used in applications that need to access to a database:
IBDatabase acts like the DBX SQLConnection component and is used to set up the database connection. The BDE also uses the specific Session component to perform some global tasks done by the IBDatabase component.
IBTransaction allows complete control over transactions. It is important in InterBase to use transactions explicitly and to isolate each transaction properly, using the Snapshot isolation level for reports and the Read Committed level for interactive forms. Each dataset explicitly refers to a given transaction, so you can have multiple concurrent transactions against the same database and choose which datasets take part in which transaction.
IBSQL lets you execute SQL statements that don't return a dataset (for example, DDL requests, or update and delete statements) without the overhead of a dataset component.
IBDatabaseInfo is used for querying the database structure and status.
IBSQLMonitor is used for debugging the system, because the SQL Monitor debugger provided by Delphi is a BDE-specific tool.
IBEvents receives events posted by the server.
This group of components provides greater control over the database server than you can achieve with dbExpress. For example, having a specific transaction component allows you to manage multiple concurrent transactions over one or multiple databases, as well as a single transaction spanning multiple databases. The IBDatabase component allows you to create databases, test the connection, and generally access system data, something the Database and Session BDE components don't fully provide.
IBX datasets let you set up the automatic behavior of a generator as a sort of auto-increment field. You do so by setting the GeneratorField property using its specific property editor. An example is discussed later in this chapter in the section "Generators and IDs."
The InterBase Admin page of Delphi's Component Palette hosts InterBase administrative components. Although your aim is probably not to build a full InterBase console application, including some administrative features (such as backup handling or user monitoring) can make sense in applications meant for power users.
Most of these components have self-explanatory names: IBConfigService, IBBackupService, IBRestoreService, IBValidationService, IBStatisticalService, IBLogService, IBSecurityService, IBServerProperties, IBInstall, and IBUninstall. I won't build any advanced examples that use these components, because they are more focused toward the development of server management applications than client programs. However, I'll embed a couple of them in the IbxMon example discussed later in this chapter.
To build an example that uses IBX, you'll need to place in a form (or data module) at least three components: an IBDatabase, an IBTransaction, and a dataset component (in this case an IBQuery). Any IBX application requires at least an instance of the first two components. You cannot set database connections in an IBX dataset, as you can do with other datasets. And, at least a transaction object is required even to read the result of a query.
Here are the key properties of these components in the IbxEmp example:
object IBTransaction1: TIBTransaction Active = False DefaultDatabase = IBDatabase1 end object IBQuery1: TIBQuery Database = IBDatabase1 Transaction = IBTransaction1 CachedUpdates = False SQL.Strings = ( 'SELECT * FROM EMPLOYEE') end object IBDatabase1: TIBDatabase DatabaseName = 'C:\Program Files\Common Files\Borland Shared\Data\employee.gdb' Params.Strings = ( 'user_name=SYSDBA' 'password=masterkey') LoginPrompt = False SQLDialect = 1 end
Now you can hook a DataSource component to IBQuery1 and easily build a user interface for the application. I had to type in the pathname of the Borland sample database. However, not everyone has the Program Files folder, which depends on the local version of Windows, and the Borland sample data files could be installed elsewhere on the disk. You'll solve these problems in the next example.
Notice that I've embedded the password in the code—a naïve approach to security. Not only can anyone run the program, but someone can extract the password by looking at the hexadecimal code of the executable file. I used this approach so I wouldn't need to keep typing in my password while testing the program, but in a real application you should require your users to do so to ensure the security of their data.
The IbxEmp example includes a query that doesn't allow editing. To activate editing, you need to add an IBUpdateSQL component to the query, even if the query is trivial. Using an IBQuery that hosts the SQL select statement together with an IBUpdateSQL component that hosts the insert, update, and delete SQL statements is a typical approach from BDE applications. The similarities among these components make it easier to port an existing BDE application to this architecture. Here is the code for these components (edited for clarity):
object IBQuery1: TIBQuery Database = IBDatabase1 Transaction = IBTransaction1 SQL.Strings = ( 'SELECT Employee.EMP_NO, Department.DEPARTMENT, Employee.FIRST_NAME, '+ ' Employee.LAST_NAME, Job.JOB_TITLE, Employee.SALARY, Employee.DEPT_NO, '+ ' Employee.JOB_CODE, Employee.JOB_GRADE, Employee.JOB_COUNTRY' 'FROM EMPLOYEE Employee' ' INNER JOIN DEPARTMENT Department' ' ON (Department.DEPT_NO = Employee.DEPT_NO) ' ' INNER JOIN JOB Job' ' ON (Job.JOB_CODE = Employee.JOB_CODE) ' ' AND (Job.JOB_GRADE = Employee.JOB_GRADE) ' ' AND (Job.JOB_COUNTRY = Employee.JOB_COUNTRY) ' 'ORDER BY Department.DEPARTMENT, Employee.LAST_NAME') UpdateObject = IBUpdateSQL1 end object IBUpdateSQL1: TIBUpdateSQL RefreshSQL.Strings = ( 'SELECT Employee.EMP_NO, Employee.FIRST_NAME, Employee.LAST_NAME,'+ 'Department.DEPARTMENT, Job.JOB_TITLE, Employee.SALARY, Employee.DEPT_NO,'+ 'Employee.JOB_CODE, Employee.JOB_GRADE, Employee.JOB_COUNTRY' 'FROM EMPLOYEE Employee' 'INNER JOIN DEPARTMENT Department' 'ON (Department.DEPT_NO = Employee.DEPT_NO)' 'INNER JOIN JOB Job' 'ON (Job.JOB_CODE = Employee.JOB_CODE)' 'AND (Job.JOB_GRADE = Employee.JOB_GRADE)' 'AND (Job.JOB_COUNTRY = Employee.JOB_COUNTRY)' 'WHERE Employee.EMP_NO=:EMP_NO') ModifySQL.Strings = ( 'update EMPLOYEE' 'set' ' FIRST_NAME = :FIRST_NAME,' ' LAST_NAME = :LAST_NAME,' ' SALARY = :SALARY,' ' DEPT_NO = :DEPT_NO,' ' JOB_CODE = :JOB_CODE,' ' JOB_GRADE = :JOB_GRADE,' ' JOB_COUNTRY = :JOB_COUNTRY' 'where' ' EMP_NO = :OLD_EMP_NO') InsertSQL.Strings = ( 'insert into EMPLOYEE' '(FIRST_NAME, LAST_NAME, SALARY, DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY)' 'values' '(:FIRST_NAME,:LAST_NAME,:SALARY,:DEPT_NO,:JOB_CODE,:JOB_GRADE,:JOB_COUNTRY)') DeleteSQL.Strings = ( 'delete from EMPLOYEE ' 'where EMP_NO = :OLD_EMP_NO') end
For new applications, you should consider using the IBDataSet component, which sums up the features of IBQuery and IBUpdateSQL. The differences between using the two components and the single component are minimal. Using IBQuery and IBUpdateSQL is a better approach when you're porting an existing application based on the two equivalent BDE components, even if porting the program directly to the IBDataSet component doesn't require much extra work.
In the IbxUpdSql example, I've provided both alternatives so you can test the differences yourself. Here is the skeleton of the DFM description of the single dataset component:
object IBDataSet1: TIBDataSet Database = IBDatabase1 Transaction = IBTransaction1 DeleteSQL.Strings = ( 'delete from EMPLOYEE' 'where EMP_NO = :OLD_EMP_NO') InsertSQL.Strings = ( 'insert into EMPLOYEE' ' (FIRST_NAME, LAST_NAME, SALARY, DEPT_NO, JOB_CODE, JOB_GRADE, ' + ' JOB_COUNTRY)' 'values' ' (:FIRST_NAME, :LAST_NAME, :SALARY, :DEPT_NO, :JOB_CODE, ' + ' :JOB_GRADE, :JOB_COUNTRY)') SelectSQL.Strings = (...) UpdateRecordTypes = [cusUnmodified, cusModified, cusInserted] ModifySQL.Strings = (...) end
If you connect the IBQuery1 or the IBDataSet1 component to the data source and run the program, you'll see that the behavior is identical. Not only do the components have a similar effect; the available properties and events are also similar.
In the IbxUpdSql program, I've also made the reference to the database a little more flexible. Instead of typing in the database name at design time, I've extracted the Borland shared data folder from the Windows Registry (where Borland saves it while installing Delphi). Here is the code executed when the program starts:
uses Registry; procedure TForm1.FormCreate(Sender: TObject); var Reg: TRegistry; begin Reg := TRegistry.Create; try Reg.RootKey := HKEY_LOCAL_MACHINE; Reg.OpenKey('\Software\Borland\Borland Shared\Data', False); IBDatabase1.DatabaseName := Reg.ReadString('Rootdir') + '\employee.gdb'; finally Reg.Free; end; EmpDS.DataSet.Open; end;
For more information about the Windows Registry and INI files, see the related sidebar in Chapter 8, "The Architecture of Delphi Applications."
Another feature of this example is the presence of a transaction component. As I've said, the InterBase Express components make the use of a transaction component compulsory, explicitly following a requirement of InterBase. Simply adding a couple of buttons to the form to commit or roll back the transaction would be enough, because a transaction starts automatically as you edit any dataset attached to it.
I've also improved the program by adding an ActionList component. This component includes all the standard database actions and adds two custom actions for transaction support: Commit and Rollback. Both actions are enabled when the transaction is active:
procedure TForm1.ActionUpdateTransactions(Sender: TObject); begin acCommit.Enabled := IBTransaction1.InTransaction; acRollback.Enabled := acCommit.Enabled; end;
When executed, they perform the main operation but also need to reopen the dataset in a new transaction (which can also be done by "retaining" the transaction context). CommitRetaining doesn't really reopen a new transaction, but it allows the current transaction to remain open. This way, you can keep using your datasets, which won't be refreshed (so you won't see edits already committed by other users) but will keep showing the data you've modified. Here is the code:
procedure TForm1.acCommitExecute(Sender: TObject); begin IBTransaction1.CommitRetaining; end; procedure TForm1.acRollbackExecute(Sender: TObject); begin IBTransaction1.Rollback; // reopen the dataset in a new transaction IBTransaction1.StartTransaction; EmpDS.DataSet.Open; end;
Be aware that InterBase closes any opened cursors when a transaction ends, which means you have to reopen them and refetch the data even if you haven't made any changes. When committing data, however, you can ask InterBase to retain the transaction context—not to close open datasets—by issuing a CommitRetaining command, as mentioned before. InterBase behaves this way because a transaction corresponds to a snapshot of the data. Once a transaction is finished, you are supposed to read the data again to refetch records that may have been modified by other users. Version 6.0 of InterBase includes a RollbackRetaining command, but I've decided not to use it because in a rollback operation, the program should refresh the dataset data to show the original values on screen, not the updates you've discarded.
The last operation refers to a generic dataset and not a specific one, because I'm going to add a second alternate dataset to the program. The actions are connected to a text-only toolbar, as you can see in Figure 14.14. The program opens the dataset at startup and automatically closes the current transaction on exit, after asking the user what to do, with the following OnClose event handler:
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction); var nCode: Word; begin if IBTransaction1.InTransaction then begin nCode := MessageDlg ('Commit Transaction? (No to rollback)', mtConfirmation, mbYesNoCancel, 0); case nCode of mrYes: IBTransaction1.Commit; mrNo: IBTransaction1.Rollback; mrCancel: Action := caNone; // don't close end; end; end;
Like the dbExpress architecture, IBX also allows you to monitor a connection. You can embed a copy of the IBSQLMonitor component in your application and produce a custom log.
You can even write a more generic monitoring application, as I've done in the IbxMon example. I've placed in its form a monitoring component and a RichEdit control, and written the following handler for the OnSQL event:
procedure TForm1.IBSQLMonitor1SQL(EventText: String); begin if Assigned (RichEdit1) then RichEdit1.Lines.Add (TimeToStr (Now) + ': ' + EventText); end;
The if Assigned test can be useful when receiving a message during shutdown, and it is required when you add this code directly inside the application you are monitoring.
To receive messages from other applications (or from the current application), you have to turn on the IBDatabase component's tracing options. In the IbxUpdSql example (discussed in the preceding section, "Building a Live Query"), I turned them all on:
object IBDatabase1: TIBDatabase ... TraceFlags = [tfQPrepare, tfQExecute, tfQFetch, tfError, tfStmt, tfConnect, tfTransact, tfBlob, tfService, tfMisc]
If you run the two examples at the same time, the output of the IbxMon program will list details about the IbxUpdSql program's interaction with InterBase, as you can see in Figure 14.15.
In addition to letting you monitor the InterBase connection, the IbxMon example allows you to query some server settings using the various tabs on its page control. The example embeds a few IBX administrative components, showing server statistics, server properties, and all connected users. You can see an example of the server properties in Figure 14.16. The code for extracting the users appears in the following code fragment.
// grab the user's data IBSecurityService1.DisplayUsers; // display the name of each user for i := 0 to IBSecurityService1.UserInfoCount - 1 do with IBSecurityService1.UserInfo[i] do RichEdit4.Lines.Add (Format ('User: %s, Full Name: %s, Id: %d', [UserName, FirstName + ' ' + LastName, UserId]));