![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
© 2002 International Business Machines Corporation. All rights reserved.
In this article, I will demonstrate how to use IBM® DB2® Universal DatabaseTM as the backend database for applications written with Borland® DelphiTM 7 and dbExpressTM. Specifically, I will show how to connect the seven dbExpress components to DB2 and use them to build visual forms on top of database tables. If you don't have the ability to connect to a DB2 database, you can download a free trial or the latest beta version of DB2 from the IBM web site at http://www14.software.ibm.com/webapp/download/category.jsp?s=c&cat=data.
I use the DB2 sample database in this article. If you want to work along with me, you need to create that database as well, which you can do using the DB2 "First Steps" utility (a tool that's highly recommended for those of you with little prior DB2 experience).
An Overview of Delphi 7 and dbExpress
Delphi ships with a variety of data access libraries, such as the Borland Database Engine (BDE), dbGo for ADO, and SQL Links. The latter contains capabilities to connect to a DB2 database, but is deprecated by Borland, meaning that support for SQL Links will no longer be available after 2002. Fortunately, Delphi has featured a replacement data access technology since version 6 called dbExpress. Originally meant as the data access layer for KylixTM in Linux®, dbExpress is now a powerful cross-platform data access layer for Delphi, C++BuilderTM, and Kylix (both the Delphi and C++ versions). The dbExpress driver for DB2 included in the Enterprise editions of these tools will be used for this article.
To start, make sure DB2 is running (it should be running by default), and start a new CLX project with File -> New CLX Application. (CLX stands for Component Library for (cross)X-platform. I'm building this as a CLX application because I want to migrate it to Linux in a future article.) Then, save the empty main form in MainForm.pas and the project in DB2D7.dpr.
To connect to the DB2 database, I first need to drop a TSQLConnection component from the dbExpress tab of the Delphi Component Palette. TSQLConnection is the component that uses the dbExpress layer to connect to the DBMS. You can use the DriverName property to select a driver like DB2, InterBase®, MYSQLTM or Oracle®. However, you often have specific connection information (to a specific database) already defined in a ConnectionName, so you can also use that property instead and select the DB2Connection value.
Once you set either the DriverName or ConnectionName property value, other properties will automatically get a value as well, such as the GetDriverFunc, LibraryName, and VendorLib properties:
- LibraryName specifies the dbExpress library that talks to DB2.
- GetDriverFunc is the name of the entry point in that dbExpress library.
- VendorLib specifies the name of the DB2 library that is needed for DB2. (This library is the one provided by IBM, and should already be on your system if you are using DB2).
If you right-click with the mouse on the TSQLConnection component, you can choose more specific connection options in the dbExpress Connections Properties dialog (see Figure 1). Note that I've set the Database to SAMPLE. You can also specify your User_Name and Password here.
Figure 1. dbExpress Connections Properties.
Once everything is set correctly, close the Connections Properties dialog and set the Connected property of the TSQLConnection component to True. This results in a standard Database Login dialog. If you do not want to see that dialog (because you've specified User_Name and Password as Connection Settings already), then you can set the LoginPrompt property of the TSQLConnection component to False.
After a successful login, the application will have a connection to the DB2 SAMPLE database. It's now time to retrieve information from that database using a TSQLTable, TSQLQuery, TSQLStoredProc, or TSQLDataSet component.
Start displaying the DB2 data in your app with TSQLTable
To start with the TSQLTable, drop the TSQLTable component on the form and assign its Connection property to the TSQLConnection component. You can now use the TableName property to select one of the tables from the SAMPLE database (namely CL_SCHED, DEPARTMENT, EMP_ACT, EMP_PHOTO, EMP_RESUME, EMPLOYEE, IN_TRAY, ORG, PROJECT, SALES, and STAFF). Pick the employee table.
Before I continue, there's something you must know about dbExpress, which is different from some of the old data access libraries in Delphi (such as the BDE and SQL Links): A dbExpress dataset is provided as a read-only and unidirectional dataset. This means that the application can open the table and walk through it — from the first record to the last, but the application cannot move backwards (or jump to the last record), and cannot make any changes to it. The behavior of the dataset is like performing an SQL query and having the ability to view the resulting records one at a time but nothing more. dbExpress datasets act this way to maximize performance: The resulting data access is fast and requires little overhead. Additionally, there might be times when you indeed want to walk through your resultset only once (for example, in a reporting or Web server application), so why add overhead if you don't always need it?
When the TSQLTable is activated, the application retrieves the contents of the employee table (as a read-only and unidirectional dataset). In order for your application to walk through the table and make changes to it, the application must put the contents in a local cache — the TClientDataSet. To add this to your application, drop both a TClientDataSet and a TDataSetProvider component from the Data Access tab of the Component Palette. You must connect the TDataSetProvider to the TSQLTable component (using the DataSet property), and the TDataSetProvider will feed the records to the TClientDataSet. For this last step, make sure to point the ProviderName property of the TClientDataSet to the TDataSetProvider.
Now, when the application opens the TClientDataSet, it will send a request for data to the TDataSetProvider. The TDataSetProvider component will then open the TSQLTable component and retrieve the records from the DB2 employee table (through the TSQLConnection to the DB2 database).
To display the contents of the employee table, you need to add two (or three) more components to your application. The first component is the TDataSource component (also from the Data Access tab). The TDataSource component acts as a gateway between the TClientDataSet and any so-called data-aware components in Delphi (all found on the Data Controls tab of the Component Palette). Point the DataSet property of the TDataSource to the TClientDataSet. Next, drop a TDBGrid and TDBNavigator component on the form, and point their DataSource properties to the TDataSource component.
When you finally set the Active property of TClientDataSet to True, you'll get live DB2 data at design-time (see Figure 2).
Figure 2. Delphi 7 talking to DB2 at design-time.
Apply updates to the DB2 table
You can now compile and run the application to show the data at run time. (If you didn't set the LoginPrompt of the TSQLConnection component to False, you may see a login dialog before seeing your data.) With the application running, you may want to make some changes to the data in the grid, which are automatically posted to the underlying dataset (the TClientDataSet in this case) when you move from one record to another in the grid. When you close the application and start it again, however, you'll find that no changes have been applied to the DB2 database; they were only made to the local in-memory TClientDataSet and are now gone. To explicitly apply the updates to the DB2 database table, the application must call the ApplyUpdates method of the TClientDataSet. This can occur automatically when the user closes the application, in the OnClose event handler of the form:
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction); begin if ClientDataSet1.ChangeCount > 0 then ClientDataSet1.ApplyUpdates(0) end; |
Note that the ChangeCount property returns the number of changes currently made to the in-memory TClientDataSet that will be applied to the DB2 database.
Since all changes are kept in memory until the moment of applying the updates, it's usually a good idea to offer an Undo button to the client (in my experience is this is often appreciated). The TClientDataSet supports Undo using the UndoLastChange method, where the only argument specifies if you want your application to follow the change (for example, if you want to position the cursor at the record that was just undone).
To implement this method, drop a TButton component (from the Standard tab) on the form, call it btnUndo, set its Caption to Undo, and add this code to the component's OnClick event handler:
procedure TForm1.btnUndoClick(Sender: TObject); begin ClientDataSet1.UndoLastChange(True) end; |
Manipulating and querying your data
In addition to using a TSQLTable component, you can also use a TSQLStoredProc or TSQLQuery component. The first one can be used to execute a stored procedure, and the second to execute an SQL query.
When it comes to the TSQLQuery component, you again need to point the component's Connection property to the TSQLConnection component, and then write a query in the SQL property. Unfortunately, if you want to edit this property, you only get a string list editor, with no support for building the query (for example, by showing you the available tables and fieldnames).
Where TSQLTable, TSQLStoredProc, and TSQLQuery had one specific purpose, the TSQLDataSet is like a chameleon. It can perform either of these tasks and switch to the desired behavior at run time. The actual behavior is defined through two properties: CommandType and CommandText. You can set the CommandType property to ctQuery, ctStoredProc or ctTable, and based on this choice the CommandText property will show an SQL Query builder, a drop-down list with table names, or a drop-down list with stored procedure names. The SQL Query builder is new, as this is not part of the TSQLQuery component itself. Figure 3 shows an example of building a query on the department table.
Figure 3. SQL CommandText Editor.
Because of the added flexibility (and the design-time SQL CommandText Editor), I prefer to always use a TSQLDataSet and never use the TSQLTable, TSQLQuery, or TSQLStoredProc components.
The final dataset component on the dbExpress tab of the Component Palette is TSimpleDataSet. This one is new in Delphi 7 Studio, and replaces the TSQLClientDataSet component in Delphi 6 and Kylix. You can use the TSimpleDataSet (and previously the TSQLClientDataSet) in situations where you want to create and test a quick-and-dirty connection to a dbExpress database without having to use a TSQLDataSet - TDataSetProvider -TClientDataSet threesome. The TSimpleDataSet contains these three components in one.
To see how this component works, drop a TSimpleDataSet component on the form, and set its Connection property to SQLConnection1. You can now use the embedded DataSet property to specify a table, query, or stored procedure name to use. If you double-click on the DataSet property in the Object Inspector, it will open itself and show its sub-properties, like CommandType and CommandText that you saw earlier in the TSQLDataSet component. Set CommandType to ctTable, and select the employee table again in the CommandText property. Now you can directly connect the TDataSource component to this TSimpleDataSet, because the TDataSetProvider and TClientDataSet are already embedded in the TSimpleDataSet. Convenient, but recommended for simple use only. For serious and real-world applications you should rely on the combination of TSQLDataSet,TdataSetProvider, and TClientDataSet.
Sometimes you want to trace what's happening between the client application and your DB2 database. The TSQLConnection component can send dbExpress trace messages to the TSQLMonitor component so it can watch what's happening between your application and the database.
To set up monitoring, drop a TSQLMonitor component on your form, and point its Connection property to the TSQLConnection component you want to trace. Specify the file name that should contain the log file (like c:\db2.log), and set the AutoSave property to True to make sure the log file is generated in this file when the application closes (or the TSQLMonitor is deactivated). You can trigger the TSQLMonitor component with its Active property because trace messages are only written when Active is set to True. For more flexibility and the ability to filter certain trace categories, you can use the OnTrace and OnLogTrace event handlers.
You can download the code for this article below. In an upcoming articles, I will show you what's involved in moving this application from Delphi to Kylix so that the code can run in a Linux environment with minimal changes. (See Display and Modify DB2 Master-Detail Data in Delphi 7 Studio Apps and The Big Switch: Moving from Windows to Linux with Kylix 3.
| Name | Size | Download method |
|---|---|---|
| source.zip | 6KB | HTTP |
Information about download methods
Bob Swart (aka Dr.Bob - www.drbob42.com) is an author, trainer, consultant and Webmaster working for his own company called Bob Swart Training & Consultancy (eBob42) in Helmond, The Netherlands. Bob, who writes his own Delphi training material, has spoken at Delphi and Borland Developer Conferences since 1993. Bob has written hundreds of articles, and is co-author of the Revolutionary Guide to Delphi 2, Delphi 4 Unleashed, C++Builder 4 Unleashed, C++Builder 5 Developer's Guide, Kylix Developer's Guide, Delphi 6 Developer's Guide, and the upcoming C++Builder 6 Developer's Guide.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
