Friday, September 12, 2008

Migrating from SQL Links to dbExpress using IBM DB2

Original Source click here

Level: Intermediate

Bob Swart (, Consultant, Bob Swart Training and Consultancy

11 Dec 2003

Bob Swart demonstrates two Borland data-access technologies for connecting to IBM DB2 tables. SQL Links and dbExpress can be used by both Delphi and C++ Builder developers.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


In this article, I will demonstrate two different data access technologies from Borland® that are available to DelphiTM (and C++BuilderTM) developers when they want to connect to IBM® DB2® Universal DatabaseTM (UDB) tables, and when they want to work with the data inside those DB2 tables.

One of these technologies--the Borland Database Engine's (BDE) SQL Links--is Win32-specific; the other--called dbExpress--is a cross-platform technology that works in Windows and Linux as well as the Microsoft .NET Framework (as we'll see in detail in a future article).

Back to top

Using DB2 UDB Version 8.1

For this article, I'm using IBM DB2 UDB version 8.1, which you can download from the IBM Web site. Note that Borland and IBM have signed an agreement that results in trial versions of the Borland development environments to be included with IBM DB2, and the IBM DB2 UDB version 8.1 CD-ROM to be included with Borland development tools, like Borland C#BuilderTM.

After installation, you should use the First Steps utility that can generate a DB2 UDB SAMPLE database with the tables that I want to use in this article.

Back to top

Connecting to DB2 using BDE SQL Links

When Delphi first shipped in 1995, it contained the BDE as the (only) data access framework. Developers could use the BDE to connect to local Paradox or dBASE tables, or the BDE's SQL Links drivers to connect to DB2 and other DBMSs (you needed the Client/Server edition of Delphi to get the SQL Links drivers). For a developer, the BDE and SQL Links are available through a set of components that you can use to open a database, and talk to tables, perform queries, execute stored procedures, apply SQL update statements, etc.

As an example, let's build a Delphi application using SQL Links to work with the IBM DB2 SAMPLE database. Start Delphi, do File | New - Application and save it somewhere on your disk. Now you need to create a Data Module, which is a non-visual "container" for data access components, and the recommended way to separate your database access from your user interface (we completely ignore the GUI this time, and focus on the data access technologies only). So, do File | New - Data Module and save the data module next to your project.

Even in Delphi 7, you still have the BDE tab on the Component Palette, with the TTable, TQuery, TStoredProc, TDatabase, TSession, TBatchMove, TUpdateSQL and TNestedTable components (the last one is a recent one that we won't use).

Start by dropping a TDatabase component on the Data Module. This is the component that talks to your DB2 UDB. You can connect it to a database by using the AliasName property.

However, before you can do that, you must make sure an alias actually exists. Assuming one doesn't exist, you need to use the BDE Administrator (which can be found in the Control Panel of your computer) to create a BDE Alias that contains all the information for the DB2 UDB SAMPLE database. Inside the BDE Administrator, move to the Databases tab, right-click on the Databases node and select New. Choose the type DB2, and then fill in the Definition page. Specify the name of the database (SAMPLE, in our case) as DB2 DSN, and as username your DB2 username (in my case that's administrator). Then, save these changes in the alias name DB2SAMPLE. See Figure 1 for details:

Figure 1. BDE Administrator
Figure 1. BDE Administrator

Now you can return to Delphi and the TDatabase component on the Data Module, and set the AliasName to DB2SAMPLE. Right-click on the TDatabase component to start the Database Editor, where you can set some parameter values. Specifically, you may want to specify the USER NAME and PASSWORD parameters here (unless you want your clients to fill them in using the login dialog). See Figure 2 for details.

Figure 2. Database Editor
Figure 2. Database Editor

Since, obviously, I don't want my clients to see the Login dialog, I must also set the LoginPrompt property of the TDatabase component to False. You can now test the connection by setting the Connected property to True. If you can't connect, then make sure DB2 is running and you've set all your BDE SQL Links properties correctly.

Back to top

Working with DB2 using BDE SQL Links

Once the connection is made, you can work with the DB2 SAMPLE database. First, you need to drop a TSession component and set the AutoSession name to True. This component is required if more than one session with the database is used (and although we won't get into that at this time, I've found it good practice over the years to just include this component and set the AutoSession name to True).

