Let's look at a demonstration that highlights the key features of these components and shows how to use the ClientDataSet to provide caching and editing support for the unidirectional datasets. Later, I'll show you an example of native use of the unidirectional query, with no caching and editing support required.
The standard visual application based on dbExpress uses this series of components:
The SQLConnection component provides the connection with the database and the proper dbExpress driver.
The SQLDataSet component, which is hooked to the connection (via the SQLConnection property), indicates which SQL query to execute or table to open (using the CommandType and CommandText properties discussed earlier).
The DataSetProvider component, connected with the dataset, extracts the data from the SQLDataSet and can generate the proper SQL update statements.
The ClientDataSet component reads from the data provider and stores all the data (if its PacketRecords property is set to –1) in memory. You'll need to call its ApplyUpdates method to send the updates back to the database server (through the provider).
The DataSource component allows you to surface the data from the ClientDataSet to the visual data-aware controls.
As I mentioned earlier, the picture can be simplified by using the SimpleDataSet component, which replaces the two datasets and the provider (and possibly even the connection). The SimpleDataSet component combines most of the properties of the components it replaces.
For this first example, drop a SimpleDataSet component on a form and set the connection name of its Connection subcomponent. Set the CommandType and CommandText properties to specify which data to fetch, and set the PacketRecords property to indicate how many records to retrieve in each block.
These are the key properties of the component in the DbxSingle example:
object SimpleDataSet1: TSimpleDataSet Connection.ConnectionName = 'IBLocal' Connection.LoginPrompt = False DataSet.CommandText = 'EMPLOYEE' DataSet.CommandType = ctTable end
As an alternative, the DbxMulti example uses the entire sequence of components:
object SQLConnection1: TSQLConnection ConnectionName = 'IBLocal' LoginPrompt = False end object SQLDataSet1: TSQLDataSet SQLConnection = SQLConnection1 CommandText = 'select * from EMPLOYEE' end object DataSetProvider1: TDataSetProvider DataSet = SQLDataSet1 end object ClientDataSet1: TClientDataSet ProviderName = 'DataSetProvider1' end object DataSource1: TDataSource DataSet = ClientDataSet1 end
Both examples include some visual controls: a grid and a toolbar based on the action manager architecture.
In every example based on a local cache, like the one provided by the ClientDataSet and SimpleDataSet components, it's important to write the local changes back to the database server. This is typically accomplished by calling the ApplyUpdates method. You can either keep the changes in the local cache for a while and then apply multiple updates at once, or you can post each change right away. In these two examples, I've gone for the latter approach, attaching the following event handler to the AfterPost (fired after an edit or an insert operation) and AfterDelete events of the ClientDataSet components:
procedure TForm1.DoUpdate(DataSet: TDataSet); begin // immediately apply local changes to the database SQLClientDataSet1.ApplyUpdates(0); end;
If you want to apply all the updates in a single batch, you can do so either when the form is closed or when the program ends, or you can let a user perform the update operation by selecting a specific command, possibly using the corresponding predefined action provided by Delphi 7. We'll explore this approach when discussing the update caching support of the ClientDataSet component in more detail later in this chapter.
Another feature I've added to the DbxSingle and DbxMulti examples is the monitoring capability offered by the SQLMonitor component. In the example, the component is activated as the program starts. In the DbxSingle example, because the SimpleDataSet embeds the connection, the monitor cannot be hooked to it at design time, but only when the program starts:
procedure TForm1.FormCreate(Sender: TObject); begin SQLMonitor1.SQLConnection := SimpleDataSet1.Connection; SQLMonitor1.Active := True; SimpleDataSet1.Active := True; end;
Every time a tracing string is available, the component fires the OnTrace event to let you choose whether to include the string in the log. If the LogTrace parameter of this event is True (the default value), the component logs the message in the TraceList string list and fires the OnLogTrace event to indicate that a new string has been added to the log.
The component can also automatically store the log into the file indicated by its FileName property, but I haven't used this feature in the example. All I've done is handle the OnTrace event, copying the entire log in the memo with the following code (producing the output shown in Figure 14.6):
procedure TForm1.SQLMonitor1Trace(Sender: TObject; CBInfo: pSQLTRACEDesc; var LogTrace: Boolean); begin Memo1.Lines := SQLMonitor1.TraceList; end;
If you run the DbxSingle program and change, for example, an employee's telephone number, the monitor will log this update operation:
update EMPLOYEE set PHONE_EXT = ? where EMP_NO = ? and FIRST_NAME = ? and LAST_NAME = ? and PHONE_EXT = ? and HIRE_DATE = ? and DEPT_NO = ? and JOB_CODE = ? and JOB_GRADE = ? and JOB_COUNTRY = ? and SALARY = ? and FULL_NAME = ?
By setting the SimpleDataSet's properties there is no way to change how the update code is generated (which happens to be worse than with the SQLClientDataSet component, which had the UpdateMode you could use to tweak the update statements).
In the DbxMulti example, you can use the UpdateMode property of the DataSetProvider component, setting the value to upWhereChanged or upWhereKeyOnly. In this case you'll get the following two statements, respectively:
update EMPLOYEE set PHONE_EXT = ? where EMP_NO = ? and PHONE_EXT = ? update EMPLOYEE set PHONE_EXT = ? where EMP_NO = ?
This result is much better than in Delphi 6 (without the patches applied), in which this operation caused an error because the key field was not properly set.
If you want more control over how the update statements are generated, you need to operate on the fields of the underlying dataset, which are available also when you use the all-in-one SimpleDataSet component (which has two field editors, one for the base ClientDataset component it inherits from and one for the SQLDataSet component it embeds). I have made similar corrections in the DbxMulti example, after adding persistent fields for the SQLDataSet component and modifying the provider options for some of the fields to include them in the key or exclude them from updates.
We'll discuss this type of problem again when we examine the details of the ClientDataSet component, the provider, the resolver, and other technical details later in this chapter and in Chapter 16.
All RDBMS systems use special-purpose tables (generally called system tables) for storing metadata, such as the list of the tables, their fields, indexes, and constraints, and any other system information. Just as dbExpress provides a unified API for working with different SQL servers, it also provides a common way to access metadata. The SQLDataSet component has a SetSchemaInfo method that fills the dataset with system information. This SetSchemaInfo method has three parameters:
SchemaType Indicates the type of information requested. Values include stTables, stSysTables, stProcedures, stColumns, and stProcedureParams.
SchemaObject Indicates the object you are referring to, such as the name of the table whose columns you are requesting.
SchemaPattern A filter that lets you limit your request to tables, columns, or procedures starting with the given letters. This is handy if you use prefixes to identify groups of elements.
For example, in the SchemaTest program, a Tables button reads into the dataset all of the connected database's tables:
ClientDataSet1.Close; SQLDataSet1.SetSchemaInfo (stTables, '', ''); ClientDataSet1.Open;
The program uses the usual group of dataset provider, client dataset, and data source component to display the resulting data in a grid, as you can see in Figure 14.7. After you're retrieved the tables, you can select a row in the grid and click the Fields button to see a list of the fields of this table:
SQLDataSet1.SetSchemaInfo (stColumns, ClientDataSet1['Table_Name'], ''); ClientDataSet1.Close; ClientDataSet1.Open;
In addition to letting you access database metadata, dbExpress provides a way to access its own configuration information, including the installed drivers and the configured connections. The unit DbConnAdmin defines a TConnectionAdmin class for this purpose, but the aim of this support is limited to dbExpress add-on utilities for developers (end users aren't commonly allowed to access multiple databases in a totally dynamic way).
The DbxExplorer demo included in Delphi shows how to access both dbExpress administration files and schema information. Also check the help file under "The structure of metadata datasets" within the section "Developing database applications."
When you need slightly different versions of the same SQL query, instead of modifying the text of the query itself each time, you can write a query with a parameter and change the value of the parameter. For example, if you decide to have a user choose the employees in a given country (using the employee table), you can write the following parametric query:
select * from employee where job_country = :country
In this SQL clause, :country is a parameter. You can set its data type and startup value using the editor of the SQLDataSet component's Params property collection. When the Params collection editor is open, as shown in Figure 14.8, you see a list of the parameters defined in the SQL statement; you can set the data type and the initial value of these parameters in the Object Inspector.
The form displayed by this program, called ParQuery, uses a combo box to provide all the available values for the parameters. Instead of preparing the combo box items at design time, you can extract the available contents from the same database table as the program starts. This is accomplished using a second query component, with this SQL statement:
select distinct job_country from employee
After activating this query, the program scans its result set, extracting all the values and adding them to the list box:
procedure TQueryForm.FormCreate(Sender: TObject); begin SqlDataSet2.Open; while not SqlDataSet2.EOF do begin ComboBox1.Items.Add (SqlDataSet2.Fields .AsString); SqlDataSet2.Next; end; ComboBox1.Text := CombBox1.Items; end;
The user can select a different item in the combo box and then click the Select button (Button1) to change the parameter and activate (or re-activate) the query:
procedure TQueryForm.Button1Click(Sender: TObject); begin SqlDataSet1.Close; ClientDataSet1.Close; Query1.Params.Value := ListBox1.Items [Listbox1.ItemIndex]; SqlDataSet1.Open; ClientDataSet1.Open; end;
This code displays the employees from the selected country in the DBGrid, as you can see in Figure 14.9. As an alternative to using the elements of the Params array by position, you should consider using the ParamByName method, to avoid any problem in case the query gets modified over time and the parameters end up in a different order.
By using parametric queries, you can usually reduce the amount of data moved over the wire from the server to the client and still use a DBGrid and the standard user interface common in local database applications.
Parametric queries are generally also used to obtain master-detail architectures with SQL queries—at least, this is what Delphi tends to do. The DataSource property of the SQLDataSet component, automatically replaces parameter values with the fields of the master dataset having the same name as the parameter.
You have seen that one of the key elements of the dbExpress library is that it returns unidirectional datasets. In addition, you can use the ClientDataSet component (in one of its incarnations) to store the records in a local cache. Now, let's discuss an example in which a unidirectional dataset is all you need.
Such a situation is common in reporting—that is, producing information for each record in sequence without needing any further access to the data. This broad category includes producing printed reports (via a set of reporting components or using the printer directly), sending data to another application such as Microsoft Excel or Word, saving data to files (including HTML and XML formats), and more.
I don't want to delve into HTML and XML, so I'll present an example of printing—nothing fancy and nothing based on reporting components, just a way to produce a draft report on your monitor and printer. For this reason, I've used Delphi's most straightforward technique to produce a printout: assigning a file to the printer with the AssignPrn RTL procedure.
The example, called UniPrint, has a unidirectional SQLDataSet component hooked to an InterBase connection and based on the following SQL statement, which joins the employee table with the department table to display the name of the department where each employee works:
select d.DEPARTMENT, e.FULL_NAME, e.JOB_COUNTRY, e.HIRE_DATE from EMPLOYEE e inner join DEPARTMENT d on d.DEPT_NO = e.DEPT_NO
To handle printing, I've written a somewhat generic routine, requiring as parameters the data to print, a progress bar for status information, the output font, and the maximum format size of each field. The entire routine uses file-print support and formats each field in a fixed-size, left-aligned string, to produce a columnar type of report. The call to the Format function has a parametric format string that's built dynamically using the size of the field.
In Listing 14.1 you can see the code of the core PrintOutDataSet method, which uses three nested try/finally blocks to release all the resources properly:
procedure PrintOutDataSet (data: TDataSet; progress: TProgressBar; Font: TFont; toFile: Boolean; maxSize: Integer = 30); var PrintFile: TextFile; I: Integer; sizeStr: string; oldFont: TFontRecall; begin // assign the output to a printer or a file if toFile then begin SelectDirectory ('Choose a folder', '', strDir); AssignFile (PrintFile, IncludeTrailingPathDelimiter(strDir) + 'output.txt'); end else AssignPrn (PrintFile); // assign the printer to a file AssignPrn (PrintFile); Rewrite (PrintFile); // set the font and keep the original one oldFont := TFontRecall.Create (Printer.Canvas.Font); try Printer.Canvas.Font := Font; try data.Open; try // print header (field names) in bold Printer.Canvas.Font.Style := [fsBold]; for I := 0 to data.FieldCount - 1 do begin sizeStr := IntToStr (min (data.Fields[i].DisplayWidth, maxSize)); Write (PrintFile, Format ('%-' + sizeStr + 's', [data.Fields[i].FieldName])); end; Writeln (PrintFile); // for each record of the dataset Printer.Canvas.Font.Style := ; while not data.EOF do begin // print out each field of the record for I := 0 to data.FieldCount - 1 do begin sizeStr := IntToStr (min (data.Fields[i].DisplayWidth, maxSize)); Write (PrintFile, Format ('%-' + sizeStr + 's', [data.Fields[i].AsString])); end; Writeln (PrintFile); // advance ProgressBar progress.Position := progress.Position + 1; data.Next; end; finally // close the dataset data.Close; end; finally // reassign the original printer font oldFont.Free; end; finally // close the printer/file CloseFile (PrintFile); end; end;
The program invokes this routine when you click the Print All button. It executes a separate query (select count(*) from EMPLOYEE), which returns the number of records in the employee table. This query is necessary to set up the progress bar (the unidirectional dataset has no way of knowing how many records it will retrieve until it has reached the last one). Then it sets the output font, possibly using a fixed-width font, and calls the PrintOutDataSet routine:
procedure TNavigator.PrintAllButtonClick(Sender: TObject); var Font: TFont; begin // set ProgressBar range EmplCountData.Open; try ProgressBar1.Max := EmplCountData.Fields.AsInteger; finally EmplCountData.Close; end; Font := TFont.Create; try Font.Name := 'Courier New'; Font.Size := 9; PrintOutDataSet (EmplData, ProgressBar1, Font, cbFile.Checked); finally Font.Free; end; end;