Copyright: Borland C++Builder Database Application Developer's Guide
Citation: Copyright © 1996 Borland International.


Features chart

The following table summarizes the tools and features in C++Builder 2.0. Available tools and features differ depending on C++Builder 2.0 version. All features are available in the C++Builder Client/Server Suite.

CS=Client/Server Suite   DB=Developer    DT=Desktop

Tool/Feature	Description	C/S	DB	DT
SQL Explorer	SQL-enabled integrated tool for browsing databases, managing BDE aliases, and creating data dictionaries.	X
Database Explorer	Integrated tool for browsing databases, managing BDE aliases, and creating data dictionaries.		X	X
SQL Monitor	Integrated tool for tracing and examining SQL query performance.	X
Visual Query Builder	Integrated tool for visual building of SQL queries.	X
Data Pump Expert	Standalone tool for moving metadata and data between databases	X
Data Dictionary	Stores extended field attributes apart from application code; shares attributes across fields, datasets, and applications.	X	X
Object Repository	Stores data modules and forms for sharing among applications.	X	X	X
Data modules	Non-visual component containers for centralized data-access across forms and applications.	X	X	X
Data-access components	Non-visual components for encapsulating database connections.	X	X	X
Data-aware controls	Visual components for providing a user interface to data.	X	X	X
Database Desktop (DBD)	Tool for browsing, creating, and changing desktop databases.	X	X	X
Borland Database Engine (BDE)	Borland's core database engine and connectivity software.	X	X	X
SQL Links	BDE SQL drivers for Sybase, Microsoft SQL-Server, Oracle, and InterBase	X
ODBC socket	BDE support socket for third-party ODBC drivers.	X	X
BDE API	BDE application programming interface and online help files.	X	X
Quick Reports	C++Builder components for creating pre-defined reports in an application.	X	X	X
InterBase NT	Borland InterBase Workgroup Server for NT, two-user license.	X
Local InterBase Server	32-bit Local InterBase Server.	X	X


Using the object repository with data modules

The Object Repository stores links to data modules, forms, and projects for reuse and reference. When you create a new application, you can:

Copy an existing data module, form, or project, ensuring that your copy is completely independent of the repository.

Inherit an existing data module, form, or project, ensuring that changes to the linked module, form, or project in the repository are replicated to your application when you recompile.

Use an existing data module, form, or project, ensuring that changes you make to the module, form, or project are available for use in other applications.

The Object Repository supports team development practices. It uses a referencing mechanism to data modules, forms, and projects where they exist on a network server or shared machine. Every developer in your organization can save objects to a shared location, and then set C++Builder’s Object Repository reference to point to that location.

The Object Repository is also customizable through the Tools|Repository menu in the IDE.


Using the data dictionary

The Data Dictionary provides a customizable storage area, independent of your applications, where you can create extended field attribute sets that describe the content and appearance of data.

For example, if you frequently develop financial applications, you may create a number of specialized field attribute sets describing different display formats for currency. When you create datasets for your application at design time, rather than using the Object Inspector to set the currency fields in each dataset by hand, you can associate those fields with extended fields attribute set in the Data Dictionary. Using the Data Dictionary also ensures a consistent data appearance within and across the applications you create.

In a client/server environment the Data Dictionary can reside on a remote server for additional sharing of information.

To learn how to create extended field attribute sets from the Fields editor at design time, and how to associate them with fields throughout the datasets in your application, see Using the Fields Editor. To learn more about creating a Data Dictionary and extended field attributes with the SQL and Database Explorers, see their respective online help files.


Using the SQL/Database Explorer

In C++Builder Client/Server Suite you browse databases and populate tables with data using the SQL Explorer from the IDE. In C++Builder Developer you use the Database Explorer. Both versions of the Explorer enable you to:

Examine existing database tables and structures. The SQL Explorer enables you to examine remote SQL databases, and to query them.

Populate tables with data.

Create extended field attribute sets in a Data Dictionary for later retrieval and reuse. Extended field attributes describe how values in a field are formatted and displayed.

Associate extended field attributes with fields in your application.

Create and manage BDE aliases, used by your application to connect to databases.

To learn more about the SQL Explorer, and the Database Explorer see their respective online help files.


Using the SQL Monitor

In C++Builder Client/Server Suite you can use the SQL Monitor to trace and time calls between your client application and a remote SQL database server. Timing information is useful for optimizing SQL statements and transactions. A series of options enables you to trace:

Prepared query statements

Executed query statements

Statement operations

Connect/Disconnect

Transactions

Blob I/O

Vendor errors

Vendor API calls

You can use the SQL Monitor to examine how optimally SQL statements in an application are performed, to see the SQL statements generated by the Borland Database Engine (BDE), to see if the database client libraries are functioning properly, and to see if the database server is executing a run-away query. The SQL Monitor also enables you to save and print session logs for further reference and comparison.


Using the Visual Query Builder

In C++Builder Client/Server Suite you can enter SQL statements directly in SQL and Update SQL property editors, or you can invoke the Visual Query Builder to construct a query based on a visible representation of tables and fields in a database.

To use the Visual Query Builder, select a query component, right-click it to invoke the SpeedMenu, and choose Query Builder.


Data access component summary

C++Builder provides non-visual data-access components that encapsulate your client’s communication with a database. Data-access components only deal with database connectivity, which enables you to focus your attention on your application’s data needs without worrying about user interface. Typically these components are placed in a data module container in an application.

The following table summarizes the components that appear on the Data Access page of the Component palette, and where in the Database Application Developer's Guide you can get more information about them:

Component	Purpose
TDataSource	Acts as a conduit between other data access components and data-aware visual controls. For more information about TDataSource, see Chapter 6, "Working with data sources."
TTable	Represents a dataset that retrieves all columns and records from a database table. For more information about TTable, see Chapter 8, "Working with tables."
TQuery	Represents a dataset that retrieves a subset of columns and records from one or more local or remote database tables based on an SQL query. For more information about TQuery, see Chapter 9, "Working with queries."
TStoredProc	Represents a dataset that retrieves one or more records from a database table based on a stored procedure defined for a database server. For more information about TStoredProc, see Chapter 10, "Working with stored procedures."
TDatabase	Encapsulates a client/server connection to a single database in one session. For more information about TDatabase, see Chapter 3, "Connecting to databases."
TSession	Represents a single session in a multi-threaded database application. Each session can have multiple database connections as long as each thread associated with a particular database has its own session. For more information about TSession, see Chapter 3, "Connecting to databases."
TBatchMove	Encapsulates a dataset used to move data from one table to another en masse. For more information about TBatchMove, see Chapter 16, "Working with TBatchMove."
TReport	Encapsulates a ReportSmith report in an application. For more information about TReport, see Chapter 14, "Using reports."
TUpdateSQL	Represents SQL INSERT, UPDATE, and DELETE statements that can be used to update the read-only result sets of some queries. For more information about TUpdateSQL, see Chapter 15, "Working with cached updates."


Data controls summary

C++Builder provides data-aware visual components, called visual controls, that encapsulate user interaction with your application’s data sources. You design a user interface with these controls. You place these controls on the forms in your application. Each control is linked to one or more fields or records, and controls how a user sees a field or record in your application.

You link visual controls to data-access components through a data source component. A data-source component acts as a conduit between an application’s low-level data-access interactions and the high-level view of data its users are provided.

The following table summarizes the data-aware visual controls that appear on the Data Controls page:

Component	Purpose
TDBGrid	Display and edit dataset records in tabular format.
TDBNavigator	Cursor through dataset records; enable Edit and Insert states; post new or modified records; cancel edit mode; refresh data display.
TDBText	Display a field as a label.
TDBEdit	Display and edit a field in an edit box.
TDBMemo	Display and edit multi-line or blob text in a scrollable, multi-line edit box.
TDBImage	Display and edit a graphics image or binary blob data.
TDBListBox	Display a list of choices for entry in a field.
TDBComboBox	Display an edit box and drop-down list of choices for edit and entry in a field.
TDBCheckBox	Display and set a Boolean field condition in a check box.
TDBRadioGroup	Display and set exclusive choices for a field in a radio button group.
TDBLookupListBox	Display a list of choices derived from a field in another dataset for entry into a field.
TDBLookupComboBox	Display an edit box and drop-down list of choices derived from a field in another dataset for edit and entry in a field.
TDBCtrlGrid	(Client/Server Suite and Developer only). Display and edit records in a tabular grid, where each cell in the grid contains a repeating set of data-aware components and one record.


Using TTable

A table component is the most fundamental and flexible dataset component class in C++Builder. It gives you access to every row and column in an underlying database table, whether it is from Paradox, dBASE, an ODBC-compliant database such as Microsoft Access, 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.

Creating a table component

To create a table component:

1	Place a table component from the Data Access page of the Component palette in a data module or on a form, and set its Name property to a unique value appropriate to your application.

2	Set the DatabaseName of the component to the name of the database to access.

3	Set the TableName property to the name of the table in the database. You can select tables from the drop-down list if the DatabaseName property is already specified.

4	Place a data source component in the data module or on the form, and set its DataSet property to the name of the table component. The data source component is used to pass a result set from the table to data-aware components for display.

5	To view data from a table, place a data-aware component from the Data Controls page of the Component palette on a form and set its DataSource property to the name of the table’s data source.  Set the table component’s Active property to true to display data in the data-aware component.

Note:	These are general steps for creating a table component. There are additional properties you may need to set because of application requirements.


Searching on a table

You can search a table for specific records using the generic search methods Locate and Lookup. These methods enable you to search on any type of columns in any table, whether or not they are indexed or keyed.

Table components also support Goto and Find.  While these methods are documented here to allow you to work with legacy applications, you should usually use Lookup and Locate in your new applications. Moreover, you may see performance gains in existing applications if you convert them to use the new method.

Using Locate

Locate moves the cursor to the first row matching a specified set of search criteria. In its simplest form, you pass Locate the name of a column to search, a field value to match, and an options flag specifying whether the search is case-insensitive or if it can use partial-key matching. For example, the following code moves the cursor to the first row in the CustTable where the value in the Company column is "Professional Divers, Ltd.":