Now it's time to actually work with the database tables. You have two components (from the BDE tab of the Component Palette) to do that: TTable and the TQuery. With the TTable component you can point to a TableName inside the SAMPLE database, and work with it as if it was available locally (that is, you can browse from top to bottom, read and write records, insert new records, etc.). While this may be nice, it may also be overkill or result in a dataset that's just too big to manage in your form. The alternative is to use the TQuery component, which can be used to execute an SQL statement that returns a dataset. You can use the TQuery to join tables, or restrict the records you see in tables with the WHERE clause. For SQL statements that do not return a resultset (like an UPDATE statement), you should use the TUpdateSQL component.

And if you want to call a stored procedure in your Delphi application, you can use the TStoredProc component. For all of these, you first need to point their DatabaseName property to the name of the TDatabase component (which is SAMPLE in our case).

As an example, drop a TTable component on the Data Module, set the DatabaseName property to SAMPLE, and point the TableName property to EMPLOYEE. Note that the cursor changes to a SQL Hourglass when you open up the drop-down combobox at design-time, since Delphi dynamically shows you all available tablenames in the DB2 UDB SAMPLE database. You can set the Active property of the TTable component to True in order to open the table. All you need now is a TDataSource component to point to the TTable, so your data-aware controls (from the Data Controls tab of the Delphi Component Palette) can point to it.

As an alternative, drop a TQuery component, and set the DatabaseName to SAMPLE again. This time, right-click on the TQuery to start the SQL Builder dialog. This is a rather powerful utility that you can use to visually build queries with joins, groupings, and other features. Start by selecting some tables, like EMPLOYEE, EMP_RESUME and EMP_PHOTO, and specify which fields you want to see in the resultset. Then, you can use the Joins tab to define relations between the tables (like the Employee.EMPNO with the Emp_phot.EMPNO fields). Grouping and Sorting is also possible; see Figure 3 for details.

Figure 3. SQL Builder
Figure 3. SQL Builder

The resulting SQL statement in Figure 3 is placed in the SQL property of the TQuery component. In our case, the SQL statement is as follows:

SELECT Employee.EMPNO, Employee.FIRSTNME, Employee.MIDINIT, Employee.LASTNAME,
Employee.PHONENO, Emp_resume.EMPNO, Emp_resume.RESUME, Emp_phot.PICTURE,
FROM EMPLOYEE Employee, EMP_RESUME Emp_resume, EMP_PHOTO Emp_phot
WHERE (Employee.EMPNO = Emp_resume.EMPNO)
AND (Employee.EMPNO = Emp_phot.EMPNO)

The data module should now look roughly as shown in Figure 4. (There's a deliberate space between the TTable and TQuery on the left-hand side, and the two TDataSources on the right hand side. We'll get to that in a moment.)

Figure 4. Delphi Data Module using BDE SQL Links
Figure 4. Delphi Data Module using BDE SQL Links

You can now add the Data Module to the uses clause of your visual Forms, and use the data-aware controls to design the visual part of your application. So far, so good.

The good news is that this BDE SQL Links technology has been part of Delphi since version 1, and is still available in Delphi 7 today (where I just used it to connect to DB2 UDB v8.1). The only downside is that the BDE is a Windows-only solution, running on Win16 for Delphi 1, and Win32 for Delphi 2 through 7, but not compatible with Kylix on Linux, for example. Additionally, Borland has announced that the SQL Links data access technology of the BDE will be deprecated (see,1410,28688,00.html). This means that it will not be available in future versions of Delphi (including the forthcoming Delphi 8 for the Microsoft .NET Framework). For future data access, you should use alternatives such as dbExpress, which was first introduced with Delphi 6.

Back to top

Migrating SQL Links to dbExpress

In my very first article for this portal, I wrote about dbExpress, showing how to use the dbExpress components to connect to IBM DB2 UDB v7.1 tables. But what I did not show you was how to migrate existing BDE SQL Links applications to dbExpress. A lot of the dbExpress components map directory onto their BDE counterparts, but other migration steps are less trivial, so let's migrate the Data Module from our example application from using the BDE SQL Links to dbExpress.

Migrating the connection

The BDE TDatabase component can be replaced by the dbExpress TSQLConnection component. Like the TDatabase, the TSQLConnection component is responsible for the connection to the DB2 UDB SAMPLE database. But this time, the connection information does not have to be maintained in a BDE Alias, but can be stored completely inside the client application. If you right-click on the TSQLConnection component, you get the Connections Editor, where you can select the driver or directly pick the connection to work with - like the DB2Connection, in this case. You can now specify all Connection Settings here, including the Database (SAMPLE), User_Name, and Password.

Figure 5. dbExpress Connections Editor
Figure 5. dbExpress Connections Editor

