dbExpress Components
dbExpress Components
If you start C++Builder 6 and take a look at the Component Palette, you'll notice a tab called dbExpress (see Figure 12.1). Actually, most of the components are part of DataCLX and wrap the dbExpress functionality.
Figure 12.1. dbExpress Components in C++Builder 6.
The dbExpress tab contains seven components: TSQLConnection , TSQLDataSet , TSQLQuery , TSQLStoredProcedure , TSQLTable , TSQLMonitor , and TSQLClientDataSet . Now, we'll examine these components in more detail and build a dbExpress example application along the way. Note that the actual steps are repeated at the end of this chapter, when I build a Borland Database Engine (BDE) application and a similar dbExpress application, both connecting to an InterBase database. I'll also discuss how to migrate from the BDE to dbExpress.
TSQLConnection
The TSQLConnection component is literally the connection between the dbExpress drivers and the other DataCLX components. If you drop this component on a C++Builder form or data module, you'll see only 12 properties. The DriverName property shows you the dbExpress drivers that are available in your installation of C++Builder. For instance, on my C++Builder 6 Enterprise machine the DriverName property can be DB2, InterBase, MYSQL, or Oracle. However, instead of using the DriverName property to set up a dbExpress connection, we should look at the ConnectionName property, which points to a predefined connection. Entries for the DriverName property can be found in the dbxDrivers.ini file, and entries for the ConnectionName property can be found in the dbxConnections.ini file (both files are located in the C:\Program Files\Common Files\Borland Shared\dbExpress directory and can be edited manually as well as automatically as we'll see in a moment).
You can open the Params string list editor to edit the values of the parameters. These are also automatically filled in when you select a value for the ConnectionName property. If you do not want this to happen, like when writing some nonvisual code to access databases where you want to provide your own parameter values, you can set the LoadParamsOnConnection property to false.
If you right-click the TSQLConnection component and select the Edit Connection Properties pop-up menu option, you'll see the Connection Settings for the different Connection Names (there might be fewer connection names on your machine, depending on the number of dbExpress drivers installed and connections specified in the dbxConnections.ini file).
Note the Database property, which is set to database.gdb by default, should be set to a real database instead. It is recommended to prefix the location of the actual database with the machine name (or IP address) where the database is located. In my case, that's voyager:d:\data\employee.gdb (which can be seen in Figure 12.2), but it could also be localhost:C:\Program Files\Common Files\Borland Shared\Data\Employee.gdb for the default installation of the InterBase example (and the source code on CD-ROM). Any changes that you make in the Connection Properties editor are saved to the dbxConnections.ini file again, so you only have to make these changes once.
Figure 12.2. dbExpress Connections.
When you have specified all required parameters, and have made sure InterBase is actually running, you can set the Connected property to true. This will show the Login Prompt for which you must enter sysdba as user and masterkey as (not so secret) password. To avoid seeing this login prompt, you must set the LoginPrompt property to false, but must make sure to enter the User_Name and Password parameter values in the Connection Properties dialog box.
CAUTION
If you get an error message about an"unavailable database," you must check to make sure InterBase is indeed running and that the database that you point to (in the Connection Properties) actually exists.
TSQLDataSet
When you have a connected TSQLConnection component, you can use a number of the other DataCLX components, such as the TSQLTable , TSQLQuery , TSQLStored Procedure , or TSQLDataSet (which is the most flexible of these four components). Because a TSQLDataSet can actually mimic the behavior of the TSQLTable , TSQLQuery , and TSQLStoredProcedure , I always use a TSQLDataSet component and make sure it behaves as required. In fact, the TSQLQuery , TSQLStoredProc , and TSQLTable components can be seen as specialized versions of the TSQLDataSet component.
When using TSQLDataSet , you should start by specifying the SQLConnection property of this component to (one of) the available TSQLConnection component(s) on your form or data module. The two most important properties of the TSQLDataSet component are the CommandType and CommandText properties. With these two properties you can determine the specific type and behavior of the component. If you set the value of the CommandType property to ctQuery , the CommandText property is interpreted as SQL query (and the component behaves like a TSQLQuery component). If on the other hand you set the CommandType to ctStoredProc , the CommandText specifies the name of the stored procedure (and the component behaves like a TSQLStoredProcedure component). And finally, if you set CommandType to ctTable , CommandText contains the name of the individual tables (and the component behaves like a TSQLTable component).
I just told you that the TSQLQuery , TSQLTable , and TSQLStoredProducedure are hardly necessary in everyday use (the TSQLDataSet component is flexible enough). But the purpose of these components might be to offer easier migration from existing BDE code to dbExpress (where you can replace a regular BDE TTable component with a new dbExpress TSQLTable component, for example).
In this case, using the general TSQLDataSet component, we can set the CommandType property to ctTable , and the CommandText property to CUSTOMER to select the customer table from the InterBase database. If you set the Active property to true , you get live data at design time. Note, however, that there's no way to see the data just yet (we have only used nonvisual components so far), so in the next section we'll add some data-aware controls and discover how this new dbExpress dataset behaves.
Data-Aware Controls
We can now move to the Data Controls tab of the component palette, and start using some of the data-aware controls to display the data we receive from the active TSQLDataSet component. I should warn you beforehand that we cannot use these components immediately. In fact, this is the biggest difference between the BDE and the dbExpress architecture. TSQLDataSet (and the related TSQLQuery , TSQLStoredProc , and TSQLTable components) returns a unidirectional cursor; meaning that you can move forward, but not backward. This can be a problem if you try to connect a TSQLDataSet component to data-aware components. For example, if you drop a TDataSource and TDBGrid component next to the TSQLDataSet , connect the DataSet property of the TDataSource to the TSQLDataSet component, and try to connect the DataSource property of the TDBGrid to the TDataSource component, you will see exactly what I mean. At that time, you will get an error message ( assuming the TSQLDataSet component is still active ” otherwise you'll see the error message when you set the Active property of the TSQLDataSet component to true ). The message indicates that an Operation is not allowed on a unidirectional dataset ”see Figure 12.3 for the exact dialog.
Figure 12.3. Unidirectional error message.
Apparently, the TDBGrid component performs just like an operation (caused by showing more than one record at the same time, expecting the DataSet to buffer these records for it to walk through them). Note that you can get exactly the same error message when using a TDBNavigator component and accidentally clicking the Back or First button.
In short: It's almost unworkable to connect data-aware controls directly to a TSQLDataSet component because of the unidirectional nature of this dataset.
Why Unidirectional
So, why do we get (or need) a unidirectional cursor in the first place? Well, the obvious advantage is speed. The Borland Database Engine (BDE) has never been our best friend (let's call it a friendly neighbor), but it has helped with the small and simple database needs with the dBASE and Paradox local table formats, and the InterBase connectivity. Unfortunately, the BDE footprint and overhead hasn't been small, and BDE tables have never been known for amazing speed. That's an area where Borland wanted to show some real improvements. The new cross-platform data access architecture called dbExpress is designed with this in mind. Hence, it provides unidirectional cursors as results, with no overhead for buffering data or managing metadata. This, combined with the fact that Borland has announced that development and support of the BDE has been frozen and SQL Links will even be deprecated (in future versions of C++Builder), leads to the conclusion that dbExpress will play an important role for data access with C++Builder.
A unidirectional cursor is especially useful when you really only need to see the results once or need to walk through your resultset from start to finish (again once). For example in a while -loop, processing the results of a query or stored procedure, and converting the contents of the records on-the-fly . Real-world situations where this is useful include reporting and Web server applications that produce dynamic Web pages as output.
But, especially when combined with visual data-aware controls, we realize that the user will want to go back one record, which is not supported directly. So, you need to somehow cache the provided records to be able to show them in a DBGrid and to browse back as well as forward. That's where the TClientDataSet component comes in, which you might remember from the MIDAS chapter of the C++Builder 5 Developer's Guide . Chapter 20, "Distributed Applications with DataSnap," of this book addresses this issue as well. Using dbExpress, we can use a TDataSetProvider component (from the Data Access tab of the C++Builder 6 component palette) to hook up with the TSQLDataSet component, and then use a TClientDataSet component to obtain the records from this TDataSetProvider . The result is a local ClientDataSet that gets its records (once) from a unidirectional source: the SQLDataSet . The DataSetProvider is only used as a local transportation means, whereas the ClientDataSet is used to cache the dataset locally (and feed it to TDBGrid and TDBNavigator components without problems), see Figure 12.4 for an example.
Figure 12.4. TSQLDataSet “ ”TDataSetProvider “ ”TClientDataSet .
This combination of the three TSQLDataSet , TDataSetProvider , and TClientDataSet components works very well, and in fact, ended up as a single TSQLClientDataSet component in its own right. The only downside of using the integrated TSQLClient DataSet component is that you can no longer access some of the (now hidden) properties of the internal TDataSetProvider and TSQLDataSet components.
TSQLClientDataSet
The TSQLClientDataSet component combines the speed and lightweight nature of the new dbExpress architecture with the caching and speed capabilities of the TClient DataSet component. There is another reason we have to use the TSQLClientDataSet (or the TClientDataSet component) at certain times, namely when it comes to updates. The unidirectional TSQLDataSet (and derived components) have an additional limitation in that they cannot be used to update the data in the dataset. For that, you have to use a TClientDataSet or TSQLClientDataSet component and call the ApplyUpdates method.
As a regular ClientDataSet , all changes that are made locally are cached inside the component. All changes are sent back and resolved to the actual database (via the dbExpress driver) only by calling the ApplyUpdates method. The ApplyUpdates call will use the DataSetProvider to send the so-called Delta dataset packet to the database server, something a lone TSQLDataSet component can't do, but a set of connected TSQLDataSet - TDataSetProvider - TClientDataSet can call ApplyUpdates just fine, just as a single combined TSQLClientDataSet component, of course. To implement an explicit call to ApplyUpdates , we can drop a TButton component, call it btnApplyUpdates , point its Caption property to Apply Updates, and write the following line of code in the OnClick event handler (using the TSQLClientDataSet component to apply the updates):
void __fastcall TForm1::btnApplyUpdatesClick(TObject *Sender) { SQLClientDataSet1->ApplyUpdates(0); } The user of your application might wonder about the need to click this ApplyUpdates button. Suppose the user changes a lot of data, but is surprised that other users don't see his changes because he never clicks the ApplyUpdates method. Clearly, this can be a big problem, and at first sight the ClientDataSet layer seems only to add potential confusion. But fortunately making sure the ApplyUpdates method is called on a frequent or even automatic basis can solve this. In fact, you can easily use the OnAfterPost event handler of the TClientDataSet or TSQLClientDataSet component to call the ApplyUpdates method, which will make sure that the data is immediately sent as an update packet to the database server after every (local) post to the TClientDataSet component.
void __fastcall TForm1::ClientDataSet1AfterPost(TDataSet *DataSet) { dynamic_cast<TClientDataSet*>(DataSet)->ApplyUpdates(0); } Note that because the TSQLClientDataSet is derived from the TClientDataSet , we can actually reuse the OnAfterPost event handler from the TClientDataSet as OnAfterPost event handler for the TSQLClientDataSet .
TSQLMonitor
The TSQLMonitor component is the last component from the dbExpress tab of the C++Builder 6 component palette that we need to cover. It's actually more of a supporting component, and mainly used when tracing or debugging dbExpress applications. As such, TSQLMonitor can be used to literally monitor the SQL statements and trace messages that are sent from the dbExpress application to the SQL DBMS. This can be very helpful when you need to pinpoint problems in your dbExpress application.
The TSQLMonitor component has a number of properties that we must work with. First of all, we should assign a value to the SQLConnection property to specify the TSQLConnection component that we want to monitor. After this, we can set the Active property to true to activate the monitoring and to false to (temporarily) deactivate it again. This toggle feature can be very helpful because you can actually turn the TSQLMonitor on right before things start to go wrong, so you don't have to watch everything going on as the application itself starts and the initial connection is made. The monitor messages will end up in the TraceList property of type TStrings .
Two other related properties are the FileName property and the AutoSave property. If you've set the FileName to a certain logfile and the AutoSave to true , the monitoring messages will be saved in the logfile automatically (if the Active property is also true ). You can then view the logfile with the trace messages for details.
If you want to process the trace messages directly, you can also respond to the two event handlers of the TSQLMonitor component, namely OnTrace and OnLogTrace . The OnTrace event handler is called right before a message is added to the TraceList property. Inside this event handler we can change this message or even prevent it from being logged in the TraceList altogether by using the LogTrace argument, as shown in the following code:
void __fastcall TForm1::SQLMonitor1Trace(TObject *Sender, pSQLTRACEDesc CBInfo, bool &LogTrace) { if (CBInfo->eTraceCat == traceMISC) LogTrace = false; else LogTrace = true; } CAUTION
It seems that the dbExpress driver for InterBase doesn't really use the CBInfo->eTraceCat field, so the above code may result in no trace messages at all when using in our example project. For that reason, the code is commented out in the source code on CD-ROM (until a better dbExpress driver is found that uses this field).
Although the OnTrace event handler is called right before, the OnLogTrace event handler is called right after a trace message has been added to the TraceList property. OnLogTrace can be used to display the trace message somewhere else (for example, if you always want to show the last trace message in the statusbar ). Some not very efficient C++ code in the OnLogTrace event handler to show the contents of the TraceList property in a TMemo component is as follows :
void __fastcall TForm1::SQLMonitor1LogTrace(TObject *Sender, pSQLTRACEDesc CBInfo) { Memo1->Lines->Clear(); Memo1->Lines->Add(SQLMonitor1->TraceList->Text); Memo1->Lines = SQLMonitor1->TraceList; } The problem with this code is that the OnLogTrace event handler will be called quite often, and clearing the TMemo->Lines property every time is a costly operation that will slow down the overall performance of your application. It would be better to add the individual trace messages in the OnTrace event handler. But, there's an even faster approach: directly pointing the TraceList property to the Lines property of the TMemo component ”both are of type TStrings . This means that updates to the TraceList will be shared automatically by the Lines property of the TMemo component because both point to the same memory space. The code for this is as follows:
__fastcall TForm1::TForm1(TComponent* Owner) : TForm(Owner) { Memo1->Lines->Clear(); Memo1->Lines = SQLMonitor1->TraceList; // pointing... } Finally, we can add a TCheckBox , call it cbTrace , and in the OnClick event handler set the Active property of the TSQLMonitor component to true or false , so we can control the trace messages from our own application. The implementation of this last OnClick event handler is as follows:
void __fastcall TForm1::cbTraceClick(TObject *Sender) { SQLMonitor1->Active = cbTrace->Checked; } And the final dbExpress application (at design-time) can be seen in Figure 12.5.
Figure 12.5. SQLMonitor at design time.
Running the application will show the log messages inside the Memo control as expected (we can control the appearance of new trace messages with the Trace check box), see Figure 12.6.
Figure 12.6. SQLMonitor at runtime.
Now that we've covered all seven components from the dbExpress tab of the component palette, it's time to focus on the last topic ”migrating C++Builder applications from Borland Database Engine (BDE) to dbExpress.
 |
| Top |
