Friday, September 12, 2008

Kylix 3 for C++ and IBM DB2 UDB v8.1 on Linux

Original Source click here

Level: Intermediate

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

29 Apr 2004

This article shows how to use the C++ edition of Kylix 3 to work with IBM DB2 UDB tables.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


Introduction

In this article, I will use Borland® Kylix™ 3 for C++ on Linux to work with IBM® DB2® Universal Database™ (UDB) tables. Kylix 3 produces native Linux executables, and is available for Delphi™ and C++ as a development language.

In October 2002, I made the big switch, moving a Borland Delphi 7 Enterprise application that worked with DB2 UDB to Kylix 3 (for Delphi). This was described in my article, The Big Switch: Moving from Windows to Linux with Kylix 3. That article demonstrates how easy it is to migrate the CLX projects from Windows to Linux. This time, I'll use the C++ edition of Kylix 3 and start by building a C++ project on Linux right from the start.

Although Linux is mainly seen as a server development tool, it is getting more common on the desktop. With Kylix 3, you can build server as well as desktop applications that work with DB2 UDB data. (A trial edition of Kylix 3 can be downloaded from the Borland Web site.)



Back to top


Specifying the DB2Connection

Start a new Kylix 3 for C++ application. In order to connect to the DB2 Universal Database, you need to use a dbExpress TSQLConnection component and configure the connection settings. Go to the dbExpress tab of the Component Palette, and place a TSQLConnection component on the new form. Right-click on the SQLConnection component to start the dbExpress Connections editor (see Figure 1).


Figure 1. dbExpress Connections Editor
Figure 1. dbExpress Connections Editor