{

bool LocateSuccess;

TLocateOptions SearchOptions;

SearchOptions << loPartialKey;

LocateSuccess = CustTable->Locate(“Company”, “Professional Divers, Ltd.”,

SearchOptions);

}

If Locate finds a match, the first record containing the match becomes the current record. Locate returns true if it finds a matching record, false if it does not. If a search fails, the current record does not change.

The real power of Locate comes into play when you want to search on multiple columns and specify multiple values to search for.  Search values are variants, which enables you to specify different data types in your search criteria. To specify multiple columns in a search string, separate individual items in the string with semi-colons.

Because search values are variants, if you pass multiple values, you must either pass a variant array type as an argument (for example, the return values from the Lookup method), or you must construct the variant array on the fly using the VarArrayOf function. The following code illustrates a search on multiple columns using multiple search values and partial-key matching:

{

  Variant tmp(OPENARRAY(int, (0,1)), vtInteger);

  tmp << (int)(“Sight Diver”);

  tmp << (int)(“P”);

VarArrayOf(&tmp,1);

}

Locate uses the fastest possible method to locate matching records. If the columns to search are indexed and the index is compatible with the search options you specify, Locate uses the index. Otherwise, Locate creates a BDE filter for the search.

Using Lookup

Lookup searches for the first row that matches specified search criteria. If it finds a matching row, it forces the recalculation of any calculated fields and lookup fields associated with the dataset, then returns one or more fields from the matching row. Lookup does not move the cursor to the matching row; it only returns values from it.

In its simplest form, you pass Lookup the name of column to search, the field value to match, and the field or fields to return. For example, the following code looks for the first row in the CustTable where the value in the Company column is "Professional Divers, Ltd.", and returns the company name, a contact person, and a phone number for the company:

{

Variant LookupResults;

LookupResults = CustTable->Lookup(“Company”, “Professional Divers, Ltd.”, “Company;

Contact; Phone”);

}

Lookup returns values for the specified fields from the first matching record it finds. Values are returned as variants. If more than one return value is requested, Lookup returns a variant array. If there are no matching records, Lookup returns a Null variant. For more information about variant arrays, see the C++ Language Reference.

The real power of Lookup comes into play when you want to search on multiple columns and specify multiple values to search for. To specify strings containing multiple columns or result fields, separate individual in the string items with semi-colons.

Because search values are variants, if you pass multiple values, you must either pass a variant array type as an argument (for example, the return values from the Lookup method), or you must construct the variant array on the fly using the VarArrayOf function. The following code illustrates a lookup search on multiple columns:

{

  Variant tmp(OPENARRAY(int, (0, 1)), vtInteger);

  tmp << (int)(“Sight Diver”, 0);

  tmp << (“Christiansted”, 1);

  Variant LookupResults;

  LookupResults = CustTable->Lookup(“Company;City”, tmp, “Company;Addr1;Addr2;State;Zip”);

}

Lookup uses the fastest possible method to locate matching records. If the columns to search are indexed Lookup uses the index. Otherwise, Lookup creates a BDE filter for the search.


Using TQuery

A query component encapsulates an SQL statement that returns a set of related rows and columns from one or more database tables. SQL (Structured Query Language) is an industry-standard relational database language that is used by most remote server-based database vendors, such as Sybase, Oracle, InterBase, and SQL Server.

C++Builder query components can be used with remote database servers (C++Builder Client/ Server Suite only), with Paradox and dBASE, and with OBDC-compliant databases such as Microsoft Access and FoxPro.

A query component differs from a table component in two significant ways. It can:

Access more than one table at a time (called a "join" in SQL).

Automatically access a subset of rows and columns in its underlying table(s), rather than always returning all rows and columns and requiring you to set ranges and filters to restrict row access.

The data returned by a query component depends on the selection criteria you specify in the component’s SQL property at design time or at run time. The selection criteria is in the form of an SQL statement, and can be

Static, where all parameters in the statement are specified at design time, or

Dynamic, where some or all of a statement’s parameters are set at run time.

In addition to encapsulating standard SQL features, query components also offer a powerful ability not found on most remote database servers: the ability to create "heterogeneous joins," queries against more than one server or table type.

Choosing between query and table components

Table components are full-featured, flexible, and easily used data access components that are sufficient for many database applications.

If you are a desktop client/server developer, you are familiar with the table component paradigm, and know its strengths. Query components offer different capabilities from table components, and are useful when you need to:

n	Query data in multiple tables.

n	Restrict data access to a subset of rows and columns across tables.

n	Write applications that require SQL syntax for compatibility with other SQL applications.

If you are a client/server developer used to working with remote databases, you are familiar with SQL, and know the strength in query-based data access. On the other hand, table components may occasionally offer you data access alternatives, such as ranges, that make them attractive.

Creating a query component

To create a query component:

1	Place a query component from the Data Access page of the Component palette in a data module, and set its Name property appropriately for your application.

2	Set the DatabaseName property of the component to the name of the database to query. DatabaseName can be a Borland Database Engine (BDE) alias, or an explicit directory path and database name. If your application uses database components, DatabaseName can be set to a local alias defined for the database component instead.

3	Specify an SQL statement in the SQL property of the component, and optionally specify any parameters for the statement in the Params property. For more information, see "Setting the SQL property at design time" in the Database Applications Developers Guide.

4	Place a data source component in the data module, and set its DataSet property to the name of the query component. The data source module is used to return a result set from the query to data-aware components for display.

Note:	These are the general steps for creating a query component. Depending on the type of query you create (static or dynamic), there may be additional properties you need to set before executing a query.


Using TStoredProc

A stored procedure component encapsulates a stored procedure in a database on a remote server. A stored procedure is a set of statements, stored as part of a remote server’s database metadata (like tables, indexes, and domains). A stored procedure performs a frequently-repeated database-related task on the server and passes results to a client application, such as a C++Builder database application. The stored procedure component enables C++Builder applications to execute server stored procedures.

Often, operations that act upon large numbers of rows in database tables—or that use aggregate or mathematical functions—are candidates for stored procedures. By moving these repetitive and calculation-intensive tasks to the server, you improve the performance of your database application by:

Taking advantage of the server’s usually greater processing power and speed.

Reducing the amount of network traffic since the processing takes place on the server where the data resides.

For example, consider an application that needs to compute a single value: the standard deviation of values over a large number of records. To perform this function in your C++Builder application, all the values used in the computation must be fetched from the server, resulting in increased network traffic. Then your application must perform the computation. Because all you want in your application is the end result—a single value representing the standard deviation—it would be far more efficient for a stored procedure on the server to read the data stored there, perform the calculation, and pass your application the single value it requires.

See your server’s database documentation for more information about its support for stored procedures.

Creating a stored procedure component

To create a stored procedure component for a stored procedure on a database server:

1	Place a stored procedure component from the Data Access page of the Component palette in a data module.

2	Set the DatabaseName property of the stored procedure component to the name of the database in which the stored procedure is defined. DatabaseName must be a BDE alias.

3	Set the StoredProcName property to the name of the stored procedure to use, or select its name from the drop-down list for the property.

4	Specify input parameters, if necessary, in the Params property. You can use the Parameters editor to set input parameters.

Note:	The Parameters editor also lets you see the output parameters used by the procedure to return results to your application.

Understanding input parameters

Use input parameters to pass values from an application to a stored procedure.

Many stored procedures require you to pass them a series of input arguments, or parameters, to specify what and how to process. In C++Builder, the Parameters editor retrieves information about input and output parameters from the server. For some servers, all of the information required to run the stored procedure may not be accessible. In this case, you may need to enter information about the type of parameter (input, output, result) and/or the data type of the parameter in order to use the procedure. You will also need to enter the value that you will be passing to the stored procedure as an input parameter. The order in which the input parameters are displayed is significant, and is determined by the stored procedure definition on the server. If you are not sure of the ordering of the input and output parameters for a stored procedure, use the Parameters editor.

Setting input parameters at design time

At design time, the easiest and safest way to view and edit input parameters is to invoke the Parameters editor. The Parameters editor lists input parameters in the correct order, and lets you assign values to them.

To invoke the Parameters editor, click the ellipsis in the Params property value box in the Object Inspector or:

Select the stored procedure component.

Invoke the context menu.

Choose Define Parameters.

The StoredProc Parameters editor

The Parameter name list box displays all input, output, and result parameters for the procedure. Information on input and output parameters is retrieved from the server. For some servers, not all parameter information is accessible. In this case, you must provide the missing parameter information.  If you are sure that the stored procedure is valid, but no parameter names are displayed, use SQL/Database Explorer to look for the stored procedure and inspect its text.

The Parameter type combo box describes whether a parameter selected in the list box is an input, input/output, output, or result parameter. If a server’s stored procedure allows it, a single parameter may be both an input and output. If the parameter type information is missing, you must set the parameter type for it.

Note: Sybase, MS-SQL, and Informix servers generally do not return information on parameter types.

The Data type combo box lists the data type for a parameter selected in the list box. The data type can be any standard SQL data type except BLOB and arrays of data types. If the data type has not been provided from the server, you must set the data type. Some servers will also allow you to override the default data type here.

Note:  Informix servers generally do not return information on data types.

Input parameters are passed by value from your application to a stored procedure. The Value edit box enables you to enter a value for a selected input parameter, and the NULL Value check box enables you to set a NULL value for the selected input parameter if its data type supports NULL values. Values should be assigned based on the declared data type. Each input parameter must be assigned either a value or a NULL value.

The Add button enables you to add parameters to a stored procedure definition. The Delete button enables you to remove parameters, and the Clear button removes all parameters from the list. If the stored procedure that you have named in the StoredProcName property is valid, these buttons will be disabled because you cannot modify this data. These buttons will be enabled when the stored procedure is not named, is invalid, or if C++Builder is unable to retrieve this information from the server. If you are sure that the stored procedure is valid, but no parameter names are displayed, use SQL/Database Explorer to look for the stored procedure and inspect its text and then add parameters as appropriate.