The BDE TSession component is only needed for BDE applications, and there is no dbExpress counterpart, so simply remove that component from your Data Modules.

Migrating the DataSets

The BDE TTable, Tquery, and TStoredProc components have three almost identical-looking counterparts in the TSQLTable, TSQLQuery, and TSQLStoredProc components. The difference, however, is that the resulting datasets (from the TSQLTable and TSQLQuery) are now unidirectional and read-only. Unlike the BDE, where a cursor was maintained in the database table or query resultset itself, the dbExpress components can only provide a resultset that you can inspect from first to last record, but that's all - no changes allowed, and no backwards-moving in the resultset.

This obviously means that replacing TTable, Tquery, and TStoredProc by TSQLTable, TSQLQuery, and TSQLStoredProc is not all that's needed. And indeed, as I also showed in the first article about Delphi and dbExpress, you need to insert two components between each TSQLTable or TSQLQuery and the TDataSource component (this explains the "white space" I left in the Data Module between the TTable/TQuery and TDataSource components).

You need a TDataSetProvider component to pick up the read-only, unidirectorional resultset from the TSQLTable or TSQLQuery, and place it inside a TClientDataSet component (which acts as an in-memory dataset). The TClientDataSet component in turn can be connected to the TDataSource components that were used.

So, one TTable component is replaced by a TSQLTable, TdataSetProvider, and TClientDataSet. The TSQLTable needs to point its Connection property to the SQLConnection component, and then you can use the TableName property as you did before. For the TSQLQuery component, you also need to assign the Connecting property, and then you can copy the value of the SQL property from the original TQuery component and place it in the SQL property of the TSQLQuery component. Although the properties can be mapped from one component to another, the additional TDataSetProviders and TClientDataSets result in a migrated Data Module (see Figure 6) that contains a lot more components when using dbExpress components, compared to the original version using the BDE SQL Links components.

Figure 6. Delphi Data Module for dbExpress
Figure 6. Delphi Data Module for dbExpress

Applying updates to DB2 using dbExpress

The TClientDataSet component acts as a local buffer with the records retrieved from the IBM DB2 SAMPLE database. This results in a fast way to browse through the records and display the results. However, it's still a read-only way, since any changes that the end user makes to the records and field values inside the TClientDataSet component are only made inside that very TClientDataSet component, and not in the DB2 SAMPLE database table itself. In contrast, the BDE's SQL Links approach works directly on the DB2 SAMPLE database table, and any changes that the end user makes in the SQL Links application are immediately reflected in the DB2 SAMPLE database table.

To get the same behavior with the dbExpress components, you must write one line of code in the OnAfterPost and OnAfterDelete event handlers of the TClientDataSet components (both event handlers of all TClientDataSet components can point to the same routine), namely:

procedure TDataModule2.ClientDataSet1AfterPostOrDelete(DataSet: TDataSet);
with (DataSet as TClientDataSet) do ApplyUpdates(0)

Please see the first dbExpress article that I wrote for more information about dbExpress (including a way to call the ApplyUpdates method at a later time, and the TSQLDataSet component that can be used if building new dbExpress applications instead of migrating from existing BDE SQL Links applications).

Back to top

Epilogue: Migration to .NET

In my third article for this Web portal (The Big Switch: Moving from Windows to Linux with Kylix 3), I showed how you can migrate existing dbExpress applications from Win32 to Linux. This time, I want to end by showing you what it takes to migrate the dbExpress application from Win32 to a native .NET application.

It basically consists of two steps:

  1. Open your project in Delphi for .NET
  2. Press F9 to compile

Note that this only works if you've used dbExpress to DB2, not if you've used the SQL Links part of the Borland Database Engine (although local BDE is also available in .NET).

Back to top


In this article, I have shown how you can migrate existing BDE SQL Links applications that work with IBM DB2 UDB Database tables to use dbExpress, which is compatible with Delphi on Windows (as well as Kylix on Linux), making the combination of Delphi with IBM DB2 a powerful cross-platform solution. This is not only useful for migrating existing SQL Links applications that use DB2 UDB database tables, but also for existing SQL Links application that use different databases and have to be migrated to dbExpress as well (to open up the path for Linux or .NET using Delphi in combination with IBM's DB2 UDB).

Next Time

In my next article, I will complete the move to the .NET Framework, by using Delphi 8 for the Microsoft .NET Framework to migrate (recompile) the dbExpress application that connects to IBM DB2 UDB, as well as use the new Borland Data Providers for ADO.NET to work with IBM DB2 UDB tables natively under the .NET Framework.