Using a Single Component or Many Components

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.

procedure TForm1.DoUpdate(DataSet: TDataSet);
begin
  // immediately apply local changes to the database
  SQLClientDataSet1.ApplyUpdates(0);
end;

Monitoring the Connection

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;

Figure 14.6: A sample log obtained by the SQLMonitor in the DbxSingle example

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 = ?

update EMPLOYEE  set
  PHONE_EXT = ?
where
  EMP_NO = ? and
  PHONE_EXT = ?
   
update EMPLOYEE  set
  PHONE_EXT = ?
where
  EMP_NO = ?

Accessing Database Metadata with SetSchemaInfo

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:

Figure 14.7: The SchemaTest example allows you to see a database's tables and the columns of a given 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).

A Parametric Query

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.

Figure 14.8: Editing a query component's collection of parameters

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 [0].AsString);
    SqlDataSet2.Next;
  end;
  ComboBox1.Text := CombBox1.Items[0];
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[0].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.

Figure 14.9: The ParQuery example at run time

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.

When One-Way Is Enough: Printing Data

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:

Listing 14.1: The Core Method of the UniPrint Example

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[0].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;