You will not be able to add, delete, or clear parameters for servers that pass parameter information to C++Builder except if you are working with Oracle overloaded stored procedures. For more information on working with Oracle overloaded stored procedures, see “Working with Oracle overloaded stored procedures” in the Database Applications Developers Guide.

To signal the end of parameter definition, choose OK.

Important: Defining parameters at design time also prepares the stored procedure for execution. A stored procedure must be prepared before it can be executed at run time.

Setting parameters at run time

To assign values to parameters at run time, access the Params property directly. Params is an array of parameter strings. For example, the following code assigns the text of an edit box to the first string in the array:

StoredProc1->Params[0]->Items[0]->AsString = Edit1->Text;

You can also access parameters by name using the ParamByName method:

StoredProc1->ParamByName(“Company”)->AsString = Edit1->Text;

The ParamBindMode property determines how the elements of the Params array will be matched with stored procedure parameters. If the ParamBindMode property is set to pbByName (the default), parameters will be bound based on their names in the stored procedure. If ParamBindMode is set to pbByNumber, parameters will be bound based on the order in which they are defined in the stored procedure.

ParamBindMode = pbByName;

Preparing and executing a stored procedure

To use a stored procedure, you must prepare it and execute it. You can prepare a stored procedure at:

Design time, by choosing OK in the Parameters editor.

Run time, by calling the Prepare method of the stored procedure component.

For example, the following code prepares a stored procedure for execution:

StoredProc1->Prepare();

Note:	You can prepare a stored procedure both at run time and at design time. In fact, if your application changes parameter information at run time, such as when using Oracle overloaded procedures, you should prepare the procedure again.

To execute a prepared stored procedure, call the ExecProc method for the stored procedure component. The following code illustrates code that prepares and executes a stored procedure:

StoredProc1->Params[0]->Items[0]->AsString = Edit1->Text;

StoredProc1->Prepare();

StoredProc1->ExecProc();

When you execute a stored procedure, it returns either output parameters or a result set. There are two possible return types: singleton returns, which return a single value or set of values, and result sets, which return many values, much like a query.

Understanding output parameters and result sets

A stored procedure returns values in an array of output parameters. Return values can be either asingleton result or a result set with a cursor, if the server supports it.

To access a stored procedure’s output parameters at run time, you can index into the Params string list, or you can use the ParamByName method to access the values. The following statement both set the value of a edit box based on output parameters:

Edit1->Text = StoredProc1->Params[6]->Items[0]->AsString;

Edit1->Text = StoredProc1->ParamByName(“Company”)->AsString;

Note:	If a stored procedure returns a result set, you may find it more useful to access and display return values in standard data-aware controls.

Working with result sets

On some servers, such as Sybase, stored procedures can return a result set similar to a query. Applications can use data aware controls to display the output of such stored procedures.

To display the results from a stored procedure in data-aware controls:

1	Place a datasource component on the data module.

2	Set the DataSet property of the datasource to the name of the stored procedure component from which to receive data.

3	Set the DataSource properties of the data-aware controls to the name of the datasource component.

The data-aware controls can now display the results from a stored procedure when the Active property for the stored procedure component is true.


Using TBatchMove

The TBatchMove component enables you to copy, append, delete, and update tables and data, and is most often used to:

Download data from a server to a local data source for analysis or other operations.

Move a desktop database into tables on a remote server as part of an upsizing operation.

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. It inherits many of its fundamental properties and methods from TDataSet.

Creating a batch move component

To create a batch move component:

1	Place a TBatchMove component from the Data Access page of the Component palette in a data module or on a form, and set its Name property to a unique value appropriate to your application.

2	Set the Source property of the component to the name of the table to copy, append, or update from. You can select tables from the drop-down list of available table components.

3	Set the Destination property to the name of the table to create, append to, or update. If you are appending, updating, or deleting, Destination must be the name of an existing table. You can select tables from the drop-down list of available table components in these cases.

4	Set the Mode property to indicate the type of operation to perform. Valid operations are batAppend (the default), batUpdate, batAppendUpdate, batCopy, and batDelete. For more information about these modes, see "Specifying a batch move mode" in the Database Applications Developers Guide.

5	Optionally set column mappings using the Mappings property. You need not set this property if you want batch move to match column based on their position in the source and destination tables.

6	Optionally specify the ChangedTableName, KeyViolTableName, and ProblemTableName properties. Batch move stores problem records it encounters during the batch move operation in the table specified by ProblemTableName. If you are updating a Paradox table through a batch move, key violations can be reported in the table you specify in KeyViolTableName. ChangedTableName lists all records that changed in the destination table as a result of the batch move operation. If you do not specify these properties, these error tables are not created or used.


Using Filters

An application is frequently interested in only a subset of records within a dataset. For example, you may be interested in retrieving or viewing only those records for companies based in California in your customer database, or you may want to find a record that contains a particular set of field values. C++Builder supports filtering of a table or query to handle both of these requirements. Using filters you can temporarily restrict an application’s view of data.

There are three ways to filter a dataset:

Restricting record visibility at the time of record retrieval using an OnFilterRecord event handler.

Setting the Filter property of the dataset.

Finding a record in a dataset that matches search values using the Locate method for the dataset. If you use Locate, C++Builder automatically generates a filter for you at run time if it needs to.

Note:	Filters are applied to every record retrieved in a dataset. When you want to filter large volumes of data, it may be more efficient to use a query to restrict record retrieval, or to set a range on an indexed table rather than using filters.

Setting the Filter property of the dataset

The simplest and most common way to use filtering is to turn on filtering for the entire dataset. To turn on filtering a dataset, set the Filtered property to true. With filtering on, the dataset generates an event for each record in the dataset. In handling that event, you can determine whether to accept or “filter out” each record. You could also supply a filter condition as the value of the Filter property instead of writing an event handler.

Note:  When filtering is on, user edits to a record may mean that the record no longer meets a filter’s test conditions which can make the current record disappear if it no longer passes the filter. When this occurs, the next record that passes the filter condition becomes the current record.

You can also filter records programmatically by creating a standard control (such as an edit box). The following code example illustrates this.

void __fastcall TForm1::Button1Click(TObject *Sender)

{

Table1->Filtered = true;

Table1->Filter = Edit1->Text;

Table1->Refresh;

}

Some other filter examples:

CustTable->Filter = “State = California”;

Table1->Filter = “PatientAge >= 18”;

Turning filters on and off at run time

At run time you can turn filtering off by setting the Filtered property to false, and you can turn it on by setting the Filtered property to true. If you turn off filtering, all records in a dataset are available to your application, but in order for the application to see all records, you must also call the dataset’s Refresh method. For example:

{

CustTable->Filtered = false;

CustTable->Refresh();

}

If you turn on filtering at run time, you must also call Refresh again to make the filter take effect. The current record may no longer pass the filter condition, and may disappear. If that happens, the next record that passes the filter condition becomes the current record.

Filtering records with the OnFilterRecord event handler

To restrict visible records in a dataset to a subset of those that match a certain set of criteria, follow these steps:

1	Write an OnFilterRecord event handler for the dataset.

2	Set the Filtered property for the dataset to true.

When Filtered is true, a dataset generates an OnFilterRecord event for each record it fetches. The event handler for the OnFilterRecord tests each record, and only those that meet the Filter’s conditions are visible in the application.

Note:	When filtering is on, user edits to a record may mean that the record no longer meets a filter’s test conditions. The next time the record is retrieved from the dataset, it may therefore “disappear.” If that happens, the next record that passes the filter condition becomes the current record.

Switching filter event handlers at run time

You can change how records are filtered at run time by assigning a different handler to the OnFilterRecord event for a dataset. To apply the new filter to the dataset after changing it, call the dataset’s Refresh method:

Table1.OnFilterRecord := Query2FilterRecord;

Refresh;

Filtering datasets with the Filter property

You can filter dataset components to restrict which records you want to see in the dataset by setting these properties:

Set the value of the Filter property for the dataset.

Set the Filtered property for the dataset to true.

Fine-tune the filter by modifying the FilterOptions property.

This method of filtering datasets is most valuable when the filter will be set at run-time. The value in the Filter property cannot be compared to a value outside of the table, for example, it cannot be compared to a value in an edit box. To change the value of a filter “on the fly”, see the section on “Filtering datasets with the OnFilterRecord event”.

The Filter property is a string that lets you set conditions on one or more fields of your dataset. You can compare fields to literal values and constants using the comparison operators in the following table and the AND, NOT, and OR operators to combine comparisons. You can also compare a field to a field in an edit control (see the example in “Using the Filtered property” for an example). You must enclose field names that contain spaces within square brackets. :

The following are some examples that can be used to set filtering conditions:

PatientAge >= 18

State = ‘FL’

(PatientAge >= 18) AND (Balance > 0)

(Temperature < 212) AND (NOT Windy)

In the above example Windy is a logical field.

(SalePrice < $10,000) OR (Terms > 30)

[Sale Date] > ‘1/1/96’.


Writing an OnFilterRecord event handler

A filter for a dataset is an event handler that responds to OnFilterRecord events generated by the dataset for each record it retrieves. At the heart of every filter handler is a test that determines if a record should be included in those that are visible to the application.

To indicate whether a record passes the filter condition, your filter handler must set an Accept parameter to true to include a record, or false to exclude it. For example, the following filter displays only those records with the State field set to “CA”:

void __fastcall TForm1::CustTableFilterRecord(TDataSet *DataSet, Boolean &Accept)

{

Accept = (String(“CA”) == DataSet->FieldByName(“State”)->AsString);

}


Using TDatabase

A database component encapsulates the connection to a single database within an application. If you do not need to control database connections (such as specifying a transaction isolation level), do not create database components. Temporary database components are created automatically at run time when an application attempts to open a table for which there is not already a database component. But if you want to control the persistence of database connections, logins to a database server, property values of database aliases, or transactions, then you must create a database component for each desired connection.