As you can see in the dialog of Figure 1, you can select a Driver and Connection, and then specify the specific connection settings. For the DB2 UDB SAMPLE database, you need to specify SAMPLE as Database name, and the User_Name and Password that can work with this instance (in my case, that's db2inst1, but it may be a different User_Name value on your Linux installation, of course).

Note that you do not have to specify a password here; if the password is not specified or incorrect, the login prompt dialog will be shown (unless you've set that property to false).

You can test the dbExpress Connection Settings by clicking on the button with the checkmark. This will attempt to connect and login to the DB2 UDB SAMPLE database, and show the login prompt dialog when required. Once you can successfully connect, you can close this dialog and continue by creating the SQL command(s) needed.



Back to top


Building the SQL command

Place a TSQLDataSet component on the form, right under the TSQLConnection component. Point the SQLConnection property of the TSQLDataSet component to the TSQLConnection component. A TSQLDataSet can "act" as a Table, Query, or Stored Procedure, based on the value of the CommandType property (ctTable, ctQuery, or ctStoredProc). By default, it's set to ctQuery. Based on the value of CommandType, the CommandText property is interpreted differently: it's either considered to be the name of a table, a SQL command, or the name of a stored procedure. The property editor for the CommandText property acts accordingly, showing a list of available tables, a SQL dialog, or a list of available Stored Procedures.

By default, CommandType is set to ctQuery, and the CommandText property editor will produce a SQL building dialog, listing the available tables and fields to help you build a SQL select statement, as can be seen in Figure 2.


Figure 2. SQLDataSet CommandText Editor
Figure 2. SQLDataSet CommandText Editor

Once you're happy with the SQL Query, you can close this dialog. The TSQLDataSet can be activated, and will result in a read-only, unidirectional dataset. This means that when the query is active, you can walk through the resultset from top to bottom (first record to last record), but you can't go back and you can't make changes to the data in these records. In some cases, that's exactly what you want; for example, in Web server applications, where all you might need to do is produce some HTML based on the results of a query, so there's no need to walk through the resultset more than once (or make any changes).

However, for desktop applications, it would be nice if you could modify the contents, and if some of the visual components (like the TDBGrid) actually moved forward and backwards through a dataset. In this situation, they cannot be connected to a TSQLDataSet directly but this can be done with the help of a little caching dataset: the TClientDataSet.



Back to top


Caching the DataSet

The TClientDataSet component can be seen as an in-memory table that can be used as placeholder for records from other datasets. You can move in all directions (forward, backward) and make all kinds of modifications in the TClientDataSet. You just have to remember that it's only an in-memory dataset - not (directly) connected to the underlying DBMS. But we'll get back to that aspect in a minute, when I show you how to update the DB2 UDB SAMPLE database from a TClientDataSet component.

Before you place a TClientDataSet component on the form, you first need to place another component - the one to connect the TClientDataSet with the unidirectional, read-only TSQLDataSet. That particular component is called the TDataSetProvider. Place it right under the TSQLDataSet, and point its DataSet property to the TSQLDataSet component. Now you can place a TClientDataSet component under the TDataSetProvider, and point the ProviderName property of the TClientDataSet component to the TDataSetProvider. When you "activate" (or Open) the TClientDataSet, it will use the TDataSetProvider to retrieve all records from the TSQLDataSet (which means the SQL command of the TSQLDataSet is executed, and the resultset is examined from top to bottom, passing all records through the TDataSetProvider onto the TClientDataSet).



Back to top


Showing the data

You are almost there. In order to actually work with the records in the TClientDataSet, you can use the data-aware controls in Kylix, found on the Data Controls tab. Data-aware controls can connect to data in a DataSet using a DataSource component as connector (just as the SQLConnection component is the connector between the Kylix application and the DB2 UDB SAMPLE database itself).

Place a TDataSource component on the form, and point its DataSet property to the TClientDataSet. Make sure that you do not accidentally connect it to the TSQLDataSet instead, since that's a read-only, unidirectional dataset which cannot be modified (or used in combination with a TDBGrid or TDBNavigator component).

From the Data Controls tab, you can now use all visual components that you want to, including the TDBNavigator and TDBGrid. For all data-aware controls, you need to make sure that their DataSource property is connected to the TDataSource component. For controls that are connected to single fields (like the TDBEdit), you also need to specify the FieldName. (For multi-field controls like the TDBGrid, that's not necessary.)

When you set the Active property of the TClientDataSet component to True, it will retrieve all records (from the SQL command in the TSQLDataSet) and show them inside the TDBGrid component, as can be seen in Figure 3.


Figure 3. Kylix 3 for C++ Form at design-time
Figure 3. Kylix 3 for C++ Form at design-time

This is live data at design-time, and is very helpful to help you design your forms (so you can determine how wide a TDBGrid or other data-aware controls should be, without the need to compile and run the application).



Back to top


Connecting at run-time

Live data at design-time can be helpful, but it is not recommended to leave the TClientDataSet active at design-time when you compile your project. With an active TClientDataSet, the application will automatically need to make a connection to the DB2 UDB SAMPLE database at start-up of your application. If the connection cannot be made, you'll get an error message. The same thing will happen if you try to open the project source code in Kylix 3; live data at design-time requires an active connection, so you will only be able to open the project on a development machine that can access the DB2 UDB SAMPLE database. That may not be the case at all times, so it's safer to make sure the connection is not active at design-time (and use live data only as a designing feature, but turn it off before you save or compile the project).

In order to explicitly activate the connection to the DB2 UDB SAMPLE database, you can place a TButton on the form, call it btnConnect, and write the following code in the OnClick event handler:

void __fastcall TForm1::btnConnectClick(TObject *Sender)
{
ClientDataSet1->Active = !ClientDataSet1->Active;
if (ClientDataSet1->Active)
{
btnConnect->Caption = "Disconnect";
}
else
{
btnConnect->Caption = "Connect";
SQLConnection1->Connected = false;
}
}

Note that this code will modify the Caption of the TButton from "Connect" to "Disconnect," and it will also explicitly set the Connected property of the TSQLConnection component to false when you disconnect. Assigning false to the Active property of the TClientDataSet does not do that by default - the connection is kept until you explicitly close it.



Back to top


Undoing changes

Now that you can connect to the database to retrieve data and show it in the TDBGrid, it's very tempting to make changes to this data. Making changes is useful, but if your client cannot apply those changes back to the underlying database, it will do them no good. And apart from being able to apply the updates, it's very user-friendly to offer the ability to undo the local changes (before you've applied them to the database, that is). There are even two "undo" options: undo the last change, which will bring you to the record that was just undone, and undo all changes (if you have completely changed your mind about something). These two options are implemented through the UndoLastChange and CancelUpdates methods of the TClientDataSet component.



Back to top


Sending updates

Sending the updates to the database is done with a call to ApplyUpdates. This call will go from the TClientDataSet component to the TDataSetProvider. From there, it will ignore the TSQLDataSet component (which is read-only and unidirectional), and will produce a SQL update, insert, and/or delete command which is sent directly to the DB2 UDB SAMPLE database through the TSQLConnection component.

void __fastcall TForm1::btnUndoClick(TObject *Sender)
{
ClientDataSet1->UndoLastChange(true);
}

void __fastcall TForm1::btnUndoAllClick(TObject *Sender)
{
ClientDataSet1->CancelUpdates();
}

void __fastcall TForm1::btnUpdateClick(TObject *Sender)
{
ClientDataSet1->ApplyUpdates(0);
}

You can pass a value to ApplyUpdates, specifying the number of update errors that you want to allow before you roll back the update and get back to the client with an error. The error is raised and triggered through the OnReconcileError event handler of the TClientDataSet component, which is defined as follows:

void __fastcall TForm1::ClientDataSet1ReconcileError(
TCustomClientDataSet *DataSet, EReconcileError *E,
TUpdateKind UpdateKind, TReconcileAction &Action)
{
// respond to reconcile error
}

Here you can alert the user to the fact that an update error has occurred. In order to help you build a form that provides the necessary information, Borland has already implemented a Reconcile Error dialog (which can be found in the Object Repository; just do File | New - Other, and you can find it in the Dialogs tab of the Object Repository). This dialog is not very user-friendly but it already contains a lot of information, and can be seen at design-time in Figure 4.


Figure 4. Borland's Reconcile Error Dialog
Figure 4. Borland's Reconcile Error Dialog

When you add this dialog to your Kylix application, you can use it by calling the HandleReconcileError function (which is defined by the unit for the Update Error dialog), as follows:

void __fastcall TForm1::ClientDataSet1ReconcileError(
TCustomClientDataSet *DataSet, EReconcileError *E,
TUpdateKind UpdateKind, TReconcileAction &Action)
{
Action = HandleReconcileError(Owner, DataSet, UpdateKind, E);
}

See the online help for more documentation of this dialog, and the reconcile errors and actions that are available for your end users.



Back to top


Enabling buttons

There's one more thing I'd like you to implement before you compile and run the application, and that's the fact that the Undo, Undo All, and Update buttons should only be enabled if there are any changes in the local TClientDataSet. If not, then there is no need to have them enabled, since there's nothing for them to do in the first place.

You can use the ChangeCount property of the TClientDataSet to verify if there are any changes, and I've added a special method called UpdateButtonStatus to the form that will update the Enabled property of the btnUndo, btnUndoAll, and btnUpdate buttons based on the value of TClientDataSet's ChangeCount property, as follows:

void __fastcall TForm1::UpdateButtonStatus(void)
{
btnUndo->Enabled = (ClientDataSet1->ChangeCount > 0);
btnUndoAll->Enabled = btnUndo->Enabled;
btnUpdate->Enabled = btnUndo->Enabled;
}

The UpdateButtonStatus method can be called in the OnClick event handlers for the btnUndo, btnUndoAll, and btnUpdate buttons (to verify if there are changes left), as well as the OnAfterPost and OnAfterDelete event handlers of the TClientDataSet itself, which will notify you of any new changes in the data.

void __fastcall TForm1::ClientDataSet1AfterPostAndDelete(TDataSet *DataSet)
{
UpdateButtonStatus();
}



Back to top


Action!

It's time to save the project and compile the application. When you first start it, it will show an empty TDBGrid, three disabled buttons (Undo, Undo All, and Update) and one Connect button. If you click on the Connect button, then the TClientDataSet is activated, which means the SQL command from the TSQLDataSet is executed, and the connection to the DB2 UDB SAMPLE database is opened as a result. This results in the Database Login dialog being shown, as can be seen in Figure 5. (unless you already supplied the correct User_Name and Password in the connection editor of Figure 1).


Figure 5. Database Login Dialog at run-time
Figure 5. Database Login Dialog at run-time

After a successful login, the data is retrieved and shown in the TDBGrid. You can now edit and make changes in the grid. As soon as you make a change and move to another record, the OnAfterPost is called, which will enable the Undo, Undo All, and Update buttons. This can be seen in Figure 6.


Figure 6. Kylix 3 for C++ Main Form at run-time
Figure 6. Kylix 3 for C++ Main Form at run-time

The changes are still only made to the local TClientDataSet, however, and not to the DB2 UDB SAMPLE database - until you click on the Update button.



Back to top


Disconnecting

A final event handler that I've implemented is the OnClose event from the form, where I explicitly close the connection to the DB2 UDB SAMPLE database by assigning false to the Connection property of the TSQLConnection component.

void __fastcall TForm1::FormClose(TObject *Sender, TCloseAction &Action)
{
SQLConnection1->Connected = false;
}

Obviously, this has no effect if the connection was already closed (but it never hurts to explicitly close it).



Back to top


Summary

In this article, you have seen how to use Borland Kylix 3 for C++ on Linux to work with DB2 UDB tables on Linux. I've shown how to build the connection, construct an SQL command, retrieve the data, make changes, and either undo changes or apply them back to the database.

Although Linux is mainly seen as a server development tool, it is becoming more common on the desktop as well. With Kylix 3, you can build both server and desktop applications that work with DB2 UDB data.

In some follow-up articles, I will keep using Kylix 3 and expose the DB2 UDB database tables on the Web, building a Linux Web server application and even a SOAP Web Service on Linux with clients that can be deployed on Linux, Windows, or anywhere you want - even the Microsoft® .NET Framework.




Back to top


0 Comments: