In this article, I will demonstrate how to use IBM DB2® Universal DatabaseTM as a database for applications written with Borland C++Builder® 6 Enterprise and dbExpressTM. Specifically, I will show how the C++Builder dbExpress components connect to DB2 and how to use them to build visual, data-driven forms on top of database tables.
Using DB2 Personal Edition v8.1
If you don't have the ability to connect to a DB2 database, you can request a CD from IBM, or 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 (for this article, I'm using the 90-day trial version of the IBM DB2 Universal Database, Version 8.1 for Windows® NT® or 2000, downloaded from this URL).
After installation, DB2 created the DB2 UDB SAMPLE database for me, which I'll use in this article. If you want to work along with me, you need to create that database using the DB2 "First Steps" utility, a tool that I highly recommend for those of you with little DB2 experience.
Working with C++Builder 6 and dbExpress
C++Builder includes a number of data access libraries, such as the Borland Database Engine (BDE), dbGo for ADO, and SQL Links. The latter features capabilities to connect to a DB2 database, but support for SQL Links is no longer available. Fortunately, C++Builder offers a replacement data access technology called dbExpress-a powerful cross-platform data access layer for C++Builder, Borland DelphiTM, and Borland KylixTM (on Linux). The Enterprise editions of these tools contain a dbExpress driver for DB2, which I'll use for this article.
Connect to the DB2 database
To get started, make sure DB2 is running, and start a new CLX project with File -> New CLX Application; CLX is the Borland Component Library for cross-platform development. By using the CLX technologies, you can later recompile the application on Linux® (for Intel platforms) with Kylix-a RAD IDE for Linux based on ANSI/ISO C++ and Delphi programming languages.
Save the empty main form in MainForm.cpp and the project in DB2BCB6.bpr. Now, to connect to the DB2 database, you first need to drop a TSQLConnection component from the dbExpress tab of the C++Builder component palette. TSQLConnection is the component responsible for using the dbExpress layer to connect to the DB2 DBMS through the DB2 client.
You can use the DriverName property to select a driver such as DB2, InterBase, MYSQL, 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.
The LibraryName is set to dbexpdb2.dll and specifies the dbExpress library that you'll use to talk to DB2 (which ships with C++Builder), and GetDriverFunc is the name of the entry point in that library. The VendorLib is set to db2cli.dll and specifies the actual name of the DB2 library needed for DB2 (this is the one provided by IBM if you install the DB2 client, and should be on your client machines if they want to connect to and use DB2 in the first place).
If you right-click with the mouse on the TSQLConnection component, you can specify more specific connection options in the dbExpress Connections Properties dialog (see Figure 1). Note that I've set the Database to SAMPLE, and also note that you can specify your User_Name and Password here already.
Figure 1. dbExpress connection properties
If everything is set correctly, you can close the Connections Properties dialog and set the Connected property of the TSQLConnection component to true. This will result in a Database Login dialog (see Figure 2). If you do not want to see this dialog (because you've specified User_Name and Password as Connection Properties already), then you can set the LoginPrompt property of the TSQLConnection component to false.
Figure 2. Database Login Dialog.
After a successful login, you'll be connected to the DB2 SAMPLE database. It's now time to retrieve information from that database using a TSQLTable, TSQLQuery, TSQLStoredProc, or TSQLDataSet component.
Retrieve information from the database
To start with the TSQLTable, drop this 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 11 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 you continue, note this fact about dbExpress that differs from some of the old data access libraries in C++Builder (such as the BDE and SQL Links): a dbExpress dataset is provided as a read-only and unidirectional dataset. This means that you can open the table and walk through it from the first record to the last, but you cannot move backwards or jump to the last record, and you cannot make any changes to the table.
In fact, this is like performing a SQL query and having the ability to view the resulting records one at a time but nothing more. The main reason that dbExpress datasets act this way is speed. The resulting data access is fast and requires little overhead. Besides, you might face circumstances where you want only to walk through your resultset just once (such as in a reporting or Web server application), so why add overhead if you don't always need it?
When you activate the TSQLTable, you get the contents of the employee table (as a read-only and unidirectional dataset). To walk through it and make changes to it, you must put the contents in a local cache-the TClientDataSet. To do that, 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 for the TDataSetProvider to feed the records to the TClientDataSet. For this last step, you must point the ProviderName property of the TClientDataSet to the TDataSetProvider.
Now, when you open the TClientDataSet, it will send a request for data to the TDataSetProvider, which will 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 two or three more components, the first of which is a TDataSource component (also from the Data Access tab). This component acts as a gateway between the TClientDataSet and any so-called data-aware components in C++Builder (all found on the Data Controls tab of the component palette). Point the DataSet property of the TDataSource to the TClientDataSet. Now drop a TDBGrid component and a TDBNavigator component on the form, and point their DataSource properties to the TDataSource component.
When you finally set the Active property of the TClientDataSet to true, you'll get live DB2 data at design time (see Figure 3).
Figure 3. C++Builder talking to DB2 at design time
Apply updates back to the database
You can now compile and run the application to show the data at runtime (which might start with the login dialog if you didn't set the LoginPrompt property of the TSQLConnection component to false). With the application running, you might 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.
However, when you close the application and start it again, you'll find that no changes have been applied to the DB2 database: they were made only to the local in-memory TClientDataSet and are now gone again. To apply the updates back to the DB2 database table explicitly, you must call the ApplyUpdates method of the TClientDataSet. You can make this happen automatically when the user closes the application, in the OnClose event handler of the form:
void __fastcall TForm1::FormClose(TObject *Sender, TCloseAction &Action)
{
if (ClientDataSet1->ChangeCount) ClientDataSet1->ApplyUpdates(0);
}
|
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.
Provide users with Undo capabilities
All changes are kept in memory until the moment of applying the updates, so it's usually a good idea to offer the client an Undo button. The TClientDataSet supports this using the UndoLastChange method, where the only argument specifies whether you want to follow the change (that is, if you want to position the cursor at the record that was just undone).
To implement this, go to the Standard tab, drop a TButton component on the form, call it btnUndo, set its Caption to "Undo Last Change", and write this code in its OnClick event handler:
void __fastcall TForm1::btnUndoClick(TObject *Sender)
{
ClientDataSet1->UndoLastChange(true);
}
|
I will leave it as an exercise for the reader to enable the button only if the ChangeCount property of the TClientDataSet is bigger than zero-so only in the case there are any local changes that can be undone.
Execute stored procedures and SQL queries
Apart from using a TSQLTable component, you can also use a TSQLStoredProc or TSQLQuery component. You can use the first one to execute a stored procedure, and the second to execute a SQL query. Unfortunately, the DB2 SAMPLE database does not contain any stored procedures, so you have to take my word for it.
When it comes to the TSQLQuery component, you again need to point its Connection property to the TSQLConnection component, and then write a query in the SQL property. Unfortunately, if you want to edit this property, you get only a string list editor with no support for building the query (by, for example, showing you the available tables and fieldnames). This is one of the reasons why I never use this component, but use the TSQLDataSet instead.
Use TSQLDataSet for combined flexibility
Where TSQLTable, TSQLStoredProc, and TSQLQuery each have one specific purpose, the TSQLDataSet is like a chameleon: it can perform either of these tasks and switch at runtime. You define the behavior through two properties: CommandType and CommandText. You can set the CommandType property to ctQuery, ctTable, or ctStoredProc, and based on this choice the CommandText property will show a SQL Query builder, drop-down list with table names, or drop-down list with stored procedure names. The SQL Query builder is new; it's not part of the TSQLQuery component itself.
Figure 4. SQL CommandText editor
Because of the added flexibility and the design-time SQL CommandText Editor (see Figure 4), I always use a TSQLDataSet and never use the TSQLTable, TSQLQuery, or TSQLStoredProc component. But that's just my personal preference.
Test a quick-and-dirty database connection
The dbExpress tab of the component palette contains one more dataset component: the TSQLClientDataSet component. You can use the TSQLClientDataSet in situations where you want to make and test a quick-and-dirty connection to a dbExpress database without having to use a TSQLDataSet-TDataSetProvider-TClientDataSet threesome. The TSQLClientDataSet contains these three components in one.
To see it in action, drop a TSQLClientDataSet 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. Double-clicking on the DataSet property in the Object Inspector opens it and shows its sub-properties, such as the CommandType and CommandText properties that you saw earlier in the TSQLDataSet component.
Set the CommandType to ctTable, and select the employee table again in the CommandText property. Now you can directly connect the TDataSource component to this TSQLClientDataSet, because the TDataSetProvider and TClientDataSet are already embedded in the TSQLClientDataSet. This is convenient, but recommended for simple use only. For serious and real-world applications, you should rely on the combination of the TSQLDataSet-TDataSetProvider and TClientDataSet.
Monitor the database connection
Sometimes you want to trace what's happening between the client application and your DB2 database. The TSQLConnection component offers the ability to send dbExpress trace messages to the TSQLMonitor component for this purpose. Drop a TSQLMonitor component, and point its Connection property to the TSQLConnection component you want to trace. Specify the Filename 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 when the TSQLMonitor is deactivated). You can trigger the TSQLMonitor with its Active property: 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.
If you compile your project, you might notice a small executable. However, this executable uses the C++Builder dynamic RTL and runtime packages, which means that you must also deploy this RTL DLL as well as the packages. Personally, I prefer a more standalone solution, which results in a bigger executable but less deployment hassle.
Before you deploy, you should go to the Project Options' Compiler tab (see Figure 5), and click on the Release button. This will set all compiler options for a release target.
Figure 5. Project Options - Compiler page
Next, go to the Linker tab of the same dialog (see Figure 6), and uncheck the "Use dynamic RTL" option.
Figure 6. Project Options - Linker page
Finally, go to the Packages page (see Figure 7) and uncheck the "Build with runtime packages" option.
Figure 7. Project Options - Packages Page.
After these changes, if you build the DB2BDB6 application again, the resulting executable will be about 1 megabyte in size, but at least you won't have to deploy the dynamic RTL or runtime packages.
You're not quite free from deployment issues, however, because you need to ensure that the DB2 client is installed on the client machines, and you also need to deploy the dbExpress driver file dbexpdb2.dll (which resides in the CBuilder\bin directory) as well as the MIDAS.dll (which contains the functionality of the DataSetProvider and ClientDataSet components).
I hope to have shown how you can use the C++Builder dbExpress components to connect to and work with tables from the DB2 SAMPLE database. You can check out the articles I wrote about using DB2 with Delphi under Resources; it shouldn't be hard to translate the source code from Delphi to C++. From now on, I will show both Delphi and C++ source code in my articles about using IBM DB2 with Borland RAD Tools.
| Name | Size | Download method |
|---|---|---|
| source.zip | 5 KB | FTP |
Information about download methods
- Create a Reusable Component to Connect Delphi 7 to DB2 with dbExpress
- Display and Modify DB2 Master-Detail Data in Delphi 7 Studio Apps
- The Big Switch: Moving from Windows to Linux with Kylix 3
- Build DB2 and Delphi 7 Internet Applications with WebSnap
- DB2 and Delphi 7 - SOAP and Database Web Services
- Create Distributed DB2 and Delphi Web Apps with DataSnap
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.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