Creating a database component

The Data Access page of the Component palette contains a database component you can place in a data module or form. The main advantages to creating a database component at design time are that you can set its initial properties and write OnLogin events for it. OnLogin offers you a chance to customize the handling of security on a database server when a database component first attaches the server.


Logging into a server

You can control server login with a database component's LoginPrompt property and the its OnLogin method. Controlling login is essential to server security, and may be required by your remote server.

LoginPrompt specifies whether your users are prompted to log in to a database server the first time your application attempts to connect to a database requiring a login. If true (the default), your application displays a standard Login dialog box. The Login dialog box prompts for a user name and password. A mask symbol (an asterisk by default) displays for each character entered in the Password edit box.

If you set LoginPrompt to false, the standard Login dialog box is not displayed at run time when an application attempts to connect to a database server that requires a login. Instead, your application must provide the user name and password, either through the Params property or the OnLogin event for the database component. At design time, the standard Login dialog box appears when you first connect to a remote database server if LoginPrompt is false and the Params property does not contain user name and password entries.

Note:	Storing hard-coded user name and password entries in the Params property or in code for an OnLogin event can compromise server security.

The Params property is a string list containing the database connection parameters for the BDE alias associated with a database component. Some typical connection parameters include path statement, server name, schema caching size, language driver, and SQL query mode.

At design time you can create or edit connection parameters in three ways:

Use the SQL/Database Explorer or BDE Configuration utility to create or modify BDE aliases, including parameters. For more information about these utilities, see their online Help files.

Double-click the Params property in the Object Inspector to invoke the String List editor. To learn more about the String List editor, see the C++Builder User’ s Guide.

Double-click a database component in a data module or form to invoke the Database Properties editor.

When you first invoke the Database Properties editor, the parameters for the BDE alias are not visible. To see the current settings, click Defaults. The current parameters are displayed in the Parameter overrides memo box. You can edit existing entries or add new ones. To clear existing parameters, click Clear. Changes you make take effect only when you click OK.


Handling transactions

There are two ways to manage transactions in a C++Builder database application using implicit and explicit control. It especially focuses on:

Using the methods and properties of the TDatabase component that enable explicit transaction control.

Using passthrough SQL with TQuery components to control transactions.

C++Builder also supports local transactions, transactions made against local Paradox and dBASE tables.

Understanding transactions

When you create a database application using C++Builder, C++Builder provides transaction control for all database access, even against local Paradox and dBASE tables. A transaction is a group of actions that must all be carried out successfully on one or more tables in a database before they are committed (made permanent). If one of the actions in the group fails, then all the actions fail.

Transactions ensure database consistency even if there are hardware failures. It also maintains the integrity of data while permitting concurrent multiuser access.

For example, an application might update the ORDERS table to indicate that an order for a certain item was taken, and then update the INVENTORY table to reflect the reduction in inventory available. If there were a hardware failure after the first update but before the second, the database would be in an inconsistent state because the inventory would not reflect the order entered. Under transaction control, both statements would be committed at the same time. If one statement failed, then both would be undone (rolled back).

Using implicit transactions

By default, C++Builder provides implicit transaction control for your applications through the Borland Database Engine (BDE). When an application is under implicit transaction control, C++Builder uses a separate transaction for each record in a dataset that is written to the underlying database. It commits each individual write operation, such as Post and AppendRecord.

Using implicit transaction control is easy. It guarantees both a minimum of record update conflicts and a consistent view of the database. On the other hand, because each row of data written to a database takes place in its own transaction, implicit transaction control can lead to excessive network traffic and slower application performance.

If you explicitly control transactions, you can choose the most effective times to start, commit, and roll back your transactions. When you develop client applications in a multi-user environment, particularly when your applications run against a remote SQL server, such as Sybase, Oracle, Microsoft SQL, or InterBase,® or remote ODBC-compliant databases such as Access and FoxPro, you should control transactions explicitly.

Note:	If you used cached updating, you may be able to minimize the number of transactions you need to use in your applications. For more information about cached updates, see Chapter 15, "Working with cached updates," in the Database Application Developer's Guide.

Working with explicit transaction control

There are two mutually exclusive ways to control transactions explicitly in a C++Builder database application:

Use the methods and properties of the TDatabase component.

Use passthrough SQL in a TQuery component. Passthrough SQL is only meaningful in the C++Builder Client/Server Suite, where you use SQL Links to pass SQL statements directly to remote SQL or ODBC servers.

The main advantage to using the methods and properties of a database component to control transactions is that it provides a clean, portable application that is not dependent on a particular database or server.

The main advantage to passthrough SQL is that you can use the advanced transaction management capabilities of a particular database server, such as schema caching. To understand the advantages of your server’s transaction management model, see your database server documentation.

Using TDatabase methods and properties

The following table lists the methods and properties of TDatabase that are specific to transaction management, and describes how they are used:

Method or property	Purpose
Commit	Commits data changes and ends the transaction.
Rollback	Undoes data changes and ends the transaction.
StartTransaction	Starts a transaction.
TransIsolation	Specifies the transaction isolation level for a transaction.
StartTransaction, Commit, and Rollback are methods your application can call at run time to start transactions, control the duration of transactions, and save or discard changes made to the database.

TransIsolation is a database component property that enables you to control how a transaction interacts with other simultaneous transactions when they work with the same tables. In particular, it affects how much a transaction "sees" of other transactions’ changes to a table.

Starting a transaction

When you start a transaction, all subsequent statements that read from and write to the database occur in the context of that transaction. Each statement is considered part of a group. Write changes made by any statement in the group must be successfully committed to the database, or every change made by every write statement made in the group are undone.

Grouping statements is useful when statements are dependent upon one another. Consider a bank transaction at an Automated Teller Machine (ATM). When a customer decides to transfer money from a savings account to a checking account, two changes must take place in the bank’s database records:

The savings account must be debited.

The checking account must be credited.

If, for any reason, one of these actions cannot be completed, then neither action should take place. Because these actions are so closely related, they should take place within a single transaction.

To start a transaction in a C++Builder application, call a database component’s StartTransaction method:

DatabaseInterBase->StartTransaction();

All subsequent database actions take place in the context of the newly started transaction until the transaction is explicitly terminated by a subsequent call to Commit or Rollback.

How long should you keep a transaction going? Ideally, only as long as necessary. The longer a transaction is active, the more simultaneous users that access the database, and the more concurrent, simultaneous transactions that start and end during the lifetime of your transaction, the greater the likelihood that your transaction will conflict with another when you attempt to commit your changes.

Committing a transaction

To make database changes permanent, a transaction must be committed using a database component’s Commit method. Executing a commit statement saves database changes and ends the transaction. For example, the following statement ends the transaction started in the previous code example:

DatabaseInterBase->Commit();

Note:	Commit should always be attempted in a try {}catch(…) statement. If a transaction cannot commit successfully, you can attempt to handle the error, and perhaps retry the operation.

Rolling back a transaction

To discard database changes, a transaction must roll back its changes using Rollback. Rollback undoes a transaction’s changes and ends the transaction. For example, the following statement rolls back a transaction:

DatabaseInterBase->Rollback();

Rollback usually occurs in:

Exception handling code when you cannot recover from a database error.

Button or menu event code, such as when a user clicks a Cancel button.

Using the TransIsolation property

TransIsolation specifies the transaction isolation level for a database component’s transactions. Transaction isolation level determines how a transaction interacts with other simultaneous transactions when they work with the same tables. In particular, it affects how much a transaction "sees" of other transaction’s changes to a table. Before changing or setting TransIsolation, you should be familiar with transactions and transactions management in C++Builder.

The default setting for TransIsolation is tiReadCommitted. The following table summarizes possible values for TransIsolation and describes what they mean:

Isolation level	Meaning
tiDirtyRead	Permit reading of uncommitted changes made to the database by other simultaneous transactions. Uncommitted changes are not permanent, and might be rolled back (undone) at any time. At this level your transaction is least isolated from the changes made by other transactions.
tiReadCommitted	Permit reading only of committed (permanent) changes made to the database by other simultaneous transactions. This is the default isolation level.
tiRepeatableRead	Permit a single, one time reading of the database. Your transaction cannot see any subsequent changes to data by other simultaneous transactions. This isolation level guarantees that once your transaction reads a record, its view of that record will not change. At this level your transaction is most isolated from changes made by other transactions.
Database servers may support these isolation levels differently or not at all. If the requested isolation level is not supported by the server, then C++Builder will use the next highest isolation level. The actual isolation level used by each type of server is listed in the following table. For a detailed description of how each isolation level is implemented, see your server documentation.

TransIsolation setting	Paradox and dBASE	Oracle	Sybase and Microsoft SQL servers	InterBase
Dirty read	Dirty read	Read committed	Read committed	Read committed
Read committed (Default)	Not supported	Read committed	Read committed	Read committed
Repeatable read	Not supported	Repeatable read  (READ ONLY)	Not supported	Repeatable Read
Note:	When using transactions with local Paradox and dBASE tables, set TransIsolation to tiDirtyRead instead of using the default value of tiReadCommitted. A BDE error is returned if TransIsolation is set to anything but tiDirtyRead for local tables

If an application is using ODBC to interface with a server, the ODBC driver must also support the isolation level. For more information, see your ODBC driver documentation.

Using passthrough SQL

To be able to use passthrough SQL to control a transaction you must:

Use the C++Builder Client/Server Suite.

Install the proper SQL Links drivers. If you chose the Standard installation when installing C++Builder, all SQL Links drivers are already properly installed.

Configure your network protocol correctly. See your network administrator for more information.

Have access to a database on a remote server.

Use the BDE Configuration utility to set SQLPASSTHROUGHMODE to NOT SHARED.

With passthrough SQL, you use a TQuery, TStoredProc, or TUpdateSQL component to send an SQL transaction control statement directly to a remote database server. The BDE does not process the SQL statement. Using passthrough SQL enables you to take direct advantage of the transaction controls offered by your server, especially when those controls are non-standard.

Setting SQLPASSTHROUGHMODE

SQLPASSTHROUGHMODE specifies whether the BDE and passthrough SQL statements can share the same database connections. In most cases, SQLPASSTHROUGHMODE is set to SHARED AUTOCOMMIT. If however, you want to pass SQL transaction control statements to your server, you must use the BDE Configuration utility to set the BDE SQLPASSTHROUGHMODE to NOT SHARED. For more information about SQLPASSTHROUGH modes, see the online help for the BDE Configuration utility.

Note:	When SQLPASSTHROUGHMODE is NOT SHARED, you must use separate database components for TQuery components that pass SQL transaction statements to the server and those other dataset components that do not.

Using local transactions

The BDE supports local transactions against Paradox and dBASE tables. From a coding perspective, there is no difference to you between a local transaction and a transaction against a remote database server.

When a transaction is started against a local table, updates performed against the table are logged. Each log record contains the old record buffer for a record. When a transaction is active, records that are updated are locked until the transaction is committed or rolled back. On rollback, old record buffers are applied against updated records to restore them to their pre-update states.

Understanding the limitations of local transactions

The following limitations apply to local transactions

Automatic crash recovery is not provided.

Data definition statements are not supported.

For Paradox, local transactions can only be performed on tables with valid indexes. Data cannot be rolled back on Paradox tables that do not have indexes.

Transactions cannot be run against temporary tables.

Transactions cannot be run against the BDE ASCII driver.

Closing a cursor on a table during a transaction rolls back the transaction unless:

Several tables are open.

The cursor is closed on a table to which no changes were made.


Using TSession

Each time an application runs, C++Builder creates a default TSession component for it. A session component provides global control over all database connections in an application. If you do not create multi-threaded database applications, you need only concern yourself with the default session in your application.

A multi-threaded database application is a single application that attempts to run two or more simultaneous operations, such as SQL queries, against the same database. If you create a multi-threaded database application, then each additional thread after the first requires its own session component.

Creating a session component

If you are creating a multi-threaded database application, then you need to create additional session components. To create a session component, place a session component from the Data Access page onto a data module used in your project.

The following table lists the properties of TSession, what they are used for, their default values if any, and whether they are available at design time:

Property	Purpose	Default	Design time  access
Active	true, starts the BDE session. false, disconnects datasets and stops the session.	false	Yes
Databases	Specifies an array of all active databases in the session.		No
DatabaseCount	Provides an integer value specifying the number of currently active databases in the session.		No
KeepConnections	true, maintain database connection(s) even if there are no open datasets. false, close database connection(s) when there are no open datasets.	true	Yes
Name	Names a session component (for example, Session1).		Yes
NetFileDir	Specifies the directory path of the Paradox network control file, which enables sharing of Paradox tables on network drives.		Yes
PrivateDir	Specifies the path in which to store temporary files (for example, files used to process local SQL statements).		Yes
SessionName	Specifies the name of the session that must be used by database components to link themselves to a particular session.		Yes


Using TSessionList

Multi-threaded applications need to manage sessions through a TSessionList component. You never need to create your own session list component, but you may certainly need to manipulate the properties and use the methods of the default session list.

A default session list component, called Sessions, is created for you whenever you start a database application.

Sessions is a component of type TSessionList. You use the properties and methods of Sessions to keep track of multiple sessions in a multi-threaded database application. The following table summarizes the properties and methods of the TSessionList component:

Property or  Method	Purpose
Count	Returns the number of sessions, both active and inactive, in the sessions list.
FindSession	Searches the session list for a session with a specified name, and returns a pointer to the session component, or nil if there is no session with the specified name. If passed a blank session name, FindSession returns a pointer to the default session, Session.
GetSessionNames	Returns a string list containing the names of all currently instantiated session components. This procedure always returns at least one string, ‘Default’ for the default session (note that the default session’s name is actually a blank string).
List	Returns the session component for a specified session name. If there is no session with the specified name, an exception is raised.
OpenSession	Creates and activates a new session or reactivates an existing session for a specified session name.
Sessions	Accesses the session list by ordinal value. Sessions is the default property, so you can call it like so Sessions[0], rather than Sessions.Sessions[0].


Specfiying a data source

A TDataSource component is a nonvisual database component that acts as a conduit between a dataset and data-aware components on a form that enable the display, navigation, and editing of the data underlying the dataset. All datasets must be associated with a data source component in order for their data to be displayed and manipulated in data-aware controls on a form. Similarly, each data-aware control needs to be associated with a data source component in order to have data to display and manipulate. You also use data source components to link datasets in master-detail relationships.

You place a data source component in a data module or form just as you place other nonvisual database components. You should place at least one data source component for each dataset component in a data module or form.

Using TDataSource properties

TDataSource has only a few published properties. The following sections discuss these key properties and how to set them at design time and run time.

Setting the DataSet property

The DataSet property specifies the name of the dataset from which the a data source component gets its data. At design time you can select a dataset from the drop down list in the Object Inspector. At run time you can switch the dataset for a data source component as needed. For example, the following code swaps the dataset for the CustSource data source component between Customers and Orders:

if (CustSource->DataSet == “Customers”)

CustSource->DataSet = “Orders”;

else

CustSource->DataSet = “Customers”;

You can also set the DataSet property to a dataset on another form to synchronize the data controls on the two forms. For example:

void __fastcall TForm2::FormCreate (TObject *Sender)

{

DataSource1->Dataset = Form1->Table1;

}

Setting the Name property

Name enables you to specify a meaningful name for a data source component that distinguishes it from all other data sources in your application. The name you supply for a data source component is displayed below the component’s icon in a data module.

Generally, you should provide a name for a data source component that indicates the dataset with which it is associated. For example, suppose you have a dataset called Customers, and that you link a data source component to it by setting the data source component’s DataSet property to "Customers." To make the connection between the dataset and data source obvious in a data module, you should set the Name property for the data source component to something like "CustomersSource".

Setting the Enabled property

The Enabled property determines if a data source component is connected to its dataset. When Enabled is true, the data source is connected to a dataset.

You can temporarily disconnect a single data source from its dataset by setting Enabled to false. When Enabled is false, all data controls attached to the data source component go blank and become inactive until Enabled is set to true. It is recommended, however, to control access to a dataset through a dataset component’s DisableControls and EnableControls methods because they affect all attached data sources.

Setting the AutoEdit property

The AutoEdit property of TDataSource specifies whether datasets connected to the data source automatically enter Edit state when the user starts typing in data-aware controls linked to the dataset. If AutoEdit is true (the default), C++Builder automatically puts the dataset in Edit state when a user types in a linked data-aware control. Otherwise, a dataset enters Edit state only when the application explicitly calls its Edit method.

Using TDataSource methods

TDataSource has three event handlers associated with it:

OnDataChange

OnStateChange

OnUpdateData

Using the OnDataChange event

OnDataChange is called whenever the cursor moves to a new record. When an application calls Next, Previous, Insert, or any method that leads to a change in the cursor position, then an OnDataChange is triggered.

This event is useful if an application is keeping components synchronized manually.

Using the OnUpdateData event

OnUpdateData is called whenever the data in the current record is about to be updated. For instance, an OnUpdateData event occurs after Post is called, but before the data is actually posted to the database.

This event is useful if an application uses a standard (non-data aware) control and needs to keep it synchronized with a dataset.

Using the OnStateChange event

OnStateChange is called whenever the state for a data source’s dataset changes. A dataset’s State property records its current state. OnStateChange is useful for performing actions as a TDataSource’s state changes.

For example, during the course of a normal database session, a dataset’s state changes frequently. To track these changes, you could write an OnStateChange event handler that displays the current dataset state in a label on a form. The following code illustrates one way you might code such a routine. At run time, this code displays the current setting of the dataset’s State property and updates it every time it changes:

void __fastcall TForm1::StateChange(TObject *Sender)

{

char S[10];

switch (CustTable->State)

{

case dsInactive: strcpy(S,“Inactive“);

case dsBrowse: strcpy(S, “Browse“);

case dsEdit: strcpy(S, “Edit“);

case dsInsert: strcpy(S, “Insert“);

case dsSetKey: strcpy(S, “SetKey“);

}

}

Similarly, OnStateChange can be used to enable or disable buttons or menu items based on the current state:

void __fastcall TForm1::StateChange( TObject *Sender)

{

if (CustTable->State == dsBrowse)

CustTableEditBtn->Enabled;

if (CustTable->State == (dsInsert|dsEdit|dsSetKey))

CustTableCancelBtn->Enabled;

...

}


Displaying data in a data control

To display data in a data control:

1	Place a data control from the Data Access page of the Component palette onto a form.

2	Set the DataSource property of the control to the name of a data source component from which to get data. A data source component acts as a conduit between the control and a dataset containing data.

3	Set the DataField property of the control to the name of a field to display, or select a field name from the drop-down list for the property.

If the Enabled property of the data control’s data source is true (the default), and the Active property of the dataset attached to the data source is also true, data is now displayed in the data control.

Note:	Two data controls, TDBGrid and TDBNavigator, access all available field components within a dataset, and therefore do not have DataField properties. For these controls, omit Step 3.

Enabling mouse, keyboard, and timer events

The Enabled property of a data control determines whether it responds to mouse, keyboard, or timer events, and passes information to its data source. The default setting for this property is true.

To prevent mouse, keyboard, or timer events from accessing a data control set its Enabled property to false. When Enabled is false, a data source does not receive information from the data control. The data control continues to display data, but the text displayed in the control is dimmed.

Enabling editing in controls on user entry

A dataset must be in dsEdit state to permit editing to its data. The AutoEdit property of the data source to which a control is attached determines if the underlying dataset enters dsEdit mode when data in a control is modified in response to keyboard or mouse events. When AutoEdit is true (the default), dsEdit mode is set as soon as editing commences. If AutoEdit is false, you must provide a TDBNavigator control with an Edit button (or some other method) to permit users to set dsEdit state at run time.

Updating fields

The ReadOnly property of a data control determines if a user can edit the data displayed by the control. If false (the default), users can edit data. To prevent users from editing data in a control, set ReadOnly to true.

Properties of the data source and dataset underlying a control also determine if the user can successfully edit data with a control and can post changes to the dataset.

The Enabled property of a data source determines if controls attached to a data source are able to display fields values from the dataset, and therefore also determine if a user can edit and post values. If Enabled is true (the default), controls can display field values.

The ReadOnly property of the dataset determines if user edits can be posted to the dataset. If false (the default), changes are posted to the dataset. If false, the dataset is read-only.

Note:	Table components have an additional, read-only run-time property CanModify that determines if a dataset can be modified. CanModify is set to true if a database permits write access. If CanModify is false, a dataset is read-only. Query components that perform inserts and updates are, by definition, able to write to an underlying database, provided that your application and user have sufficient write privileges to the database itself.

The following table summarizes the factors that determine if a user can edit data in a control and post changes to the database:

Data control ReadOnly property	Data source Enabled property	Dataset ReadOnly property	Dataset CanModify property (tables only)	Database write access	Can write to database?
false	true	false	true	Read/Write	Yes
false	true	false	false	Read-only	No
false	false	—	—	—	No
true	—	—	—	—	No
In all data controls except TDBGrid, when you modify a field, the modification is copied to the underlying field component in a dataset when you Tab from the control. If you press Esc before you Tab from a field, C++Builder abandons the modifications, and the value of the field reverts to the value it held before any modifications were made.

In TDBGrid, modifications are copied only when you move to a different record; you can press Esc in any record of a field before moving to another record to cancel all changes to the record.

When a record is posted, C++Builder checks all data-aware components associated with the dataset for a change in status. If there is a problem updating any fields that contain modified data, C++Builder raises an exception, and no modifications are made to the record.

Disabling and enabling data display

When your application iterates through a dataset or performs a search, you should temporarily prevent refreshing of the values displayed in data-aware controls each time the current record changes. Preventing refreshing of values speeds the iteration or search and prevents annoying screen-flicker.

DisableControls is a dataset method that disables display for all data-aware controls linked to a dataset. As soon as the iteration or search is over, your application should immediately call the dataset’s EnableControls method to re-enable display for the controls.

Usually you disable controls before entering an iterative process. The iterative process itself should take place inside a try{} while statement so that you can re-enable controls even if an exception occurs during processing. The catch clause should call EnableControls. The following code illustrates how you might use DisableControls and EnableControls in this manner:

CustTable->DisableControls();

try

{

CustTable->First(); // Go to first record, which sets EOF False

while(!CustTable->DB_EOF) //Cycle until EOF is True

{

// Process each record here

}

CustTable->Next(); // EOF False on success; EOF True when Next fails 	on last record

CustTable->EnableControls();

}

catch(...)

{

CustTable->EnableControls();

}

Refreshing data

The Refresh method for a dataset flushes local buffers and refetches data for an open dataset. You can use this method to update the display in data-aware controls if you think that the underlying data has changed because other applications have simultaneous access to the data used in your application.

Important

Refreshing can sometimes lead to unexpected results. For example, if a user is viewing a record deleted by another application, then the record disappears the moment your application calls Refresh. Data can also appear to change if another user changes a record after you originally fetched the data and before you call Refresh.


Opening and closing datasets

To read or write data in a table or through a query, an application must first open a dataset. There are two ways to open a dataset:

Set the Active property of the dataset to true, either at design time in the Object Inspector, or in code at run time:

CustTable.Active := true;

Call the Open method for the dataset at run time:

CustQuery.Open;

There are also two ways to close a dataset:

n	Set the Active property of the dataset to false, either at design time in the Object Inspector, or in code at run time:

CustQuery.Active := false;

n	Call the Close method for the dataset at run time:

CustTable.Close;

You need to close a dataset when you want to change any of its properties that affect the query, such as the DataSource property. At run time you may also want to close a dataset for other reasons specific to your application.


Setting dataset states

The state—or mode—of a dataset determines what can be done to its data. For example, when a dataset is closed, its state is dsInactive, meaning that nothing can be done to its data. A dataset is always in one state or another. At run time you can examine a dataset’s read-only State property to determine its current state. The following table summarizes possible values for the State property and what they mean:

Value	State	Meaning
dsInactive	Inactive	Dataset closed. Its data is unavailable.
DsBrowse	Browse	Dataset open. Its data can be viewed, but not changed. This is the default state of an open dataset.
DsEdit	Edit	Dataset open. The current row can be modified.
DsInsert	Insert	Dataset open. A new row can be inserted.
DsSetKey	SetKey	TTable only. Dataset open. Enables searching for rows based on indexed fields, or indicates that a SetRange operation is under way. A restricted set of data can be viewed, and no data can be changed.
DsCalcFields	CalcFields	Dataset open. Indicates that an OnCalcFields event is under way. Prevents changes to fields that are not calculated.
DsUpdateNew	UpdateNew	Internal use only.
DsUpdateOld	UpdateOld	Internal use only.
DsFilter	Filter	Dataset open. Indicates that a filter operation in under way. A restricted set of data can be viewed, and no data can be changed.
When an application opens a dataset, C++Builder automatically puts the dataset into dsBrowse mode. The state of a dataset changes as an application processes data. An open dataset changes from one state to another based on either the

Code in your application, or

C++Builder’s built-in behavior.

To put a dataset into dsBrowse, dsEdit, dsInsert, or dsSetKey states, call the method corresponding to the name of the state. For example, the following code puts CustTable into dsInsert state, accepts user input for a new record, and writes the new record to the database:

CustTable->Insert(); // Your application explicitly sets dataset state to Insert

AddressPromptDialog->ShowModal();

if (AddressPromptDialog->ModalResult == IDOK)

CustTable->Post(); // Ebony sets dataset state to Browse on successful completion

else

CustTable->Cancel(); // Ebony sets dataset state to Browse on cancel

This example also illustrates that C++Builder automatically sets the state of a dataset to dsBrowse when

The Post method successfully writes a record to the database. (If Post fails, the dataset state remains unchanged.)

The Cancel method is called.

Some states cannot be set directly. For example, to put a dataset into dsInactive state, set its Active property to false, or call the Close method for the dataset. The following statements are equivalent:

CustTable->Active = false;

CustTable->Close();

The remaining states (dsCalcFields, dsUpdateNew, dsUpdateOld, and dsFilter) cannot be set by your application. Instead, C++Builder sets them as necessary For example, dsCalcFields is set when a dataset’s OnCalcFields event is called. When the OnCalcFields event finishes, the dataset is restored to its previous state.

Note:	Whenever a dataset’s state changes, the OnStateChange event is called for any data source components associated with the dataset.

dsInactive

A dataset is inactive when it is closed. You cannot access records in a closed dataset. At design time a dataset is closed until you set its Active property to true. At run time, a dataset is closed until it is opened either by calling the Open method, or by setting the Active property to true. When you open an inactive dataset, C++Builder automatically puts it into dsBrowse state.

To make a dataset inactive, call its Close method. You can write BeforeClose and AfterClose event handlers that respond to the Close method for a dataset. For example, if a dataset is in dsEdit or dsInsert modes when an application calls Close, you should prompt the user to post pending changes or cancel them before closing the dataset. The following code illustrates such a handler:

void __fastcall CustForm::CustTableBeforeClose(TDataSet *DataSet)

{

bool posted;

if ((CustTable->State == dsEdit) || (CustTable->State == dsInsert))

if (::MessageBox(0, “Post changes before closing?”, ““, MB_YESNO) == IDYES)

{

CustTable->Post();

posted = true;

}

else

{

CustTable->Cancel();

posted = false;

}

}

To associate a procedure with the BeforeClose event for a dataset at design time:

1	Select the table in the data module (or form).

2	Click the Events page in the Object Inspector.

3	Enter the name of the procedure for the BeforeClose event (or choose it from the drop-down list).

dsBrowse

When an application opens a dataset, C++Builder automatically puts the dataset into dsBrowse state. Browsing enables you to view records in a dataset, but you cannot edit records or insert new records. You mainly use dsBrowse to scroll from record to record in a dataset.

From dsBrowse all other dataset states can be set. For example, calling the Insert or Append methods for a dataset changes its state from dsBrowse to dsInsert (note that other factors and dataset properties such as CanModify, may prevent this change). Calling SetKey to search for records puts a dataset in dsSetKey mode.

Two methods associated with all datasets can return a dataset to dsBrowse state. Cancel ends the current edit, insert, or search task, and always returns a dataset to dsBrowse state. Post attempts to write changes to the database, and if successful, also returns a dataset to dsBrowse state. If Post fails, the current state remains unchanged.

dsEdit

A dataset must be in dsEdit mode before an application can modify records. In your code you can use the Edit method to put a dataset into dsEdit mode if the read-only CanModify property for the dataset is true. CanModify is true if the database underlying a dataset permits read and write privileges.

On forms in your application, some data-aware controls can automatically put a dataset into dsEdit state if:

The control’s ReadOnly property is false (the default),

The AutoEdit property of the data source for the control is true, and

CanModify is true for the dataset.

Important

For TTable components only, if the ReadOnly property is true, CanModify is false, preventing editing of records.

Note:	Even if a dataset is in dsEdit state, editing records may not succeed for SQL-based databases if your application user does not have proper SQL access privileges.

You can return a dataset from dsEdit state to dsBrowse state in code by calling the Cancel, Post, or Delete methods. Cancel discards edits to the current field or record. Post attempts to write a modified record to the dataset, and if it succeeds, returns the dataset to dsBrowse. If Post cannot write changes, the dataset remains in dsEdit state. Delete attempts to remove the current record from the dataset, and if it succeeds, returns the dataset to dsBrowse state. If Delete fails, the dataset remains in dsEdit state.

Data-aware controls for which editing is enabled automatically call Post when a user executes any action that changes the current record (such as moving to a different record in a grid) or that causes the control to lose focus (such as moving to a different control on the form).

dsInsert

A dataset must be in dsInsert mode before an application can add new records. In your code you can use the Insert or Append methods to put a dataset into dsInsert mode if the read-only CanModify property for the dataset is true. CanModify is true if the database underlying a dataset permits read and write privileges.

On forms in your application, the data-aware grid and navigator controls can put a dataset into dsInsert state if

The control’s ReadOnly property is false (the default),

The AutoEdit property of the data source for the control is true, and

CanModify is true for the dataset.

Important

For TTable components only, if the ReadOnly property is true, CanModify is false, preventing editing of records.

Note

Even if a dataset is in dsInsert state, inserting records may not succeed for SQL-based databases if your application user does not have proper SQL access privileges.

You can return a dataset from dsInsert state to dsBrowse state in code by calling the Cancel, Post, or Delete methods. Delete and Cancel discard the new record. Post attempts to write the new record to the dataset, and if it succeeds, returns the dataset to dsBrowse. If Post cannot write the record, the dataset remains in dsInsert state.

Data-aware controls for which inserting is enabled automatically call Post when a user executes any action that changes the current record (such as moving to a different record in a grid).

dsSetKey

dsSetKey mode applies only to TTable components. To search for records in a TTable dataset, the dataset must be in dsSetKey mode. You put a dataset into dsSetKey mode with the SetKey method at run time. The GotoKey method, which carries out the actual search, returns the dataset to dsBrowse state upon completion of the search.

Note:	Other search methods, including FindKey, and FindNearest automatically put a dataset into dsSetKey state during a search, and return the dataset to dsBrowse state upon completion of the search.

dsCalcFields

C++Builder puts a dataset into dsCalcFields mode whenever an application calls the dataset’s OnCalcFields event handler. This state prevents modifications or additions to the records in a dataset except for the calculated fields the handler modifies itself. The reason all other modifications are prevented is because OnCalcFields uses the values in other fields to derive values for calculated fields. Changes to those other fields might otherwise invalidate the values assigned to calculated fields.

When the OnCalcFields handler finishes, the dataset is returned to dsBrowse state.

dsFilter

C++Builder puts a dataset into dsFilter mode whenever an application calls the dataset’s OnFilterRecord event handler. This state prevents modifications or additions to the records in a dataset during the filtering process so that the filter request is not invalidated.

When the OnFilterRecord handler finishes, the dataset is returned to dsBrowse state.


Using the Fields editor

The Fields editor enables you to create, delete, arrange, and define persistent field components associated with a dataset. Persistent field components guarantee your application receives a consistent view of the data underlying a dataset.

To start the Fields editor:

Double-click the dataset component, or

n	Select the component, right-click to invoke the SpeedMenu, then choose Fields editor.

The Fields editor contains a title bar, navigator buttons, and a list box.

The title bar of the Fields editor displays both the name of the data module or form containing the dataset, and the name of the dataset itself. So, if you open the Customers dataset in the CustomerData data module, the title bar displays ‘CustomerData.Customers’, or as much of the name as fits.

Below the title bar is a set of navigation buttons that enable you to scroll one-by-one through the records in an active dataset at design time, and to jump to the first or last record. The navigation buttons are dimmed if the dataset is not active or if the dataset is empty.

The list box displays the names of persistent field components for the dataset. The first time you invoke the Fields editor for a new dataset, the list is empty because the field components for the dataset are dynamic, not persistent. If you invoke the Fields editor for a dataset that already has persistent field components, you see the field component names in the list box.

Creating persistent field components

To create a persistent field component for a dataset:

Right-click the Fields editor list box.

Choose Add fields from the context menu. The Add Fields dialog box appears.

The Available fields list box displays all fields in the dataset which do not have persistent field components. Select the fields for which to create persistent field components, and click OK.

The Add Fields dialog box closes, and the fields you selected appear in the Fields editor list box. Fields in the Fields editor list box are persistent. If the dataset is active, note, too, that the Next and Last navigation buttons above the list box are enabled.

From now on, each time you open the dataset, C++Builder no longer creates dynamic field components for every column in the underlying database. Instead it only creates persistent components for the fields you specified.

Each time you open the dataset, C++Builder verifies that each non-calculated persistent field exists or can be created from data in the database. If it cannot, C++Builder raises an exception warning you that the field is not valid, and does not open the dataset.

Deleting persistent field components

Deleting a persistent field component is useful for accessing a subset of available columns in a table, and for defining your own persistent fields to replace a column in a table.To remove one or more persistent field components for a dataset:

1	Select the field(s) to remove in the Fields editor list box.

2	Press Del.

Note:	You can also delete selected fields by invoking the context menu and choosing Delete.

Fields you remove are no longer available to the dataset and cannot be displayed by data-aware controls. You can always re-create persistent field components that you delete by accident, but any changes previously made to its properties or events is lost.

Note:	If you remove all persistent field components for a dataset, then C++Builder again generates dynamic field components for every column in the database table underlying the dataset.

Arranging the order of persistent field components

The order in which persistent field components are listed in the Fields editor list box is the default order in which the fields appear in a data-aware grid component. You can change field order by dragging and dropping fields in the list box.

To change the order of a single field:

1	Select the field.

2	Drag it to a new location.

Alternatively, you can select the field, and use Ctrl-Up and Ctrl-Dn to move the field to a new location in the list.

To change the order for a block of fields:

1	Select the fields.

2	Drag them to their new location.

If you select a non-contiguous set of fields and drag them to a new location, they are inserted as a contiguous block. Within the block the order of fields to one another does not change.

Defining new persistent field components

Besides selecting which dynamic field components to make into persistent field components for a dataset, you can also create new persistent fields as additions to or replacements of the other persistent fields in a dataset. There are three types of persistent fields you can create:

Data fields, which usually replace existing fields (for example to change the data type of a field), are based on columns in the table or query underlying a dataset.

Calculated fields, which displays values calculated at run time by a dataset’s OnCalcFields event handler.

Lookup fields, which retrieve values from a specified dataset at run time based on search criteria you specify.

These types of persistent fields are only for display purposes. The data they contain at run time are not retained either because they already exist elsewhere in your database, or because they are temporary. The physical structure of the table and data underlying the dataset is not changed in any way.

To create a new persistent field component:

1	Right-click the Fields editor list box.

2	Choose New field from the SpeedMenu.

The New Field dialog box appears.

Dialog box options

New Field dialog box

The New Field dialog box contains three group boxes: Field properties, Field type, and Lookup definition.

Field type radio group

The Field type radio group box enables you to specify the type of new field component to create. The default type the first time you open the New Field dialog box in a session is Data. If you choose Lookup, the Dataset and Source Fields edit boxes in the Lookup definition group box are enabled. The Lookup definition group box is only used to create lookup fields.

Field properties group box

The Field properties group box enables you to enter general field component information. Enter the component’s field name in the Name edit box. The name you enter here corresponds to the field component’s FieldName property. C++Builder uses this name to build a component name in the Component edit box. The name that appears in the Component edit box corresponds to the field component’s Name property and is only provided for informational purposes (Name contains the identifier by which you refer to the field component in your source code). C++Builder discards anything you enter directly in the Component edit box.

Type combo box

The Type combo box in the Field properties group enables you to specify the field component’s data type. You must supply a data type for any new field component you create. For example, to display floating point currency values in a field, select Currency from the drop-down list. The Size edit box enables you to specify the maximum number of characters that can be displayed or entered in a string-based field or the size of Bytes and VarBytes fields. For all other data types, Size is meaningless.


Defining a data field

A data field replaces an existing field in a dataset. For example, for programmatic reasons you might want to replace a TSmallIntField with a TIntegerField. Because you cannot change a field’s data type directly, you must define a new field to replace it.

Important

Even though you define a new field to replace an existing field, the field you define must derive its data values from an existing column in a table underlying a dataset.

To create a data field in theNew Field dialog box:

1	Enter the name of an existing persistent field in the Name edit box. Do not enter a new field name.

2	Choose a new data type for the field from the Type combo box. The data type you choose should be different from the data type of the field you are replacing.

3	Enter the size of the field in the Size edit box, if appropriate. Size is only relevant for fields of type TStringField, TBytesField, and TVarBytesField.

4	Select Data in the Field type radio group if it is not already selected.

5	Choose OK. The New Field dialog box closes, the newly defined data field replaces the existing field you specified in Step 1, and the component declaration in the data module or form’s type declaration is updated.

To edit the properties or events associated with the field component, select the component name in the Field editor list box, then edit its properties or events with the Object Inspector.


Defining and programming a calculated field

A calculated field displays values calculated at run time by a dataset’s OnCalcFields event handler. For example, you might create a string field that displays concatenated values from other fields.

To create a calculated field in the New Field dialog box:

1	Enter a name for the calculated field in the Name edit box. Do not enter the name of an existing field.

2	Choose a data type for the field from the Type combo box.

3	Enter the size of the field in the Size edit box, if appropriate. Size is only relevant for fields of type TStringField, TBytesField, and TVarBytesField.

4	Select Calculated in the Field type radio group.

5	Choose OK. The newly defined calculated field is automatically added to end of the list of persistent fields in the Field editor list box, and the component declaration is automatically added to the form’s class declaration in the source code.

6	Place code that calculates values for the field in the OnCalcFields event handler for the dataset. For more information about writing code to calculate field values, see "Programming a calculated field," below.

To edit the properties or events associated with the field component, select the component name in the Field editor list box, then edit its properties or events with the Object Inspector.

Programming a calculated field

After you define a calculated field, you must write code to calculate its value. Otherwise it always has a null value. Code for a calculated field is placed in the OnCalcFields event for its dataset.

To program a value for a calculated field:

1	Select the dataset component from the Object Inspector drop-down list.

2	Choose the Object Inspector Events page.

3	Double-click the OnCalcFields property to bring up or create a CalcFields procedure for the dataset component.

4	Write the code that sets the values and other properties of the calculated field as desired.

For example, suppose you have created a CityStateZip calculated field for the Customers table on the CustomerData data module. CityStateZip should display a company’s city, state, and zip code on a single line in a data-aware control.

To add code to the CalcFields procedure for the Customers table, select the Customers table from the Object Inspector drop-down list, switch to the Events page, and double-click the OnCalcFields property.

The TCustomerData.CustomersCalcFields procedure appears in the unit’s source code window. Add the following code to the procedure to calculate the field:

CustomersCityStateZip->Value = CustomersCity->Value + “, “ + CustomersState->Value + “  “ + CustomersZip->Value;


Defining a lookup field

A lookup field displays values it searches for at run time based on search criteria. In its simplest form, a lookup field is passed the name of a field to search, a field value to search for, and the field in the lookup dataset whose value it should display.

For example, consider a mail-order application that enables an operator use a lookup field to determine automatically the city and state that correspond to a zip code a customer provides. In that case, the column to search on might be called ZipTable.Zip, the value to search for is the customer’s zip code as entered in Order.CustZip, and the values to return would be those in the ZipTable.City and ZipTable.State columns for the record where ZipTable.Zip matches the current value in the Order.CustZip field.

To create a lookup field in the New Field dialog box:

1	Enter a name for the lookup field in the Name edit box. Do not enter the name of an existing field.

2	Choose a data type for the field from the Type combo box.

3	Enter the size of the field in the Size edit box, if appropriate. Size is only relevant for fields of type TStringField, TBytesField, and TVarBytesField.

4	Select Lookup in the Field type radio group. Selecting Lookup enables the Dataset and Key Fields combo boxes.

5	Choose from the Dataset combo box drop-down list the dataset in which to look up field values. The lookup dataset must be different from the dataset for the field component itself, or a circular reference exception is raised at run time. Specifying a lookup dataset enables the Lookup Keys and Result Field combo boxes.

6	Choose from the Key Fields drop-down list a field in the current dataset for which to match values. To match more than one field, enter field names directly instead of choosing from the drop-down list. Separate multiple field names with semicolons.

7	Choose from the Lookup Keys drop-down list a field in the lookup dataset to match against the Source Fields field you specified in step 6. To specify more than one field, enter field names directly instead. Separate multiple field names with semicolons.

8	Choose from the Result Field drop-down list a field in the lookup dataset to return as the value of the lookup field you are creating. To return values from more than one field in the lookup dataset, enter field names directly instead. Separate multiple field names with semicolons.


Creating Data Dictionary attribute sets for field components

When several fields in the datasets used by your application share common formatting properties (such as Alignment, DisplayWidth, DisplayFormat, EditFormat, MaxValue, MinValue, and so on), it is more convenient to set the properties for a single field, then store those properties as an attribute set in the Data Dictionary. Attribute sets stored in the data dictionary can be easily applied to other fields.

To create an attribute set based on a field component in a dataset:

1	Double-click the dataset to invoke the Fields editor.

2	Select the field for which to set properties.

3	Set the desired properties for the field in the Object Inspector.

4	Right-click the Fields editor list box to invoke the context menu.

5	Choose Save Attributes to save the current field’s property settings as an attribute set in the Data Dictionary.

The name for the attribute set defaults to the name of the current field. You can specify a different name for the attribute set by choosing Save attributes as instead of Save attributes from the context menu.

Note:	You can also create attribute sets directly from the SQL/Database Explorer. When you create an attribute set from the data dictionary, the set is not applied to any fields, but you can specify two additional attributes in the set: a field type (such as TFloatField, TStringField, and so on) and a data-aware control (such as TDBEdit, TDBCheckBox, and so on) that is automatically placed on a form when a field based on the attribute set is dragged onto the form.


Associating Data Dictionary attribute sets with field components

When several fields in the datasets used by your application share common formatting properties (such as Alignment, DisplayWidth, DisplayFormat, EditFormat, MaxValue, MinValue, and so on), and you have saved those property settings as attribute sets in the Data Dictionary, you can easily apply the attribute sets to fields without having to recreate the settings manually for each field. In addition, if you later change the attribute settings in the Data Dictionary, those changes are automatically applied to every field associated with the set the next time field components are added to the dataset.

To apply an attribute set to a field component:

1	Double-click the dataset to invoke the Fields editor.

2	Select the field for which to apply an attribute set.

3	Right-click the Fields editor list box to invoke the context menu.

4	Choose Associate attributes.

5	Select or enter the attribute set to apply from the Attribute set name dialog box. If there is an attribute set in the Data Dictionary that has the same name as the current field, that set name appears in the edit box.


Unassociating a Data Dictionary attribute set from a field component

If you change your mind about associating an attribute set with a field component, you can easily remove the attribute set from the field component:

1	Double-click the dataset to invoke the Fields editor.

2	Select the field for which to remove an attribute set.

3	Right-click the Fields editor list box to invoke the SpeedMenu.

4	Choose Unassociate attributes.

After you remove an attribute set from a field component, you can either use the Object Inspector to set its properties, or you can associate a different attribute set with the component.


Accessing field values with the default dataset method

The preferred method for accessing a field’s value is to use variants with the default dataset method, FieldValues. For example, the following statement puts the value of an edit box into the CustNo field in the Customers table:

Customers->FieldByName(“CustNo”)->AsString = Edit2->Text;

Because FieldValues is the default method for a dataset, you do not need to specify its method name explicitly. The following statement, however, is identical to the previous one:

Customers->FieldValues[“CustNo”] = Edit2->Text;

For more information about variants, see the C++ Language Reference.


Accessing field values with the Fields property

You can access the value of a field with the Fields property of the dataset component to which the field belongs. Accessing field values with a dataset’s Fields property is useful when you need to iterate over a number of columns, or if your application works with tables that are not available to you at design time.

To use the Fields property you must know the order of and data types of fields in the dataset. You use an ordinal number to specify the field to access. The first field in a dataset is numbered 0. Field values must be converted as appropriate using the field component’s conversion routine. For more information about field component conversion functions, see "Using field component conversion functions."

For example, the following statement assigns the current value of the seventh column (Country) in the Customers table to an edit control:

Edit1->Text = CustTable->Fields[6]->AsString;

Conversely, you can assign a value to a field by setting the Fields property of the dataset to the desired field. For example:

{

Customers->Edit();

Customers.Fields[6]->AsString = Edit1->Text;

Customers->Post();

}


Accessing field values with the FieldByName method

You can also access the value of a field with a dataset’s FieldByName method. This method is useful when you know the name of the field you want to access, but do not have access to the underlying table at design time.

To use FieldByName, you must know the dataset and name of the field you want to access. You pass the field’s name as an argument to the method. To access or change the field’s value, convert the result with the appropriate field component conversion function, such as AsString or AsInteger. For example, the following statement assigns the value of the CustNo field in the Customers dataset to an edit control:

Edit2->Text = Customers->FieldByName(“CustNo”)->AsString;

Conversely, you can assign a value to a field:

{

Customers->Edit();

Customers->FieldByName(“CustNo”)->AsString = Edit2->Text;

Customers->Post();

}


Using conversion functions

Conversion functions attempt to convert one data type to another. For example, the AsString function converts numeric and Boolean values to string representations. The following table lists field component conversion functions, and which functions are recommended for field components by field-component type:

Function	T S t r i n g F i e l d	T I n t e g e r F i e l d	T S m a l l i n t F i e l d	T W o r d F i e l d	T F l o a t F i e l d	T C u r r e n c y F i e l d	T B C D F i e l d	T D a t e T i m e F i e l d	T D a t e F i e l d	T T i m e F i e l d	T B o o l e a n Fi e l d	T B y t e s F i e l d	T V a r B y t e s F i e l d	T B l o b F i e l d	T M e m o F i e l d	T G r a p h i c F i e l d
AsVariant
AsString
AsInteger
AsFloat
AsCurrency
AsDateTime
AsBoolean
Note that the AsVariant method is recommended to translate among all data types. When in doubt, use AsVariant.

In some cases, conversions are not always possible. For example, AsDateTime can be used to convert a string to a date, time, or datetime format only if the string value is in a recognizable datetime format. A failed conversion attempt raises an exception.

In some other cases, conversion is possible, but the results of the conversion are not always intuitive. For example, what does it mean to convert a TDateTimeField value into a float format? AsFloat converts the date portion of the field to the number of days since 12/31/1899, and it converts the time portion of the field to a fraction of 24 hours. The following table lists permissible conversions that produce special results:

Conversion	Result
String to Boolean	Converts "true," "false," "Yes," and "No" to Boolean. Other values raise exceptions.
Float to Integer	Rounds float value to nearest integer value.
DateTime to Float	Converts date to number of days since 12/31/1899, time to a fraction of 24 hours.
Boolean to String	Converts any Boolean value to "true" or "false."
In other cases, conversions are not possible at all. In these cases, attempting a conversion also raises an exception.

You use a conversion function as you would use any method belonging to a component: append the function name to the end of the component name wherever it occurs in an assignment statement. Conversion always occurs before an actual assignment is made. For example, the following statement converts the value of CustomersCustNo to a string and assigns the string to the text of an edit control:

Edit1->Text = CustomersCustNo->AsString;

Conversely, the next statement assigns the text of an edit control to the CustomersCustNo field as an integer:

CustomersCustNo->AsInteger = StrToInt(Edit1->Text);

An exception occurs if an unsupported conversion is performed at run time.


Deploying an application

To make an application available to end users, you deploy it. Deployment involves preparing a package for your end users that enables them to install and run your application and access data with it. C++Builder ships with InstallShield Express, a product that helps you deploy your applications.

A deployment package usually includes your application executable file, and support files, such as DLLs, and data files. Particularly when you are upsizing an application to use a remote server, there are additional files and DLLs you need to deploy. For example, to access remote database servers, your application must use the appropriate Borland SQL Links drivers and support files.

Before you deploy any application you build with C++Builder, see the online text file DEPLOY.TXT for the latest information about deployment and licensing.