This article shows you how to build database applications using Borland C++Builder and IBM DB2 Universal Database.

Bob Swart (drbob@chello.nl), Trainer and Consultant, Bob Swart Training and Consultancy

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.



27 February 2003

Introduction

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
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
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
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
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.


Deploying the application

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
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
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
P. 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).


Conclusion

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.


Download

DescriptionNameSize
Code samplesource.zip5 KB

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=13719
ArticleTitle=DB2 and C++ - True RAD With C++Builder
publish-date=02272003