This chapter describes how to use the TTable dataset component in your database applications. A table component encapsulates the full structure of and data in an underlying database table. A table component inherits many of its fundamental properties and methods from TDataSet, TBDEDataSet, and TDBDataSet. Therefore, you should be familiar with the general discussion of datasets in "Understanding datasets," and the BDE-specific discussion of datasets in "Using BDE-enabled datasets" before reading about the unique properties and methods of table components discussed here.
A table component gives you access to every row and column in an underlying database table, whether it is from Paradox, dBASE, Access, FoxPro, an ODBC-compliant database, or an SQL database on a remote server, such as InterBase, Sybase, or SQL Server.
You can view and edit data in every column and row of a table. You can work with a range of rows in a table, and you can filter records to retrieve a subset of all records in a table based on filter criteria you specify. You can search for records, copy, rename, or delete entire tables, and create master/detail relationships between tables.
Note: A table component always references a single database table. If you need to access multiple tables with a single component, or if you are only interested in a subset of rows and columns in one or more tables, you should use a query component instead of a table component. For more information about query components, see "Working with queries."
The following steps are general instructions for setting up a table component at design time. There may be additional steps you need to tailor a table's properties to the requirements of your application.
The DatabaseName property specifies where the table component looks for a database table. For Paradox and dBASE, DatabaseName can be a Borland Database Engine (BDE) alias, or an explicit directory path. For SQL tables, DatabaseName must be a BDE alias.
The advantage of using BDE aliases in all cases is that you can change the data source for an entire application by simply changing the alias definition in the SQL Explorer. To change the alias definition using SQL explorer, right click the SQL explorer and select Rename. This displays the BDE Administration Tool. For more information about setting and using BDE aliases, see the online help for the SQL Explorer.
To set the DatabaseName property,
Tip: If your application uses database components to control database transactions, DatabaseName can be set to a local alias defined for the database component instead. For more information about database components, see "Connecting to databases."
The TableName property specifies the table in a database to access with the table component. To specify a table, follow these steps:
Once you specify a valid table name, you can set the table component's Active property to True to connect to the database, open the table, and display and edit data.
At runtime, you can set or change the table associated with a table component by:
For example, the following code changes the table name for the OrderOrCustTable table component based on its current table name:
with OrderOrCustTable do
begin
Active := False; {Close the table}
if TableName = 'CUSTOMER.DB' then
TableName := 'ORDERS.DB'
else
TableName := 'CUSTOMER.DB';
Active := True; {Reopen with a new table}
end;
If an application accesses Paradox, dBASE, FoxPro, or comma-delimited ASCII text tables, then the BDE uses the TableType property to determine the table's type (its expected structure). TableType is not used when an application accesses SQL-based tables on database servers.
By default TableType is set to ttDefault. When TableType is ttDefault, the BDE determines a table's type from its file-name extension. Table 20.1 summarizes the file name extensions recognized by the BDE and the assumptions it makes about a table's type:
If your local Paradox, dBASE, and ASCII text tables use the file extensions as described in Table 20.1, then you can leave TableType set to ttDefault. Otherwise, your application must set TableType to indicate the correct table type. Table 20.2 indicates the values you can assign to TableType:
To view and edit a table's data in a data-aware control such as TDBGrid, open the table. There are two ways to open a table. You can set its Active property to True, or you can call its Open method. Opening a table puts it into dsBrowse state and displays data in any active controls associated with the table's data source.
To end display and editing of data, or to change the values for a table component's fundamental properties (e.g., DatabaseName, TableName, and TableType), first post or discard any pending changes. If cached updates are enabled, call the ApplyUpdates method to write the posted changes to the database. Finally, close the table.
There are two ways to close a table. You can set its Active property to False, or you can call its Close method. Closing a table puts the table into dsInactive state. Active controls associated with the table's data source are cleared.
By default when a table is opened, it requests read and write access for the underlying database table. Depending on the characteristics of the underlying database table, the requested write privilege may not be granted (for example, when you request write access to an SQL table on a remote server and the server restricts the table's access to read only).
There are three properties for table components that can affect an application's read and write access to a table: CanModify, ReadOnly, and Exclusive.
CanModify is a read-only property that specifies whether or not a table component is permitted read/write access to the underlying database table. After you open a table at runtime, your application can examine CanModify to test whether or not the table has write access. If CanModify is False, the application cannot write to the database. If CanModify is True, your application can write to the database provided that the table's ReadOnly property is False.
ReadOnly determines whether or not a user can both view and edit data. When ReadOnly is False (the default), a user can both view and edit data. To restrict a user to viewing data, set ReadOnly to True before opening a table.
Exclusive controls whether or not an application gains sole read/write access to a Paradox, dBASE, or FoxPro table. To gain sole read/write access for these table types, set the table component's Exclusive property to True before opening the table. If you succeed in opening a table for exclusive access, other applications cannot read data from or write data to the table. Your request for exclusive access is not honored if the table is already in use when you attempt to open it.
The following statements open a table for exclusive access:
CustomersTable.Exclusive := True; {Set request for exclusive lock}
CustomersTable.Active := True; {Now open the table}
Note: You can attempt to set Exclusive on SQL tables, but some servers may not support exclusive table-level locking. Others may grant an exclusive lock, but permit other applications to read data from the table. For more information about exclusive locking of database tables on your server, see your server documentation.
You can search for specific records in a table in various ways. The most flexible and preferred way to search for a record is to use the generic search methods Locate and Lookup. These methods enable you to search on any type of fields in any table, whether or not they are indexed or keyed.
You can use Locate and Lookup with any kind of dataset, not just TTable. For a complete discussion of Locate and Lookup, see "Understanding datasets."
Table components also support the Goto and Find methods. While these methods are documented here to allow you to work with legacy applications, you should always use Lookup and Locate in your new applications. You may see performance gains in existing applications if you convert them to use the new methods.
Table components support a set of Goto search methods for backward compatibility. Goto methods enable you to search for a record based on indexed fields, referred to as a key, and make the first record found the new current record.
For Paradox and dBASE tables, the key must always be an index, which you can specify in a table component's IndexNameproperty. For SQL tables, the key can also be a list of fields you specify in the IndexFieldNames property. You can also specify a field list for Paradox or dBASE tables, but the fields must have indexes defined on them. For more information about IndexName and IndexFieldNames, see "Searching on alternate indexes".
Tip: To search on nonindexed fields in a Paradox or dBASE table, use Locate. Alternatively, you can use a TQuery component and a SELECT statement to search on nonindexed fields in Paradox and dBASE fields. For more information about TQuery, see "Working with queries."
The following table summarizes the six related Goto and Find methods an application can use to search for a record:
GotoKey and FindKey are Boolean functions that, if successful, move the cursor to a matching record and return True. If the search is unsuccessful, the cursor is not moved, and these functions return False.
GotoNearest and FindNearest always reposition the cursor either on the first exact match found or, if no match is found, on the first record that is greater than the specified search criteria.
To execute a search using Goto methods, follow these general steps:
For example, the following code, attached to a button's OnClick event, moves to the first record containing a field value that exactly matches the text in an edit box on a form:
procedure TSearchDemo.SearchExactClick(Sender: TObject);
begin
Table1.SetKey;
Table1.Fields[0].AsString := Edit1.Text;
if not Table1.GotoKey then
ShowMessage('Record not found');
end;
GotoNearest is similar. It searches for the nearest match to a partial field value. It can be used only for string fields. For example,
Table1.SetKey; Table1.Fields[0].AsString := 'Sm'; Table1.GotoNearest;
If a record exists with "Sm" as the first two characters, the cursor is positioned on that record. Otherwise, the position of the cursor does not change and GotoNearest returns False.
To execute a search using Find methods, follow these general steps:
Note: FindNearest can only be used for string fields.
By default, a successful search positions the cursor on the first record that matches the search criteria. If you prefer, you can set the KeyExclusive property for a table component to True to position the cursor on the next record after the first matching record.
By default, KeyExclusive is False, meaning that successful searches position the cursor on the first matching record.
If a table has more than one key column, and you want to search for values in a sub-set of that key, set KeyFieldCount to the number of columns on which you are searching. For example, if a table has a three column primary key, and you want to search for values in just the first column, set KeyFieldCount to 1.
For tables with multiple-column keys, you can search only for values in contiguous columns, beginning with the first. For example, for a three-column key you can search for values in the first column, the first and second, or the first, second, and third, but not just the first and third.
If you want to search on an index other than the primary index for a table, then you must specify the name of the index to use in the IndexName property for the table. A table must be closed when you specify a value for IndexName. For example, if the CUSTOMER table had a secondary index named "CityIndex" on which you wanted to search for a value, you need to set the value of the table's IndexName property to "CityIndex":
Table1.Close; Table1.IndexName := 'CityIndex'; Table1.Open; Table1.SetKey; Table1['City'] := Edit1.Text; Table1.GotoNearest;
Instead of specifying an index name, you can list fields to use as a key in the IndexFieldNames property. For Paradox and dBASE tables, the fields you list must be indexed, or an exception is raised when you execute the search. For SQL tables, the fields you list need not be indexed.
Each time you call SetKey or FindKey it clears any previous values in the Fields property. If you want to repeat a search using previously set fields, or you want to add to the fields used in a search, call EditKey in place of SetKey and FindKey. For example, if the "CityIndex" index includes both the City and Country fields, to find a record with a specified company name in a specified city, use the following code:
Table1.EditKey; Table1['Country'] := Edit2.Text; Table1.GotoNearest;
An index determines the display order of records in a table. In general, records appear in ascending order based on a primary index (for dBASE tables without a primary index, sort order is based on physical record order). This default behavior does not require application intervention. If you want a different sort order, however, you must specify either
Specifying a different sort order requires the following steps:
At runtime, your application can call the GetIndexNames method to retrieve a list of available indexes for a table. GetIndexNames returns a string list containing valid index names. For example, the following code determines the list of indexes available for the CustomersTable dataset:
var IndexList: TList; ... CustomersTable.GetIndexNames(IndexList);
Note: For Paradox tables, the primary index is unnamed, and is therefore not returned by GetIndexNames. If you need to return to using a primary index on a Paradox table after using an alternative index, set the table's IndexName property to a null string.
To specify that a table should be sorted using an alternative index, specify the index name in the table component's IndexName property. At design time you can specify this name in the Object Inspector, and at runtime you can access the property in your code. For example, the following code sets the index for CustomersTable to CustDescending:
CustomersTable.IndexName := 'CustDescending';
For dBASE tables that use non-production indexes, set the IndexFiles property to the name of the index file(s) to use before you set IndexName. At design time you can click the ellipsis button in the IndexFiles property value in the Object Inspector to invoke the Index Files editor.
To see a list of available index files, choose Add, and select one or more index files from the list. A dBASE index file can contain multiple indexes. To select an index from the index file, select the index name from the IndexName drop-down list in the Object Inspector. You can also specify multiple indexes in the file by entering desired index names, separated by semicolons.
You can also set IndexFiles and IndexName at runtime. For example, the following code sets the IndexFiles for the AnimalsTable table component to ANIMALS.MDX, and then sets IndexName to NAME:
AnimalsTable.IndexFiles := 'ANIMALS.MDX'; AnimalsTable.IndexName := 'NAME';
In SQL, sort order of rows is determined by the ORDER BY clause. You can specify the index used by this clause either with the
IndexName and IndexFieldNames are mutually exclusive. Setting one property clears values set for the other. To use IndexName, see "Searching on alternate indexes".
IndexFieldNames is a string list property. To specify a sort order, list each column name to use in the order it should be used, and delimit the names with semicolons. Sorting is by ascending order only. Case-sensitivity of the sort depends on the capabilities of your server. See your server documentation for more information.
The following code sets the sort order for PhoneTable based on LastName, then FirstName:
PhoneTable.IndexFieldNames := 'LastName;FirstName';
Note: If you use IndexFieldNames on Paradox and dBASE tables, Delphi attempts to find an index that uses the columns you specify. If it cannot find such an index, it raises an exception.
When your application uses an index at runtime, it can examine the
IndexFields is a string list containing the column names for the index. The following code fragment illustrates how you might use IndexFieldCount and IndexFields to iterate through a list of column names in an application:
var I: Integer; ListOfIndexFields: array[0 to 20} of string; begin with CustomersTable do begin for I := 0 to IndexFieldCount - 1 do ListOfIndexFields[I] := IndexFields[I]; end; end;
Note: IndexFieldCount is not valid for a base table opened on an expression index.
Production tables can be huge, so applications often need to limit the number of rows with which they work. For table components, there are two ways to limit records used by an application: ranges and filters. Filters can be used with any kind of dataset, including TTable, TQuery, and TStoredProc components. Because they apply to all datasets, you can find a full discussion of using filters in "Understanding datasets."
Ranges only apply to TTable components. Despite their similarities, ranges and filters have different uses. The following section discusses the differences between ranges and filters and then discusses how to use ranges.
Both ranges and filters restrict visible records in a table to a subset of all available records, but the way they do so differs. A range is a set of contiguously indexed records that fall between specified boundary values. For example, in an employee database indexed on last name, you might apply a range to display all employees whose last names are greater than "Jones" and less than "Smith". Because ranges depend on indexes, ranges can only be applied to indexed Paradox and dBASE tables (for SQL tables, ranges can be applied to any fields you list in the IndexFieldNames property). Ranges can only be ordered based on existing indexes.
A filter, on the other hand, is any set of contiguous and noncontiguous records that share specified data points, regardless of indexing. For example, you might filter an employee database to display all employees who live in California and who have worked for the company for five or more years. While filters make use of indexes if they apply, filters are not dependent on them. Filters are applied record-by-record as an application scrolls through a dataset.
In general, filters are more flexible than ranges. Ranges, however, can be more efficient when datasets are very large and the records of interest to an application are already blocked in contiguously indexed groups. For very large datasets, it may be still more efficient to use a query component to select data for viewing and editing. For more information about using filters, see "Understanding datasets." For more information about using queries, see "Working with queries."
The process for creating and applying a range involves these general steps:
Call the SetRangeStart procedure to put the dataset into dsSetKey state and begin creating a list of starting values for the range. Once you call SetRangeStart, subsequent assignments to the Fields property are treated as starting index values to use when applying the range. Fields specified must be indexed fields when using Paradox and dBASE.
For example, suppose your application uses a table component named Customers, linked to the CUSTOMER table, and that you have created persistent field components for each field in the Customers dataset. CUSTOMER is indexed on its first column (CustNo). A form in the application has two edit components named StartVal and EndVal, used to specify start and ending values for a range. If so, the following code could be used to create and apply a range:
with Customers do
begin
SetRangeStart;
FieldByName('CustNo') := StartVal.Text;
SetRangeEnd;
if EndVal.Text <> '' then
FieldByName('CustNo') := EndVal.Text;
ApplyRange;
end
This code checks that the text entered in EndVal is not null before assigning any values to Fields. If the text entered for StartVal is null, then all records from the beginning of the table will be included, since all values are greater than null. However, if the text entered for EndVal is null, then no records will be included, since none are less than null.
For a multi-column index, you can specify a starting value for all or some fields in the index. If you do not supply a value for a field used in the index, a null value is assumed when you apply the range. If you set more values than there are fields in the index, the extra fields are ignored when computing the range.
To finish specifying the start of a range, call SetRangeEnd or ApplyRange. These methods are discussed in the following sections.
Tip: To start at the beginning of the dataset, omit the call to SetRangeStart.
You can also set the starting (and ending) values for a range with a call to the SetRange procedure. For more information about SetRange, see "Setting start- and end-range values".
Call the SetRangeEnd procedure to put the dataset into dsSetKey state and start creating a list of ending values for the range. Once you call SetRangeEnd, subsequent assignments to the Fields property are treated as ending index values to use when applying the range. Fields specified must be indexed fields for Paradox and dBASE tables.
Note: Always specify the ending values for a range, even if you want a range to end on the last record in the dataset. If you do not provide ending values, Delphi assumes the ending value of the range is a null value. A range with null ending values is always empty.
The easiest way to assign ending values is to call the FieldByName method. For example,
with Table1 do
begin
SetRangeStart;
FieldByName('LastName') := Edit1.Text;
SetRangeEnd;
FieldByName('LastName') := Edit2.Text;
ApplyRange;
end;
For a multi-column index, you can specify a starting value for all or some fields in the index. If you do not supply a value for a field used in the index, a null value is assumed when you apply the range. If you set more values than there are fields in the index, an exception is raised.
To finish specifying the end of a range, call ApplyRange. For more information about applying a range, see "Applying a range".
Note: You can also set the ending (and starting) values for a range with a call to the SetRange procedure. SetRange is described in the next section.
Instead of using separate calls to SetRangeStart and SetRangeEnd to specify range boundaries, you can call the SetRange procedure to put the dataset into dsSetKey state and set the starting and ending values for a range with a single call.
SetRange takes two constant array parameters: a set of starting values, and a set of ending values. For example, the following statements establish a range based on a two-column index:
SetRange([Edit1.Text, Edit2.Text], [Edit3.Text, Edit4.Text]);
For a multi-column index, you can specify starting and ending values for all or some fields in the index. If you do not supply a value for a field used in the index, a null value is assumed when you apply the range. To omit a value for the first field in an index, and specify values for successive fields, pass a null value for the omitted field. If you set more values than there are fields in the index, the extra fields are ignored when computing the range.
Note: Always specify the ending values for a range, even if you want a range to end on the last record in the dataset. If you do not provide ending values, Delphi assumes the ending value of the range is a null value. A range with null ending values is always empty because the starting range is greater than or equal to the ending range.
If a key is composed of one or more string fields, the SetRange methods support partial keys. For example, if an index is based on the LastName and FirstName columns, the following range specifications are valid:
Table1.SetRangeStart; Table1['LastName'] := 'Smith'; Table1.SetRangeEnd; Table1['LastName'] := 'Zzzzzz'; Table1.ApplyRange;
This code includes all records in a range where LastName is greater than or equal to "Smith." The value specification could also be:
Table1['LastName'] := 'Sm';
This statement includes records that have LastName greater than or equal to "Sm." The following statement includes records with a LastName greater than or equal to "Smith" and a FirstName greater than or equal to "J":
Table1['LastName'] := 'Smith'; Table1['FirstName'] := 'J';
By default, a range includes all records that are greater than or equal to the specified starting range, and less than or equal to the specified ending range. This behavior is controlled by the KeyExclusive property. KeyExclusive is False by default.
If you prefer, you can set the KeyExclusive property for a table component to True to exclude records equal to ending range. For example,
KeyExclusive := True; Table1.SetRangeStart; Table1['LastName'] := 'Smith'; Table1.SetRangeEnd; Table1['LastName'] := 'Tyler'; Table1.ApplyRange;
This code includes all records in a range where LastName is greater than or equal to "Smith" and less than "Tyler".
The SetRange methods described in the preceding sections establish the boundary conditions for a range, but do not put it into effect. To make a range take effect, call the ApplyRange procedure. ApplyRange immediately restricts a user's view of and access to data in the specified subset of the dataset.
The CancelRange method ends application of a range and restores access to the full dataset. Even though canceling a range restores access to all records in the dataset, the boundary conditions for that range are still available so that you can reapply the range at a later time. Range boundaries are preserved until you provide new range boundaries or modify the existing boundaries. For example, the following code is valid:
...
Table1.CancelRange;
...
{later on, use the same range again. No need to call SetRangeStart, etc.}
Table1.ApplyRange;
...
Two functions enable you to modify the existing boundary conditions for a range: EditRangeStart, for changing the starting values for a range; and EditRangeEnd, for changing the ending values for the range.
The process for editing and applying a range involves these general steps:
You can modify either the starting or ending values of the range, or you can modify both boundary conditions. If you modify the boundary conditions for a range that is currently applied to the dataset, the changes you make are not applied until you call ApplyRange again.
Call the EditRangeStart procedure to put the dataset into dsSetKey state and begin modifying the current list of starting values for the range. Once you call EditRangeStart, subsequent assignments to the Fields property overwrite the current index values to use when applying the range. Fields specified must be indexed fields when using Paradox and dBASE.
Tip: If you initially created a start range based on a partial key, you can use EditRangeStart to extend the starting value for a range. For more information about ranges based on partial keys, see "Specifying a range based on partial keys".
Call the EditRangeEnd procedure to put the dataset into dsSetKey state and start creating a list of ending values for the range. Once you call EditRangeEnd, subsequent assignments to the Fields property are treated as ending index values to use when applying the range. Fields must be indexed fields when using Paradox and dBASE.
Note: Always specify the ending values for a range, even if you want a range to end on the last record in the dataset. If you do not provide ending values, Delphi assumes the ending value of the range is a null value. A range with null ending values is always empty.
To delete all rows of data in a table, call a table component's EmptyTable method at runtime. For SQL tables, this method only succeeds if you have DELETE privilege for the table. For example, the following statement deletes all records in a dataset:
PhoneTable.EmptyTable;
Caution: Data you delete with EmptyTable is gone forever.
At design time, to delete a table from a database, right-click the table component and select Delete Table from the context menu. The Delete Table menu pick will only be present if the table component represents an existing database table (the DatabaseName and TableName properties specify an existing table).
To delete a table at runtime, call the table component's DeleteTable method. For example, the following statement removes the table underlying a dataset:
CustomersTable.DeleteTable;
Caution: When you delete a table with DeleteTable, the table and all its data are gone forever.
To rename a Paradox or dBASE table at design time, right-click the table component and select Rename Table from the context menu. You can also rename a table by typing over the name of an existing table next to the TableName property in the Object Inspector. When you change the TableName property, a dialog appears asking you if you want to rename the table. At this point, you can either choose to rename the table, or you can cancel the operation, changing the TableName property (for example, to create a new table) without changing the name of the table represented by the old value of TableName.
To rename a Paradox or dBASE table at runtime, call the table component's RenameTable method. For example, the following statement renames the Customer table to CustInfo:
Customer.RenameTable('CustInfo');
You can create new database tables at design time or at runtime. The Create Table command (at design time) or the CreateTable method (at runtime) provides a way to create tables without requiring SQL knowledge. They do, however, require you to be intimately familiar with the properties, events, and methods common to dataset components, TTable in particular. This is so that you can first define the table you want to create by doing the following:
Note: At design time, you can preload the field definitions and index definitions of an existing table into the FieldDefs and IndexDefs properties, respectively. Set the DatabaseName and TableName properties to specify the existing table. Right click the table component and choose Update Table Definition. This automatically sets the values of the FieldDefs and IndexDefs properties to describe the fields and indexes of the existing table. Next, reset the DatabaseName and TableName to specify the table you want to create, cancelling any prompts to rename the existing table. If you want to store these definitions with the table component (for example, if your application will be using them to create tables on user's systems), set the StoreDefs property to True.
Once the table is fully described, you are ready to create it. At design time, right-click the table component and choose Create Table. At runtime, call the CreateTable method to generate the specified table.
Warning: If you create a table that duplicates the name of an existing table, the existing table and all its data are overwritten by the newly created table. The old table and its data cannot be recovered.
The following code creates a new table at runtime and associates it with the DBDEMOS alias. Before it creates the new table, it verifies that the table name provided does not match the name of an existing table:
var
NewTable: TTable;
NewIndexOptions: TIndexOptions;
TableFound: Boolean;
begin
NewTable := TTable.Create;
NewIndexOptions := [ixPrimary, ixUnique];
with NewTable do
begin
Active := False;
DatabaseName := 'DBDEMOS';
TableName := Edit1.Text;
TableType := ttDefault;
FieldDefs.Clear;
FieldDefs.Add(Edit2.Text, ftInteger, 0, False);
FieldDefs.Add(Edit3.Text, ftInteger, 0, False);
IndexDefs.Clear;
IndexDefs.Add('PrimaryIndex', Edit2.Text, NewIndexOptions);
end;
{Now check for prior existence of this table}
TableFound := FindTable(Edit1.Text); {code for FindTable not shown}
if TableFound = True then
if MessageDlg('Overwrite existing table ' + Edit1.Text + '?', mtConfirmation, mbYesNo, 0) = mrYes then
TableFound := False;
if not TableFound then
CreateTable; { create the table}
end;
end;
You can use a table component's BatchMove method to import data from another table. BatchMove can
BatchMove takes two parameters: the name of the table from which to import data, and a mode specification that determines which import operation to perform. Table 20.4 describes the possible settings for the mode specification:
For example, the following code updates records in the current table with records from the Customer table:
Table1.BatchMove('CUSTOMER.DB', batUpdate);
BatchMove returns the number of records it imports successfully.
Caution: Importing records using the batCopy mode overwrites existing records. To preserve existing records use batAppend instead.
BatchMove performs only some of the functions available to your application directly through the TBatchMove component. If you need to move a large amount of data between or among tables, use the batch move component instead of calling a table's BatchMove function. For more information about using TBatchMove, see the following topic, "Using TBatchMove."
TBatchMove encapsulates Borland Database Engine (BDE) features that enable you to duplicate a dataset, append records from one dataset to another, update records in one dataset with records from another dataset, and delete records from one dataset that match records in another dataset. TBatchMove is most often used to:
A batch move component can create tables on the destination that correspond to the source tables, automatically mapping the column names and data types as appropriate.
To create a batch move component:
If you are appending, updating, or deleting, Destination must be an existing table.
If you are copying a table and Destination is an existing table, then executing the batch move overwrites all of the current data in the destination table.
If you are creating an entirely new table by copying an existing table, the resulting table has the name specified in the Name property of the table component to which you are copying. The resulting table type will be of a structure appropriate to the server specified by the DatabaseName property.
The Mode property specifies the operation a batch move component performs:
To append data, the destination dataset must already exist. During the append operation, the BDE converts data to appropriate data types and sizes for the destination dataset if necessary. If a conversion is not possible, an exception is thrown and the data is not appended.
To update data, the destination dataset must already exist and must have an index defined that enables records to be matched. If the primary index fields are used for matching, records with index fields in the destination dataset that match index fields records in the source dataset are overwritten with the source data. During the update operation, the BDE converts data to appropriate data types and sizes for the destination dataset if necessary.
To append and update data the destination dataset must already exist and must have an index defined that enables records to be matched. If the primary index fields are used for matching, records with index fields in the destination dataset that match index fields records in the source dataset are overwritten with the source data. Otherwise data from the source dataset is appended to the destination dataset. During append and update operations, the BDE converts data to appropriate data types and sizes for the destination dataset if necessary.
To make a copy of a source dataset, the destination dataset should not already exist. If it does, the batch move operation overwrites the existing destination dataset with a copy of the source dataset.
If the source and destination datasets are maintained by different types of database engines, for example, Paradox and InterBase, the BDE creates a destination dataset with a structure as close as possible to that of the source dataset and automatically performs data type and size conversions as necessary.
Note: TBatchMove does not copy metadata structures such as indexes, constraints, and stored procedures. You must recreate these metadata objects on your database server or through the SQL Explorer as appropriate.
To delete data in the destination dataset, it must already exist and must have an index defined that enables records to be matched. If the primary index fields are used for matching, records with index fields in the destination dataset that match index fields records in the source dataset are deleted in the destination table.
In batAppend mode, a batch move component creates the destination table based on the column data types of the source table. Columns and types are matched based on their position in the source and destination tables. That is, the first column in the source is matched with the first column in the destination, and so on.
To override the default column mappings, use the Mappings property. Mappings is a list of column mappings (one per line). This listing can take one of two forms. To map a column in the source table to a column of the same name in the destination table, you can use a simple listing that specifies the column name to match. For example, the following mapping specifies that a column named ColName in the source table should be mapped to a column of the same name in the destination table:
ColName
To map a column named SourceColName in the source table to a column named DestColName in the destination table, the syntax is as follows:
DestColName = SourceColName
If source and destination column data types are not the same, a batch move operation attempts a "best fit". It trims character data types, if necessary, and attempts to perform a limited amount of conversion, if possible. For example, mapping a CHAR(10) column to a CHAR(5) column will result in trimming the last five characters from the source column.
As an example of conversion, if a source column of character data type is mapped to a destination of integer type, the batch move operation converts a character value of '5' to the corresponding integer value. Values that cannot be converted generate errors. For more information about errors, see "Handling batch move errors".
When moving data between different table types, a batch move component translates data types as appropriate based on the dataset's server types. See the BDE online help file for the latest tables of mappings among server types.
Note: To batch move data to an SQL server database, you must have that database server and a version of Delphi with the appropriate SQL Link installed, or you can use ODBC if you have the proper third party ODBC drivers installed.
Use the Execute method to execute a previously prepared batch operation at runtime. For example, if BatchMoveAdd is the name of a batch move component, the following statement executes it:
BatchMoveAdd.Execute;
You can also execute a batch move at design time by right clicking the mouse on a batch move component and choosing Execute from the context menu.
The MovedCount property keeps track of the number of records that are moved when a batch move executes.
The RecordCount property specifies the maximum number of records to move. If RecordCount is zero, all records are moved, beginning with the first record in the source dataset. If RecordCount is a positive number, a maximum of RecordCount records are moved, beginning with the current record in the source dataset. If RecordCount is greater than the number of records between the current record in the source dataset and its last record, the batch move terminates when the end of the source dataset is reached. You can examine MoveCount to determine how many records were actually transferred.
There are two types of errors that can occur in a batch move operation: data type conversion errors and integrity violations. TBatchMove has a number of properties that report on and control error handling.
The AbortOnProblem property specifies whether to abort the operation when a data type conversion error occurs. If AbortOnProblem is True, the batch move operation is canceled when an error occurs. If False, the operation continues. You can examine the table you specify in the ProblemTableName to determine which records caused problems.
The AbortOnKeyViol property indicates whether to abort the operation when a Paradox key violation occurs.
The ProblemCount property indicates the number of records that could not be handled in the destination table without a loss of data. If AbortOnProblem is True, this number is one, since the operation is aborted when an error occurs.
The following properties enable a batch move component to create additional tables that document the batch move operation:
Note: If ProblemTableName is not specified, the data in the record is trimmed and placed in the destination table.
If more than one table component is linked to the same database table through their DatabaseName and TableName properties and the tables do not share a data source component, then each table has its own view on the data and its own current record. As users access records through each table component, the components' current records will differ.
You can force the current record for each of these table components to be the same with the GotoCurrent method. GotoCurrent sets its own table's current record to the current record of another table component. For example, the following code sets the current record of CustomerTableOne to be the same as the current record of CustomerTableTwo:
CustomerTableOne.GotoCurrent(CustomerTableTwo);
Tip: If your application needs to synchronize table components in this manner, put the components in a data module and include the header for the data module in each unit that accesses the tables.
If you must synchronize table components on separate forms, you must include one form's header file in the source unit of the other form, and you must qualify at least one of the table names with its form name.
CustomerTableOne.GotoCurrent(Form2.CustomerTableTwo);
A table component's MasterSource and MasterFields properties can be used to establish one-to-many relationships between two tables.
The MasterSource property is used to specify a data source from which the table will get data for the master table. For instance, if you link two tables in a master/detail relationship, then the detail table can track the events occurring in the master table by specifying the master table's data source component in this property.
The MasterFields property specifies the column(s) common to both tables used to establish the link. To link tables based on multiple column names, use a semicolon delimited list:
Table1.MasterFields := 'OrderNo;ItemNo';
To help create meaningful links between two tables, you can use the Field Link designer. For more information about the Field Link designer, see the User's Guide.
The following steps create a simple form in which a user can scroll through customer records and display all orders for the current customer. The master table is the CustomersTable table, and the detail table is OrdersTable.
If you run the application now, you will see that the tables are linked together, and that when you move to a new record in the CUSTOMER table, you see only those records in the ORDERS table that belong to the current customer.
A nested table component provides access to data in a nested dataset of a table. The NestedDataSet property of a persistent nested dataset field contains a reference to the nested dataset. Since TNestedDataSet descends from TBDEDataSet, a nested table inherits BDE functionality, and so uses the Borland Database Engine (BDE) to access the nested table data. A nested table provides much of the functionality of a table component, but the data it accesses is stored in a nested table.
The following steps are general instructions for setting up a nested table component at design time. A table or live query component must be available that accesses a dataset containing a dataset or reference field. A persistent field for the TDataSetField or TReferenceField must also already exist. See "Working with dataset fields".
To use a nested table component,
pubsweb@inprise.com
Copyright © 1999, Inprise Corporation. All rights reserved